UNIT -I

Database System Applications:

Applications of Database Management System (DBMS)

1. Railway Reservation System

The database is required to keep record of ticket booking, train departure, and arrival
status.

2. Library Management System

DBMS is used to maintain all the information related to book issuing dates, name of the
book, author, and availability of the book.

3. Banking
All thousands of transactions possible just because of DBMS that manages all the bank
transactions.

4. Universities and colleges

Student’s registrations details, results, courses, and grades all the information are stored in the
database.

5. Credit card transactions

A credit cardholder knows the importance of their information that all are secured through
DBMS.

6. Military
One can easily search for all the information about anyone within seconds with the help of
DBMS.

7. Online Shopping
Purchase information, invoice bills, and payment, all of these are done with the help of DBMS.

8. Airline Reservation system

Same as the railway reservation system, the airline also needs DBMS to keep records of flights
arrival, departure, and delay status.

9. Agriculture
DBMS can be used to maintain the record of crops, machinery, fertilizers, and livestock. One can
keep each record of everything used in agriculture using DBMS.
A Historical Perspective

Database Management Systems (DBMS) have been around for several decades, and their history
can be traced back to the early 1960s. In the early days, computer systems were designed to
manage data in a hierarchical or navigational manner, where data was stored in a tree-like
structure. This method of storing data was inefficient and difficult to use, as it required a lot of
manual effort to access and manage the data.
In the late 1960s, The first general-purpose DBMS, designed by Charles Bachman, was called
the Integrated Data Store (IDS) which was based on network data model for which he was
received the Turing Award (The most prestigious award which is equivalent to Nobel prize in
the field of Computer Science.).
In the late 1970s, Mr Edgar Codd proposed a new data representation framework called
the Relational Database Model. Mr Edgar Codd won the 1981 Turing Award for his seminal
work. This model was based on the concept of a table, with rows representing individual records
and columns representing individual fields within those records. The relational model allowed
for more efficient storage and retrieval of data and was easier to use than the hierarchical or
navigational models.
In the late 1980s IBM developed the Structured Query Language (SQL) for relational
databases, as a part of R project. This system was designed to manage large amounts of data and
was used primarily in corporate and government applications. SQL was adopted by the American
National Standards Institute (ANSI) and International Organization for Standardization (ISO).
In the 1980s, several new DBMS products were introduced, including Oracle, Sybase, and
Microsoft SQL Server. These systems were designed to be more user-friendly and to support
more advanced data modeling and query languages.
In the 1990s, object-oriented DBMS (OODBMS) emerged, which were designed to store and
manage complex data structures, such as multimedia and other types of non-traditional data.
These systems were initially popular in research and academic environments, but their adoption
was limited in the commercial sector.
In the 1991, Microsoft ships MS access, a personal DBMS and that displaces all other personal
DBMS products.
In the 1997, XML applied to database processing. Many vendors begin to integrate XML into
DBMS products.
In the 2000s, web-based applications and cloud computing became more popular, and DBMS
systems began to adapt to these new technologies. New DBMS systems were developed to
support distributed and web-based applications, including NoSQL databases such as MongoDB
and Cassandra.
Today, DBMS systems continue to evolve, with an emphasis on scalability, performance, and
support for cloud-based applications. Some of the most popular DBMS systems in use today
include Oracle, Microsoft SQL Server, MySQL, PostgreSQL, and MongoDB.
 File Systems versus a DBMS

What is a File system?

A file system is a technique of arranging the files in a storage devices like a hard disk, pen drive,
DVD, etc. It helps you to organizes the data and allows easy retrieval of files when they are
required. A file system enables you to handle the way of reading and writing data to the storage
medium. It is directly installed into the computer with the Operating systems such as Windows
and Linux.

What is DBMS?

Database Management System (DBMS) is a software for storing and retrieving user’s data while
considering appropriate security measures. It consists of a group of programs that manipulate the
database. The DBMS accepts the request for data from an application and instructs the DBMS
engine to provide the specific data. In large systems, a DBMS helps users and other third-party
software to store and retrieve data.

Difference between File System and Database systems

Basis File System DBMS

File system is a software that manages

and organizes the files in a storage DBMS is a software for
1. Structure medium within a computer. managing the database.

2. Data Redundant data can be present in a file In DBMS there is no redundant

Redundancy system. data.

It provides backup and

[Link] and It doesn’t provide backup and recovery recovery of data even if it is
Recovery of data if it is lost. lost.

4. Query There is no efficient query processing in Efficient query processing is

processing file system. there in DBMS.

There is more data consistency

There is less data consistency in file because of the process of
[Link] system. normalization.

6. Complexity It is less complex as compared to DBMS. It has more complexity in

 Basis File System DBMS

handling as compared to file

system.

DBMS has more security

[Link] File systems provide less security in mechanisms as compared to
Constraints comparison to DBMS. file system.

It has a comparatively higher

[Link] It is less expensive than DBMS. cost than a file system.

9. Data In DBMS data independence

Independence There is no data independence. exists.

Disadvantages of 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.

Advantages 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.

DataModel

