CSE 462: Project 2 solution

$24.99

Original Work ?
Category: You will Instantly receive a download link for .ZIP solution file upon Payment

Description

5/5 - (2 votes)

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.