Database E/R Diagram Relational Design

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.