A data model is a collection of high-level data description constructs that hide many low-level
storage details. A DBMS allows a user to define the data to be stored in terms of a data model.
The data models can be classified into different categories:

1. Hierarchical Model
2. Network Model
3. Entity-Relationship Model
4. Relational Model
5. Object-Based Data Model
6. Semi-structured Data model
1. Hierarchical Model

Hierarchical Model was the first DBMS model. This model organises the data in the hierarchical
tree structure.
The hierarchy starts from the root which has root data and then it expands in the form of a tree
adding child node to the parent node. This model easily represents some of the real-world
relationships like food recipes, sitemap of a website etc.
Depicts a set of one-to-many (1:M) relationships

Disadvantages of the hierarchical model:

 easy to design at the same time it is quite complex to implement.

 This model also lacks flexibility as the changes in the new tables or segments often yield
very complex system management tasks. Here, a deletion of one segment can lead to the
involuntary deletion of all segments under it.

2. Network Model

This model is an extension of the hierarchical model, the only difference is that a record can have
more than one parent. It replaces the hierarchical tree with a graph.
The network model was created to represent complex data relationships more effectively when
compared to hierarchical models, to improve database performance and standards.
Depicts both one-to-many (1:M) and many-to-many (M:N) relationships.

Disadvantages of the Network Model:

 Database contains a complex array of pointers.

 System complexity limits efficiency.
 Structural changes require changes in all application programs.
 Navigation systems yield complex implementation and management.
 Keep heavy pressure on programmers due to the complex structure.
 Any change like updating, deletion, insertion is very complex.

3. Entity-Relationship Model

An ER model is the logical representation of data as objects and relationships among them.
These objects are known as entities, and relationship is an association among these entities.
Entity-Relationship Model Components

ER diagram basically having three components:

1. Entities − It is a real-world thing which can be a person, place, or even a concept. For
Example: Department, Admin, Courses, Teachers, Students, Building, etc are some of the
entities of a School Management System.
2. Attributes − An entity which contains a real-world property called an attribute. For
Example: The entity employee has the property like employee id, salary, age, etc.
3. Relationship − Relationship tells how two attributes are related. For Example: Employee
works for a department.
An entity has a real-world property called attribute and these attributes are defined by a set of
values called domain.

Advantages of Entity-Relationship Model

 The ER model is easy to build.

 This model is widely used by database designers for communicating their ideas.
 This model can easily convert to any other model like network model, hierarchical model
etc.
 It is integrated with the dominant relational model.

Disadvantages of Entity-Relationship Model

 There is no industry standard for developing an ER model.

 Information might be lost or hidden in the ER model.
 There is no Data Manipulation
 There is limited relationship representation.
4. Relational Model

The relational model uses a collection of tables to represent both data and the relationships.
Tables are also known as relations. Each table has multiple columns represent as attributes,
Attributes are the properties which define a relation. Each row of the table represents as Tuple,
Tuple is one piece of information.

Terminologies used in Realation Model

 Tables: relations are saved in the table format. A table has two properties rows and
columns
 Attribute: columns represent as attributes
 Tuple: A Row represent as Tuple
 Relation Schema: A relation schema represents the name of the relation with its
attributes.
 Degree: The total number of attributes which in the relation is called the degree of the
relation.
 Cardinality: Total number of rows present in the Table.
 Column: The column represents the set of values for a specific attribute.
 Relation instance: The set of tuples of a relation at a particular instance of time is called
as relation instance.

Advantages of Realation Model

 Structural Independence: Structural independence is an ability that allows us to make

changes in one database structure without affecting other. The relational model have
structural independence. Hence making required changes in the database is convenient in
relational database model.
 Conceptual Simplicity: The relational model allows the designer to simply focus on
logical design and not on physical design. Hence relational models are conceptually
simple to understand.
 Query Capability: Using simple query language (such as SQL) user can get information
from the database or designer can manipulate the database structure.
 Easy design,maintenance and usage: The relational models can be designed logically
hence they are easy to maintain and use.
Disadvantages of Realation Model

 Relational model requires powerful hardware and large data storage devices.
 May lead to slower processing time.
 Poorly designed systems lead to poor implementation of database systems.

5. Object-Based Data Model

The complex real world problems are represented as objects with different attributes. In Object
Oriented Data Model, data and their relationships are contained in a single structure which is
referred as object. All objects have multiple relationships between them. Basically, it is
combination of Object Oriented programming and Relational Database Model.

Components of Object Oriented Data Model

1. Objects: An object is an abstraction of a real world entity or we can say it is an instance

of class. Objects encapsulates data and code into a single unit which provide data
abstraction by hiding the implementation details from the user.
2. Attribute: An attribute describes the properties of object.
3. Methods: Method represents the behavior of an object, it represents the real-world action
4. Class: A class is a collection of similar objects with shared structure i.e. attributes and
behavior.
5. Inheritance: new classes are created from the existing classes

Advantages of Object-Based Data Model

 Reduced Maintenance
 Real-World Modeling
 Improved Reliability and Flexibility
 High Code Reusability

Disadvantages of Object-Based Data Model

 It is a complex navigational system.

 Slow development of standards.
 High system overheads.
 Slow transactions.
