Question

Consider the following table: Employee

Emp_id	Name	Salary	Designation
E01	Kajal	78000	Manager
E02	Rahul	50000	Sales Executive
E03	Tarun	55000	Clerk

(a) You are tasked with designing a new employee database for your company. Based on the given table structure, identify the fields that would be necessary to store key information about each employee.
(b) As a database administrator, you need to ensure each employee has a unique identifier. Which field in the table would you choose as the primary key, and why?
(c) During a review of the employee database, you realize there could be another field apart from the primary key that can uniquely identify a record. Identify the field and justify why it could serve as an alternate key.
(d) You are integrating the employee table with another table that stores department details.
Explain how you would use the primary key from the employee table and a foreign key in the department table to establish a relationship between the two tables. Illustrate with an example.

Answer 1

Consider the following table: Employee

Emp_id	Name	Salary	Designation
E01	Kajal	78000	Manager
E02	Rahul	50000	Sales Executive
E03	Tarun	55000	Clerk

a. Emp_id, Name,Salary, Designation
b. Emp_id
c. Combination of Emp_id and Name
d. Primary Key: Unique identifier for each row in a table, cannot be NULL.
Example: Emp_id in Employees. 

Foreign Key: Links to a primary key in another table, establishing relationships. Example: Emp_id in Orders referencing Emp_id in Employees