Suppose we have the following requirements for a university database that is used to keep track of students’ transcripts:

Question

(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.

Answer 1

From the above description, we can presume that the following functional dependencies

hold on the attributes:

FD1: {SSSN} -> {SNAME, SNUM, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS,

MAJOR, MINOR, PROG}

FD2: {SNUM} -> {SNAME, SSSN, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS,

MAJOR, MINOR, PROG}

FD3: {DEPTNAME} -> {DEPTCODE, DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE}

FD4: {DEPTCODE} -> {DEPTNAME, DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE}

FD5: {CNUM} -> {CNAME, CDESC, CREDIT, LEVEL, CDEPT}

FD6: {SECCOURSE, SEMESTER, YEAR, SECNUM} -> {INSTRUCTORNAME}

FD7: {SECCOURSE, SEMESTER, YEAR, SECNUM, SSSN} -> {GRADE}

These are the basic FDs that we can define from the given requirements; using inference

rules IR1 to IR3, we can deduce many others. FD1 and FD2 refer to student attributes;

we can define a relation STUDENT and choose either SSSN or SNUM as its primary key.

Similarly, FD3 and FD4 refer to department attributes, with either DEPTNAME or

DEPTCODE as primary key. FD5 defines COURSE attributes, and FD6 SECTION attributes.

Finally, FD7 defines GRADES attributes. We can cre