6. Semi-structured Data model

The semi-structured data model permits the specification of data where individual data items of
same type may have different sets of attributes. The Extensible Markup Language (XML) is
widely used to represent semistructured data model.

Advantages of Semi-structured Data Model

 Data is not constrained by fixed schema.

 It is flexible.
 It is portable.

Disadvantage of Semi-structured Data Model

 Queries are less efficient than other types of data model.

Levels of Abstraction in a DBMS

Database systems include complex data-structures. In terms of retrieval of data, reduce

complexity in terms of usability of users and in order to make the system efficient, developers
use levels of abstraction that hide irrelevant details from the users. Levels of abstraction simplify
database design.
Mainly there are three levels of abstraction for DBMS, which are as follows −

 Physical or Internal Level

 Logical or Conceptual Level
 View or External Level
Physical or Internal Level
It is the lowest level of abstraction for DBMS which defines how the data is actually stored, it
defines data-structures to store data and access methods used by the database.
Logical or Conceptual Level
Logical level is the intermediate level or next higher level. It describes what data is stored in the
database and what relationship exists among those data.
View or External Level
It is the highest level. In view level, there are different levels of views and every view only
defines a part of the entire data. It also simplifies interaction with the user and it provides many
views or multiple views of the same database.
Instance
Instance or extension or database state is a collection of information that stored in a database at a
particular moment is called an instance of the database.
The Database instance refers to the information stored in the database at a given point of time.
Thus, it is a dynamic value which keeps on changing.
Types of Schema

The different types of schemas are as follows −

 Physical schema − It is a database design at the physical [Link] is hidden below the
logical schema and can be changed easily without affecting the application programs.
 Logical schema − It is a database design at the logical level. Programmers construct
applications using logical schema.
 External − It is schema at view level. It is the highest level of a schema which defines
the views for end users.
Differences
 The major differences between schema and instance are as follows −

Database Schema Database Instance

It is the definition of the database or It is a snapshot of a database at a specific moment.

it is defined as the description of the
database.

It rarely changes. It changes frequently.

Example” We take two tables emp At a moment, what is the value of the database
table and dept [Link] schema is called [Link] t=8 A.M

Id Empid name salary did

Name 1 A 5000 d1

Salary 2 B 2000 d2

dept At t=9 A.M

Dept 3

Dept_id C

dname 3000

Emp and dept both called as d3

schemas
It gives database definition
Empid 1 and 2 are called as Instance 1
At time 9 A.M instance 2 changes

This corresponds to the variable The value of the variable in a program at a point in
declaration of a programming time corresponds to an instance of the database
 Database Schema Database Instance

language. schema.

Data Independence

In DBMS there are two types of data independence

1. Physical data independence

2. Logical data independence.

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.

Logical Data Independence

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

1. External views
2. External API 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.

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
 It allows you to improve state which is undamaged or undivided
 Database incongruity is vastly reduced.
Structure of a DBMS

1. Query Processor :

It interprets the requests (queries) received from end user via an application program
into instructions. It also executes the user request which is received from the DML
compiler.

Query Processor contains the following components –

 DML Compiler –
It processes the DML statements into low level instruction (machine language), so that
they can be executed.

 DDL Interpreter –
It processes the DDL statements into a set of table containing meta data (data about
data).

 Embedded DML Pre-compiler –

It processes DML statements embedded in an application program into procedural
calls.

 Query Optimizer –
It executes the instruction generated by DML Compiler.

2. Storage Manager :

Storage Manager is a program that provides an interface between the data stored in the
database and the queries received. It is also known as Database Control System. It
maintains the consistency and integrity of the database by applying the constraints and
executes the DCL statements. It is responsible for updating, storing, deleting, and
retrieving data in the database.
It contains the following components –

 Authorization Manager –
It ensures role-based access control, i.e,. checks whether the particular person is
privileged to perform the requested operation or not.

 Integrity Manager –
It checks the integrity constraints when the database is modified.

 Transaction Manager –
It controls concurrent access by performing the operations in a scheduled way that it
receives the transaction. Thus, it ensures that the database remains in the consistent
state before and after the execution of a transaction.
 File Manager –
It manages the file space and the data structure used to represent information in the
database.

 Buffer Manager –
It is responsible for cache memory and the transfer of data between the secondary
storage and main memory.

3. Disk Storage :
It contains the following components –

 Data Files –
It stores the data.

 Data Dictionary –
It contains the information about the structure of any database object. It is the
repository of information that governs the metadata.

 Indices –
It provides faster retrieval of data item.
Introduction to Database Design:

Database Design

Database design is the organization of data according to a database model. Properly designed
databases are easy to maintain, improves data consistency.
The database design process can be divided into six steps. The ER model(Entity Relationship
model) is most relevant to the first three steps.
1. Requirement analysis
2. Conceptual database design
3. Logical database design
4. Schema refinement
5. Physical database design
6. Application and security design
1. Requirement analysis
 It is necessary to understand what data need to be stored in the database, what
applications must be built, what are all those operations that are frequently used by the
system.
 The requirement analysis is an informal process and it requires proper communication
