02/13/2019 Dr. Ugur Homework #4/Lab(10 points) Due: Feb. 18,2019 Monday Purpose: This homework is to get familiar with the Relational Model and queries in Relational Algebra. Put your full name on the top, type your answers in a Word document called hw-4.docx (or scan your hand-written answers in a pdf file: hw-4.pdf), and submit the file on Blackboard 6.16 Specify the following queries on the COMPANY relational database schema below last (Figure 3.7) using the relational operators discussed in class. Also show the result of each query as it would apply to the data (the databa state) of Figure 3.6 below. a) List the last names of employees who work for Department 5. b) List the last names of employees and their department names. c) List the last names of department managers together with department names they manage. 341 Spring 2019 02/13/2019 Dr. Ugur d) List all the department names and their locations. e) For every project located in Stafford, list the project number, the controlling department name, the department manager's last name. 02/13/2019 Dr. Ugur ITC 341 Spring 2019 EMPLOYEE Salary!Super ssn Dn Enamel Minit!Lname Bdatel Address! Sex Sr DEPARTMENT Dname Dnumber MgrssnMgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours Figure 3.7 Referential integrity constraints displayed DEPENDENT Essn Dependent name Sex Bdate Relationship on the COMPANY relational database J schema. ITC 341 Spring 2019 Figure 3.6 possible database state for the COMPANY relational database schema. Sex Salary Super ssn Dno Smth 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Fname Minit Lname Paniken Wong 333445655 1985-1208 638 Voss,Houston, X M 40000 388665555 Franklin T AliciaZelaya 999887777 1968-01-19 3321 Castie Spring TX F 25000 9876543214 JennilerS | Walace! 987654321|1941-06-201201 Berry, Belare. TX |F |43000|88866555514 ReameshK Nuryan 6e6e4444 192 09 13 07s Fre Oak Humble X M 38000 3345505 oyceA English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 333446666 5 Ahmad V Jabbar 980 Dallas, Houston, TX M 2 25000 987654321 55000 NULL- DEPT LOCATIONS 987987987 1969-03-29 lanes- -E-Bong T888666555t 1907-11 10 450Stone,Houston,TX| DEPARTMENT Dnumber Dlocation Dname start date 1988-05-22 995 01-01 888665555 1981-06-19 333445555 Stafford Bellaire WORKS ON PROJECT Pnumber Plocation Dnum Essn 123456789 123456789 666884444 453453453 453453453 2 333445555 333445555 333445555 Hours Pname 32.5 ProductY Productz 2 Sugarland 3 Houston 10 Stafford 20 Houston 30 Stafford 20.0 10.0 10 10.0 333445555 20 10.0 30.0 0.0 10 35.0 30 5.0 30 20.0 20 15.0 Dependent namex Bdate Relationship 999887777 30 999887777 10 987987987 987987987 987654321 987654321 888685555 20NULL 123456789 Elizabeth 333445555 Alioe 333445555 Theodore 333445555 Jy 987654321 Abner 123456789 Michael 123456789 Alice F 1986-04-05 Daughter M 1983-10-25 Son F 1958-05-03 Spouse M 1942-02-28 Spouse M 198801-04 Son F 1988-12-30 Daughter F 1967-05-05 Spouse
