An organisation wants to create a database EMP-DEPENDENT to maintain following details about its employees and their dependent.
EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID) DEPENDENT(EmployeeID, DependentName, Relationship)
Name the attributes of EMPLOYEE, which can be used as candidate keys.
The attributes of EMPLOYEE which can be used as candidate keys are AadharNumber and EmployeeID. Both these attributes can uniquely identify each tuple in the EMPLOYEE relation. AadharNumber is a government-issued unique identification number assigned to every individual, ensuring no two employees have the same AadharNumber. Similarly, EmployeeID is a unique identifier assigned by the organisation to each employee, making it distinct for every record.
The company wants to retrieve details of dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.
To retrieve details of dependent of a particular employee, both EMPLOYEE and DEPENDENT tables are required. The key required to retrieve this detail is EmployeeID. The EmployeeID attribute in the DEPENDENT table acts as a foreign key that references the EmployeeID in the EMPLOYEE table. This foreign key establishes a link between the two tables, allowing the database to match each dependent record with the corresponding employee record.
What is the degree of EMPLOYEE and DEPENDENT relation?
The degree of a relation refers to the number of attributes (columns) in that relation. The EMPLOYEE relation has 5 attributes (AadharNumber, Name, Address, Department, EmployeeID), so its degree is 5. The DEPENDENT relation has 3 attributes (EmployeeID, DependentName, Relationship), so its degree is 3.
Explanation
This question tests understanding of candidate keys, foreign keys, and degree of relations. Part (a) requires identifying attributes that uniquely identify records - both AadharNumber and EmployeeID serve this purpose. Part (b) tests understanding of relational database concepts where foreign keys link tables for data retrieval. Part (c) tests the definition of degree which is the count of attributes/columns in a relation schema.