INTRODUCTION TO

D A T A B A SE
SY ST E M S
WRITTEN BY

PROF. MUHAMMAD FAHIM

Head of Computer Science Department

Govt. Graduate College, Gulberg, Lahore
0300 – 774 25 26
 [Link]. COMPUTER STUDIES

1) DEFINE DATA AND INFORMATION WITH EXAMPLE? DIFFERENTIATE BETWEEN

DATA AND INFORMATION?
DATA
The collection of raw facts and figures is called data. The data may consist of text,
numbers, images or even audio & video. It is not useful for decision making. It is converted
into information with the help of computers.
EXAMPLE:
When a student fills an admission form, he writes his name, date of birth, obtained
marks, telephone number, address etc. All these facts and figures are data.
INFORMATION
The processed form of data is called information.
The data is converted into information by performing
different operations on data. The information is always
useful for decision making.
EXAMPLE:
The data collected from students during admissions
is processed to make merit list. The merit list is an
example of information. It is used to make decisions
related to the admission.
DIFFERENCES BETWEEN DATA AND INFORMATION

DATA INFORMATION
Data is raw facts and figures. Information is processed form of data.
Information is always useful for decision
Data is not useful for decision making.
making.
Information is output of the computer
Data is input for the computer system.
system.
Data is huge in volume. Information is summarized.
Information can be reproduced when
It is nearly impossible to reproduce data.
required.
Data does not depend upon information. Information depends upon data.

2 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

2) WHAT IS FILE PROCESSING SYSTEM? DISCUSS VARIOUS DISADVANTAGES OF

FILE PROCESSING SYSTEM?

FILE PROCESSING SYSTEM

File processing system is the

first computer-based method to
handle business applications.
In file processing systems, the
data is stored in flat files. Each
department has its own set of data
files. These files are designed
according to the needs of particular
departments. The records in one file
have no relationship with the records
in any other file.
Application programs written in C/C++ like programming languages are used to
access data stored in these files.

Following are important disadvantages of file processing system:

1. PROGRAM DATA DEPENDENCY

Program data dependency means that application programs are dependent on
data files. If format of a data file is changed then all application programs using that data
file would also be changed.
For example, if length of the name is changed in a data file then the application
programs using that data file would also be changed accordingly.
2. DATA REDUNDANCY
Data redundancy means that same data is stored in more than one data files. The
data redundancy wastes time and disk space. For example, the name, class and address
of a student are stored in admissions file and examination results file separately.
3. DATA INCONSISTENCY
Data inconsistency means that different files have different values of the same
data. For example, admissions file and examination results file have different addresses
of a particular student.
4. DATA ISOLATION
Data isolation means that data files are not related with each other. Therefore,
complex application programs are required to extract data from unrelated data files. For
example, it is very difficult to generate list of phone numbers of those students who have
3 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26
 [Link]. COMPUTER STUDIES

failed in the examination.

5. LOSS OF DATA INTEGRITY
Data integrity means reliability and accuracy of data. In file processing systems, it
is very difficult to apply different constraint on data. For example, it is very difficult to
ensure that the marks of a student must be numeric or the roll number is not duplicated.
6. SECURITY PROBLEMS
Security means protecting the data from unauthorized access. The data stored in
flat files is easily accessible. Therefore, data security is low in file processing systems.
For example, it is very difficult to prevent one user from deleting data while allowing the
other.
7. LIMITED DATA SHARING
Data sharing means that more than one application can access data stored in a
particular data file. In file processing systems, the data is stored in several unrelated and
incompatible data files. Therefore, the data sharing is very limited.
8. LENGTHY DEVELOPMENT TIME
Development time means the time required to develop a new application. In file
processing system, each new application requires its own set of data file. Therefore, it
takes lot of effort and time to develop a new application and its data file formats.
9. EXCESSIVE PROGRAM MAINTENANCE
Program maintenance means modifying a program to meet changing user
requirements. In the file processing system, there are different programs to perform
different applications. Therefore, a single change in user requirements may require
changing various application programs. That is why excessive program maintenance is
required.

4 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

3) WHAT IS DATABASE APPROACH? WHAT ARE ADVANTAGES AND DISADVANTAGES

OF DATABASE APPROACH?

DATABASE APPROACH / DATABASE SYSTEM

Database approach is a
computer-based method to handle
business application. It uses
databases to record and maintain
data.
A database is an organized
collection of data stored in related
tables. It stores data in efficient and
compact manner. It is created,
manipulated and maintained using a
database program.
The database program is also
known as database management system (DBMS). The structured query language is used
to interact with databases through DBMS.

ADVANTAGES OF DATABASE APPROACH

Following are the advantages of using database approach:

1. DATA INDEPENDENCE
Data independence means that application programs and data files are
independent of each other. Any change in one has no or minimum effect on the other. For
example, if length of the name is changed in the data file, it does not affect the application
programs manipulating it. Similarly, if the application programs are changed, it does not
require changing the format of data file.
2. REDUNDANCY CONTROL
Data redundancy means duplication of data. In database approach, the data is
stored at a central place. It is not duplicated unnecessarily. Therefore, data redundancy is
controlled in database systems.
3. DATA CONSISTENCY
In database approach, the data is stored at a common place and usually once. The
duplication of data is deliberate and controlled. If the duplicated data is changed at one
place, the DBMS automatically updates it everywhere. Therefore, the data is always
consistent.

5 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

4. DATA INTEGRATION
Data integration means that the data is logically centralized while it may
actually be stored at different locations.
5. DATA INTEGRITY
Data integrity means reliability and accuracy of data. In database approach, it is
very easy to apply different constraint on data. For example, it is very easy to ensure that
the marks of a student must be numeric or the roll number is not duplicated.
6. TRANSACTIONAL SUPPORT
A transaction is a series of database operations to a complete a task. In database
approach, the DBMS automatically ensures transactional properties like atomicity,
consistency, isolation and durability. These properties are commonly known as ACID
properties.
7. DATA SECURITY
In database approach, the DBMS provides authorized access to data. For
example, it is very easy to prevent one user from deleting data while allowing the other.
8. DATA SHARING
In database approach, the data is logically centralized. Therefore, the data sharing
is very high. For example, admission department and examination department can share
data of students stored at a central place.
9. LESS DEVELOPMENT TIME
In database approach, each new application shares data stored at a central place.
The new applications can be developed easily without designing their separate data files.
Therefore, it takes less time to develop new applications.
10. EASIER PROGRAM MAINTENANCE
In database approach, all applications access data stored at a central place.
Therefore, it is easier to modify applications when user requirement are changed.

DISADVANTAGES OF DATABASE APPROACH

Following are the disadvantages of database approach:

1. COST OF HARDWARE AND SOFTWARE

A high speed processor and a large sized memory are required to run DBMS
software efficiently. Therefore, the organization has to purchase better hardware and
DBMS software.
2. HIGH PROGRAMMING COST
The DBMS is very complex software. It has many features. Therefore, the

6 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

organization has to hire experienced and skilled application programmers and database
administrators at high wages.
3. COST OF DATA CONVERSION
The conversion of data and from file processing system to a database system is
difficult and time consuming. Therefore, the organization has to hire technical persons like
application programmers and database administrators at high wages.
4. DATABASE FAILURE
In database approach, the data is centralized at one place. If the database is
unavailable due to power failure or any other issues then everyone suffers.
5. COMPLEXITY AND PERFORMANCE
The databases should be carefully designed and develop. A poorly designed
database may not satisfy the user requirements. In some cases, it may be less efficient
than file processing system.

