Question 1

Give the terms for each of the following:

a) Collection of logically related records.
b) DBMS creates a file that contains description about the data stored in the database.
c) Attribute that can uniquely identify the tuples in a relation.
d) Special value that is stored when actual data value is unknown for an attribute.
e) An attribute which can uniquely identify tuples of the table but is not defined as primary key of the table.
f) Software that is used to create, manipulate and maintain a relational database.

Accepted Answer

a) Relation or Table
b) Data Dictionary or Metadata
c) Primary Key
d) NULL value
e) Candidate Key or Alternate Key
f) Database Management System (DBMS) — a) A collection of logically related records is called a relation or table in a database.
b) DBMS maintains a data dictionary or metadata file that describes the structure of the database.
c) Primary key is an attribute that uniquely identifies each tuple in a relation.
d) NULL is a special marker used when the actual data value is unknown or missing.
e) Candidate key is an attribute that can uniquely identify tuples but is not chosen as primary key.
f) DBMS is software used to create, manipulate, and maintain databases.

Question 2

Why foreign keys are allowed to have NULL values? Explain with an example.

Accepted Answer

Foreign keys are allowed to have NULL values because a foreign key represents a reference to a primary key in another table, and sometimes the relationship may be optional. When there is no related tuple in the referenced table, the foreign key can be NULL to indicate the absence of a relationship.

Example: Consider two tables, STUDENT and SPORTS_PREFERENCE. If a student has not chosen any sport, the foreign key in SPORTS_PREFERENCE referring to STUDENT's primary key can be NULL, indicating no sports preference. — Foreign keys enforce referential integrity by linking tables. However, not all tuples must have a related tuple in the referenced table. Allowing NULL values in foreign keys permits optional relationships and avoids forcing invalid references.

Question 3

Differentiate between:
a) Database state and database schema
b) Primary key and foreign key
c) Degree and cardinality of a relation

Accepted Answer

a) Database Schema vs Database State:
- Schema is the structure or design of the database, defined by tables, attributes, and constraints. It is fixed and does not change frequently.
- State is the actual data stored in the database at a particular moment. It changes as data is inserted, updated, or deleted.

b) Primary Key vs Foreign Key:
- Primary key uniquely identifies each tuple in a relation.
- Foreign key is an attribute in one relation that refers to the primary key in another relation to establish a link.

c) Degree vs Cardinality of a Relation:
- Degree is the number of attributes (columns) in a relation.
- Cardinality is the number of tuples (rows) in a relation. — Understanding these differences helps in database design and integrity enforcement. Schema defines structure, state is data content. Primary key ensures uniqueness, foreign key maintains relationships. Degree and cardinality describe relation's shape and size.

Question 4

Compared to a file system, how does a database management system avoid redundancy in data through a database?

Accepted Answer

A DBMS avoids redundancy by storing data in a structured way using relations (tables) and enforcing constraints such as primary keys and foreign keys. It normalizes data to eliminate duplicate storage and maintains data consistency by centralizing data management. Unlike file systems where data is stored in separate files leading to duplication, DBMS uses relationships and keys to store data only once and reference it as needed. — Redundancy leads to inconsistency and wastes storage. DBMS uses normalization and relational design to minimize redundancy, ensuring data integrity and efficient storage.

Question 5

What are the limitations of file system that can be overcome by a relational DBMS?

Accepted Answer

Limitations of file system overcome by relational DBMS include:
- Data redundancy and inconsistency
- Difficulty in accessing data
- Data isolation in multiple files
- Integrity problems
- Atomicity of updates
- Concurrent access anomalies
- Security problems

Relational DBMS provides centralized data management, supports complex queries, enforces integrity constraints, and allows concurrent multi-user access with security. — File systems lack mechanisms for data integrity, security, and efficient access. DBMS addresses these by providing structured storage, query languages, and transaction management.

Question 6

A school has a rule that each student must participate in a sports activity. So each one should give only one preference for sports activity. Suppose there are five students in a class, each having a unique roll number. The class representative has prepared a list of sports preferences as shown below. Answer the following:

