PART-B UNIT-3: DATABASE MANAGEMENT SYSTEMS

Q1. What is a Database? Mention the default extension of a database file in BASE.
A database is an organized collection of data. It is considered as a container of information. In the manual
system, you would maintain several files with different bits of information while in the computerized
system you would use database programs such as Microsoft Access, OpenOffice.org Base, and MySQL, to
organize the data as per your business need.
The file extension of a BASE database is .odb
Q2. What is DBMS? What is it’s use?

DBMS is a collection of programs that enables users to create, maintain database and control all the access
to the database. It is a computer-based record keeping system.

The primary goal of the DBMS is to provide an environment that is convenient and efficient for user to
retrieve and store information. It acts as an interface between the application program and the data stored
in the database. It maintains data consistency in case of multiple users. It bridges the gap between
information and data.
Q3. What is the difference between flat and relational databases?
OR
Into how many ways can data be organized in a database?
Data can be organized into two types:

 Flat: Data is stored in a single table. Usually suitable for less amount of data.
 Relational: Data is stored in multiple tables and the tables are linked using a common field.
Relational is suitable for medium to large amount of data.
Q4. What are Database Servers?
Database servers are dedicated computers that hold the actual databases and run only the DBMS and
related software.
Q5. Mention the different advantages of using a database.

 Reduce Data Redundancy: Redundancy means ‘duplication of data’. This eliminates the replication of
data item in different files. DBMS reduces data redundancy by maintaining a centralized access to data.
 Control data Inconsistency: Since access to data is centralized in DBMS, it controls redundancy. When
redundancy reduces, the chances of inconsistency also reduce.
 Enforcing Data Integrity: It means that, the data contained in the database is accurate and consistent.
Integrity constraints or consistency rules can be applied to database, so that the correct data can be
entered into the database.
 Ensures Data security: DBMS ensures data security and privacy by carrying authorization checks and
allowing only authorized users to access databases
 Backup and Recovery: The DBMS provides backup and recovery sub-system that is responsible to
recover data from hardware and software failures.
 Data Sharing: The data stored in the database can be shared among multiple users or application
programs
Q6. Enlist some features/ elements of a database.
 Table: A database can have one or many tables. A table is a set of values organised into vertical columns
and horizontal rows. Each table in a database contains information about one type of item.
 Record/ Rows: rows representing one complete unit of information. A logical record consists of contents
for all fields. It represents a single data item in a table.
 Field/ Attribute/ Columns: A column is a set of data values of a particular simple type, one for each row
of the table. The columns provide the structure according to which the rows are composed

Q7. What is the difference between ‘Degree’ and ‘Cardinality’?

 Degree: refers to the no. of attributes/columns in a table.

 Cardinality: refers to the no. of rows/ tuples.
Q8. What are the different types of keys/ key fields available in a database?
1) Primary Key: a field that is capable of uniquely identifying records in a table. Only one column attribute
can be defined as a primary key for each table.
A primary key must possess the following properties:
i) It does not allow null values
ii) It has a unique index
In the example below, the fields ‘AdmNo’ is the primary key as it uniquely identifies each record.

AdmNo Name Marks

1001 Greg 85
1002 John 95
1003 Ben 75

2) Composite Primary Key: A combination of field values which is unique in a table and capable of
identifying records uniquely. In other words, a primary key which consists of a combination of two or more
fields/ columns is a composite primary key. In the example given below, the combination of the fields
‘RollNo.’ And ‘Subject Taken’ is capable of identifying a record uniquely.

RollNo. Subject Taken Name

1 Maths Reggie
2 English Mark
1 English Reggie
2 Science Mark

3) Foreign Key: Sometimes in your current table, you have a particular field which is NOT the primary key in
the current table, but is the primary key of another table. That field is known as the foreign key of the
current table. A field of a table (relation) that references the primary key of another table is referred to as
foreign key. The relationship between two tables is established with the help of foreign key. A table may
have multiple foreign keys and each foreign key can have a different referenced table.
In the example given below, DeptID is the primary key in Department table, but is the foreign key in
Employee table.
 EmpID EmpName DeptID DeptID DeptName
