Question

Book: Fundamental of database systems

Chapter 5 exercise 5.9

Photos - 20190906_111020[3691].jpg -ox See all photos + Add to Edit & Create Share : Q u ♡ 2 # d. A view that has the project name, controm of employees, and total hours worked per week on the project for each project with more than one employee working on it. 5.9. Consider the following view, DEPT SUMMARY, defined on the COMPANY database in Figure 3.6: CREATE VIEW DEPT SUMMARY (D, C, Total s, Average_s) AS SELECT Dno, COUNT(*) SUM (Salary), AVG (Salary) FROM EMPLOYEE GROUP BY Dno; State which of the following queries and updates would be allowed on the view. If a query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database in Figure 3.6. a. SELECT FROM DEPT_SUMMARY; b. SELECT DC FROM DEPT_SUMMARY WHERE TOTAL_S > 100000; c. SELECT D, AVERAGES FROM DEPT SUMMARY WHERE C> SELECT C FROM DEPT_SUMMARY WHERE D=4); d. UPDATE DEPT_SUMMARY SET D=3 WHERE D=4; e. DELETE FROM DEPT SUMMARY WHERE C> 4; O Type here to search O P ^ . 12:19 PM Ta 9/6/2019

Figure 3-6 here provides examples on how to answer this question:

Answer 1

Solution

a)Allowed

1.li Result 123 (99988 $sqlite3 database. sdb < main.sql 1|1|55000 55000.0 4393000 31000.0 5|4|133000 | 33250.0 131 Execute > Share main.sql STDIN (999887777,10,10.0), 124 (987987987,10,35.0), 125 (987987987,30,5.0), 126 (987654321,30,20.6), 127 (987654321,20,25.0), 128 (888665555,20,NULL); 129 130 insert into dependent (essn, dependent_name, sex, bdate, relationship) 132 values 133 (333445555, 'Alice', 'F', '1986-04-05', 'Daughter'), 134 (333445555, 'Theodore', 'M', '1983-10-25', 'Son'), 135 (333445555, 'Joy', 'F', '1958-05-03', 'Spouse'), 136 (987654321, 'Abner', 'M', '1942-02-28', 'Spouse'), 137 (123456789, 'Michael', 'M', '1988-01-04', 'Son), 138 (123456789, 'Alice', 'F', '1988-12-30', 'Daughter'), 139 (123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse'); 140 141 142 CREATE VIEW DEPT SUMMARY (D, C, Total_s, Average_s) 143 AS SELECT dno, COUNT(*), SUM(salary), AVG( salary), 144 FROM employee 145 GROUP BY dno; 146 147 SELECT * FROM DEPT_SUMMARY 148 149 150

--

b)

Allowed

# Execute > Share main.sql STDIN 1.lt Result $sqlite3 database. sdb < main.sql 54 123 (999887777,10,10.6), 124 (987987987,10,35.0), 125 (987987987,30,5.0), 126 (987654321,30,20.0), 127 (987654321,20,25.0), 128 (888665555,20,NULL); 129 130 insert into dependent 131 (essn, dependent_name, sex, bdate, relationship) 132 values 133 (333445555, 'Alice', 'F', '1986-04-05', 'Daughter'), 134 (333445555, 'Theodore', 'M', '1983-10-25', Son), 135 (333445555, 'Joy', 'F, 1958-65-63', 'Spouse'), 136 (987654321, 'Abner', 'M', '1942-02-28', 'Spouse'), 137 (123456789, 'Michael', 'M', '1988-01-04", "Son), 138 (123456789, 'Alice', 'F', '1988-12-30', 'Daughter), 139 (123456789, 'Elizabeth', 'F', '1967-65-65', 'Spouse'); 140 141 142 CREATE VIEW DEPT SUMMARY (D, C, Total_s, Average_s) 143 AS SELECT dno, COUNT(*), SUM( salary), AVG(salary) 144 FROM employee 145 GROUP BY dno; 146 147 SELECT D,C 148 FROM DEPT_SUMMARY 149 WHERE TOTAL_S > 100000; 150

c)

Allowed

. Execute 1 > Share main.sql STDIN I.lResult $sqlite3 database. sdb < main.sql 1 55000.0 4 31000.0 533250.0 127 123 (999887777,10,10.0), 124 (987987987,10,35.6), 125 (987987987,30,5.0), 126 (987654321,30,20.0), (987654321,20,25.0), 128 (888665555,26,NULL); 129 130 insert into dependent (essn, dependent_name, sex, bdate, relationship) 132 values 133 (333445555, 'Alice', 'F', '1986-04-05', 'Daughter'), 134 (333445555, 'Theodore', 'M', '1983-10-25', 'Son'), 135 (333445555, 'Joy', 'F', '1958-05-03', 'Spouse'), (987654321, 'Abner', 'M', '1942-02-28', 'Spouse'), 137 (123456789, 'Michael', 'M', '1988-01-04', Son). 138 (123456789, 'Alice', 'F, '1988-12-30', 'Daughter'), 139 (123456789, 'Elizabeth', 'T', '1967-05-05', 'Spouse'); 140 141 142 CREATE VIEW DEPT SUMMARY (D, C, Total_5, Average_s) AS SELECT dno, COUNT(*), SUM( salary), AVG(salary) 144 FROM employee 145 GROUP BY dno; 146 147 SELECT D, AVERAGE_S 148 FROM DEPT_SUMMARY 149 136

--

d)

Not allowed
This query will change all employees for dept 4 to dept 3

.Execute > Share main.sql STDIN L.lı Result 123 124 125 126 $sqlite3 database. sdb < main.sql Error: near line 147: Cannot modify DEPT_SUMMARY because it is a view (999887777,10,10.0), (987987987,10,35.0), (987987987,30,5.0), (987654321,30,20.0), (987654321,20,25.0), (888665555,20,NULL); 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 insert into dependent (essn, dependent_name, sex, bdate, relationship) values (333445555, 'Alice', 'F', '1986-64-65', 'Daughter'), (333445555, 'Theodore', 'M', '1983-10-25', 'Son'), (333445555, 'Joy', 'F', '1958-65-03', 'Spouse'), (987654321, 'Abner', 'M', '1942-02-28', 'Spouse'), (123456789, 'Michael', 'M', '1988-01-04', Son), (123456789, 'Alice', 'F', '1988-12-30', 'Daughter'), (123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse'); CREATE VIEW DEPT_SUMMARY_(D, C, Total_5, Average_s) AS SELECT dno, COUNT(*), SUM(Salary), AVG(Salary) FROM employee GROUP BY dno; UPDATE DEPT_SUMMARY SET D=3 WHERE D=4;

--

e)

Not allowed
This query will remove all departments that have more that 4 people from the view

1.li Result $sqlite3 database. sdb < main.sql Error: near line 147: cannot modify DEPT_SUMMARY because it is a view . Execute > Share main.sql STDIN 123 (999887777,10,10.0), 124 (987987987,10,35.0), 125 (987987987,30,5.0), 126 (987654321,30,20.0), 127 (987654321,20,25.0), 128 (888665555,20,NULL); 129 130 insert into dependent 131 (essn, dependent_name, sex, bdate, relationship) 132 values 133 (333445555, 'Alice', 'F', '1986-04-05', 'Daughter'), 134 (333445555, 'Theodore', 'M', '1983-10-25', 'Son'), 135 (333445555, 'Joy', 'F, 1958-05-03', 'Spouse'), 136 (987654321, 'Abner', 'M', '1942-02-28', 'Spouse'), 137 (123456789, 'Michael', 'M', '1988-01-04', Son), 138 (123456789, 'Alice','f', '1988-12-30', 'Daughter), 139 (123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse'); 140 141 142 CREATE VIEW DEPT SUMMARY (D, C, Total_s, Average_s), 143 AS SELECT dno, COUNT(*), SUM(salary), AVG( salary) 144 FROM employee 145 GROUP BY dno; 146 147 DELETE FROM DEPT_SUMMARY 148 WHERE C > 4; 149

---

solved all the questions

provided screenshot and query result for better understanding

if you have any doubt, love to help

all the best

please upvote