Suppose that we have the following requirements for a university database that i

Question

Suppose that we have the following requirements for a university database that is used to keep track of students’ transcripts. For each section you can define a table that includes the attributes listed in that section. a. The university keeps track of each student’s name (Sname), student number (Snum), Social Security number (Ssn), current address (Sc_addr) and phone (Sc_phone), permanent address (Sp_addr) and phone (Sp_phone), birth date (Bdate), sex (Sex), class (Class) (‘freshman’, ‘sophomore’, ... , ‘graduate’), major department (Major_code), minor department (Minor_code) (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 (Dname), department code (Dcode), office number (Doffice), office phone (Dphone), and college (Dcollege). Both name and code have unique values for each department. c. Each course has a course name (Cname), description (Cdesc), course number (Cnum), number of semester hours (Credit), level (Level), and offering department (Cdept). The course number is unique for each course. d. Each section has an instructor (Iname), semester (Semester), year (Year), course (Sec_course), and section number (Sec_num). 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 total number of sections taught during each semester. e. A grade record refers to a student (Ssn), a particular section, and a grade (Grade). Show all the functional dependencies that should hold among the attributes.

Explanation / Answer

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 create one relation for each of STUDENT, DEPARTMENT, COURSE, SECTION, and GRADES as shown below, where the primary keys are underlined. The COURSE, SECTION, and GRADES relations are in 3NF and BCNF if no other dependencies exist. The STUDENT and DEPARTMENT relations are in 3NF and BCNF according to the general definition given in Sections 14.4 and 14.5, but not according to the definitions of Section 14.3 since both relations have secondary keys.

The foreign keys will be as follows: STUDENT.MAJOR -> DEPARTMENT.DEPTCODE STUDENT.MINOR -> DEPARTMENT.DEPTCODE COURSE.CDEPT -> DEPARTMENT.DEPTCODE SECTION.SECCOURSE -> COURSE.CNUM GRADES.(SECCOURSE, SEMESTER, YEAR, SECNUM) -> SECTION.(SECCOURSE, SEMESTER, YEAR, SECNUM) GRADES.SNUM -> STUDENT.SNUM Note: We arbitrarily chose SNUM over SSSN for primary key of STUDENT, and DEPTCODE over DEPTNAME for primary key of DEPARTMENT.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.