The project is divided into 3 parts: A) Steps 1-4 Due Date: July 14, 2008 B) Steps 5 -7 Due Date: July 21, 2008 C) Step 8 Due Date: July 31, 2008 As part of the project, you have to do the following: 1. create the E-R diagram 2. indicate the primary keys, candidate keys, weak entities if any, partial keys. Don't forget the cardinalities of relationships 3. turn the E-R diagram into tables 4. give the DDL statement to create the corresponding database. Also include UNIQUE, FOREIGN KEYS... and any other necessary tools to enforce IC. 5. State the normal form your database schema is in. If it is not in BCNF, please give appropriate reasons. 6. Create the database using your oracle account. 7. Add the data to the database. The data should be such that all the required queries return meaningful information. 8. Design and implement the queries as per the specifications 9. Design and implement GUI-based interface for queries to be entered. The queries can be the same as in step 7. You will have to use the same data as in step 6/7. ============================================================================== 1) E-R design - country db Consider a country database for an expedition which keeps track of country names, capitals, continents, oceans and populations. o For a country, we record the unique name, the population, the capital, the one and only continent it resides in, and the one or more oceans it touches. o For a continent we record the name (we assume names are unique) and size. o For an ocean, we record the name(assume unique), the size, the one or more continents it borders, and the maximum known depth. We want to know which oceans touch what countries, which continent contains which countries, what the known deepest ocean is. Also, which continents have countries above a given population and which pairs of countries are in the same continent. ============================================================================== 2) E-R design – gaming site db Consider running an online games website which needs a database to keep track of users and games. Here we consider three types of games: board games, video games, and card games. o For each user, we record their screen name (we assume the name is unique) and their last used ip address. If the person is a paying member, we additionally record their address and method of payment. Each user is signed up for at least one game and has a rating for that game. o For each game, there is the name, the type, and the amount of people registered for the game. o When a board game is played, we record the screen names of people who played, the winner, the start time and duration of the game(we assume board games are always between at least 2 people and one can only play one board game at a time). o When a video game is played, we record the screen names of people who played, the start time and duration( assume video games are always played by two people and one can only play against one person at a time). o For card games, we record the screen names of people who played, the start time and duration(assume card games are always between 4 people and one can only play a card game with 3 other people at a time to make one game). We also record who created the game and invited the others to join. o Obviously some user may have played no games at all, or just a subset of the types of games. o Users may play more than one type of game at a time, but not the same type at the same time. We want to compute the rating for board games for users using how many times they have won, friends of a user (that is those that have played a game against a particular user), and pairs of the friends-of-friends. We also want to know what games are popular and who’s playing what with whom. ============================================================================== 3) E-R design - boxing db Consider a boxing sport database which records boxing matches. We want to store information about the matches as well as information about the news/entertainment media that feature the match. o For each boxer, we record the unique boxer name and unique id, height, weight, class, and record. For a match, we record the ids of the two boxers, and one or more pieces of media on this match. Assume that the boxing match is always between 2 boxers. o For each media feature for the match, we record names of 1 or more authors or cast members (if it’s on video), the title, the date, and the media company. Assume that each author/cast member has a unique name, and each media feature is uniquely identified by the title, the date, and the media company. Assume that each media feature focuses on only one match. We only keep track of authors/cast members who have at least one media feature on a match. We want to know which authors/cast member report on what types of matches, which boxers are involved in which matches, and groups of media features all on the same match. ============================================================================== Dataset 1: The DBLP bibliography database The DBLP database stores information about various technical content published in conferences, workshops, etc. You have to create a DBLP database with the following requirements: o For each author, we record the unique author id and name, his or her affiliation o For each paper, we record a unique id, title of the paper, category the paper belongs to, and the authors The design should be such that queries like "List all the papers published by author A", "List the name and affiliation of all the co-authors of author X", etc. Create as many relationships/tables as are required.