Entry 1 level ERD 2 questions.
Business Applications of Database
Management Systems
Assignment 2
Questions 18 and 19 on textbook pages 135 to 140. The
assignment has requirements, guidance, and tips at the end of
this file.
Please answer your question using your laptop. If you draw
ERD on a paper and scan it, it won’t be accepted.
Note: You can answer questions 19(a) and 19(b) in one single
diagram.
18. The NCAA (National Collegiate Athletic Association) wants to develop a
database to keep track of information about college basketball. Each team can
have a maximum of twenty (20) players and a minimum of thirteen (13) players.
Each player can play for only one team. Players are called players because they
play in games, in fact, several games. A game involves at least ten players. It is
possible that some players simply sit on the bench and do not play in any game.
Player performance statistics (i.e., points scored, rebounds, assists, minutes
played, and personal fouls committed) are recorded for each player for every
game. Information collected about a game includes the game number, the final
score, the attendance, and the date of the game.
A player can be identified by student number (i.e., Social Security number) only.
The other attributes for a player include name, major, and grade point average. A
team is identified by the name of the university (i.e., team). Other team attributes
include current ranking, capacity of home court, and number of players. A game
can be identified by game number only.
Develop a Presentation Layer ER model for the NCAA database. The ERD should
be fully specified with the unique identifiers, other attributes for each entity
type, and the relationship types that exist among the various entity types. All
business rules that can be captured in the ERD must be present in the ERD. Any
business rule that cannot be captured in the ERD should be specified as part of a
list of semantic integrity constraints.
Hint: No more than three entity types are needed to complete this design.
19. This exercise contains additional information in the form of deletion rules that
will enable us to develop a Design-Specific ER diagram for the NCAA database in
Exercise 18.
If a team is canceled, Likewise, all links to the players for that team should be
dropped.
a. Incorporate the above business rules in the Presentation Layer ER diagram for
the NCAA database developed in the previous exercise.
b. Transform the design in (a) to a Design-Specific ER diagram.
Requirement for the Assignment:
When you upload your homework, please ensure that it falls under any of the file
types listed below:
Adobe Acrobat PDF (.pdf)
Microsoft Word (.doc)
If the TA has trouble understanding your answer, that will be treated as wrong.
Please read the following part carefully; it will help you finish your assignment
and answer questions correctly.
Guidance and Tips for Doing the Assignment:
18. Divide the entire question into smaller parts first, such as:
– the story of teams and players
– the story of players and games
Then, you can draw an ERD for each smaller part and eventually connect all of
them into one big ERD.
Ensure that the names, entities, and relationships in the ERD are clear and
accurately reflect their roles in the database.
There are no more than three entities.
• Review the provided business rules and constraints and incorporate them
into your ERD wherever possible. Ensure that your ERD reflects these rules
clearly. Please ensure the symbols for each attribute, entity, and
relationship type are correct. Use Chen’s notation/look-across approach for
your ERD.
• Reference the examples in Chapter 3 and the Bearcat example to help
answer this question.
• Don’t forget to label each entity’s unique identifier, and each entity’s
unique identifier should be a mandatory attribute.
19. Cascade deletion rule should be used. The design-specific model should use
the min-max notation/ look-near approach.
