Consider the following tables 1. BOOK (Bookld, BookName, Author) Emp Name E101 E
ID: 3747690 • Letter: C
Question
Consider the following tables 1. BOOK (Bookld, BookName, Author) Emp Name E101 E103 104 E102 15-JUL-1986 04-MAY-1989 987 23-JAN-1980 Y-1988 23-JAN-1980 12-MAR-1979 Laxmi Rani E107 E106 Table 1-Employee Define the following tems Super key, Candidate key Primary key Identify the possible keys from the table with reasoning Rubric for gradi Definition per item Descripti Definition Identification Reasonin Points Purpose: Comprehend the concept of functional dependency and apply towards identifying keysExplanation / Answer
Super key: Super key is the set of one or more columns that is used to identify every record in the table uniquely. there can be many super keys in a table. Super key means all possible set of attributes when combined can unquely identify the records. In table book (BookId) is a super key. (bookid + bookName) is another super key. (bookName + author) is another super key bookId + author is also super key.
In employee table super keys are
(Employee_id) , (Employee_id + Employee_name).(Employee_id +dept_no), (Employee_id + DOB).(Employee_Id+Gender).(Employee_id + Employee_name+dept_no), (Employee_id + Employee_name + DOB), (Employee_id + Employee_name+ Gender), (Employee_id +dept_no +DOB) (Employee_id +dept_no+ Gender) (Employee_id +DOB+ Gender) all these super keys identifies every attribute uniquely.
Candidate Key: Candidate key is a set of attributes or group of attributes that can uniquely identify the record of database without referring other records. There can be multiple candidate keys which are eligible to become primary key. Candidate key is choosen from the super key. It's database designer who chooses one candidate key as primary key rest becomes alternate key. a key can become candidate key if it is ist super key now let's see how to find candidate key. take an example of Table Book:
There are 3 super keys to be the candidate key we select minimal number of attributes there is super key (bookId + author) there is it's subset called bookid which takes minimal colums to identify the table so bookID is our ist candidate key. (bookId+ BookName) cannot be candidate key because we have bookId which can uniquely identify all records. then we have bookName + author this is a candidate key because its subset doesnt exist in super key. in short if subset of any set can do the job for us then subset becomes candidate key.
In Employee Table candidate key is
(EmployeeID). that is the only candiadate key. if you will check all the super keys there is a field called employeeId common in all keys and employee_id is enuf to identify all records in the table.
Primary key: This is an attribute which uniquely identifies all records uniquely. it is database admin who selects one candiate key as primary key. primary key should be unique. in books table primary key is BookID. in Employee table primary key is Employee_id, reason being they both are unique 2 employees cant have same Employee_id but they can have same name,DOB, Dept_id and gender. even if we try to combine multiple columns except employee id it cant still identify all records uniquely. because 2 person can have same name and dob and they can be working in same dept. and then there is obvoius thing they both have same gender. so Employe_Id is the only option here. Same is the case with Books table. BookName and Author cannot alone do it. then we need to combine 2 columns. but as we know we prefer minimal set of attributes to select it as primary key.