Database

Lesson 2: Data Models

Complete Stack Development

 Lesson Outline
• General Architecture
• Data Models
• Entity Relationship Model
• Relational Model
• Converting an ER to a Relational Model
• Assignment
• Mini Project 2: Do database modeling for a Taxi
Booking Application
 General Architecture
• When we build applications where does the database sit? I did not intend
to include this sub topic under databases as it is unusual to find it inside
database lessons even in major text books. But after some years of
teaching databases to upcoming developers and engineers, this question
is often asked. So, I have decided to take some time out to explain this
very well especially if you are already beginning to write some code on
web applications or anything related.
• The design of a DBMS depends on its architecture. It can be centralized
or decentralized or hierarchical. The architecture of a DBMS can be seen
as either single tier or multi-tier. An n-tier architecture divides the whole
system into related but independent n modules, which can be
independently modified, altered, changed, or replaced.
 General Architecture
• When we build applications where does the database sit? In 1-tier
architecture, the DBMS is the only entity where the user directly sits on
the DBMS and uses it. Any changes done here will directly be done on
the DBMS itself. It does not provide handy tools for end-users. Database
designers and programmers normally prefer to use single-tier
architecture.
• If the architecture of DBMS is 2-tier, then it must have an
application through which the DBMS can be accessed.
Programmers use 2-tier architecture where they access the
DBMS by means of an application. Here the application tier is
entirely independent of the database in terms of operation, design,
and programming.
 General Architecture
• When we build applications
where does the database sit?
3-tier Architecture
• A 3-tier architecture separates
its tiers from each other based
on the complexity of the users
and how they use the data
present in the database. It is the
most widely used architecture
to design a DBMS.
 General Architecture
• When we build applications where does the database sit?
Database (Data) Tier
• At this tier, the database resides along with its query processing
languages. We also have the relations that define the data and their
constraints at this level.
Application (Middle) Tier
• At this tier reside the application server and the programs that access the
database. For a user, this application tier presents an abstracted view of
the database. End-users are unaware of any existence of the database
beyond the application. At the other end, the database tier is not aware of
any other user beyond the application tier. Hence, the application layer
sits in the middle and acts as a mediator between the end-user and the
database.
 General Architecture
• When we build applications where does the database sit?
User (Presentation) Tier
• End-users operate on this tier and they know nothing about
any existence of the database beyond this layer. At this
layer, multiple views of the database can be provided by the
application. All views are generated by applications that
reside in the application tier.
• Multiple-tier database architecture is highly modifiable, as
almost all its components are independent and can be
changed independently.
 Database Models
What are data models?
● Data models define how the logical structure of a database is
modeled.
● Data Models are fundamental entities to introduce abstraction in a
DBMS.
● Data models define how data is connected to each other and how
they are processed and stored inside the system.
● The very first data model could be flat data-models, where all the
data used are to be kept in the same plane. Earlier data models
were not so scientific, hence they were prone to introduce lots of
duplication and update anomalies.
 Entity Relationship (ER) Model
Entity-Relationship (ER) Model is based
on the notion of real-world entities and
relationships among them. While
formulating real-world scenario into the
database model, the ER Model creates
entity set, relationship set, general
attributes and constraints.
ER Model is best used for the
conceptual design of a database.
ER Model is based on
• Entities and their attributes.
• Relationships among entities.
These concepts are explained below.
 Entity Relationship (ER) Model
Entity
An entity in an ER Model is a real-world entity having properties called attributes.
Every attribute is defined by its set of values called domain. For example, in a school
database, a student is considered as an entity. Student has various attributes like
name, age, class, etc.
Relationship
The logical association among entities is called relationship. Relationships are
mapped with entities in various ways. Mapping cardinalities define the number of
association between two entities. Mapping cardinalities:-
- one to one
- one to many
- many to one
- many to many
 Relational Model
The most popular data
model in DBMS is the
Relational Model. It is more
scientific a model than
others. This model is based
on first-order predicate
logic and defines a table as
an n-ary relation.
 Relational Model
The main highlights of this model are:-
• Data is stored in tables called relations.
• 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.
 Database Schema
• A database schema is the skeleton structure
that represents the logical view of the entire
database. It defines how the data is
organized and how the relations among
them are associated. It formulates all the
constraints that are to be applied on the
data.

• A database schema defines its entities and

the relationship among them. It contains a
descriptive detail of the database, which can
be depicted by means of schema diagrams.
It’s the database designers who design the
schema to help programmers understand
the database and make it useful.
 Database Schema
A database schema can be divided
broadly into two categories:-
Physical Database Schema:
• This schema pertains to the
actual storage of data and its
form of storage like files, indices,
etc. It defines how the data will
be stored in a secondary storage.
Logical Database Schema:
• This schema defines all the logical
constraints that need to be
applied on the data stored. It
defines tables, views, and
integrity constraints.
 Database Instance