4) WHAT ARE FILE PROCESSING SYSTEM AND DATABASE APPROACH? WHAT ARE
THE DIFFERENCES BETWEEN FILE PROCESSING APPROACH AND DATABASE
APPROACH?

FILE PROCESSING SYSTEM

File processing system is the first computer-based method to handle business

applications. In file processing systems, the data is stored in flat files. Each department has
its own set of data files. These files are designed according to the needs of particular
departments. The records in one file have no relationship with the records in any other file.
Application programs written in C/C++ like programming languages are used to access data
stored in these files.

DATABASE APPROACH

Database approach is a computer-based method to handle business application. It

uses databases to record and maintain data. A database is an organized collection of data
stored in related tables. It stores data in efficient and compact manner. It is created,
manipulated and maintained using a database program. The database program is also
known as database management system (DBMS). The structured query language is used to
interact with databases through DBMS.
FILE PROCESSING SYSTEM VS DATABASE APPROACH

FILE PROCESSING SYSTEM DATABASE APPROACH

Programs and data files are dependent on Programs and data files are independent of
each other. each other.
Data redundancy is high. Data redundancy is low.

7 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

The data may be inconsistent. Data is always consistent.

Data sharing is limited. Data sharing is high.
Data is stored in unrelated and
Data is logically centralized.
incompatible files.
It is difficult to ensure data integrity. It is easy to ensure data integrity.
It is difficult to ensure data security. It is easy to ensure data security.
Program maintenance is difficult. Program maintenance is easy.
The hardware and software are less The hardware and software are very
expensive. expensive.
Chance of overall system failure is low Chance of overall system failure is high.
It is very difficult to provided concurrent It is easy to provide concurrent access to
access to data. data.
New applications Require Lengthy New applications require shorter
Development Time. development time.

5) DEFINE THE FOLLOWING TERMS?

 FIELD
 RECORD
 TABLE
 DATABASE

FIELD

Field is the smallest unit of data item about an object. It is used to store a single piece
of data about something. For example, name and class are fields of a student.
Field

Roll No Name Class PhoneNo

101 M. Rayyan [Link]. 0333-4162604

Table
102 M. Subhan [Link]. 0300-1234567 Record

103 M. Hassaan [Link]. 0321-1234567

RECORD
Record is a collection of related fields about an object. It is used to store complete
information about something. It is stored as row in the table. For example, (102, M. Subhan,
[Link]., 0321-1234567) is a record of a student.

TABLE

A collection of related records is called table. Table is the fundamental object in

8 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26
 PAPER - III DATABASE SYSTEMS

databases. All of the data is stored in tables. All operations are performed on tables. The
results of all operations are returned as tables.
A table consists of rows and columns. Each row represents a record. Each column
represents a field. A database can have any number of tables as required.

DATABASE

Database is a structured repository of data. It consists of a set of related tables or data

files. Each table can store information about a particular topic. For example, a student table
can be used to store information about all students.

6) WHAT IS DATABASE SYSTEM? WHAT ARE THE COMPONENTS OF DATABASE

SYSTEM?

DATABASE SYSTEM

Database system is a computer-based method to handle business application. It uses

databases to record and maintain data.
A database is an organized collection of data stored in related tables. It stores data in
efficient and compact manner. It is created, manipulated and maintained using a database
program.
The database program is also known as database management system (DBMS). The
structured query language is used to interact with databases through DBMS.

COMPONENTS OF DATABASE SYSTEM

Following are the important components of database system:

1. DATA
Data is the most important component in a database system. There are two types
of data. These are actual data and metadata.
Data that is stored and processed to perform business operations is called actual
data. It is the collection of raw facts and figures. It is processed into information as and
when required. For example, the database system of a college stores data about
students, fee, courses etc. is called actual data or simply data.
Data about data is called metadata. It describes the characteristics of actual data.
It describes the data structures and data formats for actual data. The actual data is stored
according to definitions in metadata. For example, the integrity constraints like marks of
student must be numeric or the name can only be consists of alphabets are metadata.
2. DATABASE OR REPOSITORY
The database is structured repository of data. It is collection of organized and
related tables or data files. It is used to store data in efficient and compact manner. It is
9 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26
 [Link]. COMPUTER STUDIES

created, manipulated and maintained using database management system.

3. DATABASE MANAGEMENT SYSTEM
Database management system is a highly sophisticated program. It is used to
create, manipulate and maintain database. It hides all the implementation details from the
users. It provides structured query language (SQL) to interact with databases. SQL is
used by the application programmers and database administrators for creating and
maintaining database.
4. APPLICATION PROGRAMS
The programs used by end-users to insert, retrieve, delete and update data are
called application programs. Application programs provide graphical user interface to
interact with database.
The end-users issue commands using application programs. Application programs
convert these commands into SQL statements and send them to DBMS. The DBMS
performs various operations on data according to these commands.
5. DATABASE USERS
Following are different types of database users:
 APPLICATION PROGRAMMERS
Application programmers are technical personnel who design and
develop application programs. These application programs provide graphical
user interface to interact with database. Application programmers use
programming languages like Visual Basic and Java for developing application
programs.
 DATABASE ADMINISTRATORS
Database administrators are technical personnel who are responsible for
design, implementation and maintenance of database. They develop policies
and procedures related to management, security and use of database. They
are also responsible for smooth functionality of the database.
 END USERS
End-users are personnel who use the database after its development.
They are responsible for inserting, retrieving, deleting and updating data in the
database. There are two types of end-users. These are naive end-users and
sophisticated end-users.
Naive end-users do not have technical knowledge about the database.
They insert, delete, retrieve and update data by using interface provided by the
application programs. For example, clerical staff in a bank is a naive user. The
do not have technical knowledge of the database but they use application
10 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26
 PAPER - III DATABASE SYSTEMS

programs to perform their tasks.

Casual end-users have a good knowledge of query language like SQL.
They do not write application programs but they perform adhoc operations by
writing queries. They also perform routine tasks by using application programs
like naive end-users.
6. HARDWARE
The physical parts on which application programs, database management system
and database are stored and executed are called hardware. Hardware may include CPU,
Memory, Storage Devices and networking devices.

7) DEFINE DATABASE ARCHTITECTURE, DATABASE SCHEMA AND DATABASE

INSTANCE?

DATABASE ARCHITECTURE

A complex database can consists of thousands of data items. These data items may
be accessed by thousands of users. The database designer divides these users into user-
groups. He allows each user-group to use their relevant parts of the database.
The way in which database designer organizes data into different user-groups is
called database architecture. The most common database architecture is three-level
architecture.

DATABASE SCHEMA

It is important to note that the data in the database changes frequently, while the plans
or schemes remains the same over a long period of time.
The database plans consists of types of entities about which data is stored in the
database, the relationships between these entities and the way in which the relationship
between entities are expressed. The plan or scheme of the database is knows as schema.
Database schema describes structure of a particular database. It usually remains
same while the data stored in the database is modified regularly.
Database schema stores the description of all data structures used in the database.
These descriptions can be anything from an entity to the whole organization. It also stores
various constraints applied on data.
All data descriptions and constraints are called metadata i.e. data about data. The
database schema is also called intension of the database.

DATABASE INSTANCE

The actual data stored in a database at any time is called database instance. The
database instance is modified as a result of day-to-day operations performed on the

11 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

database. The database instance is also called extension of the database.

8) WRITE A DETAILED NOTE ON THREE-LEVEL ARCHITECTURE OF THE DATABASE?

THREE-LEVEL ARCHTITURE

