An organisation wants to create a database EMP-DEPENDENT to maintain following details about its employees and

Exercises · Q9

Q. 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)

a) Name the attributes of EMPLOYEE, which can be used as candidate keys.

b) 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.

c) What is the degree of EMPLOYEE and DEPENDENT relation?

Rajasthan RbseTextbookSubjective· 4mImportance★★★★★est

71% · 10/14 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — Database Schema vs State

Database Schema vs State: The Blueprint vs The Building

Imagine you are designing a new school. Before a single brick is laid, an architect draws up a detailed blueprint. That blueprint shows exactly where every classroom, corridor, and washroom will go, what size each room is, and how they connect. The blueprint is a plan — it defines the structure, the rules, and the constraints of the school.

Now, once the school is built, actual students and teachers fill those rooms. On Monday, Room 101 has a Physics class; on Tuesday, it is empty. The set of actual people, furniture, and activities present at any given moment is the state of the school.

The blueprint never changes (unless you renovate). The state changes every hour.

The Precise Statement

In a database:

Schema is the structure and rules that define the database. It specifies the tables, the columns in each table, the data types of those columns (integer, text, date), and the constraints (e.g., "every student must have a unique ID", "a course cannot exist without a department"). The schema is the intension — it describes what the database can hold.
State (or instance) is the actual data stored in the database at a particular moment in time. It is the extension — the set of rows currently in the tables.

Note

The schema is stable and changes only when you alter the design (e.g., add a new column). The state changes every time you INSERT, UPDATE, or DELETE a row.

A Concrete Example

Consider a university database with a table for students.

Schema (the blueprint):

Column Name	Data Type	Constraint
student_id	INTEGER	PRIMARY KEY
name	TEXT	NOT NULL
birth_year	INTEGER	> 1900

The schema says: "Every student must have a unique integer ID, a non-empty name, and a birth year after 1900." It does not contain a single actual student.

State (the data on a given day):

student_id	name	birth_year
101	Ananya	2003
102	Rohan	2004
103	Priya	2002

This is the state right now. Tomorrow, you might add a new student (state changes) or delete Rohan (state changes again). The schema remains exactly the same.

Why This Distinction Matters

Schema defines validity. Any state must obey the schema's rules. If you try to insert a student with birth_year = 1800, the database rejects it — the schema says no. The schema is the law; the state is the population.
Schema changes are rare and careful. Changing the schema (e.g., adding a phone_number column) affects all future states and often requires migrating existing data. Changing the state is routine. …

Start your 14-day free trial to unlock the full solution →

Candidate keys are the attributes that can each uniquely identify an employee — AadharNumber and EmployeeID. To fetch a particular employee's dependents you need both relations joined on EmployeeID (primary key in EMPLOYEE, foreign key in DEPENDENT). Degree just counts attributes: EMPLOYEE has 5, DEPENDENT has 3.

The schema:

EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)
DEPENDENT(EmployeeID, DependentName, Relationship)

a) Candidate keys of EMPLOYEE

A candidate key is a minimal attribute (set) whose value is unique for every tuple. Test each attribute:

AadharNumber — Aadhaar is unique per person by design → candidate key. ✓
EmployeeID — issued uniquely to each employee by the organisation → candidate key. ✓
Name, Address, Department — two employees can share any of these → not keys. ✗

So the candidate keys are AadharNumber and EmployeeID; one of them (typically EmployeeID) becomes the primary key and the other remains an alternate key.

b) Retrieving the dependents of a particular employee

The details needed live in both tables: the employee is identified in EMPLOYEE, and the dependent rows sit in DEPENDENT. The connecting key is EmployeeID — primary key of EMPLOYEE, and the foreign key in DEPENDENT that says whose dependent each tuple is.

SELECT E.Name, D.DependentName, D.Relationship
FROM   EMPLOYEE E
JOIN   DEPENDENT D ON D.EmployeeID = E.EmployeeID   -- FK in DEPENDENT matched to PK in EMPLOYEE
WHERE  E.EmployeeID = 'E101';

Example result for employee E101:

Name	DependentName	Relationship
Meera	Aarav	Son
Meera	Kavya	Daughter

Start your 14-day free trial to unlock the full solution →

←Previous questionQ8 — The school canteen wants to maintain records of items available in the…→Next questionQ10 — School uniform is available at M/s Sheetal Private Limited. They have …

Q.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)

🎓Unlock everything free for 14 days

Q.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)

Unlock everything free for 14 days