Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Sex Address Frame...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Sex Address Frame Minit Lhame John B Smith 1234567891965-01-00 731 Fondren, Houston, TX M 333445555 1955-12-08 638 Voss, Houston, TX M Frankdin T Wong Alicia F Jennifer S Ramesh K J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX Walace 987654321 1941-06-20 291 Berry, Bellaire, TX F Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M A English 453453453 1972-07-31 5631 Rice, Houston, TX M Joyon Ahmad V Jabbar 987987987 1060-03-29 980 Dallas, Houston, TX 888665555 1937-11-10 450 Stone, Houston, TX James E Borg DEPARTMENT Dame Research Administration Headquarters Dnumber 5 4 1 WORKS ON Esen Poo 123456789 1 123456789 2 #88884444 453463453 453463450 333445550 333445660 333445555 333445555 Hours 32.5 7.5 3 40.0 1 20.0 2 20.0 2 10.0 3 10.0 10 10:0 28829 San 20 10.0 999887777 999887777 907987987 987987987 987654321 967654321 20 888065556 20 30.0 10 10.0 10 35.0 5.0 30 30 20.0 15.0 NULL Bdate Mgr sen 333445555 987654321 888665555 Mgr start date 1988-05-22 1995-01-01 1981-06-19 DEPENDENT Esen 333445555 333445556 333445555 987654321 123456789 123456789 123456789 PROJECT Pname ProductX Product Product Computerization Reorganization Newbenefits Alice Theodore F Joy Abner Michael Alice Elizabeth Dependent name Sex F M F M 55000 NULL M M Salary Super_san De 30000 333445555 5 40000 888665555 5. F 25000 987654321 43000 888665555 F 5 38000 333445555 25000 333445555 25000 987654321 4 5 1 DEPT LOCATIONS Dnumber 1 Pnumber 1 Bellaire 2 Sugarland Houston 3 10 Stafford 20 Houston 30 Stafford 4 5 5 5 Diocation Houston Stafford Bellaire Sugarland Houston Plocation 4 4 Drum 5 5 5 1983-10-25 Son 1958-05-03 1942-02-28 Bdate Relationship 1986-04-05 Daughter 4 1 4 1988-01-04 Son 1988-12-30 1967-05-05 Spouse Spouse Daughter Spouse A. (15 points) You need to implement the database using MySQL RDBMS or any Open-source RDBMS of your choice. Examples of Open-source free RDBMS are SQLcl and SQL Developer Command Line, PostgreSQL, etc. B. (5 points) Populate the database with the tuples given in Figure 5.6 on page 162 of the Elmarsi textbook. C. (30 points) after the database is defined and populated, you then need to perform the following queries: 1. List the names of all male employees with two or more dependents 2. Find the names of all employees who are directly supervised by the employee whose SSN is '888665555' 3. For each project, list the project name and the total hours per week (by all employees) spent on that project. 4. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees 5. List the names of all employees who work on more than two projects 6. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Sex Address Frame Minit Lhame John B Smith 1234567891965-01-00 731 Fondren, Houston, TX M 333445555 1955-12-08 638 Voss, Houston, TX M Frankdin T Wong Alicia F Jennifer S Ramesh K J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX Walace 987654321 1941-06-20 291 Berry, Bellaire, TX F Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M A English 453453453 1972-07-31 5631 Rice, Houston, TX M Joyon Ahmad V Jabbar 987987987 1060-03-29 980 Dallas, Houston, TX 888665555 1937-11-10 450 Stone, Houston, TX James E Borg DEPARTMENT Dame Research Administration Headquarters Dnumber 5 4 1 WORKS ON Esen Poo 123456789 1 123456789 2 #88884444 453463453 453463450 333445550 333445660 333445555 333445555 Hours 32.5 7.5 3 40.0 1 20.0 2 20.0 2 10.0 3 10.0 10 10:0 28829 San 20 10.0 999887777 999887777 907987987 987987987 987654321 967654321 20 888065556 20 30.0 10 10.0 10 35.0 5.0 30 30 20.0 15.0 NULL Bdate Mgr sen 333445555 987654321 888665555 Mgr start date 1988-05-22 1995-01-01 1981-06-19 DEPENDENT Esen 333445555 333445556 333445555 987654321 123456789 123456789 123456789 PROJECT Pname ProductX Product Product Computerization Reorganization Newbenefits Alice Theodore F Joy Abner Michael Alice Elizabeth Dependent name Sex F M F M 55000 NULL M M Salary Super_san De 30000 333445555 5 40000 888665555 5. F 25000 987654321 43000 888665555 F 5 38000 333445555 25000 333445555 25000 987654321 4 5 1 DEPT LOCATIONS Dnumber 1 Pnumber 1 Bellaire 2 Sugarland Houston 3 10 Stafford 20 Houston 30 Stafford 4 5 5 5 Diocation Houston Stafford Bellaire Sugarland Houston Plocation 4 4 Drum 5 5 5 1983-10-25 Son 1958-05-03 1942-02-28 Bdate Relationship 1986-04-05 Daughter 4 1 4 1988-01-04 Son 1988-12-30 1967-05-05 Spouse Spouse Daughter Spouse A. (15 points) You need to implement the database using MySQL RDBMS or any Open-source RDBMS of your choice. Examples of Open-source free RDBMS are SQLcl and SQL Developer Command Line, PostgreSQL, etc. B. (5 points) Populate the database with the tuples given in Figure 5.6 on page 162 of the Elmarsi textbook. C. (30 points) after the database is defined and populated, you then need to perform the following queries: 1. List the names of all male employees with two or more dependents 2. Find the names of all employees who are directly supervised by the employee whose SSN is '888665555' 3. For each project, list the project name and the total hours per week (by all employees) spent on that project. 4. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees 5. List the names of all employees who work on more than two projects 6. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.
Expert Answer:
Answer rating: 100% (QA)
Query SELECT FnameLnameCOUNTPno AS NoofProjects FROM workson department employee WHERE worksonESS... View the full answer
Related Book For
Fundamentals Of Database Systems
ISBN: 9780133970777
7th Edition
Authors: Ramez Elmasri, Shamkant Navathe
Posted Date:
Students also viewed these databases questions
-
Which of the following is not a universal rule for achieving internal control over cash? A) Separate recordkeeping from accounting for cash to the extent possible. B) Deposit each day's cash receipts...
-
Consider the COMPANY database described in Figure 5.6. Using the syntax of Oracle triggers, write active rules to do the following: a. Whenever an employees project assignments are changed, check if...
-
Specify the following queries on the database in Figure 5.5 in SQL. Show the query results if each query is applied to the database state in Figure 5.6. a. For each department whose average employee...
-
How does the range of hFE (Fig. 3.23j, normalized from hFE = 100) compare with the range of hfe (Fig. 3.23f) for the range of IC from 0.1 to 10 mA?
-
Is there a shortage of on campus parking at your school? How might the shortage be resolved?
-
Warrenia has two regions. In Oliviland, the marginal benefit associated with pollution cleanup is MB = 300 10Q, while in Linneland, the marginal benefit associated with pollution cleanup is MB = 200...
-
2. Fluor, an engineering and construction company, was awarded a $1 billion project to build a coal gasification plant in South Africa. Fluor signed an agreement with a South African client that...
-
Alexis Company uses 800 units of a product per year on a continuous basis. The product has a fixed cost of $50 per order, and its carrying cost is $2 per unit per year. It takes 5 days to receive a...
-
can some one help me for this question? 23. With the following information about the Hilborn, Hayes & Morrone Investment Co. prepare a cash budget for the months of January to April 2018. (40 Mark...
-
You will file 2022 Federal income tax return (treat all info as for year 2022not year 2021). Ignore the last paragraph of the tax return problem info (it does not apply to 2022, the book was still on...
-
70% of boys who surveyed at the school love to play video games. 84 boys said they did not like to play video games. how many boys were surveyed
-
What is the main challenge that artisans face in promoting and selling their crafts outside of Puerto Rico? What technology tool(s) (example: preparing a site on wix.com) would you recommend to deal...
-
BaltimoreSun.com issued a par bond, i.e, its current market price is $1,000. The bond has 12 years left to maturity. And its yield to maturity is 8.5%. What is the bond's annual amount of coupon...
-
Identify some strengths and weakness that Singapore Airlines faced because of Covid-19. These are the strengths i have identified, Swift Decision Making: SIA showed during the COVID-19 epidemic that...
-
What are the advantages of Notes Payable? Provide detailed answers.
-
2) A firm can legally print any number of shares and sell them to investors. The value of one share is the total dollar value of the entire firm divided by the number of shares issued. Accountants...
-
Classify and explain the issues and problems of implementing a reverse logistics returns system for beverage items in a manufacturing, retail or military operation. Do you feel that drones could be...
-
Activator rod AB exerts on crank BCD a force P directed along line AB. Knowing that P must have a 100-N component perpendicular to arm BC of the crank, determine (a) The magnitude of the force P, (b)...
-
What is the relationship between a type and its subtype in a type hierarchy? What is the constraint that is enforced on extents corresponding to types in the type hierarchy?
-
Name the main types of spatial operators and different classes of spatial queries.
-
What are the main reasons for and potential advantages of distributed databases?
-
If it is 4:00 now, what time was it 67 hours ago?
-
If it's \(3: 00\), what time will it be in 89 hours?
-
Suppose it is 3:00, and you decide to check your email every 5 hours. What time will it be when you check your email the ninth time?
Study smarter with the SolutionInn App