The data is stored as binary numbers inside the computer. It is difficult to work with
data at this level. Three-level architecture provides data abstraction. The objective of three-
level architecture is to separate each user’s view of the database from physical
representation of the database.
The Three levels are different descriptions of the same data at different levels. It was
proposed by SPARC (ANSI) in 1975. The three levels are external level, logical level and
physical level.

 The way in which different user-groups see stored data is called external level.
 The way in which all entities, attributes and their relationships are described is
called logical level.
 The way in which data is actually stored on physical storage device is called
internal level.
1. EXTERNAL LEVEL
The way in which different user-groups see the stored data is called external level.
The external level is described using one or more external views (external schemas).

12 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

Each external view describes part of the database which is relevant to a particular
user-group. It hides rest of the database from that user-group. External level is a
description of how the data is viewed by the end-users.
For example, a user may see student’s roll number and date-of-birth but cannot
see his telephone number.
Furthermore, different external views may present same data in different formats.
For example, in one external view the date may be displayed in (dd/mm/yyyy) format and
in another external view it may be displayed in (yyyy/mm/dd) format.
It is important to note that an external view may contain a data item that is actually
not stored in the database. For example, it may display the age of a person while date of
birth is actually stored.
2. CONCEPTUAL LEVEL
The way in which all entities, attributes and their relationships are described is
called conceptual level or conceptual schema.
A conceptual schema describes the structure of whole database. Conceptual
schema hides the details of physical storage structures and concentrates on describing
entities, attributes and their relationships. It also describes security and integrity
constraints of entire database. Conceptual schema is a description of what data is stored
in the database.
It is important to note that external views are extracted from conceptual schema. It
is done by selecting entities, attributes and their relationships that are visible in a
particular external view.
There is only one conceptual schema for a particular database.
3. INTERNAL LEVEL
The way in which data is actually stored on physical storage devices is called
internal level.
Internal level describes physical storage structures of the database. The internal
schema is a description of how database is physically represented on the storage
devices.
The internal schema provides definitions of the stored records, methods of
representation, data fields, indexes and hashing schemes. It also describes data
compression and encryption techniques.
It is important to note that three schemas are only descriptions of the data; the data
that actually exists is the data at physical level only.

13 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

9) WHAT IS MAPPING? EXPLAIN DIFFERENT TYPES OF MAPPING?

MAPPING

The database architecture is described using external schema(s), conceptual schema

and internal schema. These schemas are stored in data dictionary.
In database approach, each user refers to his own external schema. The DBMS must
transform his request from external schema to conceptual schema and from conceptual
schema to internal schema. The processes of transforming requests and results between
schemas is called mapping.
The process of transforming a user’s request from external schema to conceptual
schema is called external / conceptual mapping. Similarly, the process of transforming a
request from conceptual to internal schema is called conceptual / internal mapping. The
mapping description is also stored in the data dictionary.
1. EXTERNAL / CONCEPTUAL MAPPING
Each external schema is related
to conceptual schema by external-
conceptual mapping. The mapping
between external schema and
conceptual schema describes the
correspondence between the records
and relationship of external schema and
conceptual schema. The external
schemas are abstractions of conceptual
schema.
The external-conceptual mapping
tells the DBMS which entities in
conceptual schema correspond to the
entities referred in a particular external
schema. If any changes are made in
external schema or conceptual schema
then the external-conceptual mapping
must be changed accordingly.

2. CONCEPTUAL / INTERNAL MAPPING

The conceptual schema is related to internal schema by conceptual-internal
mapping. The mapping between conceptual schema and internal schema describes the
correspondence between records and relationships of conceptual schema and internal

14 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

schema. The conceptual schema is an abstraction of internal schema.

The conceptual-internal mapping tells the DBMS that how the conceptual records
are stored and retrieved from the storage devices. If any changes are made in the
conceptual schema or the internal schema then the conceptual-internal mapping must be
changed.

10) WHAT IS DATA INDEPENDENCE? DISCUSS ITS TYPES ALSO?

DATA INDEPENDENCE

Data independence is one of the main objectives of using database approach. Data
independence means that the application programs or physical implementation can be
changed without changing the other.
There are two types of data independence. These are physical data independence
and logical data independence.
1. PHYSICAL DATA INDEPENDENCE
Physical data independence means that the physical schema of the database can
be changed without changing the logical schema. The DBMS hides such changes in
physical schema. Therefore, the external views and application programs will not be
changed too.
The changes that can be made in physical schema without affecting the logical
schema are as follows:
i. Adding new indexes
ii. Changing the file organization
iii. Changing the access method
iv. Changing the hashing algorithms
v. Changing the storage structure or storage media
2. LOGICAL DATA INDEPENDENCE
Logical data independence means that the logical schema of the database can be
changed without changing the external views. For example, if a new data item is added to
logical schema, the existing external views or application programs will only be changed if
they have to display the newly added data item.
The changes that can be made in logical schema without affecting the existing
external views or application programs are as follows:
i. Adding new entities
ii. Adding new attributes
iii. Adding new relationships
iv. Changing the data type of an attribute

15 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

v. Changing the integrity constraints of the database

11) DEFINE DATABASE MANAGEMENT SYSTEM? ALSO DISCUSS ITS IMPORTANT

FUNCTIONS?

DATABASE MANAMGENT SYSTEM (DBMS)

Database management system is a highly sophisticated program. It is used to create,

manipulate and maintain database.
Database management system hides all implementation details from the users. It
provides structured query language (SQL) to interact with databases. SQL is used by the
application programmers and database administrators for creating and maintaining database.

FUNCTIONS OF DATABASE MANAMGENT SYSTEM (DBMS)

Following are the important functions of database management system:

1. DATA PROCESSING
Database management system helps the users to interact with the database. It
allows the end-users to insert, delete, update and retrieve data. It also allows application
programmers and database administrators to perform their tasks using services provided
by DBMS.
2. TRANSACTIONAL SUPPORT
A transaction is a series of database operations to a complete a task. Database
management system provides facilities for ensuring transactional properties like atomicity,
consistency, isolation and durability. These properties are called ACID properties.
3. INTEGRITY SERVICES
Database management system provides an easy way to apply different constraints
on data. These constraints are specified when a database is created. For example, it is
very easy to ensure that the marks of a student are numeric or the roll number is not
duplicated.
4. SECURITY SERVICES
Database management system provides authorized and secured access to data.
For example, it is very easy to prevent one user from deleting data while allowing the
other.
5. BACKUP AND RECOVERY
Database management system provides facilities for taking backup of the data. It
also provides facilities for database recovery in case of a failure. It ensures that the
database always remains in a consistent state.

16 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

6. CONCURRENCY SUPPORT
There may be a situation when the same data is accessed by more than one user
simultaneously. Database management system ensures concurrent access to the same
data. It handles all the problems that may occur due to concurrent access to the data.
7. USER ACCESSIBLE CATALOGUE / DATA DICTIONARY
Data dictionary is a repository of metadata. It contains names and descriptions of
tables and fields. It also contains information about data types, field lengths and integrity
constraints.
The information about end-users and their rights is also stored in the data
dictionary. Database management system provides access to the data dictionary.
Database administrators use data dictionary to control and manage the database.
8. DATA COMMUNICATION SUPPORT
Data is logically centralized in the database. Therefore, database is commonly
stored on servers. The users access data using computer network.
All communication with the database is managed by data communication
manager (DCM). DCM is not a part of the database management system. The database
management provides facilities to work with DCM so that the data can be accessed by
the users.

12) WHAT IS DATA DICTIONARY? ALSO DISCUSS ITS TYPES AND USES?