101 Alex 1 1 Sales
102 Kate 1 2 IT
103 Jane 2 3 Admin
Employee Table Department Table
4) Alternate Key: In a table, there may be more than one fields having unique values i.e. more than one
fields are capable of becoming the primary key. In that case there is only one field that becomes the
primary key, and other such fields become the alternate keys. In the example given below, the fields
‘EmpID’, ‘PAN Card No.’ and ‘Aadhar Card No.’ contain unique values and all three fields are capable of
becoming the primary key. But only one field, ‘EmpID’ becomes the Primary key, thus making the fields
‘PAN Card No.’ and ‘Aadhar Card No.’ as alternate keys.

EmpID PAN Card No. Aadhar Card No. EmpName

1001 ABCDE12345 123456789102 Rohan
1002 VWXYZ24967 978462513058 Seema
1003 SDFGH98764 537916482084 Shyam

5) Candidate Key: The primary key along with the alternate keys are known as the candidate keys i.e. all
possible fields or field-combinations which are eligible to become the primary key are known as
candidate keys. In the example given above, the three fields ‘EmpID’, ‘PAN Card No.’ and ‘Aadhar Card No.’
contain unique values and are capable of becoming the primary key, thus these become the candidate keys
for the table.
Q9. How is a Primary Key different from a Foreign key?

FOREIGN KEY PRIMARY KEY

A field or a group of fields in a relational database A special field in a table or a combination of
table that provides an association between data in multiple fields that allows to uniquely identify each
two tables. record of table.
A table can have multiple foreign keys A table can have only one primary key

Q10. What are the features/ different elements of LibreOffice BASE?

TABLE: A database can have one or many tables. A table is a set of values organised into vertical columns
and horizontal rows. Each table in a database contains information about one type of item.
QUERY: A query is a command that helps perform operations like retrieval and manipulation of information
in a database.
FORM: A form provides the user a systematic way of storing information into the database. It is an interface
in a user specified layout that lets users to view, enter and change data directly in database objects such as
tables.
REPORT: A report helps to display the data in a summarized manner. It is used to generate the overall work
outcome in a clear format.
 Q11. What is the difference between a Primary Key and a Composite Key?

PRIMARY KEY COMPOSITE KEY

A primary key is a single column that uniquely A composite primary key is a combination of two or
identifies each record in a table. more columns that together uniquely identify each
record.
Consists of one column. Consists of two or more columns.
Eg: EmployeeID Eg: (first_name, last_name)

Q12. What is the relationship between a Primary Key, Candidate Key and Alternate Key?
A Primary Key is one of the Candidate Keys, that is selected to uniquely identify records. All the Alternate
Keys are Candidate Keys that were not chosen as the Primary Key.
Q13. What are the steps to design a database?
1. Determine purpose of your database
2. Determine the tables you need
3. Determine the fields you need
4. Identify the fields/fields which have unique values for each record
5. Determine relationships between tables
6. Refine your design
7. Enter data and create other database objects
Q14. What are Field Properties? How is it related to Data Validation? Discuss the different field
properties used during table creation.
Field properties describe the characteristics of a field in the table. When you set one or more field
properties, you are defining the rules/ norms for data which is allowed in the table. Thus, you set the
foundation for data validation. Data Validation refers to the process of ensuring that only valid data as per
set norms is entered in the table.
• Entry Required: this means that data cannot be skipped for that field. If this property is skipped, then
value not entered will be treated as NULL value which is the legal way to represent an empty or missing
value.
• Length: this provides the maximum length of data that the fields can have
• Decimal Places: it specifies the number of decimal places permitted in the field
• Default Value: it specifies a default value for a particular field i.e. the default value gets entered in case a
value has not been entered for that field.

• Format: it used to control the look of the data in a field.

Q15. What are datatypes? Explain the different datatypes available in DBMS.
Datatypes define the type of data which can be stored in each field. There are different datatypes which are
available:

 Numeric Types
 Alphanumeric Types
 Date time
 Boolean
 Binary
 1) NUMERIC:

2) TEXT/ ALPHANUMERIC:

3) DATE: range 1/1/99 to 1/1/9999

4) TIME: seconds since 1/1/1970

5) BOOLEAN: stores yes/no or true/false values

6) BINARY: stores data in binary format i.e. as 0s and 1s. This can be used for storing photos, music files
etc.

Q16. What is the difference between CHAR and VARCHAR datatypes?

