Answer the following questions given the following schema: employee(eid:integer,

Question

Answer the following questions given the following schema:

employee(eid:integer, name:string, age:integer, street:string, city:string)
works(eid:integer, cid:integer, salary:real)
company(cid:integer, name:string, city:string)
manages(manager_eid:integer, managee_eid:integer)

(1) Give an example of a foreign key constraint that involves the "company" relation. What are the options for enforcing this constraint when a user attempts to delete a "company" record?

(2) Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints.

(3) Write SQL statements to add John Doe as an employee of First Bank Corporation in Wilmington with eid 101, age 32, and salary 15,000.

(4) Write SQL statements to add Jane Doe as an employee of First Bank Corporation with eid 102, age 35, and salary 30,000. Jane Doe is the manager of John Doe.

(5) Write a SQL statement to give every employee a 10% raise.

Explanation / Answer

Dear user, 1) CREATE TABLE works(eid INTEGER NOT NULL, cid INTEGER NOT NULL, PRIMARY KEY(eid,cid), FOREIGN KEY(cid) REFERENCES company). 2) CREATE TABLE employee(eid INTEGER,name CHAR(10), age INTEGER, street CHAR(10),  
                                                city CHART(10) PRIMARY KEY(eid))
CREATE TABLE works(eid INTEGER,cid INTEGER, salary REAL, PRIMARY KEY(eid,cid),
                                          FOREIGN KEY(cid) REFERENCES company, FOREIGN KEY(eid)
                                          REFERENCES employee, ON DELETE CASCADE)
CREATE TABLE company(cid INTEGER,name CHAR(10), city CHART(10)
                                               PRIMARY KEY(cid))
CREATE TABLE manages(manager_eid INTEGER,managee_eid:INTEGER
                                               PRIMARY KEY(cid,manager_eid), FOREIGN KEY(manager_eid)
                                              REFERENCES employee, ON DELETE SET NULL)
3)
INSERT INTO employee(eid,ename,age,salary) VALUES(101,'John Doe',32,15000) 5)UPDATE employee E set E.salary=E.salary*1.10
5)UPDATE employee E set E.salary=E.salary*1.10

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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