DATA DICTIONARY

Data dictionary is file or set of files that contains metadata about the database. In a
relational database, a data dictionary contains following things:
 Names of all tables in the database and their owners
 Names and descriptions of all the fields in tables
 Names of all indexes and their related columns
 Integrity constraints defined on the tables like primary keys, foreign keys,
relationships etc.
 Information about end-users and their rights
In relational databases, the DBMS software needs data dictionary to access the data.
The data dictionary is used as a tool for communication between database designers and
end-users. It is also used by database administrators to control and manage the database.
There are two types of data dictionaries. These are integrated data dictionary and free
standing data dictionary.
1. INTEGRATED DATA DICTIONARY
Integrated data dictionary is a part of database management system. It is not only
17 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26
 [Link]. COMPUTER STUDIES

used during design and development of the database but it is also used every time a user
accesses the database.
Integrated data dictionary is automatically updated by DBMS whenever the
database schemas are modified. Therefore, it always has the latest information about
database schemas or structure of the database.
In relational databases, the data dictionary is integrated and active. It means that
the DBMS checks the data dictionary whenever the database is accessed.
2. FREE STANDING DATA DICTIONARY
Freestanding data dictionary is not a part of the DBMS. It is only used during
design and development of the database. It can be a third party product. It can also be a
simple file containing information about structure of the database.
Free standing data dictionary is developed by the database designer or database
administrator. It needs to be updated whenever structure of the database is modified.
Therefore, it may not have the latest information about the structure of database.

13) WHAT IS DATA MODEL? DISCUSS RELATIONAL DATA MODEL?

DATA MODEL

Data model is an abstraction of real world entities and their relationships. It defines
how data elements are connected to each other. It also defines how data is stored and
processed.
Data model is a collection of tools that are used to describe a database schema.
These tools are used to describe entities, their relationships, integrity constraints, storage
and processing of data.
The difference between data model and a database schema is that data model is a
collection of tools that are used to describe structure of a database while a database schema
describes the structure of a particular database.

RELATIONAL DATA MODEL

Dr. E.F. Codd presented relational data model in June 1970. It is the most widely used
data model for storage and processing of data. In relational model, all of the data is stored in
relations (tables). A table consists of rows and columns.
An important characteristic of the relational model is the usage of keys. The keys are
specially designated columns within a relation. These columns are used to order data or
relate data with the data stored in other relations.
The relational model describes a set of rules to enforce data integrity. These rules are
known as integrity constraints. It also defines how the data is to be manipulated. It further

18 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

defines a special feature called normalization. The normalization reduces data redundancy
and removes database anomalies.
RELATIONAL DATA MODEL TERMINOLOGIES / CONCEPTS
Following are some important concepts of relational data model:
1. TABLE / RELATION
Table or relation is a fundamental concept in relational data model. All of the data
is stored in tables. A table consists of rows and columns. Each table stores data about a
particular entity. A database can have any number of tables.
2. TUPLE
A single row in the table is called tuple or record. It is a collection of related data
items about a particular object. A table can have any number of tuples but there are no
duplicate tuples in a relation.
3. ATTRIBUTE
The characteristics of an entity are called its attributes. The values of a particular
attribute are stored in a particular column. The attributes are also called fields. Each
relation can have any number of columns but there are no duplicate columns in a relation.
4. DOMAIN OF ATTRIBUTE
The set of all possible values for a particular attribute is called its domain. For
example, marks of a student can be a single value from 0 to 100. The set of all values
from 0 to 100 is called domain of marks.
5. KEY ATTRIBUTE OR IDENTIFIER
An attribute that have unique values for each tuple in the relation is called key
attribute or identifier. If there is no such attribute then combination of attributes is used as
key or identifier.
6. DEGREE OF TABLE
The number of columns in a table is called its degree. The degree of a table is
changed when a columns is added or removed from the table.
7. CARDINALITY OF TABLE
The number of rows in a table is called is called its cardinality. The cardinality of
table is changed when a row is inserted or deleted from the table.

19 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

14) WHAT IS DATA FLOW DIAGRAM (DFD)? DISCUSS DIFFERENT ELEMENTS OF DATA
FLOW DIAGRAM?

DATA FLOW DIAGRAM (DFD)

Data Flow Diagram (DFD) is a graphical tool that shows flow of data within the
system. It describes how data is processed by a system in terms of inputs and outputs. It
focuses on flow of data, where data comes from, where it goes and how it gets stored.

ELEMENTS OF DATA FLOW DIAGRAM

Following are the elements of data flow diagram

1. ENTITY
A person, place or thing that supplies data to the system or receives data from the
system is called entity. For example, employees, departments and computers are entities.
An entity is represented by a rectangular box.
An entity that performs data processing activities inside the system like employees
is called internal entity. An entity that supplies data to the system or receive data from the
system like customers is called external entity.
2. DATA STORE
A data store contains data that is not moving in the system. It can be a manual
register or a computer file. It is represented by a rectangle open from the right end.
3. PROCESS
A process is an action that is performed on data inside the system. It is
represented by a rounded rectangle or a circle.
4. DATA FLOW
A data flow represents flow of data within the system. It is represented by an
arrow.

20 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

15) WHAT IS DATA FLOW DIAGRAM (DFD)? DISCUSS DIFFERENT ELEMENTS OF DATA
FLOW DIAGRAM?

DATA FLOW DIAGRAM (DFD)

Data Flow Diagram (DFD) is a graphical tool that

shows flow of data within the system. It describes how
data is processed by a system in terms of inputs and
outputs. It focuses on flow of data, where data comes
from, where it goes and how it gets stored.
Following are the different types of data flow
diagrams:
1. CONTEXT DFD
A context DFD is a top-level data flow diagram. It defines boundary of the system.
It represents system as a single large process. It shows flow of data between external
entities and the system. It also describes inputs and outputs of the system.
2. LEVEL 0 DFD
A level 0 DFD shows
detailed breakout of the
context DFD. It shows major
processes and flow of data
between them. It does not
show the details of
processing taking place
inside these processes. It
also identifies data stores
used by the major
processes. The major
processes are labeled as 1,
2, 3 and so on.
3. LEVEL 1 / DETAILED DFD
A detailed DFD is an extension of level 0 DFD. It shows details of processing
taking place inside the major processes. In detailed DFD, each major process is broken
down into sub-processes. The sub-processes are related to a particular major process
with its number in level 0 DFD. For example, the sub-processes of major process number
1 are labeled as 1.1, 1.2, 1.3 and so on in detailed DFD.

21 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

16) EXPLAIN THE STEPWISE PROCEDURE FOR CREATING A DATA FLOW DIAGRAM
WITH AN EXAMLE?

SCENARIO FOR DATA FLOW DIAGRAM

 A student (EE) sends in an application form (DF) containing his/her personal details
and desired course.
 The university checks (P) whether the course is available.
 If the course is available then the student is enrolled (P) in the course, and the
university confirms (P) the enrollment by sending a confirmation letter (DF) that
he/she is registered for the course.
 Or if the course is unavailable the student is sent a rejection letter (DF).
Note: The University, or more specifically the administration section is the system that
is being modeled.
1. CONTEXT DFD
 External Entity – Student
 Process – Student Administration Application Processing
 Data Flows – Application Form, Confirmation Letter, Rejection Letter

2. LEVEL 0 DFD
 External Entity – Student
 Processes – Check Course Availability, Enroll Student, Confirm Registration
 Data Flows – Application Form, Course Details, Course Enrolment Details,
