ARISE INTERNATIONAL SCHOOL OF EXCELLENCE
STD : XII
SUB : COMPUTER SCIENCE
Chapter 10 (Relational Database)
Assignment SOLUTIONS
1.Summarize the major differences between a relation and a traditional file.
Ans:
Relation file Traditional file
Data organized in tables with rows and
Data stored in unstructured formats.
columns.
Supports structured querying with SQL. Lacks standardized querying abilities.
Allows for defining relationships
No inherent support for relationships.
between tables.
Offers flexibility in data storage and
Limited flexibility in data organisation.
retrieval.
Examples : Text files, CSV files, Excel
Examples : MySQL, PostgreSQL
spreadsheets
2(i). Define database.
Ans : A database is defined as a collection of interrelated data stored together
to serve multiple applications.
(ii) Define SQL.
Ans : 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.
(iii) Define view.
Ans : 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.
3. What is data redundancy ? How does it impact a database ?
Ans : 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.
4. What is data inconsistency ? How does it impact a database ?
Ans : 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.
5 Define the term Domain with respect to RDBMS. Give one example to
support your answer.
Ans : In RDBMS (Relational Database Management System), a Domain is the
set of all possible valid values that an attribute (column) can take.
It defines the data type, format, and range of values allowed for a particular
attribute in a relation.
A domain ensures data integrity by restricting invalid or inconsistent values.
Example:
If we define the attribute Age in a "Student" table, its domain may be INTEGER
values between 5 and 25.
So, values like 10, 15, 20 are valid, but values like -3, 30, or "abc" are not
allowed.
6(i) Define tuple.
Ans : The rows of tables (relations) are called tuples.
(ii) Define attribute.
Ans : The columns of tables (relations) are called attributes.
(iii) Define domain.
Ans : A domain is a pool of values from which the actual values appearing in a
given column are drawn.
(iv) Define degree.
Ans : The number of attributes in a relation is called degree of a relation.
(v) Define cardinality.
Ans : The number of rows in a relation is known as cardinality of the relation.
7(i) Define primary key.
Ans : A primary key is a set of one or more attributes that can uniquely
identify tuples within the relation.
(ii) Define foreign key.
Ans : 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.
8. What is MySQL ? What are its functions and features ?
Ans : 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.
9. What is the role of database server in database management system ? Give
the key features of MySQL
Ans : 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.
10. What is the use of SQL in MySQL ?
Ans : 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.
11.How are SQL commands classified ?
Ans : 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
12.What functions should be performed by ideal DDL ?
Ans : 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.
13.Differentiate between DDL and DML commands.
Ans :
Data Manipulation
Data Definition Language (DDL)
Language (DML)
DML is a language that
DDL provides a set of definitions to specify enables users to access or
the storage structure and access methods manipulate data as
used by the database system. organized by the
appropriate data model.
DDL commands are used to perform tasks
such as creating, altering, and dropping DML commands are used to
schema objects. They are also used to grant retrieve, insert, delete,
and revoke privileges and roles, as well as modify data stored in the
for maintenance commands related to database.
tables.
Examples of DML
Examples of DDL commands are CREATE, commands are INSERT,
ALTER, DROP, GRANT, ANALYZE etc. UPDATE, DELETE, SELECT
etc.
14. Name some commands used to assign/revoke privileges from database
users.
Ans : Commands used to assign/revoke privileges from database users are
GRANT, REVOKE.
15. Name some table maintenance commands.
Ans : Table maintenance commands are ANALYZE TABLE, CHECK TABLE,
REPAIR TABLE, RESTORE TABLE.
16. What is TCL part of SQL ?
Ans : 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.
