Solution Chapter 12

Relational Databases
Class 12
Computer Science with Python Sumita Arora

Checkpoint 12.1

Question 1(a)

Define relation.
Answer
A relation is a table i.e., data arranged in rows and columns.

Question 1(b)

Define tuple.
Answer
The rows of tables (relations) are called tuples.

Question 1(c)

Define attribute.
Answer
The columns of tables (relations) are called attributes.

Question 1(d)

Define domain.
Answer
A domain is a pool of values from which the actual values appearing
in a given column are drawn.
Question 1(e)

Define primary key.

Answer
A primary key is a set of one or more attributes that can uniquely
identify tuples within the relation.

Question 1(f)

Define candidate key.

Answer
All attribute combinations inside a relation that can serve as primary
key are candidate keys as they are candidates for the primary key
position.

Question 1(g)

Define cartsian product.

Answer
The cartesian product is an operation that combines every row of
one table with every row of another table to create a new table.

Question 1(h)

Define degree.
Answer
The number of attributes in a relation is called degree of a relation.

Question 2

What are views ? How are they useful ?

Answer
A view is a (virtual) table that does not really exist in its own right but
is instead derived from one or more underlying base tables. Views
are useful to view desired information that is actually stored in a base
table and they extend the control we have over our data. They are an
excellent way to give people access to some but not all of the
information in a table.

Question 3(i)

Define primary key.

Answer
A primary key is a set of one or more attributes that can uniquely
identify tuples within the relation.

Question 3(ii)

Define candidate key.

Answer
All attribute combinations inside a relation that can serve as primary
key are candidate keys as they are candidates for the primary key
position.

Question 3(iii)

Define alternate key.

Answer
A candidate key that is not the primary key is called an alternate key.

Question 3(iv)

Define foreign key.

Answer
A non-key attribute, whose values are derived from the primary key
of some other table, is known as foreign key in its current table.

Question 4

What is an Alternate Key ?

Answer
A candidate key that is not the primary key is called an alternate key.
Question 5

What is the importance of a Primary Key in a table ? Explain with a

suitable example.
Answer
The importance of a Primary Key in a table lies in its ability to
uniquely identify tuples (or rows) within the table.

Salesman Number First Name Surname

NO-32 Sandeep Sethi

SO-09 Subhash Kumar

SO-11 Anand Swami

In this table, the "Salesman Number" column can be designated as

the primary key. Each "Salesman Number" value uniquely identifies
a salesperson in the table, and no two salespersons can have the
same number. Additionally, the "Salesman Number" column would
not accept null values, ensuring that every salesperson has a valid
identifier.

Question 6

What do you understand by the terms Primary Key and Degree of a

relation in relational database ?
Answer
A primary key is a set of one or more attributes that can uniquely
identify tuples within the relation. The primary key is non-redundant,
meaning it does not have duplicate values in the same relation, and
non-null attribute, meaning a null value cannot be inserted into it.
The number of attributes in a relation is called Degree of a relation. A
relation having 3 attributes is said to be a relation of degree 3.
Similarly, a relation having n attributes is said to be a relation of
degree n.
Question 7

What do you understand by the terms Candidate Key and Cardinality

of a relation in relational database ?
Answer
All attribute combinations inside a relation that can serve as primary
key are candidate keys as they are candidates for the primary key
position.
The number of rows in a relation is known as cardinality of the
relation.

Multiple Choice Questions

Question 1

A relational database consists of a collection of

1. Tables
2. Fields
3. Records
4. Keys

Answer
Tables
Reason — A relational database consists of a collection of tables,
which are used to organize and store data. Each table consists of
rows and columns, where rows represent individual records or
tuples, and columns represent attributes or fields.

Question 2

A relational database consists of a collection of

1. Tuples
2. Attributes
3. Relations
4. Keys
Answer
Relations
Reason — A relational database consists of a collection of tables,
which are used to organize and store data. These tables are called
relations. Each table consists of rows and columns, where rows
represent individual records or tuples, and columns represent
attributes or fields.

Question 3

A(n) ............... in a table represents a logical relationship among a

set of values.

1. Attribute
2. Key
3. Tuple
4. Entry

Answer
Tuple
Reason — A tuple (rows) in a table represents a logical relationship
among a set of values.

Question 4

The term ............... is used to refer to a record in a table.

1. Attribute
2. Tuple
3. Field
4. Instance

