Chapter 3 Database Management System

1. Differentiate between DBMS and RDBMS.

A database management system is a software package with computer

programs that controls the creation, maintenance, and use of a database. It
allows organizations to conveniently develop databases for various
applications.

RDBMS: Data is stored in multiple tables and the tables are linked using a
common field. Relational is suitable for medium to large amounts of data.
The software takes care of describing data structures for storing the data
and retrieval procedures for answering queries.

2. How is data organized in a RDBMS?

Data in a relational database management system (RDBMS) is organized in

the form of tables which are related to each other.

Each row (or tuple) in a table represents a set of related data, and every row
in the table has the same structure. While the rows actually contain data in
a related fashion, the columns actually contain data of a similar kind i.e.,
data in a column is always of a similar kind.

Datatypes are used to identify which type of data (value) we are going to
store in the database.

3. Define the following terms:

a) Fields: a field describes the attributes of a data item. E.g. roll_no,

first_name, last_name.

b) A column is a set of data values of a particular simple type, one for

each row of the table.

c) Record: a record is a set of information which includes all fields that

belong to a data item. (one row/tuple in a database). Every row in the
table has the same structure.

d) Value: A value is a set of characters, text, numerical, date that is

entered while adding information to the database.

e) Table: A table is a set of data elements (values) that is organized

1
 using a model of vertical columns (which are identified by their
name) and horizontal rows. A table has a defined number of
columns, but can have any number of rows. A table is the basic
building block of the database.

f) Primary key : A primary key is a unique column that identifies a

row in a table and cannot be duplicated. They are also indexed in the
database, making it faster for the database to search for a record. It
cannot be null and it is a required field. A table can have only one
primary key, which may consist of single or multiple fields. When
multiple fields are used as a primary key, they are called a composite
key.

g) Composite Primary Key: When multiple fields are made as a primary

key it is known as Composite Primary Key.

h) Datatypes are used to identify which type of data (value) we are

going to store in the database. Fields themselves can be of different
types depending on the data they contain.

Data types in OpenOffice base are broadly classified into five

categories listed below:

• Numeric Types: describing numeric values like phone number,

admission number. Data types used are: tinyint, Integer, Numeric,
Decimal, real, float .

• Alphanumeric Types: both character and number like address,

email id, product description . Data types used are: char, varchar,
longvarchar , varchar_ignorecase.

• Binary Types: binary formats (music, image or any object in binary

formats). Data types used are: binary, varbinary, longvarbinary.

• Date time: describing date and time values like date_of_join,

date_of_ birth, Date_of_ leaving, issue_date . Data types used are:
date, time and timestamp.

• Other Variable types : stores serialised Java objects. Data type used
is other.

2
4. Write the steps to create a database in the Open office database (Base).

● Double click on the Open office icon.

● Click on the Database option. Select -> create a new open database
option.
● Select on Next option. Select ->open the database for editing option.
● Save the database.

5. Differentiate between flat file and databases.

Flat File Database

It consists of data files that contain It stores data in several tables which
records which have a small and have a large number of fields and
fixed number of fields. records.

There is no structured relationship A structured relationship between

between the tables. the tables can be created with the
common fields.

E.g. Excel, Spreadsheet, Google E.g. Access, MySQL, Open office

sheets database, Oracle

6. Mention some advantages of using Databases.

Reduces Data Redundancy: The database management systems contain

multiple files that are to be stored in many different locations in a system or
even across multiple systems. Because of this, there were sometimes
multiple copies of the same file which lead to data redundancy.

This is prevented in a database as there is a single database and any change

in it is reflected immediately. Because of this, there is no chance of
encountering duplicate data.

Sharing of Data: There are various levels of authorisation to access the

data, and consequently the data can only be shared based with the
authorized users.

Data Integrity: Data integrity means that the data is accurate and consistent
3
 in the database. All of these databases contain data that is visible to
multiple users. So it is necessary to ensure that the data is correct and
consistent in all the databases and for all the users.

Data Security: Only authorised users should be allowed to access the

database and their identity should be authenticated using a username and
password. Unauthorised users should not be allowed to access the database
under any circumstances as it violates the integrity constraints.

Privacy: The privacy rule in a database states that only the authorized users
can access a database according to its privacy constraints. To secure data
levels are set in the database and a user can only view the data which is
allowed to be seen. For example - In social networking sites, access
constraints are different for different accounts a user may want to access.

Backup and Recovery: Database Management System automatically takes

care of backup and recovery. The users don't need to backup data
periodically because this is taken care of by the DBMS. Moreover, it also
restores the database after a crash or system failure to its previous
condition.

Data Consistency: Data consistency is ensured in a database because there

is no data redundancy. All data appears consistently across the database
and must be same for all the users viewing the database. Moreover, any
changes made to the database are immediately reflected to all the users and
there is no data inconsistency.

7. Analyze database requirements for your school, airlines, shopping site.

Identify the various tables, fields and their data types.

For example: tables required for School:

a)Student_profile : admn_no : numeric, first_name char, Last_name:

varchar, date_of_join:date, address: varchar , city: char, state:char

b) Student_marks : admn_no, first_name, Last_name, English_marks,

Science_marks, Language_marks

c)Students_transport:admn_no, first_name, Last_name, transport_mode,

transport_fees, address, stop_alight.

4
8.

In the above table: Client

ClientID is the primary key

Field Name Data type

ClientID Numeric

Name Varchar

Address Varchar

Phone Numeric

Sales table : OrderID is the primary key

Field Name Data type

OrderID Numeric

ClientID Numeric

SaleDate Date

5
 #ItemsBought Numeric

9. In the given table: store, identify the data types for the various fields.

item_no item qty rate lastbuy

101 Sharpener 75 10.00 2018-12-05

102 Ball Pen 100 25.00 2019-04-07

0.25

103 Eraser small 60 7.00 2019-03-09

104 Eraser Big 50 15.00 2018-12-24

105 Ball Pen 0.5 75 50 2018-11-10

10. Differentiate between char and varchar field types.

Char: Stores exactly the length specified by the user. Pads with trailing
spaces for shorter strings.

Example: If Char (25) is set for School_name and data entered is Sanskriti
School:16 characters , then 9 spaces will still be allocated and it will be filled
with spaces .

Varchar: Stores up to the specified length. No padding is done by the

database. It saves space in the storage of the database.

Example: If you specify VARCHAR(60) for School_name that means it can

hold up to 60 characters. If data entered is: Sanskriti School :16 characters .
No spaces will be left.

6
11.

In the above example, the two tables are connected by the BookID, which
acts as a key between the two tables. Here you can see the two tables are
related to each other or share a relation defined using BookID.

12. Define the term Database Servers.

Database servers are dedicated computers that hold the actual databases
and run only the DBMS and related software. Typically databases available
on the database servers are accessed through command line or graphic user
interface tools referred to as Frontends; database servers are referred to as
Back-ends. Such type of data access is referred to as a client-server model.

13. Fill in the blanks:

1. A __________ is an organized collection of data.

2. A ________________ is a software package that can be used for

creating and managing databases.

3. A ____________ is a database management system that is based on the

relational model.
4. Three popular DBMS software are ________, ________, & _______.

5. A ____________ is a unique value that identifies a row in a table.

6. .odb is the extension of the database stored in an open office base.
7. A table is a set of data elements that is organized using a model

7
 of vertical ___________ and horizontal ____________.
8. _____________ are used to identify which type of data we are going to

store in the database.

9. ________are the basic building blocks of a database.
10. When creating a database an important feature is, every record is

_______ in every table. (Unique).

11. ____ are the basic building blocks for the database.