(1) The following figure shows an ER schema for a database. Map this schema into a relational schema and specify all primary keys and foreign keys. (5 Marks) Time Time SHP MOVEMENT Longhide HISTORY Torna HA (SN SHOP TYPE SHP TYPE Owner 199 Se Ende IN (1.1) HOME PORT VISITS VISITS 09 Continent STATE COUNTRY PORT ON SEAOCEANLAKE (2) Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts: (5 Marks) a) The university keeps track of each student's name, student number, Social Security number, current address and phone number, permanent address and phone number, birth date, sex, class (freshman, sophomore, .... graduate), major department, minor department (if any), and degree program (B.A., B.S....., Ph.D.). Some user applications need to refer to the city, state, and ZIP Code of the student's permanent address and to the student's last name. Both Social Security number and student number have unique values for each student b) Each department is described by a name, department code, office number, office phone number, and college. Both name and code have unique values for each department c) Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of the course number is unique for each course. d) Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, ... up to the number of sections taught during each semester. e) A grade report has a student section, letter grade, and numeric grade (0, 1, 2, 3, or 4). Design an ER schema for this application and draw an ER diagram for the schema. Specify key attributes of each entity type, and structural constraints on each relationship type. Note any unspecified requirements and make appropriate assumptions to make the specification complete. (3) Answer the following queries that applied to the following database. (10 Marks) 4 EMPLOYEE Frame Minit Lame San Bdate Address Sex Salary Super_san Dno John B Smith 1234567891965-01-09 731 Fondren, Houston, TX M30000 3334455555 Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M40000 8886655555 Alicia J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Jennifer S Wallace 9876543211941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak Humble, TX M 38000 333445565 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-20 980 Dallas, Houston, TX M 25000 987654321 4 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 DEPARTMENT DEPT_LOCATIONS Drame Dnumber Morson Mgr_start date Dnumber Dlocation Research 5 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 1 888665555 1981-08-19 5 Bellaire 5 Sugarland 5 Houston WORKS ON PROJECT oc D ProduceX + PY + Sugarland 5 9 123456789 123456700 166004144 453453453 453453453 31465656 331465656 3445665 333445535 TTTT 9900 2 3 1 2 2 4 325 75 400 300 200 100 100 100 100 2 3 10 20 30 Consertion Recanation New St 4 3 1) 10 O DEPENDENT Sex 06 Deart | Alice 100 100 | 10 10 10 30 30 20 30 007007907 307854301 987654321 18000 50 333445505 333465666 333445566 07654321 123456789 123456789 123456789 Joy At M Alice 1968 6400 1003-10-25 som 1968-650 1943-000 1968-0104 Son 1900-1930 Duo 196700.00 Spo 200 150 NULL F F 1) Create all of the above tables using SQL statements. 2) Retrieve the names of all employees in departments who work more than 5 hours per weck on the Product/project 3) List the names of all employees who have a dependent with the same first name as themselves 4) Find the names of all employees who are directly supervised by James Bong" 5) For each project, list the project name and the total hours per week (by all employees pent on that project For each department, retrieve the depuitment name, and the average salary of employees working in that department 7) Retrieve the average salary of all female employees
