IS 341: Project 3 Solution
30 HW points, 10 PDP points

Homework

• Do you see any potential data redundancy here? If so, give 1 or 2 examples.

You need to store a student's first and last name for every course they take and you need to store a course's title and semester hours for every student that takes it. For example, if a Jose is taking 4 courses this semester, then his first and last name must be stored 4 times (when once would be enough). Similarly, the name of IS 341 ("database administration") would have to be stored 5 times this semester because there are 5 students enrolled in the course (and again, once would be enough).

• What are the functional dependencies? List them in the form A -> B, C, meaning A functionally determines B and C.

studentID -> firstName, lastName
courseID -> courseTitle, semesterHours
studentID, courseID, semester -> letterGrade

Paraphrases: If you give the registrar a student's ID, then they can tell you the student's first and last name. Giving them the course ID will allow them to tell you the course title and semester hours (e.g., IS 341 is always called "database administration" and always has 3 hours). Giving them a student's ID, a course ID and the appropriate semester will allow them to tell you the student's grade. Note here, that a student may retake a course several times - Calvin allows this, but only the grade of the most recent course is used in GPA computation.

Note that sectionLetter was a bit tricky here, so I didn't worry too much about how you handled it.

• Is this schema in BCNF? If so, explain why, if not, decompose it so that it is.

The key of the relation is:

{studentID, courseID, semester} 

The relation is not in BCNF because there exists at least one functional dependency whose left hand side is not a key (i.e., the first two FDs listed above have left-hand sides that are not keys).

A reasonable decomposition of the given relation is as follows:

Students(studentID, firstName, lastName)
Courses(courseID, courseTitle, sectionLetter, semesterHours)
StudentGrades(studentID, courseID, semester,  letterGrade)

Note here that we have a separate relation for each of the functional dependencies given above, and that the FDs in each of these relations have keys on their left hand side. For example, the new Students relation has the FD studentID -> firstName, lastName, and the key studentID. The same is true for each of the 3 new relations.

Personal Database Project

As usual, I accepted just about everything here, so long as you built something that made sense and did it properly.