with user groups.
 There are several methods for organizing and presenting information gathered in this
step. Some automated tools can also be used for this purpose.
2. Conceptual database design
 The information gathered, is used to develop a high-level description of the data to be
stored in the database
 This is a steps in which E-R Model i.e. Entity Relationship model is built.
 The goal of this design is to create a simple description of data that matches with the
requirements of users.
3. Logical database design
 This is a step in which ER model in converted to relational database schema, sometimes
called as the logical schema in the relational data model.
4. Schema refinement
 In this step, relational database schema is analyzed to identify the potential problems
and to refine it.
 The schema refinement can be done with the help of normalizing and restructuring the
relations.
5. Physical database design
 The design of database is refined further.
 This step may simply involve building indexes on tables and clustering tables, redesign
of parts of the database schema obtained from the earlier design steps.
6. Application and security design
 Using design methodologies like UML(Unified Modeling Language) try to address the
complete software design of the database can be accomplished.
 The role of each entity in every process must be reflected in the application task.
 For each role, there must be the provision for accessing and prohibiting some part of
database.
  Thus some access rules must be enforced on the application(which is accessing the
database) to protect the security features.

ER Diagrams
Introduction of ER Model
The Entity Relational Model is a model for identifying entities to be represented in the database
and representation of how those entities are related. The ER data model specifies enterprise
schema that represents the overall logical structure of a database graphically.

The Entity Relationship Diagram explains the relationship among the entities present in the
database. ER models are used to model real-world objects like a person, a car, or a company and
the relation between these real-world objects. In short, the ER Diagram is the structural format of
the database.

Why Use ER Diagrams In DBMS?

 ER diagrams are used to represent the E-R model in a database, which makes them easy to
convert into relations (tables).
 ER diagrams provide the purpose of real-world modeling of objects which makes them
intently useful.
 ER diagrams require no technical knowledge and no hardware support.
 These diagrams are very easy to understand and easy to create even for a naive user.
 It gives a standard solution for visualizing the data logically.

Symbols Used in ER Model

ER Model is used to model the logical view of the system from a data perspective which consists
of these symbols:
 Rectangles: Rectangles represent Entities in the ER Model.
 Ellipses: Ellipses represent Attributes in the ER Model.
 Diamond: Diamonds represent Relationships among Entities.
 Lines: Lines represent attributes to entities and entity sets with other relationship types.
 Double Ellipse: Double Ellipses represent Multi-Valued Attributes.
 Double Rectangle: Double Rectangle represents a Weak Entity.
Entities

An entity is a “thing” or “object” in the real world. An entity contains attributes, which
describe that entity. So anything about which we store information is called an entity. Entities
are recorded in the database and must be distinguishable, i.e., easily recognized from the
group.
For example: A student, An employee, or bank a/c, etc. all are entities.

Kinds of Entity:

There are two kinds of entities, which are as follows:

1. Tangible Entity:

It is an entity in DBMS, which is a physical object that we can touch or see. In simple words, an
entity that has a physical existence in the real world is called a tangible entity.

For example, in a database, a table represents a tangible entity because it contains a physical
object that we can see and touch in the real world. It includes colleges, bank lockers, mobiles,
cars, watches, pens, paintings, etc.

2. Intangible Entity:

It is an entity in DBMS, which is a non-physical object that we cannot see or touch. In simple
words, an entity that does not have any physical existence in the real world is known as an
intangible entity.

For example, a bank account logically exists, but we cannot see or touch it.
 Entity Sets

An entity set is a collection of similar types of entities that share the same attributes.

Types of Entity Sets-

An entity set may be of the following two types-

1. Strong entity set

2. Weak entity set

1. Strong Entity Set-

 A strong entity set is an entity set that contains sufficient attributes to uniquely identify
all its entities.
 In other words, a primary key exists for a strong entity set.
 Primary key of a strong entity set is represented by underlining it.

Symbols Used-

 A single rectangle is used for representing a strong entity set.

 A diamond symbol is used for representing the relationship that exists between two
strong entity sets.
 A single line is used for representing the connection of the strong entity set with the
relationship set.
 A double line is used for representing the total participation of an entity set with the
relationship set.
 Total participation may or may not exist in the relationship.
 Example-

Consider the following ER diagram-

In this ER diagram,

 Two strong entity sets “Student” and “Course” are related to each other.
 Student ID and Student name are the attributes of entity set “Student”.
 Student ID is the primary key using which any student can be identified uniquely.
 Course ID and Course name are the attributes of entity set “Course”.
 Course ID is the primary key using which any course can be identified uniquely.
 Double line between Student and relationship set signifies total participation.
 It suggests that each student must be enrolled in at least one course.
 Single line between Course and relationship set signifies partial participation.
 It suggests that there might exist some courses for which no enrollments are made.

2. Weak Entity Set-

 A weak entity set is an entity set that does not contain sufficient attributes to uniquely
identify its entities.
 In other words, a primary key does not exist for a weak entity set.
 However, it contains a partial key called as a discriminator.
 Discriminator can identify a group of entities from the entity set.
 Discriminator is represented by underlining with a dashed line.
NOTE-

 The combination of discriminator and primary key of the strong entity set makes it