Student Details, Confirmation/Rejection Letter
 Data Stores – Courses, Students

22 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

17) WHAT IS ENTITY-RELATIONSHIP MODEL? WHAT ARE THE ELEMENTS OF ENTITY-

RELATIONSHIP MODEL?

ENTITY-RELATIONSHIP MODEL

Entity-Relationship model is a graphical tool. It is used to design conceptual schema

of a database. The conceptual schema describes what is stored in the database. It describes
all entities, attributes and their relationships. It also describes various constraints on the data.
It is the complete description of data stored in the database.

ELEMENTS ENTITY-RELATIONSHIP MODEL

The elements of E-R model as follows:

1. ENTITY
An entity is a person, place, thing, event, idea or a concept about which we have
store data in the database.
For a COLLEGE SYSTEM, we have to store data about STUDENT, CLASS,
TEACHER and FEE etc. Therefore, STUDENT, CLASS, TEACHER and FEE are entities
in COLLEGE SYSTEM.

23 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

It is important to note that the term entity is used for entity type, entity instance and
entity set. The context in which the term entity is used explains its meanings.
 ENTITY TYPE
The description of an entity is called entity type. An entity is described by its
attributes. For example, STUDENT and TEACHER entities are described using
their attributes as follows:
STUDENT (RollNo, SName, Class, DOB, TelephoneNo, Address)
TEACHER (TeacherId, TName, Subject, Qualification, Address)
 ENTITY INSTANCE
Anything that exists according to the description of a particular entity type is
called its entity instance.
For example, a person having attributes (123, M. Rayyan, 1st Year, 07-04-
1997, 0333-1234567, Pak Colony Multan) is an instance of STUDENT entity type.
Similarly, a person having attributes (123, M. Subhan, Computer Science,
MPhil, Islampura Lahore) is an instance of TEACHER entity type.
 ENTITY SET
Set of all entity instances of the same type is called entity set. For example,
set of all entity instances of STUDENT entity is a STUDENT entity set.
Similarly, set of all entity instances of TEACHER entity is also a TEACHER
entity set.
 STRONG ENTITY
An entity that has its own identifier is called strong entity. For example,
LOAN entity has an attribute LoanID that has unique values for each LOAN
instance so LoanID can be used as identifier. Therefore, LOAN is a strong entity.
It is also said that an entity whose instances can exist independently is
called strong entity.
 WEAK ENTITY
An entity that does not have its own identifier is called weak entity. The
instances of a weak entity are identified by the identifier of a strong entity.
For example, the loan is returned in installments. Each INSTALLMENT
instance is identified by the identifier of LOAN entity i.e. LoanID. Therefore,
INSTALLMENT is a weak entity.

24 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

It is alt is also said that an entity whose instance cannot exist without the
instances of a strong entity is called weak entity. The instances of INSTALLMENT
entity cannot exist without the instances of LOAN entity.
 ASSOCIATIVE ENTITY
An associative entity does not exist as other types of entities. It comes into
existence when a many-to-many relationship establishes between the instances of
two related entities.

For example, there exist a relationship between CUSTOMER entity and

PRODUCT entity. As a customer may order many products while a product may be
ordered by many customers. In this case the ORDER is an associative entity that
comes into existence because of many-to-many relationship.
2. ATTRIBUTES
The characteristics of an entity are called its attributes. For example, TName,
Subject, Qualification and Grade are attributes of TEACHER Entity.
The set of all possible values for a particular attribute is called its domain. The
domain of an attribute is defined as a range of values or a as a rule. For example, the
marks of a student can any single value from 0 to 100 or name can consist of any
combination up to 30 characters.

25 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

 SIMPLE ATTRIBUTE
An attribute that cannot be subdivided into smaller attributes is called simple
attribute. For example, RollNo of a STUDENT cannot be subdivided. Therefore,
RollNo is a simple attribute.

 COMPOSITE ATTRIBUTES
An attribute that can be subdivided into smaller attributes is called
composite attribute. For example, Name of a STUDENT can be subdivided into
FirstName and LastName. Therefore, the Name is a composite attribute.
 SINGLE-VALUED ATTRIBUTES
An attribute that can have only one value for an instance is called single
valued attribute. For example, a STUDENT can have only one RollNo. Therefore,
RollNo is a single valued attribute.
 MULTI-VALUED ATTRIBUTES
An attribute that may have more than values for an instance is called multi-
valued attribute. For example, a STUDENT may have more than one
TelephoneNo. Therefore, TelephoneNo is a multi-valued attribute.
 DERIVED ATTRIBUTES
An attribute that is not actually stored in the database but derived from other
attribute(s) is called derived attribute. For example, Grade of a STUDENT is not
stored in the database. It is derived from Marks. Therefore, Grade is a derived
attribute.
 IDENTIFIER
An attribute(s) that can uniquely identify each entity instance within an entity
set is called identifier. For example, RollNo of a STUDENT can uniquely identify an
instance within STUDENT entity set.

26 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

3. RELATIONSHIPS
The association between different entity instances is called relationship. For
example, a relationship exists between instances of STUDENT and CLASS because
students are enrolled in the classes.
A relationship is called total if all instances of the entities are related with one
another. A relationship is called partial if some instances of the entities are related with
one another.
 DEGREE OF RELATIONSHIPS
The number of entities whose instances are related with one another is
called degree of relationship.
i. UNARY RELATIONHSIP
The relationship of degree one is
called unary relationship. In a unary
relationship, the instances of only one
entity are related with each other.
ii. BINARY RELATIONSHIP
The relationship of degree two is called binary relationship. In a
binary relationship, the instances of two different entities are related with
each other.
iii. TERNARY RELATIONSHIP
The relationship of degree three is called ternary relationship. In a
ternary relationship, the instances of three different entities are related with
each other.

27 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

 CARDINALITY OF RELATIONSHIPS
The number of entity instances of one entity which are related to the other
entity is called cardinality of relationship.
The most common type of relationship is binary relationship. Therefore, we
will discuss cardinality of the binary relationship.
Depending upon the cardinality, the relationship may be called as one-to-
one relationship, one-to-many relationship, and many-to-many relationship.
i. ONE-TO-ONE RELATIONSHIPS
In one-to-one relationship, one instance of entity X is related to only
one instance of entity Y and one instance of entity Y is also related to only
one instance of entity X.
For example, a company car can be allocated to only one employee
while an employee can be allocated only one company car, then this
relationship is called one-to-one relationship.

ii. ONE-TO-MANY RELATIONSHIPS

In one-to-many relationship, one instance of entity X is related to
many instances of entity Y but one instance of entity Y is related to only one
instance of entity X.
For example, a department has many employees working in it while
an employee can work in only one department, then this relationship is
called one-to-many relationship.

iii. MANY-TO-MANY RELATIONSHIPS

In many-to-many relationship, one instance of entity X is related to
many instance of entity Y and one instance of entity Y is also related to
many instances of entity X.
For example, a project may have many employees working on it
while an employee may work on many projects as well, then this
relationship is called many-to-many relationship.

28 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

 MODALITY OF RELATIONSHIPS
The modality of relationships determines whether a relationship is
mandatory or optional. For example, if each company car must be allocated to
some employee then the relationship is mandatory. If each employee may or may
not be allocated a company car then the relationship is optional.

29 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

18) EXPLAIN STEP-WISE PROCEDURE OF CREATING AN E-R DIAGRAM WITH THE HELP
OF AN EXAMPLE?

SCENARIO FOR E-R DIAGRAM

