1

DATABASE MANAGEMENT SYSTEM

1. DATA MOELING FOR A DATABASE:

The database is used to store information useful to an organization.

To represent this information, some mean of modeling is used.
Different components are used in this modeling
These components are limited according to objects and relationship among the objects
These objects have certain characteristics or properties are called its attributes

1.1 ENTITIES AND THEIR ATTRIBUTES:

1.1.1. ENTITIES:
Entities are the basic unit used in modeling classes of concrete objects. Entity is a thing in the real world with an independent
existence.
Example:
Building, room, chair, transaction, course, machine, employee.

1.1.2. ENTITY TYPE (or) ENTITY SET:

It is a group of similar objects of concern to an organization for which it maintains data. (entity set is collection or set all entities
of a particular entity type at any point of time.
example:
a company have many employees ,and these employees are defined as entities(e1,e2,e3....) and all these entities having same
attributes are defined under ENTITY TYPE
Example:
Transaction concepts, job position, course, employee, inventories, students.
 2

1.1.3. ATTRIBUTES: An attribute is also referred to by the term data item, data element, data field,dataitem.

EMPLOYEE

NAME SEC-SEC.NOADDRESS SKILL DESIGN

Error:
Reference source not found

1.1.4. ATTRIBUTE VALUE AND DOMAINS:

ENTITY SET ATTRIBUTES VALUE

NAME GEORGE
SEC-SEC-NO 78739
EMPLOYEE ADDRESS VELLORE
SKILL COOK
ANNUAL-SAL 4255.00

Domain: the set of possible values that a given attribute can have is called domain.

1.2. KEYS:
A key is a single attribute or combination of two or more attribute of an entity set that is used to identify one or more
instance (occurrence) of the set.

There are various types of keys

 primary key
 super key
 candidate key
 secondary key
 3

1.2.1. PRIMARY KEY:

Primary key is used to uniquely identify a record. It is also called entity identification.There should be only one primary
key for a table
Example:
Empno in the employee table.

1.2.2. SUPER KEY:

In super key it includes any number of attributes that process a uniqueness property.

Example:
If we add additional attribute to a primary key the resulting combination would still uniquely identify an instance of the
entity set. Such key are called super key.
Eg: employee Number and Phone number

1.2.3. CANDIDATE KEY:

More than one data item is used to identifying a record, it is called candidate key.

EXAMPLE: Both empno and empname is employee table

1.2.4. SECONDARY KEY:

Is used for Identification of Rows but not usually Unique We can have multiple Secondary Key per table
It is an attribute or combination of attribute that may not be a candidate key but that classifies the entity set on a
particular characteristics.

1.3. RELATIONSHIP:
The relationship set is used in data modeling to represent an association between entity set.

Relationship could be of following types:

 one to one relationship

 one to many relationship
 many to many relationship
 4

example:
Error: Reference source not found
EMPLOYEE EMPLOYEE-NO

DEPARTMENT EMPLOYEE

EMPLOYEE SALARY

2. THE THREE LEVEL ARCHITECTURE PROPOSAL FOR A DBMS:

ANSI-SPARC three-tier database architecture is shown in the figure. It consist of following three levels:

 internal level
 conceptual level or logical view
 external level
The view at each level is described by a scheme. A scheme is an outline or plan that describes the records and relationships
existing in the view. It can be called as schema

View View View

A B C

Error: Reference source not

found
 5

2.1 EXTERNAL OR USER VIEW:

 It is at the highest level of database abstraction.(concept)

 It is other wise called user/application view defined by user or application programmer
 Each external view is described by mean of a scheme(method ) called an external schema.
 The external schema consists of the definition of the logical records(related data elements) and the relationship in
the external view
 It contains methods and objects
 The objects includes entities, attributes and relationships

2.2. CONCEPTUAL OR GLOBAL VIEW or logical view

It describes structure of whole database

 At this level all the database entities and the relationship among them are included.
 The conceptual view is defined by the conceptual schema
 It describes records and relationships
 This schema contains the methods of deriving objects from the objects in internal view

2.3. INTERNAL VIEW or physical

Describes physical storage structure of the database

 This view all the lowest level of abstraction.

 It indicates how the data will be stored and describes the data structure and access methods to be used by the
database.

 The internal view expressed by the internal schema, which contains the definition of the stored record, the methods
of representing the data fields, and the access aids used.
 6

 How the data is stored in the database.

 Physical implementation of the DB to achieve optimal run–time performance and storage space utilization.
 Storage space allocation for data and indexes
 Record description for storage
 Record placement
 Data compression, encryption

3. MAPPING BETWEEN VIEWS:

The process of programming requests and results between the Internal, Conceptual & External levels are called mapping.

 Conceptual database is the model of the object of the concern database

View is the subset of the object model in the conceptual database that is used by an application. There could be any
number of views of a conceptual database
A view can be used to limit the portion of the database that is known and accessible to a given application
 DB schema: overall description of the DB.
 ANSI-SPARC model provides the following two-stages mapping as,
1. external/conceptual mapping.
 7

2. conceptual/internal mapping.

3.2. EXTERNAL / CONCEPTUAL MAPPING:

 The external conceptual mapping defines the communication between a particular external view and the conceptual view.

 It provide the communication among the records and relationship of the external and conceptual view.

3.1. CONCEPTUAL /INTERNAL MAPPING:

 The conceptual internal mapping defines the communication between the conceptual view and the storage database. or
internal view

 It specifies how conceptual records and field are presented at the internal view.

 In case of any changes in the structure of the stored database, the conceptual/internal mapping is also changed according
by the DBA.
n

4. DATA INDEPENDENCE:
Data Independence is the ability to modify a schema definition in one level without affecting a schema definition in the
next higher level.

Data independence is a major objective of implementing DBMS in an organization.

There are two types of data independence the mapping at three tier architecture,

1. Physical data independence.

2. Logical data independence.

4.1. PHYSICAL DATA INDEPENDENCE:

The ability to change the physical schema without changing the logical schema is called
physical data independence.
 8

It indicates that the physical storage structure could be changed without a change in the conceptual view or any of the
external view.

For example, a change to the internal schema, such as using different file organization or storage structures, storage
devices, or indexing strategy, should be possible without having to change the conceptual or external schemas.
Modifications at this level are usually to improve performance.

4.2. LOGICAL DATA INDEPENDENCE:

The ability to change the logical (conceptual) schema without changing the External schema (User View) is called
logical data independence.

For example, the addition or removal of new entities, attributes, or relationships to the conceptual schema should be
possible without having to change existing external schemas or having to rewrite existing application programs.

 In logical data independence, the user are shield from changes in the logical structure of the data.

 Changes to the conceptual schema, such as additional and deletion of the entity, attribute, relationship, must be
possible without changing existing external schemas.

Usually done when logical structure of database is altered

5. COMPONENTS OF DBMS:

5.1.CLASSIFICATIONS OF DBMS USERS.

The users of database system can be classified into the following groups,

1. Navie users
2. Online users
3. Application programmers
4. Database administration.

5.1.1. NAVIE USERS:

Users interact with the system with one of the application program that have been written previously.
 9

Example:
A bank teller who needs to transfer, this program asks the teller for the amount of money to the transferred the account
from which the money is to be transferred, and the account to which the money is to be transferred.

5.1.2. ONLINE USERS:

 These are users who may communicate with the database directly via an online terminal or indirectly via a user
interface and application program.

 These users are aware of the presence of the database system.

5.1.3. APPLICATIONN PROGRAMMERS:

Application programs are computer professional who interact with the system through DML calls which are embedded
in a program written in a host language

Example:
COBOL, PL/I, PASCAL, C.

These programs are commonly referred to as application programs.

Example in banking system include programs that contains payroll checks, that debit accounts, that credit accounts.

5.1.4. DATA BASE ADMINISTRATOR:

 Centralized control of the database is exerted by a person or group of persons under the supervision of a high-level
administrator.

 Thus person or group is referred to as the database administrator ( DBA)

 He is in charge of the data and control the database structure

 He is responsible for external view, definition and implementation of internal view and also conceptual view

 Mapping is also defined by DBA

 10

 He is responsible for granting permission to the users of the database and stores theprofile of each user in the
database

 They are the users who are most familiar with the database and are responsible for creating, modifying and
maintaining, its three levels,

6. DBMS FACILITIES:

Two main types of facilities are provided by a DBMS

1. The data definition facility (DDL)
2. The data manipulation facility( DML)

6.1. Data Definition Facility (Or) Language:

o It can be used to define the conceptual schema and also give some details about how to implement this schema
in the physical device used to store the data.
o The definition includes all the entity set and association and its relationship
o The definition also includes constraints
o It describes the compiled form of definition such as data dictionary, directory or system catalogue.

6.2. Data Manipulation Language:

 This language used to manipulate data in the database.

 Data manipulation involves retrieval of data from the database, insertion of new data into the database or deletion or
modification of existing data.

 These manipulation operations is called a query.

 A query is a statement in the DML that requests the retrieval of data from the database.

7. STRUCTURE OF DBMS:

The major components of database management systems are

 11

Data definition language compiler

The DDL compiler converts the data definition statements into a set of tables

Data Manager

 Data manager is the central software component of the DBMS

 One of the functions of the data manager is to convet operations in the user’s queries coming directly via the query
processor or indirectly via an application program to a physical file system
 It is responsible for interfacing with the file system
 It is also responsible to maintain the integrity and security of the data
 It also entrusted with backup and recovery operations

File Manager

 It is responsible for the structure of files and managing the file space
 It also responsible for locating the block containing the required record , requesting the block from the disk manager and
transmitting the required record to the data manager
 12

Disk Manager
 It is a part of Os
 All input and output operations are performed by it
 The disk manager transfers the block or page requested by the file manager
Query Processor
 It is used to interpret the online user’s query and convert it into an efficient series of operation in a form capable of
being sent to the data manager for execution
 It makes use of data dictionary to find the structure of relevant portion of the database and uses this information

Data Files
Data files contain the data portion of the database

Data Dictionary

 which stores metadata about the structure of database.

 The system catalog also describes this meta data
 The data dictionary which is a database itself, documents the data
 The data dictionary stores information concerning the external conceptual and internal levels of the data
base
 It stores the source of each data filed value, the frequency of its use and an audit trail concerning updates
including who and when of each update
Access Aids
Indexs are used to access the records
Commands are available to build and destroy additional temporary indexes

Data base Access

 Any access to the stored data is done by the data manager

 The user request for data is received by the data manager which determine the physical record required
 The decision will be taken with the consultation of data dictionary
 Then the data manager sends the request for a specific physical record to the file manager
 The file manager decides which physical block of secondary storage devices contains the required record and sends the
request for the appropriate block to the disk manager
 A block is a unit of physical input/output operation between primary and secondary storage
 The disk manager retrieves the block and send it to the file manager which sends the required record to the data manager
 13

8. ADVANTAGES AND DISADVANTAGES OF DBMS:

8.1. ADVANTAGES OF DBMS:

1. Reduction Of Redundancies.
2. Shared Data
3. Integrity
4. Security
5. Conflict Resolution
6. Data Independence

REDUCTION OF REDUNDANCIES:
 Centralized control of data by the DBA to avoids the unnecessary duplication of data.
 It also eliminates the extra processing necessary to trace the required data in a large mass of data.

SHARED DATA:
 A database allows the sharing of data under its control by any number of application programs or users.

INTEGRITY:
 Data integrity means that the data contained in the database is both accurate and consistent.
 Data values being entered for storage could be checked to ensure that they fall within a specified range.

Example:
The value of the age of an employee may be in the range of 16 and 75.

SECURITY:
 14

 Data is of vital importance to an organization and may be confidential.

 Such confidential data must not be accessed by un authorized persons.

CONFLICT RESOLUTION:
Since the database is under the control of DBA she/he should resolve the conflicting requirements of various users and
applications.

DATA INDEPENDENCE:
It consider from two point of view.
1. physical data independence
2. logical data independence.

Physical data independence allows changes in the physical storage devices.

Logical data independence indicates that the conceptual schema can be changed without affecting the existing external
schema.

8.2. DISADVANTAGES:
1. problems associated with centralization.
2. cost of software or hardware and migration.
3. complexity of backup and recovery.

DATA MODELS CLASSIFICATION:

A data model is a mechanism that provides this abstraction for database application.
Data models can be classified as
1. File Based System.
2. Traditional Data Models
3. Semantic Data Models.

9.1 FILE BASED SYSTEM

 Entity or objects of interest are represented by records that are stored together in files.
 Relationship between objects are represented by using directories of various kinds.
 15

9.2 TRADITIONAL DATA MODELS:

These are the hierarchical network and relational model.
9.3 SEMANTIC DATA MODELS:
 This class of data models was influenced by the semantic matters developed by the artificial intelligence researchers.
 Semantic data model are able to express greater independencies among entities of interest.

10. ENTITY RELATIONSHIP MODEL:

The database structure employing the ER model is usually shown pictorially using .

Symbols of E-R Diagram:

Rectangle which represents entity set.

Ovals which represents attributes.

Diamond which represents relationship set.

Lines which represents link attributes to entity set

and entity set to relationship set.
Example

SEC-SEC-NO CUS STREET

AMOUNT
LOAN-NO

CUS NAME CUS CITY

BORROWER

CUSTOMER LOAN
 16

Entities

An entity is an objects that is of interest to an organization. An entity often represents a group of people (eg children,
applicants, stakeholders) but it can also represent a group of objects (eg textbooks), activities (eg assignments) or concepts (eg school
terms).

Entities set:
Objects of similar are characterized by the same set of attributes or properties . such similar objects form entity set or
entity type. Or An entity set is a set of entities of the same type that share the same properties.
Example: set of all persons, companies, trees, holidays

Entity set classified into two

1. weak entity set
2. strong entity set.

WEAK ENTITY SET:

An entity set may not have sufficient attributes to form a primary key which means it will not have a primary key Such
an entity set is called a weak entity set.

Empdoj

Employee Emp name

Designation

STRONG ENTITY SET:

An entity set has a primary key then it is termed a strong entity set.

Emp-id

Employee Emp name

Designation
 17

10.2 RELATIONSHIP SETS:

 An association among entities is called relationship.

 A collection of relationship of the same type is called a relationship set.

 A relationship is a binary relationship it the number of entity set involves in the relationship is two.
 A relation that involves N entities is called N-any relationship.
Defintion :
Given the entity sets E1,E2……., Ek not necessarily distinct , then the relationship R is a subset of the set defined as

R is {e1,e2,….ek such that ei €Ei, I = 1 to k}

--------------

10.3 REPRESENTAION OF ENTITIES:

The entity set EMPLOYEE can be described as follows,

Entity set : EMPLOYEE

Emp-no : Numeric: ( * primary key*)
Name : String:
Skill : String:

THE EMPLOYEE TABLE:

EMPLOYEE:
EMP-NO NAME SKILLS
12345 John Waiter
12346 Kumar Busboy
12347 Go Bell boy
12348 Suresh Hosters

10.4 REPRESENTAION OF RELATIONSHIP SET:

 The entity relationship diagrams are useful in representing the relationship
among entities.

 E-R diagram Duty-Allocation relationship between entity sets

 18

EMPLOYEE and POSITION.

Date shift

duty allocate
EMPLOYEE position

EMPLOYEE:
EMP-NO NAME SKILL

12345 John Waiter

12346 Suresh Bus boy

POSITION:
POSITON-NO SKILL

321 Waiter
322 Bus boy

DUTY ALLOCATION:
POSITION-NO EMP NO DATE SHIFT

321 12345 9/12/09 1

322 12346 10/12/09 2

11. GENERALIZATION AND AGGREGATION:

11.1 GENERALIZATION:
 19

Abstract is the simplification mechanism used to hide superfluous details of a set of object it allows one to
concentrate on the properties that are of inters to the application
Eg: Car is an abstraction of vehicle but does not reveal details about model, year, color etc
Two main abstraction mechanism are generalization of aggregation;
Generalization is the process of identifying some common characteristic of a collection of entity set and creating a new
entity set.
Example:

No-of passengers model price

Vehicle-id
Max speed CAR

Licence no

price
model Vehicle-id

Max speed Registration

Two-wheeler

The above two entity type namely CAR, TWO-WHEELER is generalization to vehicle type.

MODEL PRICE

Max SPEED VEHICLE ID

VEHICLE

CAR TWO-
WHEELER
 20

No_of Paseenger

Registration

11.2 AGGREGATION:

 Aggregation is the process of compiling the information in an object there by abstracting a higher level object

 In this manner the entity person is derived by aggregation the characteristic name, address and social security
number.

Example of aggregation:

PERSON

NAME ADDRESS SSN SOCIAL SEC NO

Another form of aggregation is abstracting a relationship between objects and viewing the relationship as an object

REGISTRATION

ENROLLMENT
STUDENT COURSE

ENROLLMENT relationship between entities student and course could be viewed as entity REGISTRATION
 21

12. RELATIONAL DATA MODEL:

 The relational data model uses a collection of tables to represent both data and the relationship among those
data.
 In this model relation is the only construct required to represent the association among the attributes of an
entity as well as the relationship among different entities
 A relation may be visualized as a named table eg: customer and account
 Rows of the relation are referred as tuples and the column are its attributes
 Each attribute has distinct name
 The value for an attribute is called as domain
 Each table has multiple columns and each columns has a unique name.

Example:
customer:

CUS NAME SOCIAL SEC NO CITY ACC NO

Johnson 192-193 Paloalto A-101

Smith 019-28 Rye A-215
Hays 677-89 Harrison A-102
Johson 192-83 Paloalto A-201
Smith 019-28 Rye A-201

Account:
ACC-NO BALANCE

A-101 500
A-215 700
A-102 400
 22

a-201 900

For example:
Here two are identical and the ordering of the rows is not significant
 That customer Johnson with social security number 192-183, lives on paloatlto and has two account A-101, with a
balance of $500, and A-201 with a balance of $900.

 Note that customers Johnson and smith share account-no A-201.

13. NETWORK MODEL:

 Data in the network model are represented as collection of records

 Relationships in the network model are represented as links
 Each record is a collection of fields:
type customer = record
customer-name: string;
customer-street: string;
customer-city: string;
end
type account = record
account-number: string;
balance : integer;
end

Network Model – Data-Structure Diagrams

Data-structure diagram:
- Same purpose as an ER diagram.
- Boxes are represented as (record types)
- Lines are represented as (links)

 The records in the database are organized as collection of arbitrary graphs.

Example:
 23

A-101 500

A-215 700

A-102 400

A-201 900

Johnson 92-83 paloalto

Smith 019-28 rye

Hays 677-89 harrison

14. HIERARCHIAL MODEL:

 Hierarchical Database model is one of the oldest database models. This model is like a structure of a tree with the records
forming the nodes and fields forming the branches of the tree.

 The hierarchical model is similar to the network model in the sense that data and relationship among data are represented
by records and links respectively.

 It differs from the network model in that the records are organized as collections of trees rather than arbitrary graphs.

COMPARISON:
 24

The hierarchical data model :The hierarchical data model organizes data in a tree structure.
 There is a hierarchy of parent and child data segments.
 This structure implies that a record can have repeating information, generally in the child data segments.
 Data in a series of records, which have a set of field values attached to it.
 It collects all the instances of a specific record together as a record type.
 These record types are the equivalent of tables in the relational model, and with the individual records being the
equivalent of rows.
 To create links between these record types, the hierarchical model uses Parent Child Relationships.
 These are a 1:N mapping between record types.
 This is done by using trees, like set theory used in the relational model, "borrowed" from maths.

The network data model :

 The popularity of the network data model coincided with the popularity of the hierarchical data model.
 Some data were more naturally modeled with more than one parent per child.
 So, the network model permitted the modeling of many-to-many relationships in data.

The relational model:

 A database based on the relational model developed by E.F. Codd.
 A relational database allows the definition of data structures, storage and retrieval operations and integrity
constraints.
 In such a database the data and relations between them are organized in tables.
 A table is a collection of records and each record in a table contains the same fields.
properties of relational database: 1. Values Are Atomic 2. Each Row is Unique
3. Column Values Are of the Same Kind
4.The Sequence of Columns is Insignificant 5. The Sequence of Rows is Insignificant
6. Each Column Has a Unique Name

------------------------------------------------------------------------------------------------------------------------------------------------------