Example Database

    The database example below shows the course-taking information of students in a university in a particular semester. This example is used to illustrate the characteristics of data dependencies including functional dependency (FD), inclusion dependency (IND), multivalued dependency (MVD) and join dependency (JD).

The Schema

-       student (sid, sname, sfullname, sprogram, sdept)
-       department (did, dname)
-       professor (pid, pname, pdept)
-       course (cid, cdescription, credit)
-       requirement (dept, course)
-       pattern (student, dept, course)
-       teach (course, cdescription, lecturer)
-       enrollment (student, course, lecturer)

　

The Semantics of the Example Database

-         sid in student denotes a unique number identifying a student; sname denotes the student’s name; sfullname denotes the student's full name, sprogram denotes the program that the student has registered. sdept denotes the department the student belongs to.

-         did in department denotes the department ID; dname denotes its name.

-         pid in professor denotes the professor’s unique identifying number; pname denotes his/her name; pdept denotes the did of his/her corresponding department

-         cid in course denotes a unique number identifying a course; cdescription denotes the description of a course; credit denotes the number of credit a course carries

-         requirement is the relation showing the required courses in a certain department. dept denotes the did of a department; course the cid of course required.

-         pattern is the relation showing what courses each student needs to take under the department he/she belongs to. student denotes the sid of a student, dept the did of the department he/she belongs to; course denotes the cid of course required

-         teach is the relation showing what course a professor teaches. course denotes the cid of a course, cdescription denotes the description of a course; lecturer denotes the pid of a professor that teaches this course.

-         enrollment is the relation showing a student takes a certain course (cid in course) which is taught by a lecturer (pid in professor).

　

The Relations

Student sid sname sfullname sprogram sdept 1001 stud_A stud_A Au BEng(COMP) 1 1002 stud_B Stud_B Lo BEng(COMP) 1 1003 stud_C stud_C Ko BEng(ELEC) 2 1004 stud_D stud_D Ng BEng(ELEC) 2			department 　 did dname 1 Computer Sci. 2 Electronic Eng.
professor pid pname pdept 1 prof_A 1 2 prof_B 1 3 prof_C 1 4 prof_D 2 5 prof_E 2 6 prof_F 2	course cid cdescription credit COMP104 C++ 5 COMP171 Algorithms 3 ELEC102 Electronics 5 ELEC151 Digital Circuit 5			requirement 　 dept course 1 COMP104 1 COMP171 2 ELEC102 2 ELEC151
pattern 　 student dept course 1001 1 COMP104 1001 1 COMP171 1002 1 COMP104 1002 1 COMP171 1003 2 ELEC102 1003 2 ELEC151 1004 2 ELEC102 1004 2 ELEC151		teach course cdescription lecturer COMP104 C++ 1 COMP104 C++ 2 COMP171 Algorithms 3 ELEC102 Electronics 4 ELEC151 Digital Circuit 5 ELEC151 Digital Circuit 6
enrollment 　 student course lecturer 1001 COMP104 1 1001 COMP171 3 1002 COMP104 2 1002 COMP171 3 1003 ELEC102 4 1003 ELEC151 5 1004 ELEC102 4 1004 ELEC151 6
NOTE: 1.      In this example database, it is assumed that each student only needs to take the courses from their own department, which is illustrated in the relation requirement. 2.      Redundancy in some relations (such as pattern) is not optimized in order to illustrate any dependency characteristic in the later part.