COP5725 Database Management SystemsMidterm – Fall 2005Instructor: Alin DobraOctober 27, 2005Name:UFID:Email:• This is a 120 minute, closed-book exam. You are allowed to use handwritten notes on bothsides of a US letter size sheet of paper.• This exam contains 9 single-sided sheets of paper (including this one)• Write all answers on these pages, preferably on the white space in the problem statement.Continue on the back page if running out of space but clearly number your answers if doingso.• Make sure you attack every problem; partial credit will be awarded for incomplete or partiallycorrect results.Grading:1 202 153 504 15Total 10011. E-R Model[20]A university registrar’s office maintains data about the following entities:• courses, including number, title, credits, syllabus and prerequisites• course offerings, including course number, year, semester, section number, instructor,timings, and classro om• students, including student-id, name, and program• instructors, including identification number, name, department, and titleFurther, the enrolment of students in classes and grades awarded to students in each coursethey are enrolled for must be appropriately modeled.a. [15] Construct an E-R diagram for the registrar’s offi ce . Document all assumptions youmake about the mapping constraints. Use the back of the page if you need more space.2b. [5] Translate the E-R diagram into a relational schema (you do not have to normalizethe schema).2. Schema Normalization[15]Suppose you have the relation R = (A, B, C, D, E) and the following functional dependenciesA → BCCD → EB → DE → Aa. [5] Find all keys (justify your answer).3b. [5] Decompose the relation so that it is in BCNF, or justify why it cannot be decomposed.c. [5] Suppose we remove the last functional dependency (i.e. E → A). Decompose therelation so that it is in BCNF, or justify why it cannot be decomposed.3. Relational Algebra and SQL [50]Consider the following relational schema:Emp(eid:integer, ename:string, age:integer, salary:real)Works(eid:integer, did:integer, pct_time:integer)Dept(did:integer, dname:string, budget:real, managerid:integer)An employee can work in more than one department; the pct time field of the Works relation4shows the percentage of time that a given employee works in a given department. Managersare not employees of any department.Write the following queries in relational algebra:a. [5] Print the name and the age of each employee who works in both Hardware andSoftware departments.b. [5] For each employee, print the names of all its managers.c. [5] For each manager that is at most 40 years old, print its name and its salary.5d. [5] Print the name of all managers that manage more than one department.e. [5] Print the name of all employees that work exactly in one department.6Write the following queries in SQL:a. [5] Print the name and the age of each employee who works in both Hardware andSoftware departments.b. [5] Print the name and the number of departments each employee works in.c. [5] For each department with more than 20 full-time-equivalent employees (i.e., wherethe part-time and full-time employees add up to at least that many full-time employees),print the did together with the number of employees that work in that department.7d. [5] Find the ename of the manager who manages the department with the largest budget.e. [5] Find the ename of managers who manage only departments with budgets larger than$1 million, but at least one department with budget less than $5 million.4. Database Modifications and Constraints [15]Consider the schema in the previous problem. To define constraints, you have to give theSQL code that specifies both the schema and the constraints.8Answer the following questions:a. [5] Define a constraint on Emp that will e nsure that every employee makes at least$10, 000. What kind of constraint is it (attribute or table)?b. [5] What are the foreign-key constraints that c an be specified on the schema. Give theSQL code that specifies the constraints.c. [5] Write SQL statements to delete all information about employees whose salaries exceedthat of the manager of one or more departments that they work in. Be sure to maintainall the relevant integrity