Data models:

1. Hierarchical Model:
The hierarchical Model is one of the oldest models in the data model
which was developed by IBM, in the 1950s. In a hierarchical model,
data are viewed as a collection of tables, or we can say segments that
form a hierarchical relation. In this, the data is organized into a tree-
like structure where each record consists of one parent record and
many children. Even if the segments are connected as a chain-like
structure by logical associations, then the instant structure can be a fan
structure with multiple branches. We call the illogical associations as
directional associations.
Example 1: Consider the below Student database system hierarchical
model.

Hierarchical model
In the above-given figure, we have few students and few course-enroll
and a course can be assigned to a single student only, but a student can
enroll in any number of courses and with this the relationship becomes
one-to-many. We can represent the given hierarchical model like the
below relational tables:
FACULTY Table

Name Dep Course-taught

John CS CA
 E

CS
Jake SE
E

CS
Royal DBMS
E

STUDENT Table

Course-
Name Grade
enroll

Gami CA 2.0

Mary SE 3.0

Mayen SE 4.0

2. Network Model:
The Network Model was formalized by the Database Task group in
the 1960s. This model is the generalization of the hierarchical model.
This model can consist of multiple parent segments and these
segments are grouped as levels but there exists a logical association
between the segments belonging to any level. Mostly, there exists a
many-to-many logical association between any of the two segments.
Example : Network model for a Finance Department.
Below we have designed the network model for a Finance
Department:
Network model of Finance Department.

3. Object-Oriented Data Model:

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

4. Float Data Model:

The float data model basically consists of a two-dimensional array of
data models that do not contain any duplicate elements in the array.
This data model has one drawback it cannot store a large amount of
data that is the tables can not be of large size.

5. Context Data Model:

The Context data model is simply a data model which consists of
more than one data model. For example, the Context data model
consists of ER Model, Object-Oriented Data Model, etc. This model
allows users to do more than one thing which each individual data
model can do.

6. Semi-Structured Data Model:

Semi-Structured data models deal with the data in a flexible way.
Some entities may have extra attributes and some entities may have
some missing attributes. Basically, you can represent data here in a
flexible way.

Advantages of Data Models:

Data Models help us in representing data accurately.
It helps us in finding the missing data and also in minimizing Data
Redundancy.
Data Model provides data security in a better way.
The data model should be detailed enough to be used for building the
physical database.
The information in the data model can be used for defining the
relationship between tables, primary and foreign keys, and stored
procedures.
Disadvantages of Data Models:
In the case of a vast database, sometimes it becomes difficult to
understand the data model.
You must have the proper knowledge of SQL to use physical models.
Even smaller change made in structure require modification in the
entire application.
There is no set data manipulation language in DBMS.
To develop Data model one should know physical data stored
characteristics.
Schema :
Here the DBMS schema means designing the database. For example,
if we take the example of the employee table. The employee table
contains the following attributes. These attributes are EMP_ID,
EMP_ADDRESS, EMP_NAME, EMP_CONTACT. These are the
schema of the employee table.
Schema is further divided into three types. These three are as follows.
1. Logical schema.
2. View schema.
3. Physical schema.
The schema defines the logical view of the database. It provides some
knowledge about the database and what data needs to go where.

1. Physical schema:
In the physical schema, the database is designed at the physical level.
At this level, the schema describes how the data block is stored and
how the storage is managed.
2. Logical schema:
In the logical schema, the database is designed at a logical level. At
this level, the programmer and data administrator perform their work.
Also, at this level, a certain amount of data is stored in a structured
way. But the internal implementation data are hidden in the physical
layer for the security proposed.
3. View schema:
In view schema, the database is designed at the view level. This
schema describes the user interaction with the database system.
Moreover, Data Definition Language (DDL) statements help to denote
the schema of a database. The schema represents the name of the
table, the name of attributes, and their types; constraints of the tables
are related to the schema. Therefore, if users want to modify the
schema, they can write DDL statements.
Instance:
In DBMS, the data is stored for a particular amount of time and is
called an instance of the database. The database schema defines the
attributes of the database in the particular DBMS. The value of the
particular attribute at a particular moment in time is known as an
instance of the DBMS.
For example, in the above example, we have taken the example of the
attribute of the schema. In this example, each table contains two rows
or two records. In the above schema of the table, the employee table
has some instances because all the data stored by the table have some
instances.

