CS360: Lab 7

---

Due: See Moodle for the due date.

Goals:

· Create an ERD from a problem statement

· Create an initialization script that creates tables needed by the ERD

· Create stored procedures as the interface used to manipulate data in these tables

Background:

This lab defines a database for collecting basketball statistics.

The database must store data for a basketball league as follows:

• Name of each team – no two teams may have the same name
• Name of each player – no two players may have the same name
• Jersey number of each player (no two players on the same team can have the same number, but a player on one team may have the same number as a player on another team)
• Which team each player plays for. A player may play for only one team.
• Games between teams, including the following information for each game:
• All baskets attempted during the game, including:
1. player who attempted the basket
2. point value (0, 1, 2, or 3) for each basket made
• All fouls committed during the game, including: 

1. player who committed the foul (assume all fouls are committed by some particular player)

• Games have home and away teams and a date on which the game was played.  The database assumes teams never play each other on the same side (home or away), more than once on the same day.  So, team A and team B may play each other twice on the same day, but team A would have to be home for one game and away for the other.

NOTE: Items not listed in the ‘background’ list above do NOT have to be accounted for.

· The basketball example occurs at two different places in the notes, and both of them are slightly different from each other and different than what is shown in the list above.  The list above does not mention uniform, but it does mention player number, and it does mention basket.

Requirements:

1. Save lab7.sql.  You will modify this file and submit your modified version for this lab.

2. In lab7.sql, find the comment containing – ERD GOES HERE:.  Modify this comment as follows:

· Insert your own name in place of <Your Name>

· Write an ERD in Pete’s foot.  Show all entities and relationships, but not attributes.  Give each many to many relationship a name -- remember, many to many relationships produce new weak entitites -- e.g.,
Team = 0 ----  hosts / visits ---- 0 = Team  (the ‘Game’ relationship). 
Note: you can use new weak entities like ‘Game’ in other relationships.

· Below this set of relationships diagrams, show the attributes for each entity shown in the relationships in the form:

EntityName:

attributeName1

attributeName2

For example:

Game:

            home

            away

            date

3. Below this comment, write the SQL statements necessary to drop if exists then create the tables necessary for your ERD.

4. Modify the stored procedure bodies given at the bottom of lab7.sql according to the requirements given in the comment at the top of lab7.sql.  Note: you may NOT modify the stored procedure headers.

 5. Submit your lab7.sql file on moodle by clicking on the Lab 7 assignment.  NOTE: You can continue to add to your assignment as long as you click the Edit Submission button, but you must click the Submit Assignment button, then the ‘my own work’ checkbox, then the Continue button to finalize your submission.

Hints:

• Implement the stored procedures one at a time using the techniques shown in week08notes01, week08notes02 and test each one before going on to the next.
• Perform operations ‘by hand’ (outside of your stored procedures) to determine what errors you might encounter, so you can create handlers to catch and process these errors.
• Test your stored procedures with the call statement, passing appropriate test data and checking the results returned and the appropriate database tables.