CSE 462: Project 2
Problem 1: E-R (10 points)
You are given the following relational schema (keys underlined):
Employee(SSN, Name)
Faculty(SSN)
(Faculty(SSN) references Employee(SSN))
Staff(SSN, ManagerSSN)
(Staff(SSN) references Employee(SSN);
Staff(ManagerSSN) references Staff(SSN))
Student(PersonNo, Name)
Course(CourseNo, Title)
Offering(CourseNo, Semester, InstructorSSN, Credit)
(Offering(CourseNo) references Courses(CourseNo);
Offering(InstructorSSN) references Faculty(SSN))
Enrolls(CourseNo, Semester, PersonNo, Grade)
(Enrolls(CourseNo, Semester) references Offering(CourseNo, Semester);
Enrolls(PersonNo) references Student(PersonNo))
Decompile the above schema into an E-R schema representing the same information.
Problem 2: Relational design(10 points)
You are given the following relational schema (keys underlined):
Faculty(SSN,Name)
Student(PersonNo, Name)
Course(CourseNo, Title)
Offering(CourseNo, Semester, InstructorSSN, Credit)
Enrolls(CourseNo, Semester, PersonNo, Grade)
To do:
1. Represent the above schema as a single relation schema R with a set of functional
dependencies F.
2. Is the resulting schema R in BCNF? Prove your answer using the appropriate definitions.
3. If R is not in BCNF, provide its lossless join decomposition into BCNF and determine
whether it preserves the dependencies in F.
Problem 3: Extra credit (5 points)
Notation: A, B, C and D are distinct attributes a relation schema R(ABCD). MVDs are
defined over R.
Are the following inferences correct? Prove or disprove them using formal definitions.
1. If A ? B and A ? C, then A ? BC.
2. If A ? BC, then A ? B.
3. If AB → C, then A → C.
