Description
In this assignment, you will create a relational data model for a university enrollment system.
Before starting, it would be helpful if you read more about Entity Relationship (ER) diagrams.
Following are some suggested links:
https://www.smartdraw.com/entity-relationship-diagram/
https://www.lucidchart.com/pages/er-diagrams
https://jackzheng.net/teaching/archive/cis3730-2010-fall/files/1.5-erd.pdf
A nice tool to draw ER diagrams online is: https://www.draw.io/. Another tool is ERDPlus –
https://erdplus.com/
The key entities in the university enrollment diagram would be:
1. Student having following properties:
Netid
First Name
Last Name
Major
IsGraduate (Boolean indicating whether they are a graduate student)
2. Course having following properties:
Course ID
Course Name
Department
Semester
Year
3. Professor having following properties:
Netid
First Name
Last Name
Rank
Department
4. Department having following properties:
Code
Name
Chairman
Following are details about the relationships:
– A student can enroll in many courses
– A professor can teach many courses
– A professor can advise many graduate students
– A department can have one professor as chairman
– You have to ensure that the schema is normalized (3rd Normal Form)
What to turn in:
– Entity Relationship diagram
– Relational model corresponding to the ER diagram.
An example of how to represent a relational model is:
CUSTOMER (CustomerID, Name, Address, Phone)
ORDER (OrderNumber, OrderDate, SalesPerson, CustomerID (fk))
ORDERITEMS (OrderNumber, ItemNumber, PartNumber, Quantity, Price)
– SQL code to create the tables above.