A graphical tool used in database design. At the completion of this assignment students will be able to:
1. Identify the different components of an ERD
2. Recognize some business rules from the relationships contained in an ERD
3. Understand and use the 3 different type of relationship classifications 1:1, 1:M, M:N and their for the linking of tables in the RDM ((relational data model)
4. To develop relational models (RDM) where each table has a primary key (entity integrity) and some tables may have foreign keys (referential integrity)
5. Convert M:N relationships into associative entities
6. Convert the conceptual ER model , given a problem scenario, into a RDM with appropriate primary keys (PKs) and foreign keys (FKs) 7. Recognize possible alternate keys
Question 1
Design an ER diagram for a Wacky University student database.
1. List Entities
2. List Attributes
3. List Relationships and cardinalities
4. Draw ER Diagram
Show all attributes and include relationship and connectivity. Convert all M:N relationships into entities. You are to use Crow's Foot notationshowing all attributes for each entity and naming each relationship. Do not show M:N relationship, convert it into 1:M&N:1 relationship
The business rules for the ER model are as follows.
1. A university has many departments. Each department employs one or more lecturers (at least one), and each lecturer may work in many departments.
2. A lecturer teaches exactly one subject, and a subject is taught by at least one lecturer.
3. Each lecturer will only have one title i.e. Lecturer A, Lecturer B, Senior Lecturer, Associate Professor, Professor.
4. Students may study 0 subjects or only 1 subject, and a subject must have at least one student enrolled in it for the subject to run. Students will receive a Grade for the subject they are enrolled in.
5. Each student must have one or more academic advisors. An academic advisor is a lecturer. An advisor may advise zero or many students.
6. A lecturer may only supervise a maximum of one other lecturer. However a lecturer may be supervised by zero or many lecturers.
Attributes that need to be included in your ERD are: DepartNo, DepartName, LectNo, LectName,LectAddress, LectPhoneNo, LectTitle, SubCode, grade, SubName, StudNo, StudName, StudAddress, StudDOB
Question 2:
1. Write Business rules to create ERD as given in Question 1.
2. List Entities, Attributes and Relationships
3. Design an ER diagram for the problem scenario given below.
4. Convert your ERD into the relational data model (RDM) using the guidelines from as follows.
Table_Name( attribute_1, attribute_2, attribute_3, ..... ,attribute_x)
Primary Key ( attribute_1, attribute_2)
Foreign Key attribute_4 References Table_Name_2 (repeat for each foreign key)
Alternate Key (attribute_5, attribute_6) (repeat for each alternate key) Additionally primary keys should be underlined.
Qantas Airlines has asked you to create a database for their airline operations i.e. its flight and airplane history. Hopefully they are in a position to pay your database design fee.
The database requirements are as follows.
A flight is uniquely identified by the combination of a flight number and a date. In addition, every flight has an actual departure time and an actual arrival time. Every passenger who has flown on a Qantas flight has a unique passenger number plus their name, address, and telephone number. For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it. Clearly, a passenger may have taken many flights (he must have taken at least one to be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number, a name, date of birth, and date of hire. A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights but a pilot may be new to the company, is in training, and has not flown any flights, yet.
Each airplane has a unique serial number, a model, manufacturer name, passenger capacity, and year built. A flight on a particular date used one airplane. Each airplane has flown on many flights and dates, but a new airplane may not have been used at all, yet.
Qantas also wants to maintain data about its airplanes' maintenance history. A maintenance procedure has a unique procedure number, a procedure name, and the frequency with which it is to be performed on every airplane. A maintenance location has a unique location name, plus an address, telephone number, and manager. Qantas wants to keep track of which airplane had which maintenance procedure performed at which location. For each such event it wants to know the date of the event and the duration.