Consider the following scenario to design an Entity Relationship (ER) model for a
college database. We have the following statements.
 A college contains many departments
 Each department can offer any number of courses
 Many instructors can work in a department
 An instructor can work only in one department
 For each department there is a Head
 An instructor can be head of only one department
 Each instructor can take any number of courses
 A course can be taken by only one instructor
 A student can enroll for any number of courses
 Each course can have any number of students

STEP – 1: IDENTIFY THE ENTITIES

From the given statements, the entities are:
1. Department
2. Course
3. Instructor
4. Student

STEP – 2: IDENTIFY THE RELATIONSHIPS AND THEIR CARDINALITIES

1. One department offers many courses. But one particular course can be offered
by only one department. Hence the cardinality between department and course
is One-to-Many. (1:N)
2. One department has multiple instructors. But instructor belongs to only one
department. Hence the cardinality between department and instructor is One-
to-Many. (1:N)
3. One department has only one head and one head can be the head of only one
department. Hence the cardinality is one to one. (1:1)
4. One course can be enrolled by many students and one student can enroll for
many courses. Hence the cardinality between course and student is Many-to-
Many. (M:N)
5. One course is taught by only one instructor. But one instructor teaches many
courses. Hence the cardinality between course and instructor is Many-to-One.
(N :1)

30 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

STEP – 3: IDENTIFY THE KEY ATTRIBUTES

1. Department_Name can identify each department instance uniquely. Hence

Department_Name is the key attribute for the Entity "Department".
2. Course_ID is the key attribute for "Course" Entity.
3. Student_ID is the key attribute for "Student" Entity.
4. Instructor_ID is the key attribute for "Instructor" Entity.

STEP – 4: IDENTIFY THE OTHER RELEVANT ATTRIBUTES

1. For the department entity, other attributes are location

2. For course entity, other attributes are course_name,duration
3. For instructor entity, other attributes are first_name, last_name, phone
4. For student entity, first_name, last_name, phone

STEP – 5: DRAW COMPLETE E-R DIAGRAM

By connecting all these details, we can now draw ER diagram as given below.

31 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

19) WHAT IS MEANT BY LOGICAL DATABASE DESIGN PROCESS? WHAT ACTIVITIES

ARE PERFORMED DURING LOGICAL DATABASE DESIGN PROCESS?

LOGICAL DATABASE DESIGN PROCESS

The process of mapping conceptual schema to data model of the selected DBMS is
called logical database design process. The logical database design describes what is
stored in the database. It describes all entities, attributes and their relationships. It also
describes various constraints on the data. It is the complete description of data stored in the
database.
The logical database design process consists of the following steps:
1. REPRESENT ENTITIES
In this step, each entity in the conceptual model is converted into a relation. The
name of entity becomes name of the relation. The attributes of entity becomes attributes
of the relation. The identifier of entity becomes primary key of the relation.
2. REPRESENT RELATIONSHIPS
In this step, each relationship in conceptual model is also represented in the logical
database design. The representation of a relationship depends upon its type. It may be
represented by including primary key of one of the relation as foreign key in the other
relation or by making a new relation including primary key of both relations as its foreign
keys. The primary key of new relation consists of both foreign keys of the related
relations.
3. NORMALIZE RELATIONS
In this step, the relations created in step 1 and step 2 are normalized. The
normalization helps in controlling unnecessary data redundancy. As a result of the
normalization, new relations are created from the existing relations. The normalization
also helps in removing various database anomalies.
4. MERGE RELATIONS OR DENORMALIZATION
In this steps, the relations resulted from normalization may be merged again. This
process is also called de-normalization. This merging is done to meet performance
constraints of the database.
When a user requests data from multiple relations, it takes more time due to
complexity of the operation. It may be the case that the frequency of such operations is
very high. Therefore, relations are merged to improve the performance of such
operations.

32 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

20) WRITE A DETAILED NOTE ON STAGED DATABASE DESIGN APPROACH?

STAGED DATABASE DESIGN APPROACH

Staged database design approach is a top-down approach. It starts by analyzing the

database system as a whole. It divides the development task into different stages. The
different stages of this approach are as follows:
1. ANALYZE USER REQUIREMENTS
In this step, the database designer studies existing system and its inputs and
outputs. He uses different techniques like interviews to list down current and future
requirements of the users.
2. DEVELOP CONCEPTUAL MODEL
In this step, the database designer develops
conceptual model of the database. It describes all entities,
attributes and their relationships. It also describes security,
integrity, performance and financial constraints of the
database.
3. SELECT THE DBMS
In this step, the database designer selects a
database management system that satisfies all the
requirements and constraints identified in the conceptual
model. He considers cost, features, hardware requirements
and performance of the DBMS.
4. DEVELOP LOGICAL DATA MODEL
In this step, the database designer maps conceptual
model to the data model used by the selected DBMS. He
actually develops logical data model of the database.
5. DEVELOP PHYSICAL DATA MODEL
In this step, the database designer creates exact
layout of the database. This layout is described using
physical schema. He creates this layout according to the
facilities and data structures provided by the selected
DBMS.
6. EVELUATE PHYSICAL DATA MODEL
In this step, the database designer evaluates
performance of the physical model. He may implement a portion of the database to
validate external views and performance requirements. He implements portion of the

33 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

database to identify weaknesses and suggest improvements.

7. TUNE SYSTEM
In this step, the database designer modifies layout of the database to overcome
issues identified in the previous stage. Tuning improves performance of the database
when it is implemented. As a result of tuning, the database users will be more satisfied.
8. IMPLEMENT SYSTEM
In this step, the database designer implements the database. He creates the tables
with required fields and appropriate data types. He also applies integrity constraints on
data. End-users and database administrators start using the database system to perform
their day-to-day business operations.

21) WHO IS DATABASE ADMINISTRATOR? DESCRIBE THE FUNCTIONS OF DATABASE

ADMINISTRATOR?

DATABASE ADMINISTRATOR (DBA)

Database administrator is a key person in the development and implementation of a

database system. He is the only technical person within the organization. He knows the
current environment as well as the technical details of database systems. He acts as a
bridge between end-users, database designer and application programmers.

FUNCTIONS OF DATABASE ADMINISTRATOR

The important functions of database administrator are as follows:

1. PRELIMINARY DATABASE PLANNING
DBA should be appointed early in the development process. He may participate in
preliminary database planning.
2. IDENTIFYING USER REQUIREMENTS
DBA has a better knowledge about the organization and its objectives. He plays a
vital role in identifying the user requirements.
3. DEVELOPING AND MANAGING DATA DICTIONARY
DBA is responsible for developing the data dictionary. The data dictionary contains
definitions of all data items. It is updated whenever a new data item is added or removed
from the database.
4. DESINGING CONCEPTUAL DATA MODEL
DBA creates conceptual model of the database. The conceptual model describes
all entities, attributes and their relationships. It also describes the volume of data, types
and frequency of transactions, performance and consistency constraints etc.

34 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

5. SELECTING THE DBMS

