UNIT -1

INTRODUCTION TO BASIC CONCEPTS OF DATABASE SYSTEMS:

What is Data?

The raw facts are called as data. The word “raw” indicates that they have not been processed.

Ex: For example 89 is the data.

What is information?

The processed data is known as

information. Ex: Marks: 89; then it becomes

information. What is Knowledge?

1. Knowledge refers to the practical use of information.

2. Knowledge necessarily involves a personal experience.

DATA/INFORMATION PROCESSING:

The process of converting the data (raw facts) into meaningful information is called
as data/information processing.

When When
Data Information Knowledge
Processed Processed

Note: In business processing knowledge is more useful to make decisions for any organization.

[Link],[Link],CSE Department,SCCE Page 1

 DIFFERENCE BETWEEN DATA AND INFORMATION:

DATA INFORMATION
[Link] facts [Link] data
2. It is in unorganized form 2. It is in organized form
3. Data doesn’t help in 3. Information helps in
decision Decision
making process making process

FILE ORIENTED APPROACH:

The earliest business computer systems were used to process business records and
produce information. They were generally faster and more accurate than equivalent manual
systems. These systems stored groups of records in separate files, and so they were called
file processing systems.

1. File system is a collection of data. Any management with the file system, user has
to write the procedures

2. File system gives the details of the data representation and Storage of data.

3. In File system storing and retrieving of data cannot be done efficiently.

4. Concurrent access to the data in the file system has many problems like a Reading
the file while other deleting some information, updating some information

5. File system doesn’t provide crash recovery mechanism.

Eg. While we are entering some data into the file if System crashes then content of the
file is lost.

6. Protecting a file under file system is very difficult.

The typical file-oriented system is supported by a conventional operating system.

Permanent records are stored in various files and a number of different application
programs are written to extract records from and add records to the appropriate files.

[Link],[Link],CSE Department,SCCE Page 2

DISADVANTAGES OF FILE-ORIENTED SYSTEM:

The following are the disadvantages of File-Oriented System:

Data Redundancy and Inconsistency:

Since files and application programs are created by different programmers over a
long period of time, the files are likely to be having different formats and the programs may
be written in several programming languages. Moreover, the same piece of information
may be duplicated in several places. This redundancy leads to higher storage and access
cost. In addition, it may lead to data inconsistency.

Difficulty in Accessing Data:

The conventional file processing environments do not allow needed data to be

retrieved in a convenient and efficient manner. Better data retrieval system must be
developed for general use.

Data Isolation:

Since data is scattered in various files, and files may be in different formats, it is
difficult to write new application programs to retrieve the appropriate data.

Concurrent Access Anomalies:

In order to improve the overall performance of the system and obtain a faster
response time, many systems allow multiple users to update the data simultaneously. In
such an environment, interaction of concurrent updates may result in inconsistent data.

Security Problems:

Not every user of the database system should be able to access all the data. For
example, in banking system, payroll personnel need only that part of the database that has
information about various bank employees. They do not need access to information about
customer accounts. It is difficult to enforce such security constraints.

[Link],[Link],CSE Department,SCCE Page 3

Integrity Problems:

The data values stored in the database must satisfy certain types of consistency
constraints. For example, the balance of a bank account may never fall below a prescribed
amount. These constraints are enforced in the system by adding appropriate code in the
various
application programs. When new constraints are added, it is difficult to change the programs
to enforce them. The problem is compounded when constraints involve several data items
for different files.

application programs. When new constraints are added, it is difficult to change the programs to
enforce them. The problem is compounded when constraints involve several data items for
different files.

Atomicity Problem:

A computer system like any other mechanical or electrical device is subject to failure.
In many applications, it is crucial to ensure that once a failure has occurred and has been
detected, the data are restored to the consistent state existed prior to the failure

Example:

Consider part of a savings-bank enterprise that keeps information about all

customers and savings accounts. One way to keep the information on a computer is to store
it in operating system files. To allow users to manipulate the information, the system has a
number of application programs that manipulate the files, including:

• A program to debit or credit an account

• A program to add a new account

• A program to find the balance of an account

• A program to generate monthly statements

Programmers wrote these application programs to meet the needs of the bank. New
application programs are added to the system as the need arises. For example, suppose that
the savings bank decides to offer checking accounts.
As a result, the bank creates new permanent files that contain information about all
the checking accounts maintained in the bank, and it may have to write new application
programs to deal with situations that do not arise in savings accounts, such as overdrafts.

[Link],[Link],CSE Department,SCCE Page 4

 Thus, as time goes by, the system acquires more files and more application programs. The
system stores permanent records in various files, and it needs different

Application programs to extract records from, and add records to, the appropriate
files. Before database management systems (DBMS) came along, organizations usually
stored information in such systems. Organizational information in a file-processing system
has a number of major disadvantages:

Data Redundancy and Inconsistency:

The address and telephone number of a particular customer may appear in a file that
consists of savings-account records and in a file that consists of checking-account records.
This redundancy leads to higher storage and access cost. In, it may lead to data
inconsistency; that is, the various copies of the same data may no longer agree. For
example, a changed customer address may be reflected in savings-account records but not
elsewhere in the system.

1. Difficulty in Accessing Data:

Suppose that one of the bank officers needs to find out the names of all customers
who live within a particular postal-code area. The officer asks the data-processing
department to generate such a list. Because there is no application program to generate
that. The bank officer has now two choices: either obtain the list of all customers and
extract the needed information manually or ask a system programmer to write the
necessary application program. Both alternatives are obviously unsatisfactory.

2. Data Isolation:

Because data are scattered in various files and files may be in different formats,
writing new application programs to retrieve the appropriate data is difficult.

3. Integrity Problems:

The balance of a bank account may never fall below a prescribed amount (say, $25).
Developers enforce these constraints in the system by adding appropriate code in the
various application programs. However, when new constraints are added, it is difficult to
change the programs to enforce them. The problem is compounded when constraints
involve several data items from different files.

4. Atomicity Problems:

[Link],[Link],CSE Department,SCCE Page 5

 A computer system, like any other mechanical or electrical device, is subject to
failure. In many applications, it is crucial that, if a failure occurs, the data be restored to the
consistent state that existed prior to the failure. Consider a program to transfer $50 from
account A to account B. If a system failure occurs during the execution of the program, it is
possible that the
$50 was removed from account A but was not credited to account B, resulting in an
inconsistent database state. Clearly, it is essential to database consistency that either both
the credit and debit occur, or that neither occur. That is, the funds transfer must be atomic—
it must happen in its entirety or not at all. It is difficult to ensure atomicity in a conventional
file-processing system.

5. Concurrent-Access Anomalies:

For the sake of overall performance of the system and faster response, many
systems allow multiple users to update the data simultaneously. In such an environment,
interaction of concurrent updates may result in inconsistent data. Consider bank account A,
containing $500. If two customers withdraw funds (say $50 and $100 respectively) from
account A at about the same time, the result of the concurrent executions may leave the
account in an incorrect (or inconsistent) state. Suppose that the programs executing on
behalf of each withdrawal read the old balance, reduce that value by the amount being
withdrawn, and write the result back. If the two programs run concurrently, they may both
read the value $500, and write back $450 and
$400, respectively. Depending on which one writes the value last, the account may contain
$450 or $400, rather than the correct value of $350. To guard against this possibility, the
system must maintain some form of supervision. But supervision is difficult to provide
because data may be accessed by many different application programs that have not been
coordinated previously.

