Unit-I

Database System Concept

Total Marks- 12

Contents:
1. Concept of Data, database, DBMS, advantages of DBMS over file Processing system, Application
of database.
2. Three level architecture for database system.
3. Data abstraction: Different level of Data abstraction, Instance and schema, Data independence-
Logical and Physical independence.
4. Overall Structure of DBMS.
5. Data Modelling: Record based logical model- Relation, Network, Hierarchical.
6. Data Modelling using the E-R Model: Entity Relationship Model, Strong Entity set, Weak Entity
set, Types of Attributes, E-R Diagrams.

1. An Introduction to Database

Q. Define the following terms: (i) Data (ii) Database (ii) DBMS (3 Marks)

1.1 What is Data?

• Data can be facts related to any object in consideration.
• Data is a collection of facts, such as numbers, words, measurements, observations or even just
descriptions of things.
• Example: - Name, age, height, weight etc.

1.2 What is Database?

• Definition: Database is defined as collection of related data.
• Example:-
1. An online telephone numbers, other contact details etc.
2. Facebook it needs to store, manipulate & present data related to members, their friends,
member activities, messages etc.

1.3 What is DBMS?

Q. What is DBMS? Explain its functions.
• Definition: Database Management System (DBMS) is a collection of interrelated data & set of
programs to access the data.
DBMS provides us with an interface or a tool, to perform various operations like creating database,
storing data in it, updating data, creating tables in the database and a lot more.
DBMS also provides protection and security to the databases. It also maintains data consistency in
case of multiple users.
Here are some examples of popular DBMS used these days:
o MySql
o Oracle
o SQL Server
o IBM DB2
o PostgreSQL
o Amazon SimpleDB (cloud based) etc.
 Fig.1. DataBase Management Systems

Functions of DBMS. (IMP)

1. Concurrency: concurrent access (meaning 'at the same time') to the same database by multiple users.
2. Security: security rules to determine access rights of users.
3. Backup and recovery: processes to back-up the data regularly and recover data if a problem occurs.
4. Integrity: database structure and rules improve the integrity of the data.
5. Data descriptions: a data dictionary provides a description of the data.

1.4 Disadvantages of File Processing System:-

Q. List disadvantages of typical file processing system.
Q. Explain the disadvantages of file processing system. (4 Marks)
1. Data Redundancy:-Redundancy is unnecessary duplication of data. For example, if accounts
department & registration department both keep student name, number & address.
2. Data Inconsistency: - It means different copies of the same data are not matching. That means
different versions of same basic data are existing.
3. Difficulty in accessing data:-The file processing system do not allow to access data in
convenient & efficient way.
4. Data Isolation:-Data are scattered in various files, & the files may be in different format, writing
new application program to retrieve data is difficult.
5. Integrity Problems: - The data values may need to satisfy some integrity constraints. For
example, the balance field value must be greater than 5000. We have to handle this through
program code in file processing systems. But in database we can declare the integrity constraints
along with definition itself.
6. Atomicity Problem: - If any failure occurs in the system the transactions which are executing
should fully get executed or should not, so that database remains in consistent (correct) state. For
example, if a transaction of transferring money from account X to account Y is in process. If a
system failure occurs the money from account X to Y should get transferred or should not get
transferred. File processing system do not ensures such atomicity.
7. Security Problems:-Enforcing security constraints in file processing system is very difficult as
the application programs are added to the system in an ad-hoc manner.
 Advantages of DBMS over file processing system
Q. State and explain four advantages of DBMS over file processing system. (4 Marks)
1. Reduction in Redundancy: Duplication of records is reduced.
2. Avoiding Inconsistency: As the redundancy is reduced inconsistency is avoided.
3. Maintaining Integrity: Accuracy is maintained.
4. Sharing of data: Sharing of data is possible.
5. Enforcement of Security: Security can be enforced.
6. Transaction support.

Disadvantages of DBMS

