L02 - Data models

Examples of Relational Database,

Database Schemas, View of Data
Data Models
• A collection of tools for describing
• Data
• Data relationships
• Data semantics - data that has been structured to add meaning to the data
• Data constraints

• Relational model

• Entity-Relationship data model (mainly for database design)

14-Aug-22 Data models 2

 Semantics Data Model define relationships between objects.
14-Aug-22 Data models 3
Relational Model
All the data is stored in various tables.
Example of tabular data in the relational model
Columns

Rows

Ted Codd
Turing Award 1981

14-Aug-22 Data models 4

Relational Model
• This type of model designs the data in the form of rows and columns
within a table.
• Tables are also called relations.

• Thus, a relational model uses tables for representing data and in-
between relationships.

• This model was initially described by Edgar F. Codd, in 1969.

• The relational data model is the widely used model which is primarily
used by commercial data processing applications.
14-Aug-22 Data models 5
Relational Model
The main highlights of this model are −
• Relations can be normalized.
• In normalized relations, values saved are atomic
values.
• Each row in a relation contains a unique value.
• Each column in a relation contains values from a same
domain.

Features of Relational Model

Tuples: Each row in the table is called tuple. A row contains all the information about any instance of the object.
In the above example, each row has all the information about any specific individual like the first row has
information about John.
Attribute or field: Attributes are the property which defines the table or relation. The values of the attribute
should be from the same domain. In the above example, we have different attributes of the employee like Salary,
Mobile_no, etc.
14-Aug-22 Data models 6
Advantages and Disadvantages of Relational Model
Advantages Disadvantages
• Simple: • Hardware Overheads:
• This model is simpler as compared to • For hiding the complexities and making
the network and hierarchical model. things easier for the user this model
requires more powerful hardware
• Scalable: computers and data storage devices.
• This model can be easily scaled as we
can add as many rows and columns we
want. • Bad Design:
• Structural Independence: • As the relational model is very easy to
design and use. So the users don't need
• We can make changes in database to know how the data is stored in order
structure without changing the way to to access it. This ease of design can
access the data. When we can make lead to the development of a poor
changes to the database structure database which would slow down if the
without affecting the capability to DBMS database grows.
to access the data we can say that
structural independence has been
achieved.

14-Aug-22 Data models 7

A Sample Relational Database

14-Aug-22 Data models 8

Entity-Relationship Data 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.
• This model was designed by Peter Chen and published in 1976 papers. It was
widely used in database designing.

• A set of attributes describe the entities.

• For example, student_name, student_id describes the 'student' entity.
• A set of the same type of entities is known as an 'Entity set', and the
set of the same type of relationships is known as 'relationship set'.

14-Aug-22 Data models 9

Entity-Relationship Model
ER Model is best used for the conceptual design of a database.

ER Model is based on −
Entities and their attributes.
Relationships among entities.

14-Aug-22 Data models 10

 Entities: Entity is a real-world thing. 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, salary, age, etc.

Relationship: Relationship tells how two attributes are related.

Example: Teacher works for a department.
14-Aug-22 Data models 11
Features of ER Model

• Graphical Representation for Better Understanding: It is very easy

and simple to understand so it can be used by the developers to
communicate with the stakeholders.

• ER Diagram: ER diagram is used as a visual tool for representing the

model.

• Database Design: This model helps the database designers to build the
database and is widely used in database design.

14-Aug-22 Data models 12

Advantages and Disadvantages of ER Model
Advantages Disadvantages
• Simple: • No industry standard for notation:
• Conceptually ER Model is very easy to • There is no industry standard for
build. If we know the relationship developing an ER model. So one
between the attributes and the entities developer might use notations which are
we can easily build the ER Diagram for not understood by other developers.
the model.
• Effective Communication Tool: • Hidden information:
• This model is used widely by the • Some information might be lost or
database designers for communicating hidden in the ER model. As it is a high-
their ideas. level view so there are chances that
• Easy Conversion to any Model: some details of information might be
hidden.
• This model maps well to the relational
model and can be easily converted
relational model by converting the ER
model to the table. This model can also
be converted to any other model like
network model, hierarchical model etc.
14-Aug-22 Data models 13
DBMS 3-tier Architecture
• DBMS 3-tier architecture divides the complete system into three inter-
related but independent modules as shown below:
VIEW LEVEL

LOGICAL LEVEL

14-Aug-22 Data models 14

DBMS 3-tier Architecture
• Physical Level:
• At the physical level, the information about the location of database objects in the data store is
kept. Various users of DBMS are unaware of the locations of these objects.
• In simple terms, physical level of a database describes how the data is being stored in secondary
storage devices like disks and tapes and also gives insights on additional storage details.

• Conceptual Level: (LOGICAL)

• At conceptual level, data is represented in the form of various database tables.
• For Example, STUDENT database may contain STUDENT and COURSE tables which will be
visible to users but users are unaware of their storage. Also referred as logical schema, it
describes what kind of data is to be stored in the database.

• External Level: (VIEW)

• An external level specifies a view of the data in terms of conceptual level tables.
• Each external level view is used to cater to the needs of a particular category of users.
• For Example, FACULTY of a university is interested in looking course details of students,
STUDENTS are interested in looking at all details related to academics, accounts, courses and
hostel details as well. So, different views can be generated for different users. The main focus of
external level is data abstraction.

14-Aug-22 Data models 15

Data Independence
• Data independence means a change of data at one level should not affect
another level.
• Two types of data independence are present in this architecture:

• Physical Data Independence:

• Any change in the physical location of tables and indexes should not affect the
conceptual level or external view of data. This data independence is easy to achieve and
implemented by most of the DBMS.

• Conceptual (Logical) Data Independence:

• The data at conceptual level schema and external level schema must be independent.
This means a change in conceptual schema should not affect external schema. e.g.;
• Adding or deleting attributes of a table should not affect the user’s view of the table. But
this type of independence is difficult to achieve as compared to physical data
independence because the changes in conceptual schema are reflected in the user’s
view.

14-Aug-22 Data models 16

View of Data
• An architecture for a database system

14-Aug-22 Data models 17

Levels of Abstraction
• Physical level: describes how a record (e.g., instructor) is stored.
• Logical level: describes data stored in database, and the relationships
among the data.
type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;
• View level: application programs hide details of data types. Views can
also hide information (such as an employee’s salary) for security
purposes.
14-Aug-22 Data models 18
Instances and Schemas
• Like types and variables in programming languages
• Logical Schema – the overall logical structure of the database
• Example: The database consists of information about a set of
customers and accounts in a bank and the relationship
between them
• Analogous to type information of a variable in a program
• Physical schema – the overall physical structure of the
database
• Instance – the actual content of the database at a particular point
in time
• Analogous to the value of a variable
14-Aug-22 Data models 19