It is important that we distinguish these two terms individually.
Database schema is the skeleton of database. It is designed when the
database doesn't exist at all. Once the database is operational, it is
very difficult to make any changes to it. A database schema does not
contain any data or information.

A database instance is a state of operational database with data at any

given time. It contains a snapshot of the database. Database instances
tend to change with time. A DBMS ensures that its every instance
(state) is in a valid state, by diligently following all the validations,
constraints, and conditions that the database designers have imposed.
 ER Model Basics
Attributes
Entities are represented by means of their properties, called attributes. All attributes have values. For
example, a student entity may have name, class, and age as attributes.
There exists a domain or range of values that can be assigned to attributes. For example, a student's name
cannot be a numeric value. It has to be alphabetic. A student's age cannot be negative, etc.

Types of Attributes

Simple attribute

Simple attributes are atomic values, which cannot be divided further. For example, a student's phone number
is an atomic value of 10 digits.

Composite attribute

Composite attributes are made of more than one simple attribute. For example, a student's complete name
may have first_name and last_name.
 ER Model Basics
Types of Attributes

Derived attribute

Derived attributes are the attributes that do not exist in the physical database,
but their values are derived from other attributes present in the database. For
example, average_salary in a department should not be saved directly in the
database, instead it can be derived. For another example, age can be derived
from data_of_birth.

Single-value attribute

Single-value attributes contain single value. For example −

Social_Security_Number.
 ER Model Basics
Types of Attributes

Multi-value attribute

Multi-value attributes may contain more than one values. For example,
a person can have more than one phone number, email_address, etc.

These attribute types can come together in a way like:-

- Simple single-valued attributes
- Simple multi-valued attributes
- Composite single-valued attributes
- Composite multi-valued attributes
 ER Model Basics
Entity-Set and Keys
Key is an attribute or collection of attributes that
uniquely identifies an entity among entity set.
For example, the roll_number of a student makes
him/her identifiable among students.
Super Key
A set of attributes (one or more) that collectively
identifies an entity in an entity set.
 ER Model Basics
Candidate Key
A minimal super key is called a candidate key. An
entity set may have more than one candidate key.
Primary Key
A primary key is one of the candidate keys chosen
by the database designer to uniquely identify the
entity set.
 ER Model Basics
Relationship
The association among entities is called a relationship. For
example, an employee works_at a department, a student
enrolls in a course. Here, Works_at and Enrolls are called
relationships.
Relationship Set
A set of relationships of similar type is called a relationship
set. Like entities, a relationship too can have attributes. These
attributes are called descriptive attributes.
 ER Model Basics
Degree of Relationship
The number of participating entities in a
relationship defines the degree of the
relationship.
Binary = degree 2
Ternary = degree 3
n-ary = degree
 ER Model Basics
Mapping Cardinalities
Cardinality defines the number of entities in one entity set, which can
be associated with the number of entities of other set via relationship
set.

Types of cardinalities:

- One-to-one
- One-to-Many
- Many-to-One
- Many-to-Many
 ER Model Basics
Types of cardinalities:

One-to-one

One entity from entity set A can

be associated with at most one
entity of entity set B and vice
versa.
Example: A citizen has only one
ID card and an ID card can belong
to only one citizen.
 ER Model Basics
Types of cardinalities:

One-to-many

One entity from entity set A can be

associated with more than one
entities of entity set B however an
entity from entity set B, can be
associated with at most one entity.
Example: A student can take only
one program and a program can
have many students
 ER Model Basics
Types of cardinalities:

Many-to-one

More than one entities from

entity set A can be associated
with at most one entity of entity
set B, however an entity from
entity set B can be associated
with more than one entity from
entity set A.
 ER Model Basics
Types of cardinalities:

Many-to-many

One entity from A can be

associated with more than
one entity from B and vice
versa.
 ER Diagram Representation
When asked to develop the design of a database, the assumption made is that
you have some requirements for the application to be built for this database.

The requirements will tell you two things from a database perspective, what are
the entities we need to gather and store information above and what
associations (relationships) exist between these entities.

To identify entities, read the requirements and user stories and pick all nouns.
To identify relationships, read the requirements and user stories and pick all the
verbs.

After making a list of nouns and verbs, for each noun ask yourself how relevant
it is to keep track of that noun to the owners of the database. What kind of
information needs to be tracked per noun?
 ER Diagram Representation
Once these lists are clear, the next step would be to start drawing the
ER diagram, you can use any tool but the shapes and symbols remain
the same.

Every engineer who knows databases in the world uses and

understands an ER diagram and what each symbol means. It is the
standard way to communicate database designs.