1. Increased Cost: DBMS implementation cost is high compared to the file system.
2. Complexity: Database systems are complex to understand
3. Performance: Database systems are generic, making them suitable for various applications. However
this feature affect their performance for some applications
4. Size: They are large in size.
5. Higher impact of a failure: The DBMS is placed at centralized location if the failure of any
component can bring operations to a halt.

Q. Comparison between File Processing system & DBMS.

S. N. File Processing System DBMS

1 PC- Based small systems. Mini-mainframe based large systems.
2 Single user system. Multiple user system.
3 Relatively cheaper. Relatively expensive.
4 Less number of files used. More number of files used.
5 Data redundancy & inconsistency occur. Data independent & non-redundant.
6 Data access is difficult. Data access is efficient.
7 Security problems. Better security.
8 Data is isolated. Data is integrated.
9 Transaction concept is not used. The concept of transaction is important aspect.

1.5 Applications of DBMS

Q. Explain different applications of DBMS.
Q. List any four applications of DBMS. (2 Marks)
1. Airlines and Railways: Online database for reservation, displaying the schedule information.

2. Library Management System: There are thousands of books in the library so it is very difficult to
keep record of all the books in a copy or register. So DBMS used to maintain all the information
relate to book issue dates, name of the book, author and availability of the book
3. Banking: Customer inquiry, accounts, loans, and other transactions.
4. Education: Course registration, result, and other information.
5. Telecommunications: Communication network, telephone numbers, record of calls, for
 generating monthly bills, etc.
6. E-commerce: Business activity such as online shopping, booking of holiday package,
consulting adoctor, etc.
7. Human resources: Organizations use database for storing information about their employees,
salaries, benefits, taxes, and for generating salary checks.
8. Military: Military keeps records of millions of soldiers and it has millions of files that should be
keep secured and safe. As DBMS provides a big security assurance to the military information so it is
widely used in militaries. One can easily search for all the information about anyone within seconds
with the help of DBMS.
9. Web based services:-For web user’s feedback, responses, resource sharing etc.
10. Sales: - For customer, product & purchase information.

2 Three level architecture for database system.

This architecture has three levels:

1. External level
2. Conceptual level
3. Internal level

1. External level
It is also called view level. The reason this level is called “view” is because several users can view their desired
data from this level which is internally fetched from database with the help of conceptual and internal level
mapping.

The user doesn’t need to know the database schema details such as data structure, table definition etc. user is
only concerned about data which is what returned back to the view level after it has been fetched from database
(present at the internal level).
External level is the “top level” of the Three Level DBMS Architecture.

2. Conceptual level
It is also called logical level. The whole design of the database such as relationship among data, schema of data
etc. are described in this level.

Database constraints and security are also implemented in this level of architecture. This level is maintained by
DBA (database administrator).

3. Internal level
This level is also known as physical level. This level describes how the data is actually stored in the storage
devices. This level is also responsible for allocating space to the data. This is the lowest level of the
architecture.

3. Data abstraction: Different level of Data abstraction, Instance and schema, Data independence-
Logical and Physical independence.

3.1 Data Abstraction (IMP)

Define:- (2M,W-18)
i)Data Abstraction
ii)Data Redundancy
Ans:
1. Data Abstraction
Many end users are not computer trained so it is needed to hide complex data structures from them.
Hiding complexity of data structures from end user through different levels is known as data abstraction.
It has 3 levels :
a. Physical level
b. logical level
c. view level
2. Data redundancy :
The repetition of information is known as redundancy .This redundancy leads to higher storage and access
cost. It may lead to data inconsistency, that is different copies of the same data may have different values.

Q. Explain three levels of data abstraction with suitable diagram.

OR
Q. Describe data abstraction with neat diagram. (Diagram- 1 Mark, Description of level-1 Mark)
OR
Q. Explain three levels of data abstraction with suitable diagram.

Ans: - Data Abstraction:-

 It can be defined as the process of hiding the complexity of data & representing the data which
need to be shown to the user.
 The major purpose of a database system is to provide users with an abstract view of the system.
 The system hides certain details of how data is stored and created and maintained complexity
should behidden from database users.
 Fig.:- Three levels of data abstraction