CHAR VARCHAR
Full form Stands for Character datatype Stands for Variable Character datatype
Definition Fixed length of characters Variable length of character
Storage Allocates fixed amount of space regardless Allocates space based on actual data size
of data size
Length The length is fixed, and padding is done The length is variable, storing only the actual
with spaces if the string is shorter than the data length. No padding done
defined length.
Memory Consumes the full amount of space Only uses the space needed for the actual
Usage defined, even if the string is shorter. data

Q17. What is Referential Integrity? How is it useful?

Referential Integrity is a system of rules that a DBMS uses to ensure that relationships between records in
related tables are valid and that users do not accidentally delete or change related data. It is used to
maintain accuracy and consistency of data in a relationship. It helps to avoid:
 Adding records to a related table if there is no associated record available in the primary key table.
 Changing values in a primary if any dependent records are present in associated table(s).
 Deleting records from a primary key table if there are any matching related records available in
associated table(s).
The following conditions need to be met for Referential Integrity:
• Matching field from Primary Table is a Primary Key or has a unique index
• Related fields have same datatype
• Both tables have same database
Q18. What is a relationship? How is it useful?
A relationship refers to an association/ connection between two or more tables. Relationships help to:

 Save time as there is no need to enter the same data in separate tables
 Reduce data entry errors
 Summarize data from related tables
Q19. Discuss in detail the different relationships possible between tables.
Assume that we have two related tables, Table A and Table B
1) One-to-One: each record in Table-A can have only one matching record in Table-B and each record in
Table-B can have only one matching record in Table-A.

2) One-to-Many: a record in Table-A can have multiple matching records in Table-B, but a record in Table-B
can have only one matching record in Table-A. Depending on perspective, you can term it as one-to-
many (1:N) or many-to-one (N:1)

3) Many-to-Many: a record in Table-A can have multiple matching records in Table-B and a record in Table-
B can have multiple matching records in Table-A. This type of relationship is possible by defining a third
table called junction table which consists of two fields i.e. the foreign keys from both tables A and B.
In the example below, one teacher can teach multiple subjects and one subjects can be taught by many
teachers.
Q20. Into which categories can queries be classified? Explain each one in detail.
1. DDL (Data Definition Language): DDL is used to define the structure of your tables and other
objects in the database. In SQL, the Data Definition Language allows you to create, alter and destroy
database objects. Basically, it allows user to create and modify the structure of database objects in a
database.
Data definition language consists of various commands that lets you to perform some specified
tasks as follows:
 CREATE: Uses to create objects in the database.
 ALTER: Uses to alter the structure of the database table. This command can add up
additional columns, drop existing columns and even change the data type of columns
involved in a database table.
 DROP: Uses to delete objects from the database.
 TRUNCATE: Uses to remove all records from a table.
 RENAME: Uses to rename an object.
2. DML (Data Manipulation Language): commands which allows you to manipulate data ie retrieve
data, insert new records, deletion and modification of data in a database. It provides various commands
used to access and manipulate data in existing database. This manipulation involves inserting data into
database tables, retrieving existing data, deleting data from existing tables and modifying existing data.
Various data manipulation language commands are as follows
 SELECT: Used to retrieve data from a database.
 INSERT: Used to insert data into a table.
 UPDATE: Used to update existing data within a table.
 DELETE: Used to delete all records from a table, the space of the records remains.
3. TCL (Transaction Control Language): commands that allow you to manage and control transactions
(Transaction is one complete unit of work involving many steps) like
 Making and undoing changes to a database
 Creating save-points (checkpoints)

Q21. What is the difference between DDL and DML commands?

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

Used to define, modify, and remove database Used to manipulate and manage data within tables.
structures (like tables, views etc.)
CREATE, ALTER, DROP, TRUNCATE, RENAME SELECT, INSERT, UPDATE, DELETE
Affects the structure of the database. Affects the content of the database (rows of tables).

Q22. What is the difference between DROP and DELETE?

DROP DELETE
It is a DDL command It is a DML command
Used to remove a database object (like a table, Used when you want to delete specific rows from
view, or index) completely from the database. a table without affecting the table structure.
Syntax: DROP TABLE <table_name>; Syntax: DELETE FROM <table_name> WHERE
<condition>;