Answer
Tuple
Reason — Tuple (Rows) of the table is used to refer to a record in a
table.
Question 5

The term ............... is used to refer to a field in a table.

1. Attribute
2. Tuple
3. Row
4. Instance

Answer
Attribute
Reason — Attribute (columns) of the table is used to refer to a field
in a table.

Question 6

A ............... is a property of the entire relation, which ensures

through its value that each tuple is unique in a relation.

1. Rows
2. Key
3. Attribute
4. Fields

Answer
Key
Reason — Within the given relation, a set of one or more attributes
having values that are unique within the relation and thus are able to
uniquely identify that tuple, is said to be key of the relation.

Question 7

Which of the following attributes cannot be considered as a choice

for primary key ?

1. Id
2. License number
3. Dept_id
4. Street
Answer
Street
Reason — Attributes "Id," "License number," and "Dept_id" are
unique identifiers and can be suitable choices for a primary key.
However, "Street" might not be unique for each tuple, as multiple
tuples could have the same street value, making it unsuitable for a
primary key.

Question 8

An attribute in a relation is a foreign key if it is the ............... key in

any other relation.

1. Candidate
2. Primary
3. Super
4. Sub

Answer
Primary
Reason — A non-key attribute, whose values are derived from the
primary key of some other table, is known as foreign key in its
current table.

Question 9

Consider the table with structure as :

Student(ID, name, dept name, tot_cred)
In the above table, which attribute will form the primary key ?

1. Name
2. Dept
3. Total_credits
4. ID

Answer
ID
Reason — The "ID" attribute serves as a unique identifier for each
student, making it suitable for use as a primary key.

Question 10

Which of the following is not a legal sub-language of SQL ?

1. DDL
2. QAL
3. DML
4. TCL

Answer
QAL
Reason — A legal sub-language of SQL includes DDL (Data
Definition Language), DML (Data Manipulation Language), and TCL
(Transaction Control Language).

Fill in the Blanks

Question 1

Collection of logically related data tables is called a database.

Question 2

The duplication of data is known as data redundancy.

Question 3

A pool of values wherefrom a field can draw values, is

called domain.

Question 4

A row in a relation is called a tuple.

Question 5

A column in a relation is called an attribute.

Question 6

The number of attributes in a relation is called its degree.

Question 7

The number of tuples in a relation is called its cardinality.

Question 8

An attribute that can uniquely identify each tuple in a relation is

called primary key.

Question 9

A non-key attribute derived from the primary key of some other

relation is called foreign key.

Question 10

A data model wherein data is arranged in tabular forms called

relations and linked through common attributes of relations, is
called relational data model.

True/False Questions

Question 1

A table in a relational database can store empty values.

Answer
True
Reason — In a relational database, a table can store empty values,
represented as NULL.

Question 2

A relation is a table having unordered non-atomic values.

Answer
False
Reason — A relation is a table having ordered atomic values.
Question 3

A primary key can store empty values in it.

Answer
False
Reason — A primary key is non-null, meaning a null value cannot be
inserted into it because such values would violate the uniqueness
constraint required by the primary key.

Question 4

Common attribute of two tables is called a foreign key.

Answer
False
Reason — A common attribute between two tables is not necessarily
called a foreign key. Instead, a non-key attribute, whose values are
derived from the primary key of some other table, is known as foreign
key in its current table. A foreign key is used to represent the
relationship between two tables.

Question 5

A common attribute of two tables is called a foreign key if it is the

primary in one table.
Answer
True
Reason — A non-key attribute, whose values are derived from the
primary key of some other table, is known as a foreign key in its
current table.

Question 6

Part of SQL which creates and defines tables and other database
objects, is called DDL.
Answer
True
Reason — DDL (Data Definition Language) commands are used to
create and define tables and other database objects in SQL
(Structured Query Language). DDL commands such as CREATE,
ALTER, and DROP, are used to create, define, change and delete
objects like tables, indexes, views, and constraints.

Question 7

Part of SQL which manipulates data in tables, is called TCL.

Answer
False
Reason — Part of SQL which manipulates data in tables, is called
DML.

Question 8

Part of SQL which accesses and manipulates data in tables, is called

DML.
Answer
True
Reason — A Data Manipulation Language (DML) is a language that
enables users to access or manipulate data as organized by the
appropriate data model. Hence, part of SQL which accesses and
manipulates data in tables, is called DML. These commands include
SELECT, LOCK TABLE, UPDATE, INSERT INTO, DELETE.