1. Physical Level:
• It is lowest level of abstraction.
• This level defines lowest complicated data structure of database system.
• This level hidden from user.
• It defines how the data are stored.
2. Logical /Conceptual Level:
• The level next to physical level is called logical level.
• This level defines what data stored in the database and what the relationships among these data
are.
• Fully decides the structure of the entire database.
• Describes What data are stored.
3. View Level:
• This level is used to show the part of database to user.
• There is more complexity in physical as well as logical level so user should not interact with
complicated database.
• So different view of database can be created for user to interact with database easily.
• Describes part of the database for a particular group of users.
• E.g. tellers in a bank get a view of customer accounts, but not of payroll data.

3.2 Instance & Schema (IMP)

Q. Define Schema? (1 Mark)
Q. Define Instance? (1 Mark)

Instance :- ( Definition)
• Definition: The data stored in the database at a particular moment of time is called an “instance”
or a database state.
• Every time we update (i.e. insert, delete or modify) the value of a data item in record, that
time onestate of the database changes into another state.
 Schema:-
• Definition: Design of a database is called the schema.
• A database schema is the skeleton structure that represents the logical view of the entire database.
• A database schema defines its entities and the relationship among them.
For example: An employee table in database exists with the following attributes:

EMP_NAME EMP_ID EMP_ADDRESS EMP_CONTACT

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

This is the schema of the employee table. Schema defines the attributes of tables in the database.

3.3 Data Independence (IMP)

Q. What are data independence? What are its types? (Definition of data independence - 1 Mark,
list oftypes - 1 Mark, definition of each type - 1Mark each)

Ans: - Data Independence:

• It is the ability to modify a schema in one level without affecting schema in another level.
• When we change data at one layer, it does not affect the data at another level. This data is
independent but mapped to each other.

There are two types of data independence:

1) Logical data independence
2) Physical data independence.

1. Logical data independence:

• It refers to the ability to modify the Logical or Conceptual schema without causing any
changes inschema followed at view levels.
• For example, a table (relation) stored in the database and all its constraints, applied on that relation.
2. Physical data independence:
• It refers to the ability to modify the schema followed at the physical level without affecting the
schema followed at the logical level/conceptual level.
• For example, in case we want to change or upgrade the storage system itself − suppose we
want toreplace hard-disks − it should not have any impact on the logical data or schemas.

4. Overall Structure of DBMS

4.1 Components of DBMS & Overall Structure of DBMS (IMP)

Q. Draw a neat labeled diagram of overall DBMS structure. OR

Q. Explain the overall structure of DBMS with suitable diagram.(4 Marks)
Q. Enlist different components of DBMS.(2 Marks)
• A database system is divided into modules that deal with each of the responsibilities of the
overallsystem.
• The functional components of a database system can be broadly divided into the Storage
manager, Query processor & Disk Storage.
1. Query Processor:-

 Query Processor helps the database system, simplify and facilitate access to data.

 The work of Query Processor is to execute the query successfully

o It interprets (queries) received from end user via an application program into
instructions

o It also executes the user request which is received from the DML compiler.

a). DDL Interpreter: DDL interpreter converts DDL statements (like schema definition) into a set of
tables containing meta data or data dictionary.
b). DML Compiler: It translates DML statements ( like select, insert, update, delete) into low level
instructions (object code), so that query evaluation engine understands.
c). Embedded DML pre-compiler:It processes DML statements embedded in an application program
into procedural calls.
d). Query Evaluation Engine: It executes low-level instructions (object code) generated by the DML
compiler.

2. Storage Manager:-

 Storage Manager is & program that provides an interface between the data stored in the
database and the queries received.

 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, It is responsible for updating, storing, deleting, and retrieving data in the database.

a). Authorization and Integrity Manager: It checks the authority of users to access data and checks
the integrity constraints when the database is modified.

b). Transaction Manager : It ensures that the database remains in a consistent (correct) state despite
 system failures, and that concurrent transaction executions proceed without conflicting

c). File Manager :

It manages the allocation of space on disk storage and the data structures used to represent information
stored on disk