Let us now learn how the ER Model is represented by means of an ER

diagram. Any object, for example, entities, attributes of an entity,
relationship sets, and attributes of relationship sets, can be represented
with the help of an ER diagram.
 ER Diagram Representation
Entity
Entities are represented
by means of rectangles.
Rectangles are named
with the entity set they
represent.
 ER Diagram Representation
Attributes

Attributes are the properties

of entities. Attributes are
represented by means of
ellipses. Every ellipse
represents one attribute and
is directly connected to its
entity (rectangle).
 ER Diagram Representation
Attributes

If the attributes are composite,

they are further divided in a
tree like structure. Every node
is then connected to its
attribute. That is, composite
attributes are represented by
ellipses that are connected
with an ellipse.
 ER Diagram Representation
Attributes

Derived attributes are

depicted by dashed
ellipse.
 ER Diagram Representation
Relationships

Relationships are represented by diamond-shaped box. Name of the

relationship is written inside the diamond-box. All the entities
(rectangles) participating in a relationship, are connected to it by a line.

Binary Relationship and Cardinality

A relationship where two entities are participating is called a binary

relationship. Cardinality is the number of instance of an entity from a
relation that can be associated with the relation.
 ER Diagram Representation
Representing Cardinalities

One-to-many

When more than one instance of an

entity is associated with a
relationship, it is marked as '1:N'. The
following image reflects that only one
instance of entity on the left and more
than one instance of an entity on the
right can be associated with the
relationship. It depicts one-to-many
relationship.
 ER Diagram Representation
Representing Cardinalities

Many-to-one

When more than one instance of

entity is associated with the
relationship, it is marked as 'N:1'. The
following image reflects that more
than one instance of an entity on the
left and only one instance of an entity
on the right can be associated with
the relationship. It depicts many-to-
one relationship.
 ER Diagram Representation
Representing Cardinalities

Many-to-many

The following image reflects

that more than one instance of
an entity on the left and more
than one instance of an entity
on the right can be associated
with the relationship. It depicts
many-to-many relationship.
 ER Diagram Representation
Participation Constraints

Total Participation

Each entity is involved in the

relationship. Total participation is
represented by double lines.

Partial participation

Not all entities are involved in the

relationship. Partial participation is
represented by single lines.
 ER Diagram Representation
Generalization Aggregation

The ER Model has the power of expressing database entities in a

conceptual hierarchical manner. As the hierarchy goes up, it
generalizes the view of entities, and as we go deep in the hierarchy, it
gives us the detail of every entity included.
Going up in this structure is called generalization, where entities are
clubbed together to represent a more generalized view. For example, a
particular student named Mira can be generalized along with all the
students. The entity shall be a student, and further, the student is a
person. The reverse is called specialization where a person is a
student, and that student is Mira.
 ER Diagram Representation
Generalization Aggregation

As mentioned above, the process of

generalizing entities, where the
generalized entities contain the
properties of all the generalized
entities, is called generalization. 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.
 ER Diagram Representation
Specialization

Specialization is the opposite of

generalization. In specialization, a group of
entities is divided into sub-groups based on
their characteristics. Take a group ‘Person’
for example. A person has name, date of
birth, gender, etc. These properties are
common in all persons, human beings. But in
a company, persons can be identified as
employee, employer, customer, or vendor,
based on what role they play in the company.

Similarly, in a school database, persons can be

specialized as teacher, student, or a staff, based
on what role they play in school as entities.
 ER Diagram Representation
Inheritance

We use all the above features of ER-Model in

order to create classes of objects in object-
oriented programming. The details of entities are
generally hidden from the user; this process
known as abstraction.
Inheritance is an important feature of
Generalization and Specialization. It allows lower-
level entities to inherit the attributes of higher-
level entities.

For example, the attributes of a Person class such

as name, age, and gender can be inherited by
lower-level entities such as Student or Teacher.
 Relational Model
• Relational data model is the primary data model, which is
used widely around the world for data storage and
processing. This model is simple and it has all the properties
and capabilities required to process data with storage
efficiency.

• The relational model (RM) for database management is an

approach to managing data using a structure and language
consistent with first-order predicate logic, first described in
1969 by English computer scientist Edgar F. Codd, where all
data is represented in terms of tuples, grouped into relations.
 Relational Model
• The relational model represents the database as a collection
of relations. A relation is nothing but a table of values. Every
row in the table represents a collection of related data values.
These rows in the table denote a real-world entity or
relationship.

• The table name and column names are helpful to interpret the
meaning of values in each row. The data are represented as a
set of relations. In the relational model, data are stored as
tables. However, the physical storage of the data is
independent of the way the data are logically organized.
 Relational Model Concepts