Table: Sports Preferences
|  Roll no | Preference  |
| --- | --- |
|  9 | Cricket  |
|  13 | Football  |
|  17 | Badminton  |
|  17 | Football  |
|  21 | Hockey  |
|  24 | NULL  |
|  NULL | Kabaddi  |

a) Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student's preference field?
b) Roll no 17 has given two preferences sports. Which property of relational DBMC is violated here? Can we use any constraint or key in the relational DBMS to check against such violation, if any?
c) Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation?

Accepted Answer

a) Yes, a NULL value can be assigned to the preference field for roll no 24 if the student is not interested in any sport. NULL indicates unknown or no data.

b) The property violated is the 'Entity Integrity' or 'Uniqueness' property because roll no 17 appears twice with different preferences, violating the rule that each student should have only one preference. To prevent this, a PRIMARY KEY or UNIQUE constraint can be applied on the 'Roll no' attribute to ensure uniqueness.

c) Yes, it is possible to have a tuple with NULL roll no and preference 'Kabaddi' if the roll no is unknown or missing. However, this violates referential integrity if 'Roll no' is a foreign key referencing students. Normally, such tuples should not exist unless roll no is optional. — a) NULL values represent missing or unknown data.
b) Primary key constraints enforce uniqueness.
c) Tuples with NULL in key fields may violate integrity constraints.

Question 7

In another class having 2 sections, the two respective class representatives have prepared 2 separate Sports Preferences tables, as shown below:

Sports preference of section 1 (arranged on roll number column)

Table: Sports Preferences
|  Roll no | Sports  |
| --- | --- |
|  9 | Cricket  |
|  13 | Football  |
|  17 | Badminton  |
|  21 | Hockey  |
|  24 | Cricket  |

Sports preference of section 2 (arranged on Sports name column, and column order is also different)

Table: Sports Preferences
|  Sports | Roll no  |
| --- | --- |
|  Badminton | 17  |
|  Cricket | 9  |
|  Cricket | 24  |
|  Football | 13  |
|  Hockey | 21  |

Are the states of both the relations equivalent? Justify.

Accepted Answer

Yes, the states of both relations are equivalent because they contain the same set of tuples, only the order of rows and columns differ. In relational databases, the order of tuples and attributes does not affect the relation's state. Both tables represent the same information about students' sports preferences. — Relational model treats relations as sets of tuples, so order is irrelevant. Column order and tuple order do not affect equivalence of relations.

Question 8

The school canteen wants to maintain records of items available in the school canteen and generate bills when students purchase any item from the canteen. The school wants to create a canteen database to keep track of items in the canteen and the items purchased by students. Design a database by answering the following questions:

a) To store each item name along with its price, what relation should be used? Decide appropriate attribute names along with their data type. Each item and its price should be stored only once. What restriction should be used while defining the relation?

b) In order to generate bill, we should know the quantity of an item purchased. Should this information be in a new relation or a part of the previous relation? If a new relation is required, decide appropriate name and data type for attributes. Also, identify appropriate primary key and foreign key so that the following two restrictions are satisfied:

i) The same bill cannot be generated for different orders.
ii) Bill can be generated only for available items in the canteen.

c) The school wants to find out how many calories students intake when they order an item. In which relation should the attribute 'calories' be stored?

Accepted Answer

a) Relation: ITEM
Attributes: ItemName (VARCHAR), Price (FLOAT)
Restriction: ItemName should be PRIMARY KEY to ensure each item and its price is stored only once.

b) Quantity of items purchased should be stored in a new relation named PURCHASE or BILL_DETAIL.
Attributes: BillNo (INT), ItemName (VARCHAR), Quantity (INT)
Primary Key: Composite key of (BillNo, ItemName)
Foreign Key: ItemName referencing ITEM(ItemName)
This ensures:
i) Same bill number cannot be used for different orders because BillNo identifies the bill uniquely.
ii) Bill can be generated only for available items because ItemName must exist in ITEM relation.

c) The attribute 'calories' should be stored in the ITEM relation as it is a property of the item itself, not of the purchase. — a) Storing item and price once avoids redundancy.
b) Purchase details need a separate relation to handle multiple items per bill.
c) Calories are intrinsic to items, so stored in ITEM relation.