Database Schema Database Instance

It is the definition of the

It is a snapshot of a database at
database, or it is defined as the
a specific moment.
description of the database.

It rarely changes. It changes frequently.

This corresponds to the The value of the variable in a

variable declaration of a program at a point in time
 corresponds to an instance of
programming language.
the database schema.

Defines the basic structure of

It is the set of Information
the database, i.e., how the data
stored at a particular time.
will be stored in the database.

Data in instances can be

Schema is same for whole
changed using addition,
database.
deletion, updation.

It does not change very

It changes very frequently
frequently.

Data Independence:
o Data independence can be explained using the three-schema
architecture.
o Data independence refers characteristic of being able to modify
the schema at one level of the database system without altering
the schema at the next higher level.
There are two types of data independence:
1. Logical Data Independence
o Logical data independence refers characteristic of being able to
change the conceptual schema without having to change the
external schema.
o Logical data independence is used to separate the external level
from the conceptual view.
o If we do any changes in the conceptual view of the data, then the
user view of the data would not be affected.
o Logical data independence occurs at the user interface level.
2. Physical Data Independence:
 o Physical data independence can be defined as the capacity to
change the internal schema without having to change the
conceptual schema.
o If we do any changes in the storage size of the database system
server, then the Conceptual structure of the database will not be
affected.
o Physical data independence is used to separate conceptual levels
from the internal levels.
o Physical data independence occurs at the logical interface level.

Fig: Data Independence

DBMS Architecture
o The DBMS design depends upon its architecture. The basic
client/server architecture is used to deal with a large number of
PCs, web servers, database servers and other components that
are connected with networks.
o The client/server architecture consists of many PCs and a
workstation which are connected via the network.
o DBMS architecture depends upon how users are connected to
the database to get their request done.
Types of DBMS Architecture
Database architecture can be seen as a single tier or multi-tier. But
logically, database architecture is of two types like: 2-tier
architecture and 3-tier architecture.
1-Tier Architecture
o In this architecture, the database is directly available to the user.
It means the user can directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database
itself. It doesn't provide a handy tool for end users.
o The 1-Tier architecture is used for development of the local
application, where programmers can directly communicate with
the database for the quick response.
2-Tier Architecture
o The 2-Tier architecture is same as basic client-server. In the two-
tier architecture, applications on the client end can directly
communicate with the database at the server side. For this
interaction, API's like: ODBC, JDBC are used.
o The user interfaces and application programs are run on the
client-side.
o The server side is responsible to provide the functionalities like:
query processing and transaction management.
o To communicate with the DBMS, client-side application
establishes a connection with the server side.
Fig: 2-tier Architecture
3-Tier Architecture
o The 3-Tier architecture contains another layer between the client
and server. In this architecture, client can't directly communicate
with the server.
o The application on the client-end interacts with an application
server which further communicates with the database system.
o End user has no idea about the existence of the database beyond
the application server. The database also has no idea about any
other user beyond the application.
o The 3-Tier architecture is used in case of large web application.
Fig: 3-tier Architecture
Centralized and Client-Server Architecture for DBMS:
 Centralized DBMSs Architecture

All DBMS functionality, application program execution, and user interface

processing carried out on one machine.

Figure: A physical centralized architecture

 Disadvantages:
 When the central site computer or database system goes down, then
everyone is blocked from using the system.
 Communication costs from the terminals to the central site can expensive.

 Basic Client/Server Architectures

The client/server architecture was developed to deal with computing environments

in which a large number of PCs, workstations, file servers, printers, database
servers, Web servers, e-mail servers, and other software and equipment are
connected via a network.
 Define specialized servers with specific functionalities.
 For example file server that maintains the files of the client machines.
 The resources provided by specialized servers can be accessed by many
client machines.
 The client machines provide the user with the appropriate interfaces to
utilize these servers and local processing power to run local applications
 Figure: Logical two-tier client/server architecture

Figure: Physical two-tier client/server architecture

 The concept of client/server architecture assumes an underlying framework that