possible to uniquely identify all entities of the weak entity set.
 Thus, this combination serves as a primary key for the weak entity set.
 Clearly, this primary key is not formed by the weak entity set completely.
 Symbols Used-

 A double rectangle is used for representing a weak entity set.

 A double diamond symbol is used for representing the relationship that exists between
the strong and weak entity sets and this relationship is known as identifying
relationship.
 A double line is used for representing the connection of the weak entity set with the
relationship set.
 Total participation always exists in the identifying relationship.

Example-

Consider the following ER diagram-

In this ER diagram,

 One strong entity set “Building” and one weak entity set “Apartment” are related to
each other.
 Strong entity set “Building” has building number as its primary key.
 Door number is the discriminator of the weak entity set “Apartment”.
 This is because door number alone can not identify an apartment uniquely as there may
be several other buildings having the same door number.
 Double line between Apartment and relationship set signifies total participation.
 It suggests that each apartment must be present in at least one building.
 Single line between Building and relationship set signifies partial participation.
 It suggests that there might exist some buildings which has no apartment.

Note:

Weak entity always has total participation but Strong entity may not have total
participation.
 Attributes

An attribute is a property or characteristic of an entity. An entity may contain any number of

attributes. One of the attributes is considered as the primary key. In an Entity-Relation model,
attributes are represented in an elliptical shape.

Types of Attributes-

1. Simple attributes
2. Composite attributes
3. Single valued attributes
4. Multi valued attributes
5. Derived attributes
6. Key attributes

1. Simple Attributes-

Simple attributes are those attributes which can not be divided further.
2. Composite Attributes-

Composite attributes are those attributes which are composed of many other simple attributes.

3. Single Valued Attributes-

Single valued attributes are those attributes which can take only one value for a given entity from
an entity set.

4. Multi Valued Attributes-

Multi valued attributes are those attributes which can take more than one value for a given entity
from an entity set.
5. Derived Attributes-

Derived attributes are those attributes which can be derived from other attribute(s).

6. Key Attributes-

Key attributes are those attributes which can identify an entity uniquely in an entity set.

Relationships

A relationship is used to describe the relation between entities. Diamond or rhombus is used to
represent the relationship.
Relationship Sets

Relationship Set-

A relationship set is a set of relationships of same type.

Example-

Set representation of above ER diagram is-

Degree of a Relationship Set-

The number of entity sets that participate in a relationship set is termed as the degree of that
relationship set. Thus,

Degree of a relationship set = Number of entity sets participating in a relationship set

Types of Relationship Sets-

On the basis of degree of a relationship set, a relationship set can be classified into the following
types-

1. Unary relationship set

2. Binary relationship set
3. Ternary relationship set
4. N-ary relationship set

1. Unary Relationship Set-

Unary relationship set is a relationship set where only one entity set participates in a relationship
set.

Example-
One person is married to only one person
2. Binary Relationship Set-

Binary relationship set is a relationship set where two entity sets participate in a relationship set.

Example-
Student is enrolled in a Course

3. Ternary Relationship Set-

Ternary relationship set is a relationship set where three entity sets participate in a relationship
set.
Example-

4. N-ary Relationship Set-

N-ary relationship set is a relationship set where ‘n’ entity sets participate in a relationship set.
Cardinality:

The number of times an entity of an entity set participates in a relationship set is known
as cardinality. Cardinality can be of different types:
1. One to One
2. One to Many
3. Many to One
4. 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.

Additional Features of the ER Model:

As the complexity of data increased, it became more and more difficult to use the traditional ER
Model for database modelling. Hence some Additional Features were made to the existing ER
Model to make it able to handle the complex applications better.
Hence, Three new concepts were added to the existing ER Model, they were:

 Generalization
 Specialization
 Aggregration
Some entities have relationships that form hierarchies. For instance, Employee can be an hourly
employee or contracted employee.
In this relationship hierarchies, some entities can act as superclass and some other entities can act
as subclass.
Superclass: An entity type that represents a general concept at a high level, is called superclass.
Subclass: An entity type that represents a specific concept at lower levels, is called subclass.
The subclass is said to inherit from superclass. When a subclass inherits from one or more
superclasses, it inherits all their attributes. In addition to the inherited attributes, a subclass can
also define its own specific attributes.
The symbol used for specialization/ Generalization is
Generalization

Generalization is the process of extracting common properties from a set of entities and create a
generalized entity from it. It is a bottom-up approach in which two or more entities can be
generalized to a higher level entity if they have some attributes in common.
In generalization, a number of entities are brought together into one generalized entity based on
their similar characteristics. For example, pigeon, house sparrow, crow and dove can all be
generalized as Birds.

STUDENT and FACULTY can be generalized to a higher level entity called PERSON as shown
in Figure 1. In this case, common attributes like P_NAME, P_ADD become part of higher entity
(PERSON) and specialized attributes like S_FEE become part of specialized entity (STUDENT).

Specialization

In specialization, an entity is divided into sub-entities based on their characteristics. It is a top-

