Representing Entity-Relationship Models with Functional Dependencies

---

Entity-Relationship, or ER, models are a popular way to represent a data design. The following E-R diagram shows a simple course-student-professor scenario. The COURSE entity represents courses but not specific offerings. Course offerings are represented by a SECTION weak entity. Students enroll in specific course sections and obtain a grade as a result. Professors are assigned to teach specific course sections.

This ER model can be represented with the following functional dependencies.

CourseNo->Title,Description,C_Department

StudentNo->S_Email,S_Name,Major

EmpNo->P_Email,P_Name,P_Department, Office

SectionNo,CourseNo->Location,Timetable

SectionNo,StudentNo->Grade

SectionNo,EmpNo->$temp1

 

Notice a temporary attribute is used to represent any entity or relationship that is, in effect, all key. For example the N:N TEACHES relationship could be represented by a functional dependency:

SectionNo,EmpNo->SectionNo,EmpNo

but this would be a trivial dependency because the right hand attributes are a subset of the left hand attributes and the dependency would be removed when a minimal cover is computed. A simple trick is to add a temporary attribute that can be removed from the final design.

SectionNo,EmpNo->$temp1

(The database normalizer application will remove temporary non-primary-key attributes that contain a "$" in their name from the final normalized tables.)

In the above example a few attribute names are disambiguated using an entity name abreviation as a prefix. For example S_Email vs. P_Email. Also since there is only one relationship between any two entities the left hand attributes in the functional dependencies did not need to be disambiguated in this simple case.

For more complex situations a more formal naming convention could be used as shown below. The attribute name prefixes can then also be used to help generate SQL to represent the tables including extracting foreign key references.

C_CourseNo->C_Title,C_Description,C_Department

S_StudentNo->S_Email,S_Name,S_Major

P_EmpNo->P_Email,P_Name,P_Department, P_Office

SC_SectionNo,SC_C_CourseNo->SC_Location,SC_Timetable

E_SC_SectionNo,E_S_StudentNo->E_Grade

T_SC_SectionNo,T_P_EmpNo->T_$temp1

 

The naming convention above has enough detail to generate the following (SQLite) SQL. The column type information has been added (it was not interpolated from the attribute naming).

CREATE TABLE COURSE (
   CourseNo text PRIMARY KEY NOT NULL,
   Title text,
   Description text,
   Department text
)

CREATE TABLE STUDENT (
   StudentNo text PRIMARY KEY NOT NULL,
   Email text,
   Name text,
   Major text
)

CREATE TABLE PROFESSOR (
   EmpNo text PRIMARY KEY NOT NULL,
   Email text,
   Name text,
   Department text,
   Office int
)

CREATE TABLE SECTION (
   SectionNo text NOT NULL,
   CourseNo text NOT NULL,
   Location text,
   Timetable text,
   PRIMARY KEY (SectionNo,CourseNo),
   FOREIGN KEY (CourseNo) REFERENCES COURSE (CourseNo)
)

CREATE TABLE ENROLLED (
   SectionNo text NOT NULL,
   CourseNo text NOT NULL,
   StudentNo text NOT NULL,
   GRADE real,
   PRIMARY KEY (SectionNo,CourseNo,StudentNo),
   FOREIGN KEY (SectionNo,CourseNo) REFERENCES SECTION (SectionNo,CourseNo),
   FOREIGN KEY (StudentNo) REFERENCES STUDENT (StudentNo)
)

CREATE TABLE TEACH (
   SectionNo text NOT NULL,
   CourseNo text NOT NULL,
   EmpNo text NOT NULL,
   PRIMARY KEY (SectionNo,CourseNo,EmpNo),
   FOREIGN KEY (SectionNo,CourseNo) REFERENCES SECTION (SectionNo,CourseNo),
   FOREIGN KEY (EmpNo) REFERENCES PROFESSOR (EmpNo)
)