consists of many PCs and workstations as well as a smaller number of mainframe
machines, connected via LANs and other types of computer networks.
 A client is a user machine that provides user interface capabilities and local
processing.
 When a client requires access to additional functionality such as database access
that does not exist at that machine, it connects to a server that provides the needed
functionality.
 A server is a system containing both hardware and software that can provide
services to the client machines, such as file access, printing, archiving, or database
access.
The software components are distributed over two systems: client and server

 Server handles

- Query and transaction functionality related to SQL

 Processing Client handles

- User interface programs and application programs

 The user interface programs and application programs can run on the client side.
When DBMS access is required, the program establishes a connection to the DBMS
 (which is on the server side) once the connection is created, the client program can
communicate with the DBMS.
 A client program can actually connect to several RDBMSs and send query and
transaction requests using the ODBC API, which are then processed at the server
sites. Any query results are sent back to the client program, which can process and
display the results as needed. A related standard for the Java programming
language, called JDBC, has also been defined to allow Java client programs to access
one or more DBMSs through a standard interface.

UNIT-II: E-R Modelling

 ER model stands for an Entity-Relationship model. It is a high-level data model.

 The Entity Relationship model was proposed by Peter Chen in 1976.
 ER model is a logical representation of an enterprise data. ER model is a
diagrammatic representation of logical structure of database.
 E-R model describes relationship among entities and attributes.
 Entity Relationship Diagrams are the best tools to communicate within the entire
system.
 These diagrams are the graphical representation of the flow of data and information.
 These diagrams are most commonly used in business organizations to make data
travel easy.
 This conceptual database model is an effective way of communicating with the
individuals at all the levels.
 The most common use of this diagram is to present the relation of the various tables
present in a database.

Following are the main components and its symbols in ER Diagrams:

 Rectangles: This Entity Relationship Diagram symbol represents entity types

 Ellipses : Symbol represent attributes
 Diamonds: This symbol represents relationship types
 Lines: It links attributes to entity types and entity types with other relationship types
 Primary key: attributes are underlined
 Double Ellipses: Represent multi-valued attributes
 1. Entity

It may be an object, person, place or event that stores data in a database. In E-R diagram an entity is
represented in rectangle form. For example, students, employees, managers, etc.

The entity is pictorially depicted as follows:

Entity set

It is a collection of entities of the same type which share similar properties. For example, a group of
students in a college and students are an entity set.
Entity is characterised into two types as follows:

a. Strong entity set

b. Weak entity set

a. Strong entity set: The entity types which consist of key attributes or if there are enough
attributes for forming a primary key attribute are called a strong entity set. It is represented
by a single rectangle.

b. Weak entity set: An entity does not have a primary key attribute and depends on
another strong entity via foreign key attribute. It is represented by a double
rectangle.

2. Attributes

These are the data characteristics of entities or data elements and data fields.

Types of attributes

The types of attributes in the Entity Relationship (ER) model are as follows:

1. Single value attribute − these attributes contain a single value. For example, age,
salary etc.
2. Key Attributes- The key attribute is used to represent the main characteristics of an
entity. It represents a primary key. The key attribute is represented by an ellipse with
the text underlined.
 3. Multivalued attribute − they contain more than one value of a single entity. For
example, phone numbers, Email_Ids, etc.

4. Composite attribute − the attributes which can be further divided. For example,
Name
consists of First name, Middle name, last name

5. Derived attribute − the attribute that can be derived from other attributes. For
example, age can be derived based on DoB.

3. Relationships

A relationship is used to describe the relation between entities. Diamond or rhombus is used to
represent the relationship.
Degree of Relationship: A relationship where a number of different entities set participate is called a
degree of a relationship.

It is categorised into the following:

1. Unary Relationship: A unary relationship exists when both the participating entity
type are the same. When such a relationship is present we say that the degree of
relationship is 1.

For example, suppose in a classroom, we have many students who belong to a particular club-like
dance club, basketball club etc. and some of them are club leads. So, a particular group of
student is managed by their respective club lead. Here, the group is formed from students and
also, the club leads are chosen from students. So, the ‘Student’ is the only entity participating
here. We can represent this relationship using the E-R diagram as follows:

2. Binary Relationship: A binary relationship exists when exactly two entity type
participates. When such a relationship is present we say that the degree is 2. This is
the most common degree of relationship. It is easy to deal with such relationship as
these can be easily converted into relational tables.

For example, we have two entity type ‘Customer’ and ‘Account’ where each ‘Customer’ has an
‘Account’ which stores the account details of the ‘Customer’. Since we have two entity types
participating we call it a binary relationship. Also, one ‘Customer’ can have many ‘Account’
but each ‘Account’ should belong to only one ‘Customer’. We can say that it is a one-to-many
binary relationship.

3. Ternary Relationship: A ternary relationship exists when exactly three entity type
participates. When such a relationship is present we say that the degree is 3. As the
number of entity increases in the relationship, it becomes complex to convert them
into relational tables.

For example, we have three entity type ‘Employee’, ‘Department’ and ‘Location’. The
relationship between these entities are defined as an employee works in a department,
 an employee works at a particular location. So, we can see we have three entities participating in a
relationship so it is a ternary relationship. The degree of this relation is 3.

4. n-ary Relationship: An N-ary relationship exists when ‘n’ number of entities are
participating. So, any number of entities can participate in a relationship. There is no
limitation to the maximum number of entities that can participate.

Mapping Constraints
 A mapping constraint is a data constraint that expresses the number of entities to
which another entity can be related via a relationship set.
 It is most useful in describing the relationship sets that involve more than two entity
sets.

For binary relationship set R on an entity set A and B, there are four possible mapping cardinalities.

a. One-to-One (1:1)
b. One-to-Many (1:M)
c. Many-to-One (M:1)
d. Many-to-Many (M:M)
a. One-to-One Relationship: When only one instance of an entity is associated with the
relationship, then it is known as one to one relationship.

For example: A female can marry to one male, and a male can marry to one female.
 b. One-to-many relationship: When only one instance of the entity on the left, and more
than one instance of an entity on the right associates with the relationship then this is
known as a one-to-many relationship.

For example, Scientist can invent many inventions, but the invention is done by the only specific
scientist.

c. Many-to-one relationship: When more than one instance of the entity on the left, and
only one instance of an entity on the right associates with the relationship then it is known
as a many-to-one relationship.

For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship: When more than one instance of the entity on the left, and
more than one instance of an entity on the right associates with the relationship then it is
known as a many-to-many relationship.
 For example, Employee can assign by many projects and project can have many employees.

Comparison between Strong and Weak Entity Set

Strong Entity Set Weak Entity Set

1. Strong entity set always has a primary key.
1. It does not have enough attributes to build a
primary key.

2. It is represented by a rectangle symbol. 2. It is represented by a double rectangle symbol.

3. It contains a Primary key represented by the

3. It contains a Partial Key which is represented
underline symbol.
by a dashed underline symbol.
4. The member of a strong entity set is called
4. The member of a weak entity set called as a
as dominant entity set. subordinate entity set.
5. In a weak entity set, it is a combination of
5. Primary Key is one of its attributes which
primary key and partial key of the strong
helps to identify its member.
entity set.
6. In the ER diagram the relationship between 6. The relationship between one strong and a
two strong entity set shown by using a weak entity set shown by using the double
diamond symbol. diamond symbol.
7. The connecting line of the strong entity set 7. The line connecting the weak entity set for
with the relationship is single. identifying relationship is double.

Converting E-R model into relational model

A given ER model can be converted into Relational model. A Relational model includes Relations,
Tuples, Attributes, Keys, and Foreign keys.

 Relation is a table made from tuples.

 A Tuple is a row of data.
 An Attribute is a characteristic of the relation.

There is a direct mapping between ER model and Relational model.

Rules of converting ER model to Relational Model:

 Entity type is converted to a Relation table.

 1:1 or 1: N relationship type is converted to foreign key.
 M: N relationship type is converted to a relation with two foreign key.
 Simple attribute converted to an attribute.
 Value set converted to a domain.
 Key attribute converted to a primary key.

Overall transformation summary is as follows:

Consider the following example:

Now for the above example we can create three relations:

 Employee
 Works_On
 Projects

Transform attributes to fields:

 Employee will have E_ID, Name, Designation and Dob.

 Works_On will have E_ID, Status and P_ID.
 Projects will have P_ID, S_Date and E_Date.

