Database Systems 6th Edition by Ramez Elmasri, Shamkant B. Navathe

Exercise 4

Suppose we have the following requirements for a university database that is used to keep track of students' transcripts:
(a) The university keeps track of each student's name (SNAME), student number
(SNUM), social security number (SSSN), current address (SCADDR) and phone
(SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate
(BDATE), sex (SEX), class (CLASS) (freshman, sophomore, ..., graduate),
major department (MAJORDEPTCODE), minor department (MINORDEPTCODE)
(if any), and degree program (PROG) (B.A., B.S., ..., Ph.D.). Both ssn and
student number have unique values for each student.
(b) Each department is described by a name (DEPTNAME), department code
(DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and
college (DEPTCOLLEGE). Both name and code have unique values for each
department.
(c) Each course has a course name (CNAME), description (CDESC), code number
(CNUM), number of semester hours (CREDIT), level (LEVEL), and offering
department (CDEPT). The value of code number is unique for each course.
(d) Each section has an instructor (INSTUCTORNAME), semester (SEMESTER), year
(YEAR), course (SECCOURSE), and section number (SECNUM). Section numbers
distinguish 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 record refers to a student (Ssn), refers to a particular section, and
grade (GRADE).
Design an relational database schema for this database application. First show all
the functional dependencies that should hold among the attributes. Then, design
relation schemas for the database that are each in 3NF or BCNF. Specify the key
attributes of each relation. Note any unspecified requirements, and make
appropriate assumptions to make the specification complete.