Question

1a) i) Explain the candidate key, primary key and foreign key with suitable examples [6]

ii) Draw a suitable ER diagram to show the “IS-A” relationship between subtype and supertype [5]

iii) Briefly explain the recursive relationship with a suitable example. [2]

Answer 1

i)

A column or group of columns in a table which helps us to uniquely identifies every row in that table is called a primary key.

In the following example, StudID is a Primary Key.

StudID   Roll_No   First_Name   LastName   Email
1       11       Tom           Price       [email protected]
2       12       Nick       Wright       [email protected]
3       13       Dana       Natan       [email protected]

A superkey is a group of single or multiple keys which identifies rows in a table.
A super key with no repeated attribute is called candidate key.
The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key.

In the given table Stud ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table.

StudID   Roll_No   First_Name   LastName   Email

1       11       Tom           Price       [email protected]
2       12       Nick       Wright       [email protected]
3       13       Dana       Natan       [email protected]

A foreign key is a column which is added to create a relationship with another table. Foreign keys help us to maintain data
integrity and also allows navigation between two different instances of an entity.

Example
Table Department
DeptCode   DeptName
001           Science
002           English
005           Computer

Table teach
Teacher_ID   Fname   Lname
B002       David   Warner
B017       Sara   Joseph
B009       Mike   Brunton

In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.

Teacher_ID   DeptCode   Fname   Lname
B002       002           David   Warner
B017       002           Sara   Joseph
B009       001           Mike   Brunton

ii)

Here Employee is supertype and Hourly_Emps and Contract_Emps

iii)

A relationship has always been between occurrences in two different entities. However, it is possible for the same entity to participate in
the relationship. This is termed a recursive relationship.


Let us take the example of an employee who is also a manager. But a manager is also an employee, whose details will be held in the employee entity. To implement this a foreign key of the employee's manager number would be held in each employee record.

Employee entity
Employee no
Employee surname
Employee forename
Employee DOB
Employee NI number

Manager no * (this is the employee no of the employee's manager)
Graphically, this can be shown as:

If you have any query regarding the code please ask me in the comment i am here for help you. Please do not direct thumbs down just ask if you have any query. And if you like my work then please appreciates with up vote. Thank You.