d). Buffer Manager :

It is responsible for fetching data from disk storage into main memory, and deciding what data to
cache in main memory.

3. Data Structures:- It contains the following components

a). Data Dictionary : It stores Metadata(data about data) about the structure of the database.
b). Data Files : It stores the database.
c). Indices : It provides faster retrieval of data item.
d). Statistical Data: It stores statistical information about the data in the database. This information
is used by query processor to select efficient ways to execute query.
4.2 Database Users (IMP)

• There are different types of database users:-

1. Naive Users: -
• Naive means Lacking Experience (untrained) these are the users who need not be aware of the
presence of the Data Base System.
• Example of these type of users is the user of an ATM machine. Because these users only responds
to the instructions displayed on the screen (enter your pin number, click here, enter the required
money etc.). Obviously operations performed by these users are very limited.

2. Application Programmers: -

• Professional / Application programmers are those who are responsible for developing application
programs or user interface. The application programs could be written in a general-purpose
programming language or the commands available to manipulate a database.

• For example: Writing a C program to generate the report of employees who are working in
particular department, will involve a query to fetch the data from database. It will include a
embedded SQL query in the C Program.

3. Sophisticated Users:-
• Simply we can say that these are the EXPERIENCED users. These people interact with the
system without writing programs. They form requests by writing queries in database query
language.
• Sophisticated users are the users who are familiar with the structure of database & facilities of
DBMS.

4. Specialized Users:-
• These are the sophisticated users who write specialized database applications.
• For example computer aided design (CAD) systems, knowledge-based and expert systems.

5. Database Administrator:-
• It is responsible for managing the whole database system.
• He/She designs, creates & maintains the database.
• He/She manages the users who can access this db & controls integrity issues.

6. Database Designers:-
• Database designers are the computer professionals which responsible for developing db
orientedapplication /software's.
 4.3 Functions of Database Administrator (DBA) (IMP)
Q. List four function of database administrator. (List any four function - 1 Mark each)
• The DBA (Database Administrator) is a person or group of persons who is responsible
for the management of the database.
• The person having central control over the system is called DBA. The functions of DBA are listed
below:-
1. Schema Definition: - The Database Administrator creates the database schema by executing DDL
statements. Schema includes the logical structure of database table (Relation) like data types of
attributes, length of attributes, integrity constraints etc.
2. Storage structure and access method definition: - Database tables or indexes are stored
in the following ways: Flat files, Heaps, B+ Tree etc.
3. Schema and physical organization modification: - The DBA carries out changes to the
existingschema and physical organization.
4. Granting authorization for data access:-
• The DBA provides different access rights to the users according to their level. Ordinary
users might have highly restricted access to data.
• DBA is responsible for granting the access to the database.
5. Integrity- Constraint specification: Integrity constraints are written by DBA and they are
stored in aspecial file, which is accessed by database manager, while updating the data.
6. Routine Maintenance: - Some of the routine maintenance activities of a DBA is given below.
a) Taking backup of database periodically
b) Ensuring enough disk space is available all the time.
c) Monitoring jobs running on the database.
d) Ensure that performance is not degraded by some expensive task submitted by some users.
e) Performance Tuning

5. Modelling: Record based logical model- Relation, Network, Hierarchical.

Data Models
Q. List various data models (2 Marks)
Q. List any two data models (2 Marks)
• A data model organizes elements of data and standardizes how they relate to one another and to
properties of the real world entities.
• A data model is a collection of concepts that can be used to describe the logical structure of a
database.
• A data model is underlying structure of the database.
• This is the way which describes the design of database at physical, logical & view level.
• Types of Data Models
– Hierarchical Model
– Network Model
– Relational Model
– E-R model

5.1 Hierarchical Model

 A hierarchical database model is a data model in which the data are organized into a tree-like
 structure.
 The structure is based on the rule that one parent can have many children but children are
allowedonly one parent. For example, department is the parent entity called root and it has
several children entities like course, students, professors and many more.
 It preserves one to many relations.