Question 9

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?

Accepted Answer

a) Candidate keys in EMPLOYEE are AadharNumber and EmployeeID because both uniquely identify an employee.

b) To retrieve dependents of a particular employee, use DEPENDENT table with EmployeeID as foreign key referencing EMPLOYEE. The key required is EmployeeID.

c) Degree of EMPLOYEE relation is 5 (AadharNumber, Name, Address, Department, EmployeeID).
Degree of DEPENDENT relation is 3 (EmployeeID, DependentName, Relationship). — a) Candidate keys uniquely identify tuples.
b) Foreign key EmployeeID links DEPENDENT to EMPLOYEE.
c) Degree is number of attributes in relation.

Question 10

School uniform is available at M/s Sheetal Private Limited. They have maintained SCHOOL_UNIFORM Database with two relations viz. UNIFORM and COST. The following figure shows database schema and its state.

School Uniform Database

Attributes and Constraints

Table: COST
| UCode | Size | COST Price |
| 1 | M | 500 |
| 1 | L | 580 |
| 1 | XL | 620 |
| 2 | M | 810 |
| 2 | L | 890 |
| 2 | XL | 940 |
| 3 | M | 770 |
| 3 | L | 830 |
| 3 | XL | 910 |
| 4 | S | 150 |
| 4 | L | 170 |
| 5 | S | 180 |
| 5 | L | 210 |
| 6 | M | 110 |
| 6 | L | 140 |
| 6 | XL | 160 |

a) Can they insert the following tuples to the UNIFORM Relation? Give reasons in support of your answer.

i) 7, Handkerchief, NULL
ii) 4, Ribbon, Red
iii) 8, NULL, White

b) Can they insert the following tuples to the COST Relation? Give reasons in support of your answer.

i) 7, S, 0
ii) 9, XL, 100

Accepted Answer

a) UNIFORM Relation:

i) (7, Handkerchief, NULL): Cannot insert because UColor is NOT NULL as per constraint.
ii) (4, Ribbon, Red): Cannot insert because UCode 4 already exists (primary key violation).
iii) (8, NULL, White): Cannot insert because UName is NOT NULL (from schema, UName is not explicitly NOT NULL but logically should be), if not specified as NOT NULL, then insertion possible but generally name should not be NULL.

b) COST Relation:

i) (7, S, 0): Cannot insert because Price must be > 0 as per constraint.
ii) (9, XL, 100): Cannot insert because UCode 9 does not exist in UNIFORM relation (foreign key violation). — a) Primary key must be unique and NOT NULL fields must have values.
b) Price constraint and foreign key constraints must be satisfied.

Question 11

What is a file in the context of a computer system?

Accepted Answer

A container to store data in a computer — A file is understood as a container to store data in a computer. It can contain text, program code, CSV, images, audio, video, or web pages.

Question 12

Which of the following is NOT a limitation of manual record keeping in schools?

Accepted Answer

Easy and quick data retrieval — Manual record keeping suffers from data loss, errors in calculation, and inconsistencies due to manual entry. Easy and quick data retrieval is not a limitation but a desired feature.

Question 13

Which of the following best describes data redundancy in file systems?

Accepted Answer

Duplication of same data in multiple places — Data redundancy means the same data is duplicated in different files or places, leading to excess storage use and possible inconsistency.

Question 14

Data inconsistency in file systems occurs because:

Accepted Answer

Data is duplicated and not updated uniformly — When the same data is stored in multiple places and updates are not made in all places, data inconsistency occurs.

Question 15

What does data isolation mean in file systems?

Accepted Answer

Data files are stored separately without links — Data isolation means that files are stored independently without any relationship or mapping, making it difficult to access related data across files.

Database Chapter Concepts

Database Chapter Concepts — Study Notes

7.1 INTRODUCTION

7.2 FILE SYSTEM

7.2.1 Limitations of a File System

Practice Questions — Database Chapter Concepts

All 8 Chapters in Informatics Practices