down approach where higher level entity is specialized into two or more lower level entities. For
Example, EMPLOYEE entity in an Employee management system can be specialized into
DEVELOPER, TESTER etc. as shown in Figure 2. In this case, common attributes like
E_NAME, E_SAL etc. become part of higher entity (EMPLOYEE) and specialized attributes
like TES_TYPE become part of specialized entity (TESTER).
Aggregation

In aggregation, the relation between two entities is treated as a single entity. In aggregation,
relationship with its corresponding entities is aggregated into a higher level entity.
For example: Center entity offers the Course entity act as a single entity in the relationship
which is in a relationship with another entity visitor. In the real world, if a visitor visits a
coaching center then he will never enquiry about the Course only or just about the Center instead
he will ask the enquiry about both.
Conceptual Design With the ER Model

Process of Conceptual Database Design

The process of conceptual database design involves the following steps −

 Requirements gathering − The first step in conceptual database design is to gather the
business requirements from the stakeholders. This involves identifying the data elements,
relationships, and constraints that are essential to the organization's business
requirements.
 Entity-relationship modeling − The second step in conceptual database design is to
create an entity-relationship (ER) model, which represents the entities, attributes, and
relationships between the entities. The ER model is a graphical representation of the data
elements and their relationships.
 Normalization − The third step in conceptual database design is to normalize the ER
model, which ensures that the data is organized efficiently and reduces data redundancy
 Review and feedback − The fourth step in conceptual database design is to review the
ER model with the stakeholders and incorporate their feedback into the design.

Conceptual Design Using the ER Model

Design choices:

 Should a concept be modelled as an entity or an attribute?

 Should a concept be modelled as an entity or a relationship?
 Identifying relationships: Binary or ternary? Aggregation?

Constraints in the ER Model:

A lot of data semantics can (and should) be captured. But some constraints cannot be captured in
ER diagrams.

Need for further refining the schema: Relational schema obtained from ER diagram is a good
first step. But ER design subjective & can’t express certain constraints; so this relational schema
may need refinement.
Entity vs. Attribute

Should address be an attribute of Employees or an entity (connected to Employees by a

relationship)?
Depends upon the use we want to make of address information, and the semantics of the data:
 If we have several addresses per employee, address must be an entity (since attributes
cannot be set-valued).
 If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a
given city, address must be modelled as an entity (since attribute values are atomic).

Works_In2 does not allow an employee to work in a department for two or more periods.

Similar to the problem of wanting to record several addresses for an employee: we want to
record several values of the descriptive attributes for each instance of this relationship.

Entity vs. Relationship

First ER diagram OK if a manager gets a separate discretionary budget for each dept.

What if a manager gets a discretionary budget that covers all managed depts?

Redundancy of dbudget, which is stored for each dept managed by the manager.

Misleading: suggests dbudget apptnum tied to managed dept.

Binary vs. Ternary Relationships
Aggregation vs Ternary relationship
 UNIT-II

Introduction to the Relational Model in DBMS

The Relational Model(RM) for database management is an approach to logically
represent and manage the data stored in a database. In this model, the data is
organized into a collection of two-dimensional inter-related tables, also known
as relations. Each relation is a collection of columns and rows, where the column
represents the attributes of an entity and the rows (or tuples) represents the records.

Terminologies used in Realation Model

 Tables: relations are saved in the table format. A table has two properties rows and
columns
 Attribute: columns represent as attributes
 Tuple: A Row represent as Tuple
 Relation Schema: A relation schema represents the name of the relation with its
attributes.
 Degree: The total number of attributes which in the relation is called the degree of the
relation.
 Cardinality: Total number of rows present in the Table.
 Column: The column represents the set of values for a specific attribute.
 Relation instance: The set of tuples of a relation at a particular instance of time is
called as relation instance.

Advantages of Realation Model

 Structural Independence: Structural independence is an ability that allows us to make

changes in one database structure without affecting other. The relational model have
structural independence. Hence making required changes in the database is convenient
in relational database model.
 Conceptual Simplicity: The relational model allows the designer to simply focus on
logical design and not on physical design. Hence relational models are conceptually
simple to understand.
 Query Capability: Using simple query language (such as SQL) user can get information
from the database or designer can manipulate the database structure.
 Easy design,maintenance and usage: The relational models can be designed logically
hence they are easy to maintain and use.
Disadvantages of Realation Model

 Relational model requires powerful hardware and large data storage devices.
 May lead to slower processing time.
 Poorly designed systems lead to poor implementation of database systems.

Integrity constraint over relations in DBMS

Integrity constraints are rules that help to maintain the accuracy and consistency of data
in a database. For example, a simple integrity constraint in DBMS might state that all
students must have a valid Roll Number. This would prevent someone from accidentally
entering an invalid roll number into the database.
Integrity constraints can also be used to enforce relationships between tables. For
example, if a student can only have one aadhaar number, then an integrity constraint
can be used to ensure that only one aadhaar number is entered for each student.

Different types of Integrity Constraints

 Domain Constraint
 Entity Integer Constraint
 Referential Integrity Constraint
 Key Constraints

