Volleyball League Database Design Assignment

User Generated

irrpb

Computer Science

Description

hello i need help with my project its simple and easy to implement. all the details are attached in files.

Unformatted Attachment Preview

Spring 2017 Database Design The purpose of this project is to give you some experience in database design. We will explore both theoretical and practical aspects of the database design steps. Once you master it well, it is a simple matter of translating the theoretical concepts you developed into the terms of a concrete DBMS to implement the database. In our case, you can implement the database system using Oracle or MySQL. Part I: Conceptual/Logical Database Design You must choose some facet of real life that you feel needs to be modeled by a database. Then, you must design a relational database schema that adequately represents this information, and demonstrate its efficacy by providing a set of useful queries that your database can support. In the process, you must use standard relational modeling tools and techniques we examined in class. In concrete terms, this part of the project consists of the following steps: (1) Begin by choosing an application, e.g. the database for a local volleyball league or the database for a Society of Roasted-Shrimp Fans. It must be non-trivial, but not huge. As a general guideline, you should probably have about seven to ten entity sets and similar number of relationship sets. (2) Formulate in English at least 10 realistic queries you believe would be useful to somebody using the database. The queries must be realistic and you should have more than few complex queries. Note, the queries that the database system must be able to answer ultimately determine what information needs to be maintained in the database. For example, you need not include the information about best friends of students if no user of your database will be asking for that. On the other hand, some additional information, not reflected in your 10 queries, may be included in the database if you feel that somebody may be interested in it. (3) Specify the assumptions about the database in English (informally). Here you talk about attributes, keys, the nature of relationships between entities, etc. Do not discuss something that is obvious (e.g., that a student can take several courses). In addition, don't make too many simplifying assumptions. (4) Give an ER diagram, which will reflect your choice of the entity sets, their relevant attributes, and the relationships among them. Again, make your relationships realistic and meaningful. (5) Convert the ER diagram into a relational database schema. (6) (Up to 5 extra points) For each relation schema obtained in Step(5), specify the functional dependencies satisfied by the schema. Based on the knowledge of functional dependencies, you can analyze how good your initial DB design is, i.e. the potential for repetition of information and other anomalies we examined in class. For each relation schema, specify the normal forms it satisfies. (7) (Up to 5 extra points) If necessary, perform decompositions of the relations, such that the entire database is in 3NF or BCNF. Note that if your initial design is non-trivial, then at least one table should be in 1Nfor 2NF, so that you can decompose it. (8) Formulate the queries in Step(2) using relational algebra. If, at this point, you are unable to state the queries in relational algebra, this is probably because your database is not powerful enough. Then, you must go back to step 3 and see how the database can be improved. (9) Of course, no database is capable of supporting all queries. Very often (in fact, it is almost always the case), databases are unable to support some reasonable ad hoc queries. I expect that your database will be one of them. Thus, to evaluate the database design properly, you must identify reasonable, but unanswerable queries as well, and explain why your database cannot support them. For the purposes of this project, it will be sufficient to state two queries of this kind in English. Part II: Report on Logical Database Design Good DBAs must be able to prepare a full design report. You should hand in the full, legible report (hopefully typeset) with a cover page that gives your name, the title of the project, and its brief description. The final report should include sufficient detail to describe all steps of your design: (1) Describe what you have chosen as your database. (2) List at least 10 reasonable queries in English. (3) Add one or more atomic transactions to your query set. (4) State assumptions about the database as briefly as possible. (5) Give the ER diagram of the database. (6) Give the relational database schema. (7) (Up to 5 extra points) For each relation schema, give the associated FDs and specify the normal forms the schema satisfies. (8) (Up to 5 extra points) Perform the decomposition into 3NF or BCNF and give the final database schema along with the FDs. (9) Give the relational algebra expressions for the queries formulated in step 2. (10) Give at least two reasonable, but unanswerable queries, and explain why the database cannot support them. (11) In conclusion, describe what you have learned from the project, and include your observations about the power of relational systems and languages. Part III: Implementation Your task now is to implement your database using Oracle or MySQL. (1) (2) (3) (4) (5) Create tables obtained in Step(7) of Part II. Insert 10 to 15 records into each table and print the contents of each (using SELECT * ). Compose SQL queries formulated in Step(8) of Part II; (Up to 5 extra points): Add one or more atomic transactions to your query set; Execute the queries and print the results.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Running Head: VOLLEYBALL LEAGUE

1

Volleyball League
Name
Institution

VOLLEYBALL LEAGUE

2

PART II
1. Local Volleyball League Database.
The entities are Teams, Players, Coaches, Results, Fixtures, Overall Standings and
Playoff Standings.

2. Queries in English
How many teams are there in the volleyball league and what are their names?
Who are the players in the respective teams in the volleyball league?
Who are the coaches of the respective teams in the volleyball league?
What are the results of the different matches played in a season?
What are the fixtures of matches in a given season?
What are the team positions in the overall standings in a league season?
Which teams have advanced to the playoffs in a league season?
Who are the winners of the title in a league season?
Which team has won the league title most number of times?
Which teams have advanced to final in a league season?

3. Atomic transaction.
Insert a new team into the teams table and commit the transaction.

4. Assumptions
Below are the attributes of the different entities and their keys.
Entities

Attributes

Teams

team_id (Primary Key), team name

Players

player_id (Primary Key), player_name,
team_id

Coaches

coach_id (Primary Key), coach_name,
team_id (Foreign Key)

Results

result_id

(Primary

(Foreign Key), result

Key),

fixture_id

...


Anonymous
Really useful study material!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags