关键词 > INFS2200/7903
INFS2200/7903 – Relational Database Systems Tutorial 2
发布时间：2022-08-30
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
INFS2200/7903 – Relational Database Systems
Tutorial 2
Question 1 Suppose that each of the following operations is applied directly to the database in Figure 1. Discuss all integrity constraints violated by each operation, if any, given the schema in Figure 2.
Figure 1: Database
Figure 2: Schema Diagram
A. Insert <'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX', 'M', 58000, '888665555', 1 > into EMPLOYEE.
B. Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
C. Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
D. Insert < '677678989', null, '40.0' > into WORKS_ON
E. Insert < '453453453', 'John', 'M', '12-DEC-60', 'SPOUSE' > into DEPENDENT.
F. Delete the WORKS_ON tuples with ESSN = '333445555'.
G. Delete the EMPLOYEE tuple with SSN = '987654321'.
H. Delete the PROJECT tuple with PNAME = 'ProductX'.
I. Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER = 5 to '123456789' and '01-OCT-88', respectively.
J. Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN = '999887777' to '943775543'.
K. Modify the HOURS attribute of the WORKS_ON tuple with ESSN = '999887777' and PNO = 10 to '5.0'.
Question 2 Consider the following relational schema. An employee can work in more than one department; the pcttime field of the Works relation shows the percentage of time that a given employee works in a given department.
|
Emp (eid: integer, ename: string, age: integer, salary: real)
Dept (did: integer, dname: string, budget: real, managerid: integer)
Works (eid: integer, did: integer, pcttime: integer) |
A. Give an example of a foreign key constraint that involves the Dept relation. What are the options for enforcing this constraint when a user attempts to delete a Dept tuple?
B. Write the SQL statements required to create the above relations, including appropriate versions of all primary and foreign key integrity constraints.
C. Define the Dept relation in SQL so that every department is guaranteed to have a manager.
D. Given the referential integrity constraints you chose for this schema, explain what happens when an SQL statement that deletes the Toy department is executed.
E. Define a table constraint on Emp that will ensure that every employee makes at least $10,000.
F. Define an assertion on Dept that will ensure that all managers have age > 30.
G. Define an assertion that will ensure that the total percentage of all appointments for an employee is under 100%.
H. Define an assertion that will ensure that the manager must always have a higher salary than any employee that he or she manages.
