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 notation showing 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.