Assignment 1 – ER Modeling

Assignment #1 (Individual) CS605-GC1 – Database Management, Summer 2020
Page 1 of 3
Assignment 1 – ER Modeling
This assignment is based on Chapters 1-3 of the textbook. Review the in-class handouts and
exercises for background.
Solutions to problems should be submitted electronically through Blackboard and are due at the
end of the day on Friday, June 12th.
Part 1. (Worth 30 points)
A shuttle bus company records the details of the operation of their bus routes. The following ERD
illustrates the conceptual model.
Answer the questions on the next page based on the diagram below.
Assignment #1 (Individual) CS605-GC1 – Database Management, Summer 2020
Page 2 of 3
1) Based on the diagram, is it possible for a bus route be operated by more than a single driver?
Justify your answer.
2) According to this diagram, is it possible for two different bus routes to have the same Route
Name? Justify your answer.
3) Some drivers hold drivers licenses for more than one state. However, the presented model
does not allow recording more than one license per driver. Make the necessary changes in the
ERD to allow recording all licenses for a driver. Note that different states can issue licenses
under the same license number.
4) The company has decided to implement three schedules for each route: one for weekdays,
one for Saturdays and one for Sundays and holidays. Explain why the presented conceptual
design does not allow three different schedules to be stored for the same bus Route Number.
5) Modify the diagram to allow for three schedules (as described in question 4) to be recorded
for the same Route Number. The type of schedule (weekday, Saturday or Sunday) should
also be recorded.
For question 5, it is allowed to show only the changed section of the diagram(s), if you wish.
Part 2 (worth 70 points)
Develop data models for the situations described below, using some drawing tool such as
PowerPoint, SmartDraw, or DrawIO (or another tool of your choice – but NOT by hand).
Clearly state (in writing on a separate page) any assumptions that you have made regarding
details that you find missing in the situation descriptions. Also, for each diagram, write down
descriptions of any constraints and any special conditions that are not represented.
Your work must be neat and legible (it is quite possible that you will need to redraw your first
draft of the assignment to meet this requirement). Even though you are submitting only a
digital version, each (entire) diagram should fit on one sheet of standard size (8.5″ x 11″)
page. Again, be sure to have your name on each page of your submission.
For an A-level answer, your diagram should provide complete cardinality labels that
describe both the minimum and maximum cardinality of each (and every!) relationship.
For each entity, you must indicate the identifier (and/or partial identifier), as well as all
other attributes. Composite and multivalue attributes should be designated
appropriately. Any business rule that cannot be captured in the diagrams should be
listed as additional information separate from the data model diagrams.
Assignment #1 (Individual) CS605-GC1 – Database Management, Summer 2020
Page 3 of 3
The NCAA (National Collegiate Athletic Association) wants to develop a database to keep
track of information about college basketball. Each university team belongs to only one
conference (e.g., the University of Oklahoma belongs to the Big Twelve Conference; the
University of Alabama belongs to the Southeast Conference, etc.), but a few teams may not
belong to any conference. A conference has several teams; no conference has less than five (5)
teams. Each team can have a minimum of thirteen players and a maximum of twenty (20)
players. Each player can play for only one team. Each team has from three (3) to seven (7)
coaches on its coaching staff, and a coach works for only one team. Lots of games are played
in each university location each year, but a game between any two universities is played at a
given location only one time a year. Three referees from a larger pool of referees are assigned
to each game. A referee can work several games; however some referees may not be assigned
to any game. Players are called players because they play in games – in fact in 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 final score, the attendance, and the date
of the game. During the summer months, some of the players serve as counselors in summer
youth basketball camps. These camps each have unique campsite locations (e.g., Kenwood,
Mason, Bellaire, Waltham, etc.). Each camp has at least three (3) players who serve as
counselors, and a player serving as a counselor may work in a number of camps.
Each player has a unique student number (i.e., Social Security number). Other attributes for a
player include name, major, and grade point average. For a coach, relevant attributes include
name, title (e.g., head coach, assistant coach), salary, address, and telephone number. Attributes
for a referee include name, salary, years of experience, address, telephone number, and
certifications. Both coaches and referees are identified by their personal NCAA identification
number. 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. Each conference has a
unique name, number of teams, and an annual budget. For the basketball camps, data is available
on the campsite (i.e., location) and the number of courts.
Finally, consider both coaches and referees as basketball professionals (each having chosen
different careers within this profession. Hint: no more than eight entity types are needed to
complete the model.
Draw an EER Diagram for the conceptual data model for the NCAA database.
To submit: Upload PDF printouts of (1) all answers to Part 1 questions, and (2) your 1-page
diagram for Part 2 (with any notes/comments on a second page) to Blackboard by end of day on
Friday, June 12.


Buy plagiarism free, original and professional custom paper online now at a cheaper price. Submit your order proudly with us



Essay Hope