Domain Constraint
A domain constraint is a restriction on the values that can be stored in a column.
Strings, character, time, integer, currency, date etc. Are examples of the data type of
domain constraints.
example, if you have a column for "age" domain integrity constraints in DBMS would
ensure that only integer values can be entered into that column. This ensures that only
valid data is entered into the database.

Entity Integer Constraint

Entity integrity constraints would ensure that null values are not entered into any
required columns. It states that primary key value can't be null. This is because the
primary key value is used to identify individual rows in relation and if the primary key
has a null value, then we can't identify those rows
For example, if you have a column for "roll_number" an entity integrity constraint in
DBMS would ensure that this column cannot contain any null values.
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
PRIMARY KEY(column)
);

Referential Integrity Constraint

A referential integrity constraint is a restriction on how foreign keys can be used. A
foreign key is a column in one table that references a primary key in another table.
For example, let's say you have a table of Students and a table of Marks. The
"roll_number" column in the Marks table would be a foreign key that references the
"roll_number" column in the Students table.
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
FOREIGN KEY (column) REFERENCES table_name1(column)
);

Key Constraints
A key constraint is a rule that defines how data in a column(s) can be stored in a table.
A key is composed of one or more columns whose values uniquely identify each row in
the table. There are several different types of key constraints in DBMS, each with its
own specific purpose.
1. Unique Key
A unique key refers to a column or a set of columns that identify every record uniquely
in a table. All the values in this key would have to be unique. values of a unique key
won’t allow duplicate values and it is only capable of having one null value.
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
UNIQUE (column)
);

Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
CONSTRAINT constraint_name UNIQUE(column)
);

2. Primary Key
The primary key refers to a column of a table that helps us identify all the records
uniquely present in that table. Any table can consist of only a single primary key
constraint. values of a primary key won’t allow null value or a duplicate values.
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
CONSTRAINT constraint_name PRIMARY KEY(column)
);

3. Foreign Key
We use a foreign key to establish relationships between two available tables. The
foreign key would require every value present in a column/set of columns to match the
referential table’s primary key. A foreign key helps us to maintain data as well as
referential integrity.
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
CONSTRAINT constraint_name FOREIGN KEY (column)
REFERENCES table_name1(column)
);

4. Composite Key
The composite key refers to a set of multiple attributes that help us uniquely identify
every tuple present in a table. The attributes present in a set may not be unique
whenever we consider them separately. Thus, when we take them all together, it will
ensure total uniqueness.
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
CONSTRAINT constraint_name UNIQUE(column,column)
);

5. Super Key
A super key refers to the set of all those keys that help us uniquely identify all the rows
present in a table. It means that all of these columns present in a table that can identify
the columns of that table uniquely act as the super keys.

6. Candidate Key
The candidate keys refer to those attributes that identify rows uniquely in a table. In a
table, we select the primary key from a candidate key. Thus, a candidate key has similar
properties as that of the primary keys that we have explained above. In a table, there
can be multiple candidate keys.

7. Alternate Key
As we have stated above, any table can consist of multiple choices for the primary key.
But, it can only choose one. Thus, all those keys that did not become a primary key are
known as alternate keys.

CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column. If
you define a CHECK constraint on a column it will allow only certain values for this
column. If you define a CHECK constraint on a table it can limit the values in certain
columns based on values in other columns in the row.
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
CONSTRAINT constraint_name CHECK (condition)
);

Example:

CREATE TABLE students (

ID int,
LastName varchar(30),
FirstName varchar(30),
Age int,
CONSTRAINT con_age CHECK (Age>=18)
);

DEFAULT Constraint
The DEFAULT constraint is used to set a default value for a column. The default value
will be added to all new records, if no other value is specified.
The DEFAULT constraint can also be used to insert system date, by using functions like
GETDATE().
Syntax in Mysql

CREATE TABLE table_name (

column1 datatype,
column2 datatype DEFAULT 'value',
column3 datatype DEFAULT GETDATE(),
....
);

Example:

CREATE TABLE students (

ID int,
LastName varchar(30),
FirstName varchar(30),
Age int DEFAULT 18,
joining_date DEFAULT GETDATE()
 Enforcing Integrity Constraints in DBMS

Integrity Constraints are specified when a relation is created and enforced when a
relation is modified. The impact of domain, PRIMARY KEY, and UNIQUE constraints is
straightforward: If an insert, delete, or update command causes a violation, it is rejected.
Every potential Integrity violation is generally checked at the end of each SQL statement
execution, although it can be deferred until the end of the transaction executing the
statement.
On the other hand, insertions of Students tuples do not violate referential integrity, and
deletions of Students tuples could cause violations.
SQL provides several alternative ways to handle foreign key violations. We must
consider three basic questions:
1. What should we do if an Enrolled row is inserted, with a student_id column
value that does not appear in any row of the Students table?
In this case, the INSERT command is simply rejected.

2. What should we do if a Students row is deleted?

The options are:
☑ Delete all Enrolled rows that refer to the deleted Students row.
Syntax in Mysql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
FOREIGN KEY (column) REFERENCES table_name1(column) ON DELETE
CASCADE
);

☑ Disallow the deletion of the Students row if an Enrolled row refers to it.
Syntax in Mysql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
FOREIGN KEY (column) REFERENCES table_name1(column) ON DELETE NO
ACTION
);

