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.

Tamil Nadu DgeTextbookSubjective· 4mImportance★★★★★

34% · 10/29 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 Fundamentals

Database Fundamentals

Think of a database as a well-organised filing cabinet. In your daily life, you already manage small databases without realising it — your phone's contact list, the catalogue of a library, or even the list of students in a class register. Each of these is a collection of related information stored in a structured way so that you can find what you need quickly.

What Exactly Is a Database?

A database is a collection of related data that is stored and managed systematically. The key word here is related. A random pile of papers on a desk is not a database — but the same papers, sorted into folders with labels and a clear order, become one. The purpose of a database is to allow efficient storage, retrieval, modification, and deletion of data.

Note

A database is not the same as a spreadsheet. A spreadsheet is a single table meant for one person's use. A database is designed for multiple users, handles large volumes of data, and ensures that the data remains consistent even when many people access it at once.

Why Do We Need Databases?

Imagine a small business that keeps customer orders in a notebook. As the business grows, the notebook becomes impossible to manage — pages get lost, two customers might be given the same order number, and finding a specific order takes forever. A database solves all these problems.

Databases provide:

Centralised control — all data lives in one place, so everyone works with the same information
Data consistency — rules can be enforced (for example, an order must belong to a real customer)
Security — different users can be given different levels of access
Reduced redundancy — the same piece of information is not stored in multiple places unnecessarily
Data independence — the way data is stored can change without affecting how users view it

The Core Idea: Tables and Relationships

The most common type of database is the relational database. Think of it as a collection of tables, where each table stores information about one kind of thing.

A table is made up of rows and columns. Each column represents a field (a single piece of information, like a name or a date), and each row represents a record (one complete set of fields, like one customer's full details).

For example, a university might have a Student table with columns: Roll Number, Name, Date of Birth, and Course. Each row is one student.

The real power comes from relationships between tables. The same university might have a Course table and an Enrolment table. The Enrolment table links a student to a course using the student's roll number and the course code — without repeating all the student's details or all the course details. This is what makes a database efficient.

Important

The primary key is a column (or a set of columns) that uniquely identifies each row in a table. For the Student table, Roll Number is the primary key — no two students can share the same roll number. A foreign key is a column in one table that refers to the primary key of another table, creating the link between them.

Database Management System (DBMS)

A Database Management System is the software that lets you create, manage, and interact with a database. You do not directly touch the data files — the DBMS does that for you. Popular examples include MySQL, Oracle, and Microsoft Access.

The DBMS handles:

Data storage and retrieval — it decides how to physically store data on a disk
Query processing — when you ask for "all students born after 2005", the DBMS finds the answer …

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

(a) Candidate Keys for EMPLOYEE

A candidate key is an attribute or a set of attributes that uniquely identifies each tuple (row) in a relation (table) and is minimal (no proper subset of it can uniquely identify tuples).

From the schema EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID):

AadharNumber is a unique identifier for individuals, making it a candidate key.
EmployeeID is also designed to be a unique identifier for each employee within the organization, making it a candidate key.

(b) Tables and Key for Retrieving Dependent Details

To retrieve details of a dependent for a specific employee, information from both the EMPLOYEE and DEPENDENT tables is required.

The EMPLOYEE table contains the employee's EmployeeID.
The DEPENDENT table contains EmployeeID to link dependents to their respective employees. The common attribute EmployeeID acts as a foreign key in the DEPENDENT table, referencing the EmployeeID (which is a primary key or candidate key) in the EMPLOYEE table. This key is used to establish the relationship between the two tables and retrieve the desired details.

(c) Degree of EMPLOYEE and DEPENDENT Relations …

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

This solution identifies candidate keys for the EMPLOYEE table, specifies the tables and keys needed to retrieve dependent details, and states the degree (number of attributes) for both the EMPLOYEE and DEPENDENT relations.

When designing a relational database, understanding concepts like keys and relation properties is fundamental. Keys are crucial for uniquely identifying records and establishing relationships between tables, while properties like degree help describe the structure of a table.

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

A candidate key is an attribute or a set of attributes that can uniquely identify each tuple (row) in a relation (table) and is minimal, meaning no proper subset of it can also uniquely identify tuples.

Let's examine the attributes of the EMPLOYEE table: EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID).

AadharNumber: In India, an Aadhar Number is designed to be a unique identifier for every individual. Therefore, AadharNumber can uniquely identify each employee. Since it's a single attribute, it is inherently minimal.
EmployeeID: This attribute is explicitly named EmployeeID, which strongly suggests it is an identifier assigned by the organization to each employee. Such identifiers are typically unique within the organization. Assuming this uniqueness, EmployeeID can also uniquely identify each employee. As a single attribute, it is also minimal.
Name, Address, Department: These attributes, individually or in combination (without AadharNumber or EmployeeID), cannot guarantee unique identification for every employee. For instance, multiple employees might have the same name, live at the same address, or work in the same department.

Therefore, the attributes that can serve as candidate keys for the EMPLOYEE table are AadharNumber and EmployeeID.

(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.

To retrieve details of a dependent for a specific employee, we need to link information from two different tables: EMPLOYEE and DEPENDENT.

The EMPLOYEE table contains details about the employees, including their EmployeeID.
The DEPENDENT table contains details about dependents, and crucially, it includes EmployeeID to link each dependent back to their respective employee.

The common attribute between these two tables is EmployeeID. In the EMPLOYEE table, EmployeeID acts as a primary key (or at least a candidate key), uniquely identifying each employee. In the DEPENDENT table, EmployeeID acts as a foreign key, referencing the EmployeeID in the EMPLOYEE table. This foreign key relationship is precisely what allows us to connect a dependent to their employee. …

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)

(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.

🎓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