6. Security Problems:

Not every user of the database system should be able to access all the data. For
example, in a banking system, payroll personnel need to see only that part of the database
that has information about the various bank employees. They do not need access to
information about customer accounts. But, since application programs are added to the
system in an ad hoc manner, enforcing such security constraints is difficult. These
difficulties, among others, prompted the development of database systems.

[Link],[Link],CSE Department,SCCE Page 6

INTRODUCTION TO DATABASES: Historical Perspective

History of Database Systems:

1950s and early 1960s:

• Magnetic tapes were developed for data storage

• Data processing tasks such as payroll were automated, with data stored on tapes.

• Data could also be input from punched card decks, and output to printers.

• Late 1960s and 1970s: The use of hard disks in the late 1960s changed the scenario
for data processing greatly, since hard disks allowed direct access to data.

• With disks, network and hierarchical databases could be created that allowed data
structures such as lists and trees to be stored on disk. Programmers could construct
and manipulate these data structures.

• With disks, network and hierarchical databases could be created that allowed data
structures such as lists and trees to be stored on disk. Programmers could construct
and manipulate these data structures.

• In the 1970’s the EF CODD defined the Relational Model.

In the 1980’s:
• Initial commercial relational database systems, such as IBM DB2, Oracle, Ingress, and
DEC Rdb, played a major role in advancing techniques for efficient processing of
declarative queries.

• In the early 1980s, relational databases had become competitive with network
and hierarchical database systems even in the area of performance.

• The 1980s also saw much research on parallel and distributed databases, as well
as initial work on object-oriented databases.

Early 1990s:

• The SQL language was designed primarily in the 1990’s.

• And this is used for the transaction processing applications.

• Decision support and querying re-emerged as a major application area for databases.

[Link],[Link],CSE Department,SCCE Page 7

Database vendors also began to add object-relational support to their databases.

Late 1990s:

• The major event was the explosive growth of the World Wide Web.

• Databases were deployed much more extensively than ever before. Database
systems now had to support very high transaction processing rates, as well as very
high reliability and 24 * 7 availability (availability 24 hours a day, 7 days a week,
meaning no downtime for scheduled maintenance activities).

• Database systems also had to support Web interfaces to data.

The Evolution of Database systems:

The Evolution of Database systems are as follows:

1. File Management System

2. Hierarchical database System

3. Network Database System

4. Relational Database System

File Management System:

The file management system also called as FMS in short is one in which all data is
stored on a single large file. The main disadvantage in this system is searching a record or
data takes a long time. This lead to the introduction of the concept, of indexing in this
system. Then also the FMS system had lot of drawbacks to name a few like updating or
modifications to the data cannot be handled easily, sorting the records took long time and so
on. All these drawbacks led to the introduction of the Hierarchical Database System.

Hierarchical Database System:

The previous system FMS drawback of accessing records and sorting records which
took a long time was removed in this by the introduction of parent-child relationship
between records in database. The origin of the data is called the root from which several
branches have data at different levels and the last level is called leaf

[Link],[Link],CSE Department,SCCE Page 8

The main drawback in this was if there is any modification or addition made to the structure
then the whole structure needed alteration which made the task a tedious one. In order to
avoid this next system took its origin which is called as the Network Database System.

Fig: Hierarchical Database System

Network Database System:

In this the main concept of many-many relationships got introduced. But this also
followed the same technology of pointers to define relationships with a difference in this
made in the introduction if grouping of data items as sets.

[Link],[Link],CSE Department,SCCE Page 9

Relational Database System:

In order to overcome all the drawbacks of the previous systems, the Relational
Database System got introduced in which data get organized as tables and each record forms
a row with many fields or attributes in it. Relationships between tables are also formed in
this system.

DATABASE:

A database is a collection of related data.

(OR)

A database is a collection of information that is organized so that it can be easily accessed,

managed and updated.

Examples / Applications of Database Systems:

The following are the various kinds of applications/organizations uses databases for their
business processing activities in their day-to-day life. They are:

1. Banking: For customer information, accounts, and loans, and banking transactions.

2. Airlines: For reservations and schedule information. Airlines were among the first to use

[Link],[Link],CSE Department,SCCE Page 10

[Link],[Link],CSE Department,SCCE Page 11
 databases in a geographically distributed manner—terminals situated around the world
accessed the central database system through phone lines and other data networks.

3. Universities: For student information, course registrations, and grades.

4. Credit Card Transactions: For purchases on credit cards and generation of monthly
statements.

5. Telecommunication: For keeping records of calls made, generating monthly bills,

maintaining balances on prepaid calling cards, and storing information about the
communication networks.

6. Finance: For storing information about holdings, sales, and purchases of financial
instruments such as stocks and bonds.

7. Sales: For customer, product, and purchase information.

8. Manufacturing: For management of supply chain and for tracking production of items in
factories, inventories of items in warehouses/stores, and orders for items.

9. Human resources: For information about employees, salaries, payroll taxes and benefits,
and for generation of paychecks.

10. Railway Reservation Systems: For reservations and schedule information.

11. Web: For access the Back accounts and to get the balance amount.

12. E –Commerce: For Buying a book or music CD and browse for things like watches,
mobiles from the Internet.

CHARACTERISTICS OF DATABASE:

The database approach has some very characteristic features which are discussed in detail
below:
Structured and Described Data:

Fundamental feature of the database approach is that the database system does not
only contain the data but also the complete definition and description of these data. These
descriptions are basically details about the extent, the structure, the type and the format of

[Link],[Link],CSE Department,SCCE Page 12

 all data and, additionally, the relationship between the data. This kind of stored data is called
metadata ("data about data").
Separation of Data and Applications:

Application software does not need any knowledge about the physical data storage
like encoding, format, storage place, etc. It only communicates with the management system
of a database (DBMS) via a standardized interface with the help of a standardized language
like SQL. The access to the data and the metadata is entirely done by the DBMS. In this way
all the applications can be totally separated from the data.

Data Integrity:

Data integrity is a byword for the quality and the reliability of the data of a database
system. In a broader sense data integrity includes also the protection of the database from
unauthorized access (confidentiality) and unauthorized changes. Data reflect facts of the real
world.

Transactions:
A transaction is a bundle of actions which are done within a database to bring it from one
consistent state to a new consistent state. In between the data are inevitable inconsistent. A
transaction is atomic what means that it cannot be divided up any further. Within a transaction
all or none of the actions need to be carried out. Doing only a part of the actions would lead to
an inconsistent database state.
Example: One example of a transaction is the transfer of an amount of money from one bank
account to another.

Data Persistence:

Data persistence means that in a DBMS all data is maintained as long as it is not
deleted explicitly. The life span of data needs to be determined directly or indirectly be the
user and must not be dependent on system features. Additionally data once stored in a
database must not be lost. Changes of a database which are done by a transaction are
persistent. When a transaction is finished even a system crash cannot put the data in danger