Now we can create tables in DBMS.

Advantages of E-R Model

1. Conceptually E-R model is very simple: ER model is very simple because if we know
relationship between entities and attributes, then we can easily draw an ER diagram.
2. Better Visual representation: ER model is a diagrammatic representation of any logical
structure of database. By seeing ER diagram, we can easily understand relationship among
entities and relationship.
3. Effective communication tool: It is an effective communication tool for database
designer.
The clear representation of the data listed under proper headings and tables results in the
effective flow of information and communication.
4. Highly integrated with relational model: ER model can be easily converted into
relational model by simply converting ER model into tables.
5. Easy conversion to any data: ER model can be easily converted into another data model
like hierarchical data model, network data model and so on.
6. Straightforward relation representation: Having designed an E-R diagram for a database
application, the relational representation of the database model becomes relatively
straightforward.

Disadvantages of E-R Model

1. Limited constraints and specification: The constraints and specifications are limited.
2. Loss of information content: Some information be lost or hidden in ER model.
3. Limited relationship representation: ER model represents limited relationship as
compared to another data models like relational model etc.
4. No representation of data manipulation: It is difficult to show data manipulation in
ER model.
5. No industry standard for notation.
 Enhanced Entity-Relationship Model (EER model)

EER is a high-level data model that incorporates the extensions to the original ER model. Enhanced ER
Diagrams are high level models that represent the requirements and complexities of complex
database.

In addition to ER model concepts EE-R includes −

 Subclasses and Super classes.

 Specialization and Generalization.
 Category or union type.
 Aggregation.

Subclasses and Super class

o Super class is an entity that can be divided into further subtype.

 For example − consider Shape super class.

 Super class shape has sub groups: Triangle, Square and Circle.
 Sub classes are the group of entities with some unique attributes. Subclass inherits
the properties and attributes from super class.

Specialization and
Generalization

 Generalization is like a bottom-up approach in which two or more entities of lower

level combine to form a higher level entity if they have some attributes in common.
 In generalization, entities are combined to form a more generalized entity, i.e.,
subclasses are combined to make a superclass.

For example, Faculty and Student entities can be generalized and create a higher level entity Person.
  Specialization is a top-down approach, and it is opposite to Generalization. In
specialization, one higher level entity can be broken down into two lower level
entities.
 Specialization is used to identify the subset of an entity set that shares
some distinguishing characteristics.
 Normally, the superclass is defined first, the subclass and its related attributes
are defined next, and relationship set are then added.

For example: In an Employee management system, EMPLOYEE entity can be specialized as TESTER or
DEVELOPER based on what role they play in the company.

For example, in the bellow diagram we have 3 sub entities Car, Truck and Motorcycle. The three entities
can be generalized into one super class named as Vehicle (Generalization).

Specialization is a process of identifying subsets of an entity that share some different characteristic. It is
a top down approach in which one entity is broken down into low level entity i.e., Vehicle entity can
be a Car, Truck or Motorcycle (Specializations).
 Category or Union

 Relationship of one super or sub class with more than one super class.

 Owner is the subset of two super class: Vehicle and House.

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: Centre 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 Centre then he
will never enquiry about the Course only or just about the Centre instead he will ask the enquiry about
both.

IsA Relationship and Attribute Inheritance

This relationship is called IsA. Some texts will call this an IsA relationship, but do not confuse
with the concept of relationship between entities.

 Freshman IsA Student, an eagle IsA bird

 The two entities represented by IsA are always descriptions of the same real-
world object
 Typically used in databases to be implemented as Object Oriented Models.
 data base management systems

 The upper entity type (connected to the apex of the IsA triangle) is the more
abstract/general entity type (super type) from which the lower entities inherit its
attributes.

Properties of IsA

1. Inheritance - All attributes of the supertype apply to the subtype.

 E.g., An attribute of Student applies to Freshman

 The subtype inherits all attributes of its supertype.
 The key of the supertype is also the key of the subtype

2. Transitivity - This property creates a hierarchy of IsA relationships

 Student is subtype of Person,

Freshman is subtype of Student,
therefore Freshman is also a subtype of Person

pg. 26