Advantages
 Simplicity
 Security
 Database Integrity
 Efficiency

Disadvantages
 Complexity of Implementation
 Difficulty in Management
 Poor Portability

5.2 Network Model

 In the network model, entities are organized in a graph, in which some entities can be
accessedthrough several path.
 Its distinguishing feature is that the schema, viewed as a graph in which object types are
nodes and relationship types are arcs, is not restricted to being a hierarchy.
 It can preserve one to many or many to many relation.
 Advantages
 Simplicity
 Data Integrity
Disadvantages
 System complexity

5.3 Relational Model

 Most widely used model by commercial data processing applications.
 It is based on the concept of a relation, which is physically represented as a table.
 A table is a collection of rows & columns . The relational model uses a collection of tables to
represent both data and the relationships among those data.
 The tables are used to hold information about the objects to be represented in the database.
 A relation or a table is represented as a two dimensional form in which the rows of the table
corresponds to individual records and the columns corresponds to attributes.
 Each column represents attribute or field of an entity and rows represents records or tuples.

Advantages
Simplicity
Ease of Use
Accuracy
 Data Integrity
Security
Disadvantages
Maintenance Problem
Cost
Physical Storage
Lack of Scalability
Complexity in Structure
Decrease in performance over time

Q. Compare Network and Hierarchical Model.

S.N. Hierarchical Model Network Model

1. It is based on tree like structure with one root. It is based on records and links.
2. Supports one to many relationships. Supports many to many relationships
3. Less popular More popular than Hierarchical
4. The main application of hierarchical data Network model is upgraded version of the
model is in the mainframe database system hierarchical model so used in the networks.
5. It does not uses client server architecture. It uses client server architecture.
6. Uses pointers to relate data Uses links to relate data

Q. Compare Network and Relational model.

S.N. Network Model Relational Model

1. It is based on the tree like structure. This is based model i.e. it is collection of
rows & columns.
2. Supports Many to Many & One to Many Supports Many to Many & One to One
relationships. relationships.
3. It is not much popular Much Popular
4. Invented by Charles Bachman Invented by E. F. Codd

Q. Compare Hierarchical and Relational model.

S.N. Hierarchical Model Relational Model

1. It is based on tree like structure with one This is based model i.e. it is collection of
root. rows & columns.
2. Supports one to many relationships. Supports Many to Many & One to One
relationships.
3. Less popular Much Popular
4. The main application of hierarchical data There are many application of the relational
model is in the mainframe database system model which are unlimited.
 6. Data Modelling using the E-R Model: Entity Relationship Model, Strong Entity set, Weak
Entity set, Types of Attributes, E-R Diagrams.

6.1 Entity Relationship Model

 Entity Relationship(ER) model is a Graphical representation of data that describes how data is related to
each other which is known as Entity Relationship Diagram (ER Diagram).
 The ER model a high level data model that is useful in developing a conceptual design for a database.
 Creation of an ER diagram helps the designers to understand & to specify the desired components of
the database & the relationships among those components.
 It is based on the notion of real-world entities and relationships among them.
 ER diagram has the following three components:
Entities: Entity is a real-world thing or object. It can be a person, place, or even a concept.
Example: Teachers, Students, Course, Building, Department, etc are some of the entities of a School
Management System.
Attributes: An entity contains a real-world property called attribute. This is the characteristics of that
attribute.
Example: The entity teacher has the property like teacher id, name, salary, age, etc.
Relationships: Relationship tells how two attributes are related. Example Teacher works for a
department,

6.1.1 Symbols and Notations of E-R Model:-

Q. List four symbols used in E-R diagram. (2 Marks)
 Components of E-R Model
Q. State weak and strong entity set. (2 Marks)
Q. Define entity. (2 Marks)
Q. Explain strong entity and weak entity set. (4 Marks)

As shown in the above diagram, an ER diagram has three main components:

1. Entity
2. Attribute
3. Relationship

1. Entity
An entity is an object or component of data. An entity is represented as rectangle in an ER diagram.
For example: In the following ER diagram we have two entities Student and College and these two entities have
many to one relationship as many students study in a single college. We will read more about relationships later,
for now focus on entities.