Tables
• In relational data model, relations are saved in the format of Tables.
This format stores the relation among entities. A table has rows and
columns, where rows represents records and columns represent the
attributes.
Tuple
• A single row of a table, which contains a single record for that
relation is called a tuple.
Relation instance
• A finite set of tuples in the relational database system represents
relation instance. Relation instances do not have duplicate tuples.
 Relational Model Concepts
Relation schema
• A relation schema describes the relation name (table
name), attributes, and their names.
Relation key
• Each row has one or more attributes, known as
relation key, which can identify the row in the relation
(table) uniquely.
Attribute domain
• Every attribute has some pre-defined value scope,
known as attribute domain.
 Relational Model Concepts
Constraints

Every relation has some conditions that must hold for it

to be a valid relation. These conditions are called
Relational Integrity Constraints. There are three main
integrity constraints −

- Key constraints
- Domain constraints
- Referential integrity constraints
 Relational Model Concepts
Constraints

Key Constraints

There must be at least one minimal subset of attributes in the relation, which can identify a
tuple uniquely. This minimal subset of attributes is called key for that relation. If there are more
than one such minimal subsets, these are called candidate keys.

Key constraints force that

- In a relation with a key attribute, no two tuples can have identical values for key attributes.
- A key attribute can not have NULL values.
Key constraints are also referred to as Entity Constraints.
 Relational Model Concepts
Constraints

Domain Constraints

Attributes have specific values in real-world scenario. For

example, age can only be a positive integer. The same
constraints have been tried to employ on the attributes of a
relation. Every attribute is bound to have a specific range of
values. For example, age cannot be less than zero and
telephone numbers cannot contain a digit outside 0-9.
 Relational Model Concepts
Constraints

Referential integrity Constraints

Referential integrity constraints work on the concept of

Foreign Keys. A foreign key is a key attribute of a relation
that can be referred in other relation.
Referential integrity constraint states that if a relation
refers to a key attribute of a different or same relation,
then that key element must exist.
Converting an ER Model to a Relational
Model
ER Model, when conceptualized into diagrams, gives a good overview of entity-relationship,
which is easier to understand. ER diagrams can be mapped to relational schema, that is, it is
possible to create relational schema using ER diagram. We cannot import all the ER
constraints into relational model, but an approximate schema can be generated.

There are several processes and algorithms available to convert ER Diagrams into Relational
Schema. Some of them are automated and some of them are manual. We may focus here on
the mapping diagram contents to relational basics.

ER diagrams mainly comprise of:

- Entity and its attributes

- Relationship, which is association among entities.
Converting an ER Model to a Relational
Model
Mapping Entity

An entity is a real-world object

with some attributes.

Mapping Process (Algorithm)

- Create table for each entity.

- Entity's attributes should
become fields of tables with their
respective data types.
- Declare primary key.
Converting an ER Model to a Relational
Model
Mapping Relationship

A relationship is an association among

entities.

Mapping Process (Algorithm)

- Create table for a relationship.

- Add the primary keys of all participating
Entities as fields of table with their respective
data types.
- If relationship has any attribute, add each
attribute as field of table.
- Declare a primary key composing all the
primary keys of participating entities.
- Declare all foreign key constraints.
Converting an ER Model to a Relational
Model
Mapping Weak Entity Sets

A weak entity set is one which does

not have any primary key associated
with it.

Mapping Process

- Create table for weak entity set.

- Add all its attributes to table as field.
- Add the primary key of identifying
entity set.
- Declare all foreign key constraints.
Converting an ER Model to a Relational
Model
Mapping Hierarchical Entities

ER specialization or generalization comes in

the form of hierarchical entity sets.

Mapping Process

- Create tables for all higher-level entities.

- Create tables for lower-level entities.
- Add primary keys of higher-level entities in
the table of lower-level entities.
- In lower-level tables, add all other attributes
of lower-level entities.
- Declare primary key of higher-level table
and the primary key for lower-level table.
- Declare foreign key constraints.
 Example
• School Management System Database
 Assignment
• Developing, interpreting and
understanding user stories and
requirements documents
– Develop user stories or requirements
document for a taxi ride management app.
Database Design for Taxi Booking Web App (Due: 7 days)

MINI PROJECT 2
 Project Description
Database model for Taxi Booking Application
• Part 1: Develop ER Model for Taxi Booking App
• Part 2: Convert the ER Model into a Relational
model explaining each step
• Part 3: Implement the model on MySQL
Workbench
• Part 4: Document your solutions on Microsoft
word, create a pdf file and submit.
 Summary
So far, we have looked at the following:

• General Architecture
• Data Models
• Entity Relationship Model
• Relational Model
• Converting an ER to a Relational Model
• Assignment
• Mini Project 2: Do database modeling for a Taxi Booking
Application
Congratulations!