RELATIONAL DATABASES
Question 1
A relational database consists of a collection of
1. Tables
2. Fields
3. Records
4. Keys
Question 2
A relational database consists of a collection of
1. Tuples
2. Attributes
3. Relations
4. Keys
Question 3
A(n) ............... in a table represents a logical relationship among a set of values.
1. Attribute
2. Key
3. Tuple
4. Entry
Question 4
The term ............... is used to refer to a record in a table.
1. Attribute
2. Tuple
3. Field
4. Instance
Question 5
The term ............... is used to refer to a field in a table.
1. Attribute
2. Tuple
3. Row
4. Instance
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
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
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
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
Question 10
Which of the following is not a legal sub-language of SQL ?
1. DDL
2. QAL
3. DML
4. TCL
Question 11
Define the following:
Relation, Tuple, Attribute, Key, Cardinality, Degree, Domain, Primary Key, Alternate
Key, Candidate Key, Foreign Key
Question 12
Write down features of the following:
DDL, Relation, DML, SQL
Question 13
In SQL, a relation consists of 5 columns and 6 rows. If 2 columns and 3 rows are added
to the existing relation, what will be the updated degree of a relation?
Question 14
If a table which has one Primary key and two alternate keys. How many Candidate keys
will this table have? 3
Question 15
In MYSQL database, if a table, Alpha has degree 5 and cardinality 3, and another table,
Beta has degree 3 and cardinality 5, what will be the degree and cardinality of the
Cartesian product of Alpha and Beta?
Question 16
Which of the following statements is FALSE about keys in a relational database?
a. Any candidate key is eligible to become a primary key.
b. A primary key uniquely identifies the tuples in a relation.
c. A candidate key that is not a primary key is a foreign key.
d. A foreign key is an attribute whose value is derived from the primary key of another
relation.
TRUE & FALSE QUESTIONS
Question 1
A table in a relational database can store empty values.
Answer
True
Question 2
A relation is a table having unordered non-atomic values.
Answer
False
Question 3
A primary key can store empty values in it.
Answer
False
Question 4
Common attribute of two tables is called a foreign key.
Answer
False
Question 5
A common attribute of two tables is called a foreign key if it is the primary in one table.
Answer
True
Question 6
Part of SQL which creates and defines tables and other database objects, is called DDL.
Answer
True
Question 7
Part of SQL which manipulates data in tables, is called TCL.
Answer
False
Question 8
Part of SQL which accesses and manipulates data in tables, is called DML.
Answer
True
Question 9
Part of SQL which controls transactions, is called TCL.
Answer
True
Question 10
MySQL is the name of a customised query language used by Oracle.
Answer
False
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.
QUESTIONS & ANSWERS
Question 1
Summarize the major differences between a relation and a traditional file.
Answer
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 :
Speed — If the server hardware is optimal, MySQL runs very fast. It supports clustered
servers for demanding applications.
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.
Query Language Support — MySQL understands standards based SQL.
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.
Cost — MySQL is available free of cost. MySQL is a open source database.
Data Types — MySQL provides many data types to support different types of data. It
also supports fixed-length and variable-length records.
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.
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.
Connectivity — Clients can connect to MySQL Server using several protocols.
Localization — The server can provide error messages to clients in many languages.
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 :
Speed — If the server hardware is optimal, MySQL runs very fast. It supports clustered
servers for demanding applications.
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.
Query Language Support — MySQL understands standards based SQL.
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.
Cost — MySQL is available free of cost. MySQL is a open source database.
Data Types — MySQL provides many data types to support different types of data. It
also supports fixed-length and variable-length records.
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.
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.
Connectivity — Clients can connect to MySQL Server using several protocols.
Localization — The server can provide error messages to clients in many languages.
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 :
Data Definition Language (DDL) Commands
Data Manipulation Language (DML) Commands
Transaction Control Language (TCL) Commands
Session Control Commands
System Control Commands
Question 11
What functions should be performed by ideal DDL ?
Answer
An ideal DDL should perform the following functions :
It should identify the types of data division such as data item, segment, record and data-
base file.
It should give a unique name to each data-item-type, record-type, file-type, database
and other data subdivision.
It should specify the proper data types.
It should specify how the record types are related to make structures.
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.
It may define the length of the data items.
It may define the range of values that a data-item can assume.
It may specify means of checking for errors in the data.
It may specify privacy locks for preventing unauthorized reading or modification of the
data.
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 Definition Language (DDL) Data Manipulation Language (DML)
DDL provides a set of definitions to specify the storage structure and access methods
used by the database system. DML is a language that enables users to access or
manipulate data as organized by the appropriate data model.
DDL commands are used to perform tasks such as creating, altering, and dropping
schema objects. They are also used to grant and revoke privileges and roles, as well as
for maintenance commands related to tables. DML commands are used to retrieve,
insert, delete, modify data stored in the database.
Examples of DDL commands = CREATE, ALTER, DROP, GRANT, ANALYZE
Examples of DML commands = INSERT, UPDATE, DELETE, 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?Is it a
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.
RELAT
IONAL
DATAB
ASES