Emp( eid: integer, ename: string, age: integer, sala ill: real) Works( eid: inte

Question

Emp( eid: integer, ename: string, age: integer, sala ill: real) Works( eid: integer, did: integer, pet_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) 1. Give an example of a foreign key constraint that involves the Dept relation. 2. Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints. 3. Define the Dept relation in SQL so that every department is guaranteed to have a manager. 4. Write an SQL statement to add John Doe as an employee with cid = 101, age = 32 and salary = 15,000. 5. Write an SQL statement to give every employee a 10 percent raise. 6. Write an SQL statement to delete the Toy department.

Explanation / Answer

-- 1. Give an example of a foreign key constraint that involves the Dept relation.
ALTER TABLE dept ADD CONSTRAINT fk_dept FOREIGN KEY (did) REFERENCES works (did);

-- 2. Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints.
ALTER TABLE emp ADD PRIMARY KEY (eid);
ALTER TABLE works ADD PRIMARY KEY (eid, did);
ALTER TABLE dept ADD PRIMARY KEY (did);
ALTER TABLE dept ADD CONSTRAINT fk_dept FOREIGN KEY (did) REFERENCES works (did);
ALTER TABLE emp ADD CONSTRAINT fk_emp FOREIGN KEY (eid) REFERENCES works (eid);

-- 3. Define the Dept relation in SQL so that every department is guaranteed to have a manager.
ALTER TABLE dept MODIFY manager INTEGER NOT NULL;

-- 4. Write an SQL statement to add John Doe as an employee with cid = 101, age = 32 and salary = 15,000.
INSERT INTO emp VALUES (101, 'John Doe', 32, 15000);

-- 5. Write an SQL statement to give every employee a 10 percent raise.
UPDATE emp
SET    salary = salary * 1.1;

-- 6. Write an SQL statement to delete the Toy department.
DELETE FROM dept WHERE UPPER(dname) = 'TOY';

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.