Database Design Process

And ER Diagram
Let’s design the DBMS!

Dr. Rohit Saluja (IIT Mandi)

Recap
- What we learned about File Based System:
- Not self-describing
- Inadequate Abstraction: Copies may lead to Data inconsistency + Redundancy
- Insulation between Program and Data: Data’s life outlives program’s life
- Inefficient query mechanisms
- No notion of enforcing constraints (enforce a prerequisite)
■ Inconsistent state
Today: Database Design Process
Answer the following question
What is more valuable and long lasting?
A) Data
B) Query Mechanism

Raise your hands

Answer the following question
What does solving inadequate abstraction
lead to ?

Raise your hands

 Physical Design
Database Design Process

Data Model
Requirement
Conceptual Design Mapping
Analysis
(Logical Design)
stock-price price name A B
name

Company makes Product

C D
category
Consult different stakeholders E-R Model
E F
to find out requirements Analyze Design Alternatives
R.No. Name Fees CGPA
and Select Appropriate One
Database Design Process Physical Design
 Physical Design
Database Design Process

Index

Sender Date Keywords

- Optimize Queries: Different indexes are created

based on different queries which are frequently
used.
Image by Oberholster Venita from Pixabay - For quick retrieval
 Physical Design
Database Design Process

Data Model
Requirement
Conceptual Design Mapping
Analysis
(Logical Design)
stock-price price name A B
name

Company makes Product

C D
category
Consult different stakeholders E-R Model
E F
to find out requirements Analyze Design Alternatives
R.No. Name Fees CGPA
and Select Appropriate One
Database Design Process: Conceptual Design
- E-R Model
Conceptual Design
- We want to model a system for storing data about companies and the
products they make.
Entities: things
- Each company has a unique name, stock-price, etc. with independent
existence
- Similarly, each product has a price tag and a category associated with it.
Relationships, in
which entities
stock-price price pid
name participate

Attributes of
Compan makes Product Entities
y
____ Key/s of Entities:
Help uniquely identifying
category an entity
Database Design Process: Conceptual Design
E/R Diagram
Conceptual Design
name stock-price price pid
Entities: things
with independent
Compan existence
makes Product
y Relationships, in
which entities
category participate
address
employs buys Attributes of
Entities

Person ____ Key/s of Entities:

Help uniquely identifying
ssn name an entity
Multiplicity of ER Relations

a
1
b
2
C
3
d

one-one
Multiplicity of ER Relations

a
1
b
2
C
3
d

many-one
Multiplicity of ER Relations

a
1
b
2
C
3
d

many-many
Database Design Process: Conceptual Design
Conceptual Design
E/R Diagram

name stock-price price pid Entities: things

with independent
existence
Company makes Product Relationships, in
which entities
participate
category
employs
address
buys
Attributes of
Entities

Perso n ____ Key/s of Entities:

Help uniquely identifying
ssn name an entity
Types of Attributes

middle_name

first_name last_name

name
address

Person

composite attribute: name

simple attribute: address
Types of Attributes

phone_no address

Person

single valued: address

multivalued: phone number
Types of Attributes

date_of_birth
age

Person

Derived attribute: age

Age is derived from date of birth
Types of Attributes
Entity Types and Key Attributes
- An attribute for which each entity must have a unique value is called a key
attribute of the entity type. For example, Adhaar of EMPLOYEE
- A key attribute may be composite. For example, VehicleTagNumber is a key of
the CAR entity type with components (Number, State).
- An entity type may have more than one key. For example, the CAR entity type
may have two keys:
- VehicleIdentificationNumber (popularly called VIN) and
- VehicleTagNumber (Number, State), also known as license_plate number
- Entity Set – collection of entities of a particular type
Structural Constraints on Relationships
- Cardinality ratio (of a binary relationship):
- 1:1,
- 1:N,
- N:1, or
- M:N
- Participation constraint (on each participating entity type): total (called
existence dependency) or partial.
The (min,max) notation relationship constraints

(0,1) (1,1)
Employee Manages Department

(1,1) (1,N)
Employee Works for Department

Specifies that each entity e in E participates in at least min and at most max
relationship instances in R
Participation Constraints
- Total participation (indicated by double line): every entity in the entity set
participates in at least one relationship in the relationship set.
- Partial participation: some entities may not participate in any relationship in the
relationship set

Some
courses
Students registers Courses might not be
offered in a
semester.
Roles
- Sometimes an entity set appears more than once in a relationship.
- Label the edges between the relationship and the entity set with names called
roles.

manager
employee works for
worker

- Another E.g.
- Course has another course as prerequisite.
Weak Entities
- An entity type may have no key. Then it is referred to as weak entity.
- e.g. Payment of Loans
- Without a Loan, there is no meaning to Payment.
- Thus, the existence of a weak entity depends on the existence of an identifying entity

payment loan_payment loan

- Other e.g.
- Employee - has - dependants (weak entity).
- Course <- has = Sections/Course Offering (Total Participation).
Weak Entities
- The discriminator (or partial key) of a weak entity set is the set of attributes that
distinguishes among all the entities of a weak entity set.
- Total participation of weak entity
- One-to-Many relationship from the identifying to weak entity
payment-number
date

payment loan_payment loan

Takeaways
- What we learned about File Based System:
- Database Design Process
- Physical Design: Gmail Search
- ER Diagram
■ Multiplicity
■ Attribute Types
■ Participation Constraints
■ Keys
Next : Build an ER Model for Course Offerings, and Extended ER
Thank You