[Link],[Link],CSE Department,SCCE Page 13

[Link],[Link],CSE Department,SCCE Page 14
TYPES OF DATABASES:

Database can be classified according to the following factors. They are:

1. Number of Users

2. Database Location

3. Expected type

4. Extent of use

1. Based on number of Users:

According to the number of users the databases can be classified into following types. They
are :

a). Single user b). Multiuser

Single user database:

• Single user database supports only one user at a time.
• Desktop or personal computer database is an example for single user database.

Multiuser database:
• Multi user database supports multiple users at the same time.
• Workgroup database and enterprise databases are examples for multiuser database.

Workgroup database:

If the multiuser database supports relatively small number of users (fewer than 50)
within an organization is called as Workgroup database.

Enterprise database:

If the database is used by the entire organization and supports multiple users (more
than
50) across many departments is called as Enterprise database.

2. Based on Location:
According to the location of database the databases can be classified into following types.
They are:
a).CentralizedDatabase

[Link],[Link],CSE Department,SCCE Page 15

 b).Distributed
Database

Centralized Database:

It is a database that is located, stored, and maintained in a single location. This location
is most often a central computer or database system, for example a desktop or server CPU, or
a mainframe computer. In most cases, a centralized database would be used by an
organization (e.g. a business company) or an institution (e.g. a university.)

Distributed Database:

A distributed database is a database in which storage devices are not all attached to a
common CPU. It may be stored in multiple computers located in the same physical location,
or may be dispersed over a network of interconnected computers.

INTRODUCTION TO DATABASE-MANAGEMENT SYSTEM:

Database Management System:

A database-management system (DBMS) is a collection of interrelated data and a set of

programs to access those data.

The DBMS is a general purpose software system that facilitates the process of defining
constructing and manipulating databases for various applications.

Goals of DBMS:

The primary goal of a DBMS is to provide a way to store and retrieve database
information that is both convenient and efficient

1. Manage large bodies of information

2. Provide convenient and efficient ways to store and access information

3. Secure information against system failure or tampering

4. Permit data to be shared among multiple users

[Link],[Link],CSE Department,SCCE Page 16

Properties of DBMS:

1.A Database represents some aspect of the real world. Changes to the real world reflected
in the database.

2.A Database is a logically coherent collection of data with some inherent meaning.

3.A Database is designed and populated with data for a specific purpose.

Need of DBMS:

1. Before the advent of DBMS, organizations typically stored information using a “File
Processing Systems”.

Example of such systems is File Handling in High Level Languages like C, Basic and COBOL etc.,
these systems have Major disadvantages to perform the Data Manipulation. So to overcome
those drawbacks now we are using the DBMS.

2. Database systems are designed to manage large bodies of information.

3. In addition to that the database system must ensure the safety of the information stored,
despite system crashes or attempts at unauthorized access. If data are to be shared among
several users, the system must avoid possible anomalous results.

ADVANTAGES OF A DBMS OVER FILE SYSTEM:

Using a DBMS to manage data has many advantages:

Data Independence:

Application programs should be as independent as possible from details of data

representation and storage. The DBMS can provide an abstract view of the data to insulate
application code from such details.

Efficient Data Access:

A DBMS utilizes a variety of sophisticated techniques to store and retrieve data

efficiently. This feature is especially important if the data is stored on external storage devices.

[Link],[Link],CSE Department,SCCE Page 17

[Link],[Link],CSE Department,SCCE Page 18
Data Integrity and Security:

If data is always accessed through the DBMS, the DBMS can enforce integrity
constraints on the data. For example, before inserting salary information for an employee,
the DBMS can check that the department budget is not exceeded. Also, the DBMS can
enforce access controls that govern what data is visible to different classes of users.

Concurrent Access and Crash Recovery:

A database system allows several users to access the database concurrently.

Answering different questions from different users with the same (base) data is a central
aspect of an information system. Such concurrent use of data increases the economy of a
system.

An example for concurrent use is the travel database of a bigger travel agency. The
employees of different branches can access the database concurrently and book journeys for
their clients. Each travel agent sees on his interface if there are still seats available for a
specific journey or if it is already fully booked.

A DBMS also protects data from failures such as power failures and crashes etc. by
the recovery schemes such as backup mechanisms and log files etc.

Data Administration:

When several users share the data, centralizing the administration of data can offer
significant improvements. Experienced professionals, who understand the nature of the data
being managed, and how different groups of users use it, can be responsible for organizing
the data representation to minimize redundancy and fine-tuning the storage of the data to
make retrieval efficient.

Reduced Application Development Time:

DBMS supports many important functions that are common to many applications
accessing data stored in the DBMS. This, in conjunction with the high-level interface to the
data, facilitates quick development of applications. Such applications are also likely to be
more robust than applications developed from scratch because many important tasks are
handled by the DBMS instead of being implemented by the application.

[Link],[Link],CSE Department,SCCE Page 19

[Link],[Link],CSE Department,SCCE Page 20
DISADVANTAGES OF DBMS:

Complexity:

A database system creates additional complexity and requirements. The supply and
operation of a database management system with several users and databases is quite costly
and demanding.

Qualified Personnel:

`The professional operation of a database system requires appropriately trained staff.

Without a qualified database administrator nothing will work for long.

Costs:

Through the use of a database system new costs are generated for the system itself
but also for additional hardware and the more complex handling of the system.

Lower Efficiency:

A database system is a multi-use software which is often less efficient than specialized
software which is produced and optimized exactly for one problem.

DATABASE USERS & DATABASE ADMINISTRATORS:

People who work with a database can be categorized as database users or database
administrators.

Database Users:

There are four different types of database-system users, differentiated by the way they
expect to interact with the system.

Naive users:
Naive users are unsophisticated users who interact with the system by invoking one of the
application programs that have been written previously.

[Link],[Link],CSE Department,SCCE Page 21

 For example, a bank teller who needs to transfer $50 from account A to account B
invokes a program called transfer. This program asks the teller for the amount of money to
be transferred, the account from which the money is to be transferred, and the account to
which the money is to be transferred.

Application programmers:

Application programmers are computer professionals who write application

programs. Application programmers can choose from many tools to develop user interfaces.
Rapid application development (RAD) tools are tools that enable an application programmer
to construct forms and reports without writing a program.

Sophisticated users:

Sophisticated users interact with the system without writing programs. Instead, they
form their requests in a database query language. They submit each such query to a query
processor, whose function is to break down DML statements into instructions that the
storage manager understands. Analysts who submit queries to explore data in the database
fall in this category.

Specialized users:

Specialized users are sophisticated users who write specialized database applications
that do not fit into the traditional data-processing framework.

Database Administrator:

One of the main reasons for using DBMSs is to have central control of both the data
and the programs that access those data. A person who has such central control over the
system is called a database administrator (DBA).

Database Administrator Functions/Roles:

The functions of a DBA include:

Schema definition:

The DBA creates the original database schema by executing a set of data definition
statements in the DDL, Storage structure and access-method definition.

[Link],[Link],CSE Department,SCCE Page 22

Schema and physical-organization modification:

The DBA carries out changes to the schema and physical organization to reflect the
changing needs of the organization, or to alter the physical organization to improve
performance.

Granting of authorization for data access:

By granting different types of authorization, the database administrator can regulate

which parts of the database various users can access. The authorization information is kept
in a special system structure that the database system consults whenever someone attempts
to access the data in the system.

Routine maintenance:

Examples of the database administrator’s routine maintenance activities are:

1. Periodically backing up the database, either onto tapes or onto remote servers, to prevent
loss of data in case of disasters such as flooding.

2. Ensuring that enough free disk space is available for normal operations, and upgrading disk
space as required.

3. Monitoring jobs running on the database and ensuring that performance is not degraded
by very expensive tasks submitted by some users.

LEVELS OF ABSTRACTION IN A DBMS:

Hiding certain details of how the data are stored and maintained. A major purpose of
database system is to provide users with an “Abstract View” of the data. In DBMS there are 3
levels of data abstraction. The goal of the abstraction in the DBMS is to separate the users
request and the physical storage of data in the database.

Levels of Abstraction:

Physical Level:

The lowest Level of Abstraction describes “How” the data are actually stored.
The physical level describes complex low level data structures in detail.

[Link],[Link],CSE Department,SCCE Page 23

Logical Level:

This level of data Abstraction describes “What” data are to be stored in the database and
what relationships exist among those data.

Database Administrators use the logical level of abstraction.

View Level:

It is the highest level of data Abstracts that describes only part of entire database.
Different users require different types of data elements from each database.
The system may provide many views for the some database.

THREE SCHEMA ARCHITECTURE:

Schema:

The overall design of the database is called the “Schema” or “Meta Data”. A
database schema corresponds to the programming language type definition. The value of a
variable in programming language corresponds to an “Instance” of a database Schema.

Three Schema Architecture:

The goal of this architecture is to separate the user applications and the physical
database. In this architecture, schemas can be defined at the following three levels:

1. The internal level has an internal schema, which describes the physical storage structure
of the database. The internal schema uses a physical data model and describes the
complete details of data storage and access paths for the database.

2. The conceptual level has a conceptual schema, which describes the structure of the whole
database for a community of users. The conceptual schema hides the details of physical
storage structures and concentrates on describing entities, data types, relationships, user
operations, and constraints. A high-level data model or an implementation data model
can be used at this level.

3. The external or view level includes a number of external schemas or user views. Each
external schema describes the part of the database that a particular user group is
interested in and hides the rest of the database from that user group. A high-level data
model or an implementation data model can be used at this level.

[Link],[Link],CSE Department,SCCE Page 24

 Fig: Three-Schema Architecture

[Link],[Link],CSE Department,SCCE Page 25

DATA INDEPENDENCE:

A very important advantage of using DBMS is that it offers Data Independence.

The ability to modify a scheme definition in one level without affecting a scheme
definition in a higher level is called data independence.

There are two kinds:

1. Physical Data Independence

2. Logical Data Independence

Physical Data Independence:

The ability to modify the physical schema without causing application programs to be
rewritten

Modifications at this level are usually to improve performance.

Fig: Data Independence

Logical Data Independence:

The ability to modify the conceptual schema without causing application programs to be
rewritten
Usually done when logical structure of database is altered

Logical data independence is harder to achieve as the application programs are usually
heavily dependent on the logical structure of the data.

[Link],[Link],CSE Department,SCCE Page 26

DATABASE SYSTEM STRUCTURE:

A database system is partitioned into modules that deal with each of the responsibilities of the
overall system. The functional components of a database system can be broadly divided into
the storage manager and the query processor components.

The storage manager is important because databases typically require a large amount
of storage space. Some Big organizations Database ranges from Giga bytes to Tera bytes. So
the main memory of computers cannot store this much information, the information is
stored on disks. Data are moved between disk storage and main memory as needed.

The query processor also very important because it helps the database system
simplify and facilitate access to data. So quick processing of updates and queries is
important. It is the job of the database system to translate updates and queries written in a
nonprocedural language,

[Link],[Link],CSE Department,SCCE Page 27

[Link],[Link],CSE Department,SCCE Page 28
StorageManager:

A storage manager is a program module that provides the interface between the low level
data stored in the database and the application programs and queries submitted to the
system. The storage manager is responsible for the interaction with the file manager. The
storage manager translates the various DML statements into low-level file-system commands.
Thus, the storage manager is responsible for storing, retrieving, and updating data in the
database.

Storage Manager Components:

Authorization and integrity manager which tests for the satisfaction of integrity
constraints and checks the authority of users to access data.

Transaction manager which ensures that the database itself remains in a

consistent state despite system failures, and that concurrent transaction executions
proceed without conflicting.

File manager: which manages the allocation of space on disk storage and the data
structures used to represent information stored on disk.

Buffer manager which is responsible for fetching data from disk storage into main
memory. Storage manager implements several data structures as part of the physical system
implementation. Data files are used to store the database itself. Data dictionary is used to
stores metadata about the structure of the database, in particular the schema of the
database.

Query Processor Components:

DDL interpreter: It interprets DDL statements and records the definitions in the data
dictionary.

DML compiler: It translates DML statements in a query language into an evaluation plan
consisting of low-level instructions that the query evaluation engine understands.

Query evaluation engine: It executes low-level instructions generated by the DML compiler.

[Link],[Link],CSE Department,SCCE Page 29

Application Architectures:

Most users of a database system today are not present at the site of the database
system, but connect to it through a network. We can therefore differentiate between client
machines, on

which remote database users’ work, and server machines, on which the database system runs.
Database applications are usually partitioned into two or three parts. They are:

1. Two – Tier Architecture

2. Three – Tier Architecture.

Two-Tier Architecture:

The application is partitioned into a component that resides at the client machine,
which invokes database system functionality at the server machine through query
language statements. Application program interface standards like ODBC and JDBC are
used for interaction between the client and the server.

Three-Tier Architecture:

The client machine acts as merely a front end and does not contain any direct
database calls. Instead, the client end communicates with an application server, usually
through forms interface. The application server in turn communicates with a database
system to access data. The business logic of the application, which says what actions to carry
out under what conditions, is embedded in the application server, instead of being
distributed across multiple clients. Three-tier applications are more appropriate for large
applications, and for applications that run on the World Wide Web.

[Link],[Link],CSE Department,SCCE Page 30

DATABASE DESIGN:

The database design process can be divided into six steps. The ER Model is most
relevant to the first three steps. Next three steps are beyond the ER Model.

1. Requirements Analysis:

The very first step in designing a database application is to understand what data is to
be stored in the database, what applications must be built on top of it, and what operations
are most frequent and subject to performance requirements. The database designers collect
information of the organization and analyzer, the information to identify the user’s
requirements. The database designers must find out what the users want from the database.

2. Conceptual Database Design:

Once the information is gathered in the requirements analysis step a conceptual database
design is developed and is used to develop a high level description of the data to be stored in
the database, along with the constraints that are known to hold over this data. This step is
often carried out using the ER model, or a similar high-level data model.

3. Logical Database Design:

In this step convert the conceptual database design into a database schema (Logical
Database Design) in the data model of the chosen DBMS. We will only consider relational
DBMSs, and therefore, the task in the

logical design step is to convert an ER schema into a relational database schema. The result is
a conceptual schema, sometimes called the logical schema, in the relational data model.

Beyond the ER Design:

The first three steps are more relevant to the ER Model. Once the logical scheme is
defined designer consider the physical level implementation and finally provide certain
security measures. The remaining three steps of database design are briefly described
below:

[Link],[Link],CSE Department,SCCE Page 31

 4. Schema Refinement:

The fourth step in database design is to analyze the collection of relations in our
relational database schema to identify potential problems, and to refine it. In contrast to the
requirements analysis and conceptual design steps, which are essentially subjective, schema
refinement can be guided by some elegant and powerful theory.

5. Physical Database Design:

In this step we must consider typical expected workloads that our database must
support and further refine the database design to ensure that it meets desired performance

criteria. This step may simply involve building indexes on some tables and clustering some
tables, or it may involve a substantial redesign of parts of the database schema obtained
from the earlier design steps.

6. Security Design:

The last step of database design is to include security features. This is required to
avoid unauthorized access to database practice after all the six steps. We required Tuning
step in which all the steps are interleaved and repeated until the design is satisfactory.

DBMS FUNCTIONS:

DBMS performs several important functions that guarantee the integrity and consistency
of the data in the database.
Those functions transparent to end users and can be accessed only through the use of
DBMS. They include:
Data Dictionary
Management Data Storage
Management
Data transformation and
Presentation Security Management
Multiple Access Control
Backup and Recovery Management
Data Integrity Management
Database Access Languages
Databases Communication
Interfaces

[Link],[Link],CSE Department,SCCE Page 32

 Data Dictionary Management:

DBMS stores definitions of database elements and their relationship (Metadata) in the
data dictionary.
The DBMS uses the data dictionary to look up the required data component structures and
relationships.
Any change made in database structure is automatically recorded in the data dictionary.

Data Storage Management:

Modern DBMS provides storage not only for data but also for related data
entities. Data Storage Management is also important for database “performance
tuning”.
Performance tuning related to activities that make database more efficiently.

Data Transformation and Presentation:

DBMS transforms entered data to confirm to required data structures.

DBMS formats the physically retrieved data to make it confirms to user’s logical
expectations.
DBMS also presents the data in the user’s expected format.

Security Management:

DBMS creates a security system that enforces the user security and data privacy.
Security rules determines which users can access the database, which data items each
user can access etc.

DBA and authenticated user logged to DBMS through username and password or
through Biometric authentication such as Finger print and face reorganization etc.

Multiuser Access Control:

To provide data integrity and data consistency, DBMS uses sophisticated algorithms to
ensure that multiple users can access the database concurrently without
compromising the integrity of database.

Backup and Recovery Management:

DBMS provides backup and recovery to ensure data safety and integrity.

[Link],[Link],CSE Department,SCCE Page 33

 Recovery management deals with the recovery of database after failure such as
bad sector in the disk or power failure. Such capability is critical to preserve
database integrity.

Data Integrity Management:

DBMS provides and enforces integrity rules, thus minimizing data redundancy and
maximizing data consistency.

Ensuring data integrity is especially important in transaction- oriented database systems.

Database Access Languages:

DBMS provides data access through a query language.

A query language is a non-procedural language i.e. it lets the user specify what
must be done without specifying how it is to be done.

SQL is the default query language for data access.

Databases Communication Interfaces:

Current DBMS’s are accepting end-user requests via different network environments.

For example, DBMS might provide access to database via Internet through the use of
web browsers such as Mozilla Firefox or Microsoft Internet Explorer.

What is Schema?

A database schema is the skeleton structure that represents the logical view of the entire
database. (or)

The logical structure of the database is called as

Database Schema. (or)

The overall design of the database is the database schema.

It defines how the data is organized and how the relations among them are

[Link],[Link],CSE Department,SCCE Page 34

 associated. It formulates all the constraints that are to be applied on the data.

EG:STUDENT

SID SNAME PHNO

What is Instance?

The actual content of the database at a particular

point in time. (Or)

The data stored in the database at any given time is an instance of the

database Student

Sid Name Phno

1201 Venkat 9014901442
1202 teja 9014774422

In the above table 1201, 1202, Venkat etc are said to be instance of student table.

DATA INDEPENDENCE

Data Independence is defined as a property of DBMS that helps you to change the Database
schema at one level of a database system without requiring to change the schema at the next
higher level. Data independence helps you to keep data separated from all programs that make
use of it.

You can use this stored data for computing and presentation. In many systems, data
independence is an essential function for components of the system.

Types of Data Independence

In DBMS there are two types of data independence

Levels of Database

The database has 3 levels as shown in the diagram below

[Link],[Link],CSE Department,SCCE Page 35

 1. Physical/Internal
2. Conceptual
3. External

Consider an Example of a University Database. At the different levels this is how the
implementation will look like:

Type of Schema Implementation

External Schema View 1: Course info(cid:int,cname:string)

[Link],[Link],CSE Department,SCCE Page 36

 View 2: studeninfo(id:int. name:string)

Conceptual Shema Students(id: int, name: string, login: string, age:

Courses(id: int, [Link], credits:integer)
Enrolled(id: int, grade:string)

Physical Schema • Relations stored as unordered files..

Physical Data Independence

Physical data independence helps you to separate conceptual levels from the internal/physical
levels. It allows you to provide a logical description of the database without the need to specify
physical structures. Compared to Logical Independence, it is easy to achieve physical data
independence.

With Physical independence, you can easily change the physical storage structures or devices with
an effect on the conceptual schema. Any change done would be absorbed by the mapping
between the conceptual and internal levels. Physical data independence is achieved by the
presence of the internal level of the database and then the transformation from the conceptual
level of the database to the internal level.

Examples of changes under Physical Data Independence

Due to Physical independence, any of the below change will not affect the conceptual layer.

• Using a new storage device like Hard Drive or Magnetic Tapes

• Modifying the file organization technique in the Database
• Changes to compression techniques or hashing algorithms.
• Change of Location of Database from say C drive to D Drive

Logical Data Independence

Logical Data Independence is the ability to change the conceptual scheme without changing

1. External views
2. External API(Application Program Interface) or programs

Any change made will be absorbed by the mapping between external and conceptual levels.

When compared to Physical Data independence, it is challenging to achieve logical data

independence.

[Link],[Link],CSE Department,SCCE Page 37

 Examples of changes under Logical Data Independence

Due to Logical independence, any of the below change will not affect the external layer.

1. Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of

existing application programs
2. Merging two records into one
3. Breaking an existing record into two or more records

Difference between Physical and Logical Data Independence

Logica Data Independence Physical Data Independence

Logical Data Independence is mainly concerned with the Mainly concerned with the storage of the data.
structure or changing the data definition.

It is difficult as the retrieving of data is mainly dependent It is easy to retrieve.

on the logical structure of data.

Compared to Logic Physical independence it is difficult to Compared to Logical Independence it is easy to a

achieve logical data independence. physical data independence.

You need to make changes in the Application program if A change in the physical level usually does not n
new fields are added or deleted from the database. at the Application program level.

Modification at the logical levels is significant whenever Modifications made at the internal levels may or
the logical structures of the database are changed. be needed to improve the performance of the st

Concerned with conceptual schema Concerned with internal schema

Example: Add/Modify/Delete a new attribute Example: change in compression techniques, ha

[Link],[Link],CSE Department,SCCE Page 38

 algorithms, storage devices, etc

Importance of Data Independence

• Helps you to improve the quality of the data

• Database system maintenance becomes affordable
• Enforcement of standards and improvement in database security
• You don't need to alter data structure in application programs

• Easily make modifications in the physical level is needed to improve the performance of
the system.

• Data Independence is the property of DBMS that helps you to change the Database
schema at one level of a database system without requiring to change the schema at the
next higher level.
• Two levels of data independence are 1) Physical and 2) Logical
• Physical data independence helps you to separate conceptual levels from the
internal/physical levels
• Logical Data Independence is the ability to change the conceptual scheme without
changing
• When compared to Physical Data independence, it is challenging to achieve logical data
independence
• Data Independence Helps you to improve the quality of the data

File Systems versus a DBMS

• A file system is a software that manages and organizes the files in a storage medium,
whereas DBMS is a software application that is used for accessing, creating, and managing
databases.
• The file system doesn't have a crash recovery mechanism on the other hand, DBMS
provides a crash recovery mechanism.
• Data inconsistency is higher in the file system. On the contrary Data inconsistency is low in
a database management system.
• File system does not provide support for complicated transactions, while in the DBMS
system, it is easy to implement complicated transactions using SQL.
• File system does not offer concurrency, whereas DBMS provides a concurrency facility.

Features of a File system

Here are important elements of the file system:

• It helps you to store data in a group of files.

[Link],[Link],CSE Department,SCCE Page 39

 • Files data are dependent on each other.
• C/C++ and COBOL languages were used to design the files.
• Shared File System Support
• Fast File System Recovery.
•
• Features of DBMS

Here, are essential features of DBMS:

• A user-accessible catalog of data

• Transaction support
• Concurrency control with Recovery services
• Authorization services
• The value of data is the same at all places.
• Offers support for data communication
• Independent utility services
• Allows multiple users to share a file at the same time

File System DBMS

A file system is a software that manages and organizes the DBMS or Database Management System is a so
files in a storage medium. It controls how data is stored and application. It is used for accessing, creating, an
retrieved. managing databases.

The file system provides the details of data representation DBMS gives an abstract view of data that hides
and storage of data.

Storing and retrieving of data can't be done efficiently in a DBMS is efficient to use as there are a wide var
file system. methods to store and retrieve data.

It does not offer data recovery processes. There is a backup recovery for data in DBMS.

The file system doesn't have a crash recovery mechanism. DBMS provides a crash recovery mechanism

[Link],[Link],CSE Department,SCCE Page 40

Protecting a file system is very difficult. DBMS offers good protection mechanism.

In a file management system, the redundancy of data is The redundancy of data is low in the DBMS syst
greater.

Data inconsistency is higher in the file system. Data inconsistency is low in a database manage
system.

The file system offers lesser security. Database Management System offers high secu

File System allows you to stores the data as isolated data Database Management System stores data as w
files and entities. defined constraints and interrelation.

Not provide support for complicated transactions. Easy to implement complicated transactions.

The centralization process is hard in File Management Centralization is easy to achieve in the DBMS sy
System.

It doesn't offer backup and recovery of data if it is lost. DBMS system provides backup and recovery of
if it is lost.

There is no efficient query processing in the file system. You can easily query data in a database using th
language.

These system doesn't offer concurrency. DBMS system provides a concurrency facility.

[Link],[Link],CSE Department,SCCE Page 41

 Difference between File system & DBMS:

File system DBMS

File system is a collection of data. Any 1. DBMS is a collection of data and user is
[Link] Not
required to write the procedures for managing
with the file system, user has to write the the
procedures
database.
File system gives the details of
2. the data 2. DBMS provides an abstract view of data that hides
representation and Storage of data. the details.
In File system storing and retrieving of data 3. DBMS is efficient to use since there
[Link] are
Wide
varieties of sophisticated techniques to
be done efficiently. store
and
retrieve the data.
Concurrent access to the data in the file
[Link] 4. DBMS takes care of Concurrent access using some
Has
many problems like : Reading the file while other form of locking.
deleting some information, updating
some
information
File system doesn’t provide crash
5. Recovery 5. DBMS has crash recovery mechanism, DBMS
mechanism. protects user from the effects of system failures.
Eg. While we are entering some data into the file if
System crashes then content of the file is lost
[Link] a file under file system is very difficult. 6. DBMS has a good protection mechanism.

[Link],[Link],CSE Department,SCCE Page 42

Conceptual Database Design - Entity Relationship(ER) Modeling:

Database Design Techniques

1. ER Modeling (Top down Approach)
2. Normalization (Bottom Up approach)

What is ER Modeling?
A graphical technique for understanding and organizing the data independent of the actual database
implementation
We need to be familiar with the following terms to go further.
a)Entity
Any thing that has an independent existence and about which we collect data. It is also known as entity
type. In ER modeling, notation for entity is given below.

b)Entity instance
Entity instance is a particular member of the entity type.
Example for entity instance : A particular employee
c)Regular Entity
An entity which has its own key attribute is a regular
entity. Example for regular entity : Employee.
d)Weak entity
An entity which depends on other entity for its existence and doesn't have any key attribute of its own is a weak
entity.
Example for a weak entity : In a parent/child relationship, a parent is considered as a strong entity and the child
is a weak entity.
In ER modeling, notation for weak entity is given below.

[Link],[Link],CSE Department,SCCE Page 43

e)Attributes
Properties/characteristics which describe entitiesarecalled
attributes. In ER modeling, notation for attribute is given
below.

Domain of Attributes
The set of possible values that an attribute can take is called the domain of the attribute. For example, the
attribute day may take any value from the set {Monday, Tuesday ... Friday}. Hence this set can be termed as the
domain of the attribute day.
a)Key attribute
The attribute (or combination of attributes) which is unique for every entity instance is called key
attribute.
E.g the employee_id of an employee, pan_card_number of a person [Link] the key attribute consists of two or
more attributes in combination, it is called a composite key.
In ER modeling, notation for key attribute is given below.

b)Simple attribute
If an attribute cannot be divided into simpler components, it is a simple attribute.
Example for simple attribute : employee_id of an employee.

c)Composite attribute
If an attribute can be split into components, it is called a composite attribute.
Example for composite attribute : Name of the employee which can be split into First_name, Middle_name, and
Last_name.
d)Single valued Attributes
If an attribute can take only a single value for each entity instance, it is a single valued attribute.
example for single valued attribute : age of a student. It can take only one value for a particular
student. e)Multi-valued Attributes
If an attribute can take more than one value for each entity instance, it is a multi-valued attribute. Multi-valued
example for multi valued attribute : telephone number of an employee, a particular employee may have multiple
telephone numbers.

[Link],[Link],CSE Department,SCCE Page 44

 In ER modeling, notation for multi-valued attribute is given below

f)Stored Attribute
An attribute which need to be stored permanently is a stored
attribute Example for stored attribute : name of a student

g)Derived Attribute
An attribute which can be calculated or derived based on other attributes is a derived attribute.
Example for derived attribute : age of employee which can be calculated from date of birth and current
date. In ER modeling, notation for derived attribute is given below.

Relationships
Associations between entities are called relationships
Example : An employee works for an organization. Here "works for" is a relation between the entities
employee and organization.
In ER modeling, notation for relationship is given below.

However in ER Modeling, To connect a weak Entity with others, you should use a weak relationship notation as
given below

[Link],[Link],CSE Department,SCCE Page 45

Degree of a Relationship
Degree of a relationship is the number of entity types involved. The n-ary relationship is the general form for
degree n. Special cases are unary, binary, and ternary ,where the degree is 1, 2, and 3, respectively.
Example for unary relationship : An employee ia a manager of another employee

Example for binary relationship : An employee works-for department.

Example for ternary relationship : customer purchase item from a shop keeper
Cardinality of a Relationship
Relationship cardinalities specify how many of each entity type is allowed. Relationships can have four possible
connectivities as given below.
1. One to one (1:1) relationship
2. One to many (1:N) relationship
3. Many to one (M:1) relationship
4. Many to many (M:N) relationship
The minimum and maximum values of this connectivity is called the cardinality of the relationship
Example for Cardinality – One-to-One (1:1)
Employee is assigned with a parking space.

One employee is assigned with only one parking space and one parking space is assigned to only one
employee. Hence it is a 1:1 relationship and cardinality is One-To-One (1:1)
In ER modeling, this can be mentioned using notations as given below

[Link],[Link],CSE Department,SCCE Page 46

Example for Cardinality – One-to-Many (1:N)
Organization has employees

One organization can have many employees , but one employee works in only one organization. Hence it is a
1:N relationship and cardinality is One-To-Many (1:N)

In ER modeling, this can be mentioned using notations as given below

Example for Cardinality – Many-to-One (M :1)

It is the reverse of the One to Many relationship. employee works in organization

[Link],[Link],CSE Department,SCCE Page 47

 One employee works in only one organization But one organization can have many employees. Hence it is a
M:1 relationship and cardinality is Many-to-One (M :1)

In ER modeling, this can be mentioned using notations as given below.

Cardinality – Many-to-Many (M:N)

Students enrolls for courses

One student can enroll for many courses and one course can be enrolled by many students. Hence it is a M:N
relationship and cardinality is Many-to-Many (M:N)

In ER modeling, this can be mentioned using notations as given below

Relationship Participation
1. Total

[Link],[Link],CSE Department,SCCE Page 48

 In total participation, every entity instance will be connected through the relationship to another instance of
the other participating entity types
2. Partial
Example for relationship participation
Consider the relationship - Employee is head of the department.
Here all employees will not be the head of the department. Only one employee will be the head of the
department. In other words, only few instances of employee entity participate in the above relationship. So
employee entity's participation is partial in the said relationship.
However each department will be headed by some employee. So department entity's participation is total in
the said relationship.

Advantages and Disadvantages of ER Modeling ( Merits and Demerits of ER Modeling )

Advantages
1. ER Modeling is simple and easily understandable. It is represented in business users language and it can be
understood by non-technical specialist.
2. Intuitive and helps in Physical Database creation.
3. Can be generalized and specialized based on needs.
4. Can help in database design.
5. Gives a higher level description of the system.
Disadvantages
1. Physical design derived from E-R Model may have some amount of ambiguities or inconsistency.
2. Sometime diagrams may lead to misinterpretations

Advantages of File system

Here are pros/benefits of file system:

• Enforcement of development and maintenance standards.

• Helps you to reduce redundancy
• Avoid inconsistency across file maintenance to get the integrity of data independence.
• Firm theoretical foundation (for the relational model).
• It is more efficient and cost less than a DBMS in certain situations.
• The design of file processing is simpler than designing Database.

[Link],[Link],CSE Department,SCCE Page 49

Advantages of DBMS system

Here, are pros/benefits of DBMS system:

• DBMS offers a variety of techniques to store & retrieve data

• Uniform administration procedures for data
• Application programmers never exposed to details of data representation and Storage.
• A DBMS uses various powerful functions to store and retrieve data efficiently.
• Offers Data Integrity and Security
• The DBMS implies integrity constraints to get a high level of protection against
prohibited access to data.
• Reduced Application Development Time
• Consume lesser space
• Reduction of redundancy.
• Data independence.

Application of File system

Here, are an important application of the file system:

• Language-specific run-time libraries

• API programs using it to make requests of the file system
• It is used for data transfer and positioning.
• Helps you to update the metadata
• Managing directories.

Application of the DBMS system

Here, are important applications of the DBMS system:

• Admission System Examination System Library System

• Payroll & Personnel Management System
• Accounting System Hotel Reservation System Airline Reservation System
• It is used in the Banking system for Customer information, account activités, Payments,
déposits, loans, etc.
• Use for Airlines for reservations and schedules
• DBMS system also used by universities to keep call records, monthly bills, maintaining
balances, etc.
• Finance for storing information about stock, sales, and purchases of financial
instruments like stocks and bonds.

Disadvantages of File system

[Link],[Link],CSE Department,SCCE Page 50

Here, are cons/drawback of the file system:

• Each application has its data file so, the same data may have to be recorded and stored
many times.
• Data dependence in the file processing system are data-dependent, but, the problem is
incompatible with file format.
• Limited data sharing.
• The problem with security.
• Time-consuming.
• It allows you to maintain the record of the big firm having a large number of items.
• Required lots of labor work to do.

Disadvantages of the DBMS system

Here, are some cons/drawbacks of the DBMS system:

• Cost of Hardware and Software of a DBMS is quite high, which increases the budget of
your organization.
• Most database management systems are often complex systems, so the training for
users to use the DBMS is required.
• The use of the same program at a time by many users sometimes lead to the loss of
some data.
• DBMS can't perform sophisticated calculations
• Data-sets begins to grow large as it provides a more predictable query response time.
• It required a processor with the high speed of data processing.
• The database can fail because or power failure or the whole system stops.
• The cost of DBMS is depended on the environment, function, or recurrent annual
maintenance cost.

Additional Features of ER model

As the complexity of data increased in the late 1980s, it became more and more difficult to use
the traditional ER Model for database modelling. Hence some improvements or enhancements
were made to the existing ER Model to make it able to handle the complex applications better.

Hence, as part of the Enhanced ER Model, along with other improvements, three new concepts
were added to the existing ER Model, they were:

1. Generalization

2. Specialization

3. Aggregration

[Link],[Link],CSE Department,SCCE Page 51

Let's understand what they are, and why were they added to the existing ER Model.

1)Generalization

Generalization is a bottom-up approach in which two lower level entities combine to form a
higher level entity. In generalization, the higher level entity can also combine with other lower
level entities to make further higher level entity.

It's more like Superclass and Subclass system, but the only difference is the approach, which is
bottom-up. Hence, entities are combined to form a more generalised entity, in other words,
sub-classes are combined to form a super-class.

For example, Saving and Current account types entities can be generalised and an entity with
name Account can be created, which covers both.

2)Specialization

Specialization is opposite to Generalization. It is a top-down approach in which one higher level

entity can be broken down into two lower level entity. In specialization, a higher level entity
may not have any lower-level entity sets, it's possible

[Link],[Link],CSE Department,SCCE Page 52

3)Aggregration
Aggregration is a process when relation between two entities is treated as a single entity.

[Link],[Link],CSE Department,SCCE Page 53

In the diagram above, the relationship between Center and Course together, is acting as an
Entity, which is in relationship with another entity Visitor. Now in real world, if a Visitor or a
Student visits a Coaching Center, he/she will never enquire about the center only or just about
the course, rather he/she will ask enquire about both.

DATA MODELS

A Database model defines the logical design and structure of a database and defines how data
will be stored, accessed and updated in a database management system. While the Relational
Model is the most widely used database model, there are other models too:

• Hierarchical Model

• Network Model

• Entity-relationship Model

• Relational Model

[Link],[Link],CSE Department,SCCE Page 54

a)Hierarchical Model

This database model organises data into a tree-like-structure, with a single root, to which all the
other data is linked. The heirarchy starts from the Root data, and expands like a tree, adding
child nodes to the parent nodes.

In this model, a child node will only have a single parent node.

This model efficiently describes many real-world relationships like index of a book, recipes etc.

In hierarchical model, data is organised into tree-like structure with one one-to-many
relationship between two different types of data, for example, one department can have many
courses, many professors and of-course many students.

b)Network Model

This is an extension of the Hierarchical model. In this model data is organised more like a graph,
and are allowed to have more than one parent node.

In this database model data is more related as more relationships are established in this
database model. Also, as the data is more related, hence accessing the data is also easier and
fast. This database model was used to map many-to-many data relationships.

This was the most widely used database model, before Relational Model was introduced.

[Link],[Link],CSE Department,SCCE Page 55

Entity-relationship Model

In this database model, relationships are created by dividing object of interest into entity and
its characteristics into attributes.

Different entities are related using relationships.

E-R Models are defined to represent the relationships into pictorial form to make it easier for
different stakeholders to understand.

This model is good to design a database, which can then be turned into tables in relational
model(explained below).

Let's take an example, If we have to design a School Database, then Student will be
an entity with attributes name, age, address etc. As Address is generally complex, it can be
another entity with attributes street name, pincode, city etc, and there will be a relationship
between them.

Relationships can also be of different types.

[Link],[Link],CSE Department,SCCE Page 56

c)Relational Model

In this model, data is organised in two-dimensional tables and the relationship is maintained by
storing a common field.

This model was introduced by E.F Codd in 1970, and since then it has been the most widely
used database model, infact, we can say the only database model used around the world.

The basic structure of data in the relational model is tables. All the information related to a
particular type is stored in rows of that table.

Hence, tables are also known as relations in relational model.

In the coming tutorials we will learn how to design tables, normalize them to reduce data
redundancy and how to use Structured Query language to access data from tables.

[Link],[Link],CSE Department,SCCE Page 57

Working with ER Diagrams

ER Diagram is a visual representation of data that describes how data is related to each other.
In ER Model, we disintegrate data into entities, attributes and setup relationships between
entities, all this can be represented visually using the ER diagram.

For example, in the below diagram, anyone can see and understand what the diagram wants to
convey: Developer develops a website, whereas a Visitor visits a website.

[Link],[Link],CSE Department,SCCE Page 58

Components of ER Diagram

Entitiy, Attributes, Relationships etc form the components of ER Diagram and there are defined
symbols and shapes to represent each one of them.

Let's see how we can represent these in our ER Diagram.

a)Entity
Simple rectangular box represents an Entity.

Relationships between Entities - Weak and Strong

Rhombus is used to setup relationships between two or more entities.

[Link],[Link],CSE Department,SCCE Page 59

Attributes for any Entity
Ellipse is used to represent attributes of any entity. It is connected to the entity.

Weak Entity
A weak Entity is represented using double rectangular boxes. It is generally connected to
another entity.

Key Attribute for any Entity

To represent a Key attribute, the attribute name inside the Ellipse is underlined.

Derived Attribute for any Entity

Derived attributes are those which are derived based on other attributes, for example, age can
be derived from date of birth.

To represent a derived attribute, another dotted ellipse is created inside the main ellipse.

Multivalued Attribute for any Entity

Double Ellipse, one inside another, represents the attribute which can have multiple values.

[Link],[Link],CSE Department,SCCE Page 60

Composite Attribute for any Entity
A composite attribute is the attribute, which also has attributes.

ER Diagram: Entity

An Entity can be any object, place, person or class. In ER Diagram, an entity is represented
using rectangles. Consider an example of an Organisation- Employee, Manager, Department,
Product and many more can be taken as entities in an Organisation.

The yellow rhombus in between represents a relationship.

[Link],[Link],CSE Department,SCCE Page 61

ER Diagram: Weak Entity

Weak entity is an entity that depends on another entity. Weak entity doesn't have anay key
attribute of its own. Double rectangle is used to represent a weak entity.

ER Diagram: Attribute

An Attribute describes a property or characterstic of an entity. For

example, Name, Age, Address etc can be attributes of a Student. An attribute is represented
using eclipse.

[Link],[Link],CSE Department,SCCE Page 62

ER Diagram: Key Attribute

Key attribute represents the main characterstic of an Entity. It is used to represent a Primary
key. Ellipse with the text underlined, represents Key Attribute.

ER Diagram: Composite Attribute

An attribute can also have their own attributes. These attributes are known
as Composite attributes.

[Link],[Link],CSE Department,SCCE Page 63

ER Diagram: Relationship

A Relationship describes relation between entities. Relationship is represented using diamonds

or rhombus.

There are three types of relationship that exist between Entities.

1. Binary Relationship

2. Ternary Relationship

ER Diagram: Binary Relationship

Binary Relationship means relation between two Entities. This is further divided into three
types.

a)One to One Relationship

This type of relationship is rarely seen in real world.

The above example describes that one student can enroll only for one course and a course will
also have only one Student. This is not what you will usually see in real-world relationships.

b)One to Many Relationship

The below example showcases this relationship, which means that 1 student can opt for many
courses, but a course can only have 1 student. Sounds weird! This is how it is.

[Link],[Link],CSE Department,SCCE Page 64

c)Many to One Relationship

It reflects business rule that many entities can be associated with just one entity. For example,
Student enrolls for only one Course but a Course can have many Students.

d)Many to Many Relationship

The above diagram represents that one student can enroll for more than one courses. And a
course can have more than 1 student enrolled in it.

[Link],[Link],CSE Department,SCCE Page 65

ER Diagram: Ternary Relationship

Relationship of degree three is called Ternary relationship.

A Ternary relationship involves three entities. In such relationships we always consider two
entites together and then look upon the third.

For example, in the diagram above, we have three related

entities, Company, Product and Sector. To understand the relationship better or to define rules
around the model, we should relate two entities and then derive the third one.

A Company produces many Products/ each product is produced by exactly one company.

A Company operates in only one Sector / each sector has many companies operating in it.

Considering the above two rules or relationships, we see that although the complete
relationship involves three entities, but we are looking at two entities at a time.

[Link],[Link],CSE Department,SCCE Page 66