Question 9

Part of SQL which controls transactions, is called TCL.

Answer
True
Reason — Transaction Control Language (TCL) commands in SQL
are used to manage and control transactions. These commands
include COMMIT, ROLLBACK, SET TRANSACTION and
SAVEPOINT.

Question 10

MySQL is the name of a customised query language used by Oracle.

Answer
False
Reason — MySQL is a freely available open source Relational
Database Management System (RDBMS) that uses Structured
Query Language (SQL).

Assertions and Reasons

Question 1

Assertion. A database is centrally stored data and a DBMS is a

system to manage the database.
Reason. DBMS is a database management system, which is a
software managing the databases.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct
explanation of Assertion.
Explanation
A database may be defined as a collection of interrelated data stored
together (centrally) to serve multiple applications. A DBMS (Data
Base Management System) refers to a software that is responsible
for storing, maintaining and utilizing databases. A database along
with a DBMS is referred to as a database system.

Question 2

Assertion. Data redundancy may lead to data inconsistency.

Reason. When redundant data or the multiple copies of data
mismatch, it makes the data inconsistent.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct
explanation of Assertion.
Explanation
Data redundancy refers to the duplication of data in a database. This
may lead to data inconsistency. When redundant data or the multiple
copies of data mismatch, it makes the data inconsistent.

Question 3

Assertion. Data redundancy may lead to many problems.

Reason. In RDBMS, data redundancy is 100% removed.
Answer
(c)
Assertion is true but Reason is false.
Explanation
Data redundancy may lead to many problems. It refers to the
duplication of data in a database, which can result in data
inconsistencies, increased storage requirements, and difficulties in
maintaining data integrity. In RDBMS, data redundancy is minimized
but not completely eliminated.

Question 4

Assertion. A primary key is used to uniquely identify the rows in a

data table.
Reason. A primary key is a field or attribute which has a unique
value for each row or tuple.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct
explanation of Assertion.
Explanation
A primary key is used to uniquely identify the rows in a data table. It
is a set of one or more attributes that can uniquely identify tuples
(rows) within the relation.

Question 5

Assertion. A data table can have only one primary key.

Reason. In a data table, there can be only one attribute/field
containing unique values for each row.
Answer
(c)
Assertion is true but Reason is false.
Explanation
A data table can have only one primary key. There can be more than
one attribute in a relation possessing the unique identification
property. They are known as candidate keys.

Question 6

Assertion. There can be multiple options for choosing a primary key

in a data table.
Reason. All attribute combinations inside a data table that contain
unique values for each row, are the candidate keys.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct
explanation of Assertion.
Explanation
There can be more than one attribute in a relation possessing the
unique identification property. All attribute combinations inside a
relation that can serve as a primary key are candidate keys, as they
are candidates for the primary key position.
Question 7

Assertion. All types of keys contain unique values for each row.
Reason. A foreign-key attribute of a table is the primary key of
another table.
Answer
(d)
Assertion is false but Reason is true.
Explanation
Not all types of keys necessarily contain unique values for each row.
While primary keys ensure uniqueness for each row in a table, other
types of keys, such as foreign keys and candidate keys, may not
guarantee uniqueness. A non-key attribute, whose values are
derived from the primary key of some other table, is known as foreign
key in its current table.

Question 8

Assertion. The foreign-keys of tables are used to establish

relationships with other tables and must be handled carefully.
Reason. Referential integrity is a system of rules that a DBMS uses
to ensure that the relationships between tables remain valid and no
accidental change or deletion occurs in the related data.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct
explanation of Assertion.
Explanation
The foreign keys of tables are utilized to establish relationships with
other tables, while referential integrity is a system of rules that a
DBMS employs to ensure the validity of relationships between
records in related tables. This system prevents users from
accidentally deleting or changing related data. Therefore, it is crucial
to handle foreign keys carefully.
Question 9

Assertion. A unique value that identifies each row uniquely is the

primary key.
Reason. Only one column can be made the primary key.
Answer
(c)
Assertion is true but Reason is false.
Explanation
A primary key is a set of one or more attributes (columns) that can
uniquely identify tuples within the relation. When a primary key is
made up of two or more attributes, it is called as composite primary
key. Hence, the reason is false.

Assignments

Question 1

Summarize the major differences between a relation and a traditional

file.
Answer

Relation file Traditional file

Data organized in tables with Data stored in unstructured

