perators discussed in this chapter. Also show the result of each query as it would apply to the database state of Figure 3.6.  - List the managers who are controlling more than 2 projects. - List the names of employees all of whose dependents were born before 1990. - Retrieve the average salary of employees who work totally no less than 30 hours on projects

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state of Figure 3.6. 

- List the managers who are controlling more than 2 projects.

- List the names of employees all of whose dependents were born before 1990.

- Retrieve the average salary of employees who work totally no less than 30 hours on projects.  

EMPLOYEE
Fname Minit Lname Ssn
Address
Sex
Super_ssn Dno
Bdate
Salary
DEPARTMENT
Dname Dnumber Mgr_ssn Mgr_start_date
DEPT LOCATIONSs
Dnumber Dlocation
PROJECT
Pname Pnumber Plocation
Dnum
WORKS_ON
Essn
Pno
Hours
Figure 5.5
Schema diagram for the
COMPANY relational
DEPENDENT
Essn
Dependent_name Sex
Bdate
Relationship
database schema.
Transcribed Image Text:EMPLOYEE Fname Minit Lname Ssn Address Sex Super_ssn Dno Bdate Salary DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT LOCATIONSs Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours Figure 5.5 Schema diagram for the COMPANY relational DEPENDENT Essn Dependent_name Sex Bdate Relationship database schema.
Figure 5.6
One possible database state for the COMPANY relational database schema
EMPLOYEE
Fname
Super san DnD
123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555
M 40000 888665555
Minit
Lname
San
Bdate
Addross
Sar Salary
John
B.
Smith
333445555 1955-12-08 638 Voes, Houston, TX
Zelaya 999887ITI 1968-01-19 3321 Castle, Spring, TX
Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX
Franklin
Wong
Alicia
25000 987654321
Jenniler
S
F
43000 888665555
Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M
Joyce
English 453453453 1972-07-31 5631 Rice, Houston, TX
V Jabbar
E Borg
38000 333445565
F 25000 333445555
M 25000 987654321
A
5
Ahmad
987987987 1969-03-29 980 Dallas, Houston, TX
James
888665555 1937-11-10 450 Stone, Houston, TX
M 55000 NULL
1
DEPT LOCATIONS
Dlocaion
DEPARTMENT
Dramber
1
Drumber
Mgr start date
Dname
Mgr_san
Rosearch
5
333445555
1988-05-22
Houston
Administration
4
987654321
1995-01-01
4
Stafford
Hoadquartors
888665555
1981-06-19
Belaire
5
Sugarland
Houston
5
WORKS ON
PROJECT
Essn
Pno
Hours
Phame
Pnumber
Plocation
Dnum
123456789
1
325
ProductX
Bellaire
5
123456789
2
75
ProducfY
2
Sugarland
5
666884444
3.
40.0
Product
3
Hounton
5
453453453
20.0
Computorization
Roorganiration
10
Stafford
4
453453453
2
20.0
20
Houston
1
333445555
2
10.0
Newbenelits
30
Stafford
4
333445555
3
10.0
333445555
10
10.0
DEPENDENT
333445555
20
10.0
Essn
Dependent name Sox
Relationshp
1986 04-05 Daughter
Bdate
999887777
30
30.0
333445555
Alice
F
999887777
10
10.0
333445555
Theodore
M
1983-10-25
Son
10
35.0
1958-05-03 Spoune
1942-02-28 Spouse
333445555
Joy
30
5.0
987654321
Abner
M
987654321
30
20.0
123456789
Michael
M
1988-01-04 Son
987654321
20
15.0
123456789
Alice
F
1988-12-30
Daughter
888665555
20
NULL
123456789
Elizabeth
F
1967-05-05
Spouse
Transcribed Image Text:Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Super san DnD 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 M 40000 888665555 Minit Lname San Bdate Addross Sar Salary John B. Smith 333445555 1955-12-08 638 Voes, Houston, TX Zelaya 999887ITI 1968-01-19 3321 Castle, Spring, TX Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX Franklin Wong Alicia 25000 987654321 Jenniler S F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M Joyce English 453453453 1972-07-31 5631 Rice, Houston, TX V Jabbar E Borg 38000 333445565 F 25000 333445555 M 25000 987654321 A 5 Ahmad 987987987 1969-03-29 980 Dallas, Houston, TX James 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 DEPT LOCATIONS Dlocaion DEPARTMENT Dramber 1 Drumber Mgr start date Dname Mgr_san Rosearch 5 333445555 1988-05-22 Houston Administration 4 987654321 1995-01-01 4 Stafford Hoadquartors 888665555 1981-06-19 Belaire 5 Sugarland Houston 5 WORKS ON PROJECT Essn Pno Hours Phame Pnumber Plocation Dnum 123456789 1 325 ProductX Bellaire 5 123456789 2 75 ProducfY 2 Sugarland 5 666884444 3. 40.0 Product 3 Hounton 5 453453453 20.0 Computorization Roorganiration 10 Stafford 4 453453453 2 20.0 20 Houston 1 333445555 2 10.0 Newbenelits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent name Sox Relationshp 1986 04-05 Daughter Bdate 999887777 30 30.0 333445555 Alice F 999887777 10 10.0 333445555 Theodore M 1983-10-25 Son 10 35.0 1958-05-03 Spoune 1942-02-28 Spouse 333445555 Joy 30 5.0 987654321 Abner M 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth F 1967-05-05 Spouse
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 5 steps with 3 images

Blurred answer
Knowledge Booster
Arrays
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education