Develop an entity-relationship diagram as a conceptual mode
For the following requirements analysis description, develop an Entity-Relationship Diagram as a conceptual model that captures all of the requirements to the fullest extent possible.
You are provided the following set of requirements for a university database that is used to keep track of students’ transcripts.
(a) The university keeps track of each student’s name, student number, social security number, current address and phone, permanent address and phone, birthdate, gender, class (freshman, sophomore, …, graduate), major department, minor department (if any), and degree program (B.A., B.S., …, Ph.D.). Some user applications need to refer to the city, state, and zip of the student’s permanent address, and to the student’s last name. Both social security number and student number have unique values for each student.
(b) Each department is described by a name, department code, office number, office phone, and college. Both name and code have unique values for each department.
(c) Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of course number is unique for
each course.
(d) Each section has an instructor, semester, year, course, and section number. The section number distinguishes different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, …; up to the number of sections taught during each semester.
(e) A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, 4 for F, D, C, B, A, respectively).
Given the above description, complete the following. You must use the Information Engineering format discussed in the course video and slides for the ER diagram notation. You may use either the data modeling tool in MySQL workbench, or a freeform drawing tool of your choice, but the notation must meet the specification for course ER diagrams.
Draw an ER-diagram for the database, identifying:
(a) all the entity sets;
(b) all the relationship sets and their multiplicity;
(c) the primary key for each entity set (and weak entity set, if any) and each relationship set.
Indicate (what and why) any feature(s) or property(ies) in the above description that are NOT captured by your ER-diagram.
