Relational Databases

Solutions for Computer Science, Class 12, CBSE

Assertions And Reasons

9 questions

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.

Assertions And Reasons

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.

Assertions And Reasons

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.

Assertions And Reasons

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.

Assertions And Reasons

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.

Assertions And Reasons

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.

Assertions And Reasons

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.

Assertions And Reasons

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.

Assertions And Reasons

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.

Assertions And Reasons

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

22 questions

Question 1

Summarize the major differences between a relation and a traditional file.

Assignments

Answer:

Relation fileTraditional file
Data organized in tables with rows and columns.Data stored in unstructured formats.
Supports structured querying with SQL.Lacks standardized querying abilities.
Allows for defining relationships between tables.No inherent support for relationships.
Offers flexibility in data storage and retrieval.Limited flexibility in data organisation.
Examples : MySQL, PostgreSQLExamples : Text files, CSV files, Excel spreadsheets

Question 2(i)

Define database.

Assignments

Answer:

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

Question 2(ii)

Define SQL.

Assignments

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.

Assignments

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 ?

Assignments

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 ?

Assignments

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.

Assignments

Answer:

The rows of tables (relations) are called tuples.

Question 5(ii)

Define attribute.

Assignments

Answer:

The columns of tables (relations) are called attributes.

Question 5(iii)

Define domain.

Assignments

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.

Assignments

Answer:

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

Question 5(v)

Define cardinality.

Assignments

Answer:

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

Question 6(i)

Define primary key.

Assignments

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.

Assignments

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 ?

Assignments

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.

Assignments

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 ?

Assignments

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 ?

Assignments

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 ?

Assignments

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.

Assignments

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 are CREATE, ALTER, DROP, GRANT, ANALYZE etc.Examples of DML commands are INSERT, UPDATE, DELETE, SELECT etc.

Question 13

Name some commands used to assign/revoke privileges from database users.

Assignments

Answer:

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

Question 14

Name some table maintenance commands.

Assignments

Answer:

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

Question 15

What is TCL part of SQL ?

Assignments

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.

Checkpoint 121

17 questions

Question 1(a)

Define relation.

Checkpoint 121

Answer:

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

Question 1(b)

Define tuple.

Checkpoint 121

Answer:

The rows of tables (relations) are called tuples.

Question 1(c)

Define attribute.

Checkpoint 121

Answer:

The columns of tables (relations) are called attributes.

Question 1(d)

Define domain.

Checkpoint 121

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.

Checkpoint 121

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.

Checkpoint 121

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.

Checkpoint 121

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.

Checkpoint 121

Answer:

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

Question 2

What are views ? How are they useful ?

Checkpoint 121

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.

Checkpoint 121

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.

Checkpoint 121

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.

Checkpoint 121

Answer:

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

Question 3(iv)

Define foreign key.

Checkpoint 121

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 ?

Checkpoint 121

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.

Checkpoint 121

Answer:

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

Salesman NumberFirst NameSurname
NO-32SandeepSethi
SO-09SubhashKumar
SO-11AnandSwami

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 ?

Checkpoint 121

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 ?

Checkpoint 121

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.

Fill In The Blanks

10 questions

Question 1

Collection of logically related data tables is called a database.

Fill In The Blanks

Answer:

Question 2

The duplication of data is known as data redundancy.

Fill In The Blanks

Answer:

Question 3

A pool of values wherefrom a field can draw values, is called domain.

Fill In The Blanks

Answer:

Question 4

A row in a relation is called a tuple.

Fill In The Blanks

Answer:

Question 5

A column in a relation is called an attribute.

Fill In The Blanks

Answer:

Question 6

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

Fill In The Blanks

Answer:

Question 7

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

Fill In The Blanks

Answer:

Question 8

An attribute that can uniquely identify each tuple in a relation is called primary key.

Fill In The Blanks

Answer:

Question 9

A non-key attribute derived from the primary key of some other relation is called foreign key.

Fill In The Blanks

Answer:

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.

Fill In The Blanks

Answer:

Multiple Choice Questions

10 questions

Question 1

A relational database consists of a collection of

  1. Tables
  2. Fields
  3. Records
  4. Keys
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

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
Multiple Choice Questions

Answer:

QAL

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

Truefalse Questions

10 questions

Question 1

A table in a relational database can store empty values.

Truefalse Questions

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.

Truefalse Questions

Answer:

False

Reason — A relation is a table having ordered atomic values.

Question 3

A primary key can store empty values in it.

Truefalse Questions

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.

Truefalse Questions

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.

Truefalse Questions

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.

Truefalse Questions

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.

Truefalse Questions

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.

Truefalse Questions

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.

Truefalse Questions

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.

Truefalse Questions

Answer:

False

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