DBA plays his role in selecting the DBMS. The DBMS is selected according to the
user requirements and other constraints identified at the conceptual level.
6. MAPPING CONCEPTUAL DATA MODEL TO LOGICAL DATA MODEL
DBA maps conceptual model of the database to data model of the selected DBMS.
He actually develop logical model of the database.
7. DEVELOPING PHYSICAL DATA MODEL
DBA creates exact layout of the database. He creates this layout according to the
facilities and data structures provided by the selected DBMS.
8. CREATING AND LOADING DATABASE
DBA creates the structure of database. He creates the tables with required fields
and appropriate data types. He also applies integrity constraints on data. After creating
the database, he also loads data into the database.
9. DEVELOPING USER VIEWS
DBA creates external views for the different user-groups. He also guides end-users
about functionality of the database.
10. WRITING AND MAINTAINING DOCUMENTATION
DBA is responsible for writing and maintaining the documentation about the
database system. This documentation is used whenever the database system is modified.
11. DEVELOPING AND ENFORCING DATA STANDARDS
DBA is responsible for developing and enforcing the data standards. The data
must follow constraints define by data standards. The data standards ensure accuracy
and reliability of data.
12. DEVELOPING OPERATING PROCEDURES
DBA is responsible for developing the standard operating procedures (SOPs) for
all operations. These procedures include security and authorization, recording database
failures, measuring performance, taking backups and recovering the database in case of
a failure.
13. TRAINING OF THE END-USERS
DBA is responsible for training of the end-users. He guides end-users that how
they can use database system effectively to perform their day-to-day business operations.
14. TUNING AND REORGANIZING
DBA is responsible for tuning and reorganization of the database. The tuning and
reorganization is required if the database fails to satisfy performance constraints. The
DBA may have to modify indexes or even change the physical model of the database.

35 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

DATA ADMINISTRATOR (DA)

Data Administrator is appointed in very large organizations where more than one
databases are used. Each database is designed, developed and managed by its DBA.
Data Administrator is responsible for overall data of the organization. He develops
organization wide data standards and operating procedures. He also participates with DBAs
in design, development and management of the databases.

22) WHAT IS A RELATION? WHAT ARE THE PROPERTIES OF A RELATION?

RELATION

Relation is a fundamental data structure in relational databases. All of the data is

stored in relations. It is also called table.

PROPERTIES OF RELATIONS

The properties of database relations are as follows:

 There are no duplicate rows.
 There are no duplicate columns.
 The order of rows has no importance.
 The order of columns has no importance.
 The attributes of a relation must have atomic values.
 There are no multi-valued attributes or repeating groups.
 The values of an attribute for all tuples must belong to same domain.

23) WHAT ARE KEYS? WRITE A NOTE ON DIFFERENT TYPES OF KEYS IN RELATIONAL
DATABASES?

KEYS

An attribute or set of attributes that have unique values for each tuple is called key.
Therefore, it is used to uniquely identify each tuple in a relation. Keys are defined to enforce
integrity constraints. They are used for searching and sorting the stored data. They are also
used to relate data stored in multiple tables.
1. SUPER KEY
Super key is an attribute or set of attributes that uniquely identifies each tuple in a
relation. It may have some extra attributes. The tuples can also be uniquely identified
without these attributes. A relation may have more than one super key.
2. CANDIDATE KEY
A super key without any extra attribute is called candidate key. It can also be
defined as ‘The minimal super key is called candidate key’. The tuples can also be

36 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

identified uniquely with a candidate key. A relation may have more than one candidate
keys.
3. PRIMARY KEY
The selected candidate key is called primary key. A relation may have more than
one candidate keys but only one of them is selected to uniquely identify tuples in a
relation. A relation can have only one primary key.
4. ALTERNATE KEY
A candidate key that is not selected as primary key is called alternate key. It can
also be used to uniquely identify each tuple in a relation. It is used when the value of
primary key is unknown for a tuple. A relation may have more than one alternate key or it
may not have any alternate keys.
5. FOREIGN KEY
An attribute or set of attributes that is used as primary key another table is called
foreign key. The values of foreign key must match to the values of primary key in other
table. It is used to relate data stored in multiple tables. A relation may have more than one
foreign key.
It is important to note that the foreign key does not hold uniqueness property.
6. COMPOSITE KEY
Any key that consists of more than one attributes is called composite key.

ENTITY INTEGRITY

Entity integrity is a constraint on primary key. It can be described by the following

rules:
 The primary key cannot contain duplicate values
 No attribute of the primary key can be NULL

REFERENTIAL INEGRITY

Referential integrity is a constraint on foreign key. It can be described by the following

rules:
 The value of foreign key must match to a value of primary key in parent table.
 OR, all the attributes of foreign key must be NULL.

37 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

Consider the following LIBRARY SYSTEM with following two relations to clarify the
concepts discussed above:
MEMBER(MemberID, Name, DOB, CNIC#, PhoneNo, Address)
BOOK(BookID, Title, Category, Author, MemberID)
----------------------------------------------------------------------------------------------------------------------------------
The MemberID and CNIC# in MEMBER relation are unique for each tuple.
Similarly BookID in BOOK relation is unique for each tuple also. The BOOK relation also
contains an attribute MemberID which is unique in MEMBER relation but not in the BOOK
relation.
For MEMBER relation

Attribute(s) Super Candidate Primary Alternate Foreign Composite

Key Key Key Key Key Key

MemberID, Name, DOB √ × × × × √

CNIC#, Name, DOB √ × × × × √

MemberID, PhoneNo √ × × × × √

CNIC#, Address √ × × × × √

MemberID √ √ √ × × ×

CNIC# √ √ × √ × ×

DOB × × × × × ×

-----------------------------------------------------------------------------------------------------------------------
For BOOK relation

Attribute(s) Super Candidate Primary Alternate Foreign Composite

Key Key Key Key Key Key

BookID, Title, Author √ × × × × √

BookID, Title, MemberID √ × × × × √

BookID, Title √ × × × × √

BookID, Author √ × × × × √

BookID √ √ √ × × ×

MemberID × × × × √ ×

38 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

24) WHAT ARE DIFFERENT TYPES OF DEPENDENCIES IN DATABASE RELATIONS?

The database relations may have following types of dependencies:

FUNCTIONAL DEPENDENCY
Functional dependency is a relationship between attributes of the same relation.
Consider a relation R with attributes A, B and C. If A uniquely determines B and C then B
and C are functionally dependent on A. It is written as A -> B and A -> C. In this case A is
called determinant and B and C are called dependent attributes.
TRANSITIVE DEPENDENCY
Transitive dependency is a relationship between attributes of the same relation.
Consider a relation R with attributes A, B and C. If A uniquely determines B and B uniquely
determine C then A determines C also. It is written as A -> B and B -> C then A -> C. In
this case C is transitively dependent on A.

25) WHAT IS NORMALIZATION? WHAT ARE DIFFERENT NORMAL FORMS AND HOW
THEY ARE ACHIEVED?

NORMALIZATION
Normalization is a technique of organizing data in the database. It is a systematic
approach of decomposing tables to eliminate data redundancy and database anomalies. It is
a multi-step process that removes duplicated data from the relations.
PROBLEMS WITHOUT NORMALIZATION
Without normalization, it is very difficult to handle and update data in the database.
Normalization is systematic approach of decomposing tables to eliminate data redundancy
and database anomalies. Anomalies are problems that can occur in a poorly planned and un-
normalized database.
To understand these anomalies consider the following STUDENT relation:

SID CourseID CourseName SName Address

101 CS201 Computer Science M. Rayyan Multan

102 MT304 Mathematics M. Subhan Lahore

103 PS106 Physics M. Hassaan Islamabad

101 AG222 Accounting M. Rayyan Multan

1. UPDATION ANOMALY
Update anomaly occurs when same data item is required to be updated more than
once. It can result into data inconsistency.
In the above relation, if we have to update the ADDRESS of M. Rayyan then we

39 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