☑ Set the studid column to the sid of some (existing) 'default' student, for every
Enrolled row that refers to the deleted Students row.
Syntax in Mysql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
FOREIGN KEY (column) REFERENCES table_name1(column) ON DELETE SET
DEFAULT
);

3. What should we do if the primary key value of a Students row is updated?

The options here are similar to the previous case. Replace ON DELETE with ON
UPDATE
Syntax in Mysql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
FOREIGN KEY (column) REFERENCES table_name1(column) ON UPDATE NO
ACTION
);
Example:
CREATE TABLE Enrolled (
Sid int,
Cid varchar(30),
joining_date DEFAULT GETDATE()
FOREIGN KEY (Sid) REFERENCES Students
ON DELETE CASCADE
ON UPDATE NO ACTION
);

Querying Relational Data in DBMS

A relational database query is a question about the data, and the answer consists of a
new relation containing the result. For example, we might want to find all students AGE
less than 18 or all students enrolled in perticular course.
The SELECT statement is used to fetch the data from a database table which returns
this data in the form of a result table. These result tables are called result-sets.
Syntax in Mysql

SELECT column1, column2, ...

FROM table_name;

If you want to select all the fields available in the table, use the following syntax:
Syntax in Mysql

SELECT * FROM table_name;

The symbol ´*´ means that we retain all fields of selected tuples in the result.

We can retrieve rows corresponding to students who are younger than 18 withthe
following SQL query:
Example:

SELECT * FROM Students WHERE age < 18;

The condition age < 18 in the WHERE clause specifies that we want to select only
tuples in which the age field has a value less than 18.

In addition to selecting a subset of tuples, a query can extract a subset of the fields of
each selected tuple. we can compute the student_id and First_name of students who
are younger than 18 with the following query:
Example:

SELECT ID,FirstName FROM Students WHERE age < 18;

SQL Aliases
Aliases are the temporary names given to tables or columns. An alias is created with
the AS keyword.
Alias Column Syntax in Mysql

SELECT column_name AS alias_name

FROM table_name;

Alias Table Syntax in Mysql

SELECT column_name(s)
FROM table_name AS alias_name;

Example:

SELECT studentID AS ID,

FROM students AS S;

Aliases can be useful when:

 There are more than one table involved in a query

 Functions are used in the query
 Column names are big or not very readable
  Two or more columns are combined togeth

SELECT data from Multiple Tables

We can also combine information from multiple tables.
Syntax in Mysql

SELECT table1.column1, table2.column2

FROM table1, table2
WHERE table1.column1 = table2.column1;

Example:

SELECT [Link], [Link]

FROM Students AS S, Enrolled AS E
WHERE [Link] = [Link];

Views in DBMS
A view is a table whose rows are not explicitly stored, a view is a virtual table based on
the result-set of an SQL statement. A view can contain all rows of a table or select rows
from a table. A view can be created from one or many tables which depends on the
written SQL query to create a view.
A view is generated to show the information that the end-user requests the data
according to specified needs rather than complete information of the table.

Advantages of View over database tables

 Using Views, we can join multiple tables into a single virtual table.
 Views hide data complexity.
 In the database, views take less space than tables for storing data because the
database contains only the view definition.
 Views indicate the subset of that data, which is contained in the tables of the database.
Creating Views
Database views are created using the CREATE VIEW statement. Views can be created
from a single table, multiple tables or another view.
To create a view, a user must have the appropriate system privilege according to the
specific implementation.
Syntax in Mysql

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW Students_CSE AS

SELECT Roll_no,Name
FROM Students
WHERE Branch = 'CSE';

Updating a View
A view can be updated with the CREATE OR REPLACE VIEW statement.
Syntax in Mysql

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following SQL adds the "Mobile" column to the "Students_CSE" view:
Example:

CREATE OR REPLACE VIEW Students_CSE AS

SELECT Roll_no,Name,Mobile
FROM Students
WHERE Branch = 'CSE';
CREATE VIEW defines a view on a set of tables or views or both.
REPLACE VIEW redefines an existing view or, if the specified view does not exist,

Inserting a row in a view

We can insert a row in a View in a same way as we do in a table. We can use the
INSERT INTO statement of SQL to insert a row in a View.
Syntax in Mysql

INSERT INTO view_name(column1, column2, ...)

VALUES(value1,value2,.....);

Example:

INSERT INTO Students_CSE(Roll_no,Name,Mobile)

VALUES(521,'ram',9988776655);

Deleting a row in a view

Deleting rows from a view is also as simple as deleting rows from a table. We can use
the DELETE statement of SQL to delete rows from a view.
Syntax in Mysql

DELETE FROM view_name

WHERE condition;

Example:

DELETE FROM Students_CSE

WHERE Name="ram";

Querying a View
We can query the view as follows
Syntax in Mysql

SELECT * FROM view_name

Example:

SELECT * FROM Students_CSE;

Dropping a View
In order to delete a view in a database, we can use the DROP VIEW statement.
Syntax in Mysql

DROP FROM view_name

Example:

DROP FROM Students_CSE;