Answer each of the following questions briefly. The questions are based on the f

Question

Answer each of the following questions briefly. The questions are based on the following relational schema:

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pcttime: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)

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

2.Define the Dept relation in SQL so that every department is guaranteed to have a manager.

3.Write an SQL statement to add John Doe as an employee with eid = 101, age = 32 and salary = 15, 000.

4.Write an SQL statement to give every employee a 10 percent raise.

5.Write an SQL statement to delete the Toy department. Given the referential integrity constraints you chose for this schema, explain what happens when this statement is executed.

Explanation / Answer

1)
Emp table structure
//Set eid in the Emp table as primary key
CREATE TABLE Emp (
   eid INTEGER,
   ename CHAR(10),
   age INTEGER,
   salary REAL,
   PRIMARY KEY (eid)
)

Works table structure
//Set a combiation of eid and did
//in the Works table as primary key
//Set did as foreign key that references Dept
//Set eid as foreign key that references Emp
//ON DELETE CASCADE represents if did is deleted
//then the corresponding employees from Emp will
//be deleted .
CREATE TABLE Works (
   eid INTEGER,
   did INTEGER,
   PRIMARY KEY (eid, did),
   FOREIGN KEY (did) REFERENCES Dept,
   FOREIGN KEY (eid) REFERENCES Emp,
   ON DELETE CASCADE
)

Dept table structure
//If parent table Emp id deleted, then the corresponsing record
//in Dept will have the foriegn key fields set to null.
//The record in the Dept table will not be deleted.

CREATE TABLE Dept
(
   did INTEGER,
   dname CHAR(20),
   budget REAL,
   managerid INTEGER ,
   PRIMARY KEY (did),
   FOREIGN KEY (managerid) REFERENCES Emp,
   ON DELETE SET NULL
)

-------------------------------------------------------------------------------------------------
2.
//set managerid INTEGER NOT NULL
//that guarentess that every department
//is not null.

CREATE TABLE Dept (did INTEGER,dname CHAR(20)
budget REAL,managerid INTEGER NOT NULL ,
PRIMARY KEY (did),
FOREIGN KEY (managerid) REFERENCES Emp)

-------------------------------------------------------------------------------------------------

3.
//Insert a recored into Emp table
INSERT INTO Emp (eid, ename, age, salary)
VALUES (101,

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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