will have to update it more than once; otherwise the data will be inconsistent. This is
called update anomaly.
2. INSERTION ANOMALY
Insertion anomaly occurs when an additional data needs to be entered when some
data is inserted into the database.
In the above relation, if we have to insert the record of a new student who has not
taken any course yet then we cannot enter his data without entering the information about
course because CourseID cannot be null. This is called insert anomaly.
3. DELETION ANOMALY
Deletion anomaly occurs when some important data is lost while deleting other
data.
In the above relation, if M. Hassaan drops the Physics course then we will have to
delete the entire row. In this way we will lose all the information about M. Hassaan. This
is called delete anomaly.

40 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

FIRST NORMAL FORM (1NF)

A relation is in 1NF if and only if it does not contain any repeating group or multi-
valued attribute. It means that each field of the relation must contain atomic value.
In the following relation, key attribute is SID where as SName, CourseID,
CourseName and Address are non-key attributes:
REGISTRATION

SID SName CourseID CourseName Grade Address

C201 Programming Fundamentals A

101 M. Rayyan Multan
M304 Calculus B

M304 Calculus B
102 M. Subhan Lahore
C201 Programming Fundamentals C

103 M. Hassaan P106 Introduction to Physics B Islamabad

In the above relation, CourseID, CourseName and Grade are multi-valued attributes.
Therefore, it is not in 1NF. It can be converting into 1NF by eliminating multi-valued
attributes.
The following relation is in 1NF with composite primary key (SID, CourseID):
REGISTRATION

SID CourseID SName CourseName Grade Address

101 C201 M. Rayyan Programming Fundamentals A Multan

101 M304 M. Rayyan Calculus B Multan

102 M304 M. Subhan Calculus B Lahore

102 C201 M. Subhan Programming Fundamentals C Lahore

103 P106 M. Hassaan Introduction to Physics B Islamabad

SECOND NORMAL FORM (2NF)

A relation is in 2NF if and only if it is in 1NF and no non-key attribute is partially depen
dent upon key.
In the following relation, key attributes are SID and CourseID where as SName,
CourseName, Grade and Address are non-key attributes.

41 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

REGISTRATION

SID CourseID SName CourseName Grade Address

101 C201 M. Rayyan Programming Fundamentals A Multan

101 M304 M. Rayyan Calculus B Multan

102 M304 M. Subhan Calculus B Lahore

102 C201 M. Subhan Programming Fundamentals C Lahore

103 P106 M. Hassaan Introduction to Physics B Islamabad

In the above relation, SName and Address are partially dependent upon SID
while CourseName is partially dependent upon CourseID. Therefore, it is not in 2NF. It
can be converted into 2NF by splitting it into following three relations:
REGISTRATION

SID CourseID Grade

101 C201 A

101 M304 B

102 M304 B

102 C201 C

103 P106 B

STUDENT COURSE

SID SName Address CourseID CourseName

101 M. Rayyan Multan C201 Programming Fundamentals

102 M. Subhan Lahore M304 Calculus

103 M. Hassaan Islamabad P106 Introduction to Physics

The CourseID is included in REGISTRATION relation as foreign key. It is included

to make relationship between REGISTRATION relation and COURSE relation.
The SudentID is included in REGISTRATION relation as foreign key. It is included
to make relationship between REGISTRATION relation and STUDENT relation.
It is clear from the above relations that the data redundancy has been reduced.

42 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 PAPER - III DATABASE SYSTEMS

THIRD NORMAL FORM (2NF)

A relation is in 3NF if and only it is in 2NF and no non-key attribute dependent upon
another non-key attribute.
In the following relation, key attribute is TID where as TName, Qualification, CourseID
and CourseName are non-key attributes.
TEACHER

TID TName Qualification CourseID CourseName

T01 M. Fahim MPhil C201 Programming Fundamentals

T02 Nasir Mehmood PhD M304 Calculus

T03 M. Fahim MPhil G104 Physical Geography

T04 Qamar Fareed MPhil S202 Business Statistics

In the above relation, CourseName is dependent upon another non-key attribute i.e.
CourseID. Therefore, it is not in 3NF. It can be converted into 3NF by splitting it into following
two relations:
TEACHER

TID TName Qualification CourseID

T01 M. Fahim MPhil C201

T02 Nasir Mehmood PhD M304

T03 M. Fahim MPhil G104

T04 Qamar Fareed MPhil S202

COURSE

CourseID CourseName

C201 Programming Fundamentals

M304 Calculus

G104 Physical Geography

S202 Business Statistics

The CourseID is included in TEACHER relation as foreign key. It is included to make

relationship between TEACHER relation and COURSE relation.

43 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

BOYCE CODD NORMAL FORM (BCNF)

A relation is in BCNF if and only if it is in 3NF and every determinant is a candidate
key. BCNF is a higher form of 3NF.
Usually a relation in 3NF is already in BCNF
but it may be the case when a relation is in 3NF but
not in BCNF. This happens when a relation has
composite primary key and a non-key attribute is a
determinant of some key attribute. This is shown in
the diagram.
Here, A and B are key attributes while C and D are non-key attributes. The relation is
in 3NF because A,B -> C and A,B -> D but it is not in BCNF as C -> B.

26) EXPLAIN NORMALIZATION PROCESS USING A STEP BY STEP EXAMPLE?

SCENRIO FOR NORMALIZATION

Customers belong to a particular branch, this branch is supervised by particular

manager. Customers purchase/order stocks. Each stock has a title and format.
UNF: List all the attributes in one relation and choose a primary key.
CustomerID
Name
Address
BranchNo
BranchManager
StockID
Title
Format
1NF: Identify repeating groups or multi-valued attributes. Move each repeating group and its
dependent attributes into a separate table.
In the above example, StockID is a repeating group while Title and Format are
dependent on StockID. Therefore, we split our un-normalized relation into following relations:

CUSTOMER Relation ORDER Relation

CustomerID CustomerID
Name StockID
Address Title
BranchNo Format
BranchManager

The above relations are in 1NF as they do not contain any repeating group.
44 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26
 PAPER - III DATABASE SYSTEMS

2NF: Identify non-key attributes that are dependent upon part of the key and move them into
a separate table. A relation having simple primary key (a key that consists of only one
attribute) is already in 2NF as the key cannot be sub-divided. Therefore, carefully examine
only those relations which have composite primary key.
CUSTOMER Relation has simple primary key so it is in 2NF.

In ORDER Relation Title and Format are dependent upon part of the key i.e. StockID
so it is not in 2NF. Therefore, we split our relation into following relation:

ORDER Relation STOCK Relation

CustomerID StockID
StockID Title
Format

The above relations are in 2NF as they do not contain any attribute that depends upon part
of the key.

2NF: Identify non-key attributes that are dependent upon other non-key attributes and move
them into a separate table.
ORDER Relation and STOCK Relation do not have any non-key attribute that is
dependent upon a non-key attribute. Therefore, these relations are already in 3NF.

CUSTOMER Relation has a non-key attribute BranchManager that depends upon

another non-key attribute BranchNo so it is not in 3NF. Therefore, we split our relation
into following relations:

CUSTOMER Relation BRANCH Relation

CustomerID BranchNo
Name BranchManager
Address
BranchNo

The above relations are in 3NF as they do not contain any non-key attribute that
depends upon another non-key attribute.

45 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26

 [Link]. COMPUTER STUDIES

After going through normalization process, we have following normalized relations:

CUSTOMER Relation
CustomerID
Name
Address
BranchNo

BRANCH Relation
BranchNo
BranchManager

ORDER Relation
CustomerID
StockID

STOCK Relation
StockID
Title
Format

46 Prof. M. Fahim, Govt. Graduate College, Gulberg, Lahore. 0300 – 774 25 26