Strong entity: The strong entity always have primary key. Its existence is not dependent on any other entity i.e.
it is independent of other entity.

Weak Entity:
An entity that cannot be uniquely identified by its own attributes and relies on the relationship with other entity
is called weak entity. The weak entity is represented by a double rectangle. For example – a bank account
cannot be uniquely identified without knowing the bank to which the account belongs, so bank account is a
weak entity.
2. Attribute
Types of Attributes
Q. Define attribute. (1 Mark)
Q. Explain single value and multi value attribute of E-R model. (4 Marks)
Q. Explain any 4 types of attributes (4 Marks)

An attribute describes the property of an entity. An attribute is represented as Oval in an ER diagram. There are
four types of attributes:

1. Simple attribute
2. Key attribute
3. Composite attribute
4. Multivalued attribute
5. Derived attribute
6. NULL Attribute

1. Simple attribute

Simple attributes can’t be divided any further. For example, a student’s contact number. It is also called an
atomic value.
Following are the example of a simple attribute:
 Id of the Employee can be a simple attribute,
 Salary of the Employee can be a simple attribute,
 Age and class of the student can be a simple attribute, etc.

2. Key attribute:
A key attribute can uniquely identify an entity from an entity set. For example, student roll number can uniquely
identify a student from a set of students. Key attribute is represented by oval same as other attributes however
the text of key attribute is underlined.
 3. Composite attribute:
An attribute that is a combination of other attributes is known as composite attribute. For example, In student
entity, the student address is a composite attribute as an address is composed of other attributes such as pin
code, state, country.

4. Multivalued attribute:
An attribute that can hold multiple values is known as multivalued attribute. It is represented with double
ovals in an ER Diagram. For example – A person can have more than one phone numbers so the phone number
attribute is multivalued.

E-R diagram with multivalued and derived attributes:

5. Derived attribute:
A derived attribute is one whose value is dynamic and derived from another attribute. It is represented
by dashed oval in an ER Diagram. For example – Person age is a derived attribute as it changes over time and
can be derived from another attribute (Date of birth).

6. NULL Attribute: It is used when an entity does not have a value for an attribute. i.e. missing or not
known.
 Relationship
 A relationship is a association between the entities. A relationship is represented by diamond shape in
ER diagram, it shows the relationship among entities.
 The degree of a relationship = the number of entity sets that participate in the relationship.
 Most useful in describing binary relationship sets.
 Mapping cardinality of a relationship
For a binary relationship set the mapping cardinality must be one of the following types:
– One to one
– One to many
– Many to one
– Many to many

1. One to One Relationship

When a single instance of an entity is associated with a single instance of another entity then it is called one to
one relationship. For example, a person has only one passport and a passport is given to one person.

2. One to Many Relationship

When a single instance of an entity is associated with more than one instances of another entity then it is called
one to many relationship. For example – a customer can place many orders but a order cannot be placed by
many customers.

3. Many to One Relationship

When more than one instances of an entity is associated with a single instance of another entity then it is called
many to one relationship. For example – many students can study in a single college but a student cannot study
in many colleges at the same time.
4. Many to Many Relationship

When more than one instances of an entity is associated with more than one instances of another entity then it is
called many to many relationship. For example, a can be assigned to many projects and a project can be
assigned to many students.

Total Participation of an Entity set

Total participation of an entity set represents that each entity in entity set must have at least one relationship in a
relationship set. It is also called mandatory participation. For example: In the following diagram each
college must have at-least one associated Student. Total participation is represented using a double
line between the entity set and relationship set.
Q. Draw an E-R diagram of hospital management system. (Correct E-R Diagram - 4 marks)

Q. Draw an E-R diagram of Library management system

Q. Draw an E-R diagram of Banking System
Q. Draw an E-R diagram of Airline Reservation System
Q. Draw an E-R diagram of library management system considering issue and return, fine calculation
facility, also show primary key, weak entity and strong entity.(6M,W-18)
Ans