Description
1. The following are some of the relations transformed from the ER diagram for the Student
Registration System (note that some changes have been made due to the creation of a singleattribute key for Classes):
Students(sid, firstname, lastname, status, gpa, email)
Courses(dept_code, course#, title, credits, deptname)
Classes(classid, dept_code, course#, sect#, year, semester, start_time, end_time,
limit, size, classroom, capacity, fid) /* note: classid is added to serve as a single-attribute key */
Faculty(fid, firstname, lastname, rank, office, email, deptname)
Enrollments(sid, classid, lgrade, ngrade)
Do the following for each relation schema:
(a) (20 points) Identify all non-trivial functional dependencies. Don’t make unrealistic
assumptions about the data. Use the union rule to combine the functional dependencies as
much as possible. Furthermore, if a functional dependency is redundant (i.e., it can be
derived from the ones you keep), don’t include it.
(b) (20 points) Determine whether or not the schema is in 3NF or in BCNF. Justify your
conclusion.
(c) (20 points) For each schema that is not in 3NF, decompose it into 3NF schemas using
Algorithm LLJD-DPD-3NF. Show the result after each step of the algorithm. Are the
decomposed schemas in BCNF? Justify your answer.
2. (14 points) Prove or disprove the following rules:
(a) {B CD, AB E, E C} |= {AE CD}
(b) {B CD, AD E} |= {AB E}
When proving a rule, you can use all the six inference rules (i.e., reflexivity rule,
augmentation rule, transitivity rule, decomposition rule, union rule and pseudotransitivity
rule). To disprove a rule, construct a relation with appropriate attributes and tuples such that
the tuples of the relation satisfy the functional dependencies on the left of the rule but do not
satisfy the functional dependency on the right of the rule.
3. (26 points) Write relational algebra expressions to answer the following query
statements based on the tables given in page 3 of this document. For each query, show
the result that would be obtained if the query were actually executed against the tables. It is
OK to answer a query in multiple steps. You need to make sure that your relational algebra
expressions are reasonably optimized as we discussed in class, i.e., conditions involving a
single table should be specified on that table directly and Cartesian products should be
replaced by joins whenever possible.
2
(a) (6 points) Find the dept_code, course# and title of each course that was offered in the
Spring semester of 2014.
(b) (6 points) Find the first name of each student who has taken at least one CS course and at
least one Math course.
(c) (7 points) Find the dept_code and course# of each course that was not offered in 2013.
(d) (7 points) Find the sid and first name of every student who has taken all CS classes
offered in Spring 2014.
3
Tables for Homework 3 Question 3
Students
Sid Firstname Lastname Status GPA email
B001 Anne Broder junior 3.17 broder@bu.edu
B002 Terry Buttler senior 3.0 buttler@bu.edu
B003 Tracy Wang senior 4.0 wang@bu.edu
B004 Barbara Callan junior 2.5 callan@bu.edu
B005 Jack Smith graduate 3.0 smith@bu.edu
B006 Terry Zillman graduate 4.0 zillman@bu.edu
B007 Becky Lee senior 4.0 lee@bu.edu
B008 Tom Baker freshman baker@bu.edu
Courses
Dept_code Course# Title
CS 432 database systems
Math 314 discrete math
CS 240 data structure
Math 221 calculus I
CS 532 database systems
CS 552 operating systems
BIOL 425 molecular biology
Classes
Classid Dept_code Course# Sect# Year Semester Limit Size
c0001 CS 432 1 2014 Spring 35 34
c0002 Math 314 1 2013 Fall 25 24
c0003 Math 314 2 2013 Fall 25 22
c0004 CS 432 1 2013 Spring 30 30
c0005 CS 240 1 2014 Spring 40 39
c0006 CS 532 1 2014 Spring 29 28
c0007 Math 221 1 2014 Spring 30 30
Enrollments
Sid Classid Grade
B001 c0001 A
B002 c0002 B
B003 c0004 A
B004 c0004 C
B004 c0005 B
B005 c0006 B
B006 c0006 A
B001 c0002 C
B003 c0005
B007 c0007 A
B001 c0003 B
B001 c0006 B
B001 c0004 A
B001 c0005 B
4
