CIS Course Schema, Part 3: Functional Dependencies¶
In the previous two projects you’ve gathered and refined the data on CIS courses taught this semester. In this one you will state the functional dependencies in your data and use them to validate your choice of keys.
In your previous projects you collected a bunch of attributes regarding courses, sections and instructors from the catalog and directory. You’ve made choices about what attributes are relevant in your schema. For this project you should have at least the attributes listed here. You are free to add as many others as you like:
Instructor Phone Number
As mentioned in class there’s a way to describe a table in documentation. In this assignment you are required to use the format. As a reminder a table is described like this:
TableName ( key1, key2, attr1, attr2, fk1, fk2 )
For example, if you had a
Section table that looked like this:
|1||828||Th 5:30-9:30||cis-54||Mike Matera|
Instructor columns are foreign keys your table definition looks like this:
Section ( SectionNumber, Room Time, Course, Title )
Finding Functional Dependencies¶
Start by listing all of the attributes that you track in your schema. Once you have them listed write out the functional dependencies in the “arrow” form shown in class. If you use surrogate keys include them. Watch out for incorrect FDs that don’t use a surrogate key. For example, this FD is correct:
InstructorID → ( InstructorName, InstructorPhone, InstructorTitle )
This FD is incorrect because your schema won’t support two “John Smiths”:
InstructorName → ( InstructorPhone, InstructorTitle )
List your FDs clearly and verify that every attributes is represented in an FD. Watch out for multivalued dependencies there are some in there!
Rebuild your Schema¶
With FDs in hand describe your schema using the table syntax above. Also, update your schema in SQL. Once your updates are complete export your schema and submit the written part of the assignment.
Please turn in the following:
A document with your FDs and Tables called
cis_fds.*(It can’t be text because you need underlines and italics.)
You exported database called