rows and columns. formats.

Supports structured querying Lacks standardized querying

with SQL. abilities.

Allows for defining relationships No inherent support for

between tables. relationships.

Offers flexibility in data storage Limited flexibility in data

and retrieval. organisation.
 Relation file Traditional file

Examples : MySQL, Examples : Text files, CSV files,

PostgreSQL Excel spreadsheets

Question 2(i)

Define database.
Answer
A database is defined as a collection of interrelated data stored
together to serve multiple applications.

Question 2(ii)

Define SQL.
Answer
The Structured Query Language (SQL) is a language that enables us
to create and operate on relational databases (RDBMS), which are
sets of related information stored in tables.

Question 2(iii)

Define view.
Answer
A view is a (virtual) table that does not really exist in its own right but
is instead derived from one or more underlying base tables.

Question 3

What is data redundancy ? How does it impact a database ?

Answer
Duplication of data is known as data redundancy. Data redundancy
in a database leads to wasted storage and multiple copies of the
same data. When these copies do not match with one another, it
leads to data inconsistency. Additionally, data redundancy can result
in performance degradation, security risks, and increased
complexity.

Question 4

What is data inconsistency ? How does it impact a database ?

Answer
Mismatched multiple copies of same data is known as data
inconsistency. Data inconsistency undermines database reliability,
hindering decision-making, causing operational errors, and
increasing complexity.

Question 5(i)

Define tuple.
Answer
The rows of tables (relations) are called tuples.

Question 5(ii)

Define attribute.
Answer
The columns of tables (relations) are called attributes.

Question 5(iii)

Define domain.
Answer
A domain is a pool of values from which the actual values appearing
in a given column are drawn.

Question 5(iv)

Define degree.
Answer
The number of attributes in a relation is called degree of a relation.
Question 5(v)

Define cardinality.
Answer
The number of rows in a relation is known as cardinality of the
relation.

Question 6(i)

Define primary key.

Answer
A primary key is a set of one or more attributes that can uniquely
identify tuples within the relation.

Question 6(ii)

Define foreign key.

Answer
A non-key attribute, whose values are derived from the primary key
of some other table, is known as foreign key in its current table.

Question 7

What is MySQL ? What are its functions and features ?

Answer
MySQL is a freely available open source Relational Database
Management System (RDBMS) that uses Structured Query
Language (SQL). MySQL provides us with a rich set of features that
support a secure environment for storing, maintaining and accessing
data.
The functions and features of MySQL are as follows :

1. Speed — If the server hardware is optimal, MySQL runs very

fast. It supports clustered servers for demanding applications.
2. Ease of use — MySQL is a high performance, relatively simple
database system. From the beginning, MySQL has typically
 been configured, monitored and managed from the command
line. However, several MySQL graphical interfaces are also
available.
3. Query Language Support — MySQL understands standards
based SQL.
4. Portability — MySQL provides portability as it has been tested
with a broad range of different compilers and can work on many
different platforms. It is fully multi-threaded using kernel
threads. It can easily use multiple CPUs if they are available.
5. Cost — MySQL is available free of cost. MySQL is a open
source database.
6. Data Types — MySQL provides many data types to support
different types of data. It also supports fixed-length and
variable-length records.
7. Security — MySQL offers a privilege and password system
that is very flexible and secure, and that allows host-based
verification. Passwords are secure because all password traffic
is encrypted when we connect to a server.
8. Scalability and Limits — MySQL can handle large databases.
Some real life MySQL databases contain 50 million records,
some have up to 60,000 tables and about 5,000,000,000 rows.
9. Connectivity — Clients can connect to MySQL Server using
several protocols.
10. Localization — The server can provide error messages
to clients in many languages.
11. Clients and Tools — MySQL provides several client and
utility programs. These include both command-line programs
such as mysqldump and mysqladmin, and graphical programs
such as MySQL Administrator and MySQL Query Browser.
MySQL Server has built-in support for SQL statements to
check, optimize and repair tables.

Question 8

What is the role of database server in database management system

? Give the key features of MySQL.
Answer
A database server is the key to solving the problems of database
management system (information system). In general, a server must
reliably manage a large amount of data in a multi-user environment
so that many users can concurrently access the same data. A
database server must also prevent unauthorized access and provide
efficient solutions for failure recovery.
The key features of MySQL are as follows :

1. Speed — If the server hardware is optimal, MySQL runs very

fast. It supports clustered servers for demanding applications.
2. Ease of use — MySQL is a high performance, relatively simple
database system. From the beginning, MySQL has typically
been configured, monitored and managed from the command
line. However, several MySQL graphical interfaces are also
available.
3. Query Language Support — MySQL understands standards
based SQL.
4. Portability — MySQL provides portability as it has been tested
with a broad range of different compilers and can work on many
different platforms. It is fully multi-threaded using kernel
threads. It can easily use multiple CPUs if they are available.
5. Cost — MySQL is available free of cost. MySQL is a open
source database.
6. Data Types — MySQL provides many data types to support
different types of data. It also supports fixed-length and
variable-length records.
7. Security — MySQL offers a privilege and password system
that is very flexible and secure, and that allows host-based
verification. Passwords are secure because all password traffic
is encrypted when we connect to a server.
8. Scalability and Limits — MySQL can handle large databases.
Some real life MySQL databases contain 50 million records,
some have up to 60,000 tables and about 5,000,000,000 rows.
9. Connectivity — Clients can connect to MySQL Server using
several protocols.
10. Localization — The server can provide error messages
to clients in many languages.
11. Clients and Tools — MySQL provides several client and
utility programs. These include both command-line programs
such as mysqldump and mysqladmin, and graphical programs
such as MySQL Administrator and MySQL Query Browser.
 MySQL Server has built-in support for SQL statements to
check, optimize and repair tables.

Question 9

What is the use of SQL in MySQL ?

Answer
All programs and users accessing data within the MySQL database
must utilize Structured Query Language (SQL). MySQL is compatible
with standard-based SQL, enabling it to understand and process
SQL commands efficiently. Additionally, the MySQL server
incorporates built-in support for executing SQL statements, allowing
users to perform tasks such as checking, optimizing, and repairing
tables.

Question 10

How are SQL commands classified ?

Answer
SQL commands can be divided into the following categories :

1. Data Definition Language (DDL) Commands

2. Data Manipulation Language (DML) Commands
3. Transaction Control Language (TCL) Commands
4. Session Control Commands
5. System Control Commands

Question 11

What functions should be performed by ideal DDL ?

Answer
An ideal DDL should perform the following functions :

1. It should identify the types of data division such as data item,

segment, record and data-base file.
2. It should give a unique name to each data-item-type, record-
type, file-type, database and other data subdivision.
 3. It should specify the proper data types.
4. It should specify how the record types are related to make
structures.
5. It may define the type of encoding the program uses in the data
items (binary, character, bit, string etc.). This should not be
confused with the encoding employed in physical
representation.
6. It may define the length of the data items.
7. It may define the range of values that a data-item can assume.
8. It may specify means of checking for errors in the data.
9. It may specify privacy locks for preventing unauthorized reading
or modification of the data.
10. A logical data definition should not specify addressing,
indexing or searching techniques or specify the placement of
data on the storage units, because these topics are in the
domain of physical, not logical, organization.

Question 12

Differentiate between DDL and DML commands.

Answer

Data Manipulation
Data Definition Language (DDL)
Language (DML)

DML is a language that

DDL provides a set of definitions to enables users to access or
specify the storage structure and access manipulate data as
methods used by the database system. organized by the
appropriate data model.

DDL commands are used to perform

tasks such as creating, altering, and DML commands are used
dropping schema objects. They are also to retrieve, insert, delete,
used to grant and revoke privileges and modify data stored in the
roles, as well as for maintenance database.
commands related to tables.
 Data Manipulation
Data Definition Language (DDL)
Language (DML)

Examples of DML
Examples of DDL commands are
commands are INSERT,
CREATE, ALTER, DROP, GRANT,
UPDATE, DELETE,
ANALYZE etc.
SELECT etc.

Question 13

Name some commands used to assign/revoke privileges from

database users.
Answer
Commands used to assign/revoke privileges from database users
are GRANT, REVOKE.

Question 14

Name some table maintenance commands.

Answer
Table maintenance commands are ANALYZE TABLE, CHECK
TABLE, REPAIR TABLE, RESTORE TABLE.

Question 15

What is TCL part of SQL ?

Answer
TCL part of SQL includes commands for specifying the beginning
and ending of transactions along with commands to have control
over transaction processing. Some examples of TCL commands are
COMMIT, ROLLBACK, SET TRANSACTION and SAVEPOINT.
These commands manage changes made by DML commands.