Question

Database

Problem description:1. What is the difference between a candidate key and the primary key for a given relation? What is a superkey?

2. What is a foreign key constraint? Why are such constraints important? What is referential integrity?

3. Answer teach of the following questions. The questions are based on the following relational schema:Emp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did: integer, pct_time: 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 delete the Three tables: Emp, Works, Dept.

Answer 1

1) A Primary key is an attribute(column) or set of attributes that can be used to uniquely identify tuples within a relation or table. A table or a relation can only have a single Primary key. A primary key is a minimal superkey i.e , it contains only the least amount of attributes with which all tuples can be identified

A Candidate key, much like the primary key, is also an attribute or set of attributes that can uniquely identify tuples within a relation. The major difference is that, a relation may have multiple Candidate keys. Any one of them can be selected as the Primary key

A Primary Key cannot have NULL values, whereas candidate keys can have NULL values

Eg:

Student {ID, F_name, M_name, L_name, DOB} 

ID is a primary key because it is unique for every tuple

{F_name, M_name, L_name,DOB} can also be used as a primary key, but in this case, we used ID as the primary key hence {F_name, M_name, L_name, DOB} becomes a candidate key

Super Key is an attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. All super keys can’t be candidate keys but its reverse is true. Candidate keys form a proper subset of superkeys

eg: In the Student relation the three superkeys are:

1. {ID}
2. {F_name, M_name, L_name,DOB}
3. {ID,F_name, M_name, L_name,DOB}

From the above three, we choose 1 and 2 as the candidate keys since 3 has redundant attributes.

2) A Foreign key is a column (or columns) that references a column (most often the primary key) of another table. Foreign keys are used to enforce referential integrity within the relations.

Eg: CUSTOMER{SID(Primary key), Last_name, First_name}

ORDER{Order_ID(Primary key), Order_date, Customer_SID(Foreign key), Amount}

In this example, Customer_SID attribute in the ORDERS table acts as a foreign key pointing to the SID column in the CUSTOMER table.

Foreign keys are important because:

• They link tables
• Prevent actions that would destroy link between tables
• prevents insertion of invalid data onto the foreign key column of a table because it needs to be a value that is also present in the table that it points to

Referential integrity is specified between two relations and is used to maintain the consistency among tuples in two relations. The constraint states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation. In the above CUSTOMER and ORDER tables, Customer_SID of ORDER can only have values that are already present in the SID column of CUSTOMER table.

3) (1)

• CREATE TABLE Works ( eid INTEGER NOT NULL , did INTEGER NOT NULL,pct_time INTEGER, PRIMARY KEY (eid, did), UNIQUE (eid), FOREIGN KEY (did) REFERENCES Dept )
• CREATE TABLE Emp ( eid INTEGER, ename CHAR(10), age INTEGER, salary REAL, PRIMARY KEY (eid) )
• CREATE TABLE Dept ( did INTEGER, budget REAL, managerid INTEGER ,dname CHAR(10), PRIMARY KEY (did), FOREIGN KEY (managerid) REFERENCES Emp, ON DELETE SET NULL)

(3) (2) CREATE TABLE Dept ( did INTEGER, budget REAL, managerid INTEGER NOT NULL , PRIMARY KEY (did), FOREIGN KEY (managerid) REFERENCES Emp)

(3) (3) INSERT INTO Emp (eid, ename, age, salary) VALUES (101, ’John Doe’, 32, 15000)

(3)(4) Assuming the table schema's need to be kept intact and only the values need to be deleted, the commands would be

DELETE FROM Emp;

DELETE FROM Works;

DELETE FROM Dept;

alternatively, if the table scheme does not have to be kept intact DROP TABLE command may be used,

DROP TABLE Table_name;