Database Systems

Instructor: Sobia Rasheed Malik

 Data Modeling Using the
Entity-Relationship (ER) Model
Overview of DB Design Process
Design Stages
Analyze User Requirement

Develop conceptual model

Map conceptual to logical

model
Choose DBMS

Develop physical design

Implement System

Test System

Operational Maintenance

Slide 1-4
 Using High-Level Conceptual Data
Models for Database Design

● Requirements collection and analysis

– Database designers interview prospective database
users to understand and document data
requirements
– Result: data requirements
– Functional requirements of the application
 Using High-Level Conceptual Data
Models (cont’d.)
● Conceptual schema
– Conceptual design
– Description of data requirements
– Includes detailed descriptions of the entity
types, relationships, and constraints
– Transformed from high-level data model into
implementation data model
 Using High-Level Conceptual Data
Models (cont’d.)
● Logical design or data model mapping
– Result is a database schema in implementation
data model of DBMS
● Physical design phase
– Internal storage structures, file organizations,
indexes, access paths, and physical design
parameters for the database files specified
Conceptual Schema
Logical Design/Model
Physical Design/Model
 Data Modeling Using the
Entity-Relationship (ER) Model
● Entity-Relationship (ER) model
– Popular high-level conceptual data model
– A semantic data model used for graphical
representation of conceptual database design
● ER diagrams
– Diagrammatic notation associated with the ER
model
● Unified Modeling Language (UML)
 Entity Types, Entity Sets, Attributes,
and Keys
● ER model describes data as:
– Entities
– Attributes
– Relationships
Simple Example of ER Diagram
Another Example of ER Diagram
 A Sample Database Application

● COMPANY
– Employees, departments, and projects
– Company is organized into departments
– Department controls a number of projects
– Employee: store each employee’s name, Social
Security number, address, salary, sex (gender),
and birth date
– Keep track of the dependents of each employee
 Entity Type
● A name /label assigned to objects/items that
exists in real world and that have similar
properties

● It could be person, place, event or even concept

● Distinguish from other entity types based on

properties
 Entity Instance, Entity Set
● Entity Set: A particular object belonging to
particular entity type
● Example:
– Entity type: Student
– Entity instance: Fatima Ali
– Entity Set: All students
 Symbols

Regular Entity Type NAME

Weak Entity Type NAME

EMPPLOYEE DEPENDENTS

BOOK BOOKCOPY
Naming convention for entity type
● Singular name recommended

● Organization specific name

– e.g. customer/client is same term choose which
organization is used
● Use capital letter

● Abbreviation can be used, be consistent

 Attributes
● Attributes
– Particular properties that describe entity
– Types of attributes:
• Composite versus simple (atomic) attributes
• Single-valued versus multivalued attributes
• Stored versus derived attributes
• NULL values
• Complex attributes
 Symbols for Attributes
● Each represented as an oval, linked with an ET
symbol
Symbols for Attributes

Simple

Composite
Multi-valued
Derived
Symbols for Attributes
Example of derived Attributes
Example of composite
Attributes
 Example

Experienc empI empNam

e d e

EMPLOYE addres
E s

dateHire Hobbie stree houseN

d s t o
Initial Conceptual Design of the
COMPANY Database
 Super key

❑ Super Key is a set of attributes whose

set of values can uniquely identify an
entity instance in the entity set.
❑ A Super Key can contains one or
more than one attributes.
❑ Super Key is the broadest definition
of unique identifiers of an entity in an
entity set.
 Super key

The combination of “SSN” and “Name” is a super key of the following entity set
customer.
Because:
The value of attributes “SSN” and “Name”, such as 558-36-1234 and Susan,
can uniquely identify that particular customer in customer entity set, which is the
pool of all customers.

❖ We are unsurprisingly very interested in the most economical combination(s) of

attributes that can uniquely identify any particular entity.

❖ Custom
Therefore, we introduce Candidate Key next.
er-stree
SSN t

Custo Customer-city
mer-na
me customer
 Candidate key
❑ Candidate key is a set of one or more
attributes whose set of values can
uniquely identify an entity instance in the
entity set.
❑ Any attribute in the candidate key cannot
be omitted without destroying the
uniqueness property of the Candidate
key.
❑ It is minimal Super Key.
 Candidate key

Properties of
Candidate Key…..
While most entity sets have only one candidate key,
some entity sets could have more than one candidate
key.
Candidate key could have more than one attributes.
In building a database in a database software, the
software will only allow to use one candidate key to be
the unique identifier of an entity for an entity set.
 Candidate key

● Example:
● (SSN, Name) is NOT a candidate key, because taking out
“name” still leaves “SSN” which can uniquely identify an entity.
“SSN” is a candidate key of customer.
● Example: Both “SSN” and “License #” are candidate keys of
Driver entity set.
Custom
er-stree
SSN t

Custo Customer-city
mer-na
me customer
❖ Overall, Super Key is the broadest unique identifier;
Candidate Key is a subset of Super Key; and
Primary Key is a subset of Candidate Key.
❖ In practice, we would first look for Super Keys. Then
we look for Candidate Keys based on experience
and common sense.
❖ If there is only one Candidate Key, it naturally will be
designated as the Primary Key.
❖ If we find more than one Candidate Key, then we
can designate any one of them as Primary Key.
 Primary Key

❑ The Primary Key is an attribute or a set of

attributes that uniquely identify a specific instance
of an entity.
❑ Every entity in the data model must have a primary
key whose values uniquely identify instances of the
entity.
 Primary Key

Properties Of Primary
Keys....
To qualify as a primary key for an entity, an attribute must have the
following properties:
It must have a non-null value for each instance of the entity
The value must be unique for each instance of an entity
The values must not change or become null during the life of each
entity instance
❖ Primary and Foreign keys are the most basic
components on which relational theory is based.
❖ Each entity must have a attribute or attributes, the
primary key, whose values uniquely identify each
instance of the entity.
❖ Every child entity must have an attribute, the foreign
key, that completes the association with the parent
entity.
 Foreign key
❑ A Foreign key is an attribute that
completes a relationship by identifying
the parent entity.
❑ Foreign keys provide a method for
maintaining integrity in the data (called
referential integrity) and for navigating
between different instances of an entity.
❑ Every relationship in the model must be
supported by a foreign key.
 Foreign Key

Properties Of
Foreign Key….
Every dependent and category (subtype) entity in the model
must have a foreign key for each relationship in which it
participates.
Foreign keys are formed in dependent and subtype entities by
migrating the entire primary key from the parent or generic
entity. If the primary key is composite, it may not be split.
 Composite Key
❑ When a primary key is created from a
combination of 2 or more columns, the
primary key is called a composite key.
❑ Each column may not be unique by itself
within the database table but when combined
with the other column(s) in the composite key,
the combination is unique.
 Keys, and Value Sets

● Key or uniqueness constraint

– Attributes whose values are distinct for each
individual entity in entity set
– Key attribute
• Uniqueness property must hold for every entity set
of the entity type
 Key Attributes

● An attribute or set of attributes to identify

an entity instance uniquely
● Types
– Super key
– Candidate key
– Primary key
– Secondary and Alternate keys
 Example of Key

StdId StdName Address ClName CurSem

S1020 Suhail Dar Mareer Hassan MCS 4
S1038 Shoaib Baber Model Town BCS 3
S1015 Tahira Ejaz Wah Cantt MCS 2
S1018 Arif Mehmood Satellite Town BIT 4

S1025 Suhail Shah Garhi Shahoo BCS 6

 Simple or Composite Key
● A key consisting of single attribute is called
simple key, e.g., StudID, itemNo
● A key consisting of more than one attribute is
known as composite key, like
{Program_Code,Course_Code}
 Composite Key Example

OFFERING
ProgCode CourseCode MarksAlloc CrHrs
MCS DS 100 3
MCS DBS 100 3
MBA DBS 100 3
BCS NW 100 3
 Composite Key Example

StdId StdName Address ClName CurSem

S1020 Suhail Dar Mareer Hassan MCS 4
S1038 Shoaib Baber Model Town BCS 3
S1015 Tahira Ejaz Wah Cantt MCS 2
S1018 Arif Mehmood Satellite Town BIT 4

S1025 Suhail Shah Garhi Shahoo BCS 6

 Super Key
● Definition same as of key
● For example, for EMPLOYEE and
STUDENT entity types EmpID and
StudID are the superkeys respectively.
 Super Keys

StdId StdName Address ClName CurSem

S1020 Suhail Dar Mareer Hassan MCS 4
S1038 Shoaib Baber Model Town BCS 3
S1015 Tahira Ejaz Wah Cantt MCS 2
S1018 Arif Mehmood Satellite Town BIT 4

S1025 Suhail Shah Garhi Shahoo BCS 6

 Candidate Key

A candidate key is the super key that does not

contain extra attributes. It might have more
than one attribute that uniquely identifies an
entity. e.g. {name, address}
 Candidate Keys
A super key such that no proper
subset of its attributes is itself a
super key. e.g. {StudID, Major} is
not a candidate key because it
contains a subset, StudID, that is a
super key
 CK Example
StdId StdName Address ClName CurSem
S1020 Suhail Dar Mareer Hassan MCS 4
S1038 Shoaib Baber Model Town BCS 3
S1015 Tahira Ejaz Wah Cantt MCS 2
S1018 Arif Mehmood Satellite Town BIT 4

S1025 Suhail Shah Garhi Shahoo BCS 6

 Primary Key
A primary key is the main/chosen candidate key
from the possible set of candidate keys that is most
suitable for entity identification.

● It may be a single attribute or a composite key.

● None of its attributes can have NULL values.
● The other candidate keys called Alternate keys
provide another method of accessing records.
 Need for Key
● Need for unique identification and access
● Secondary Keys: We access on something not
necessarily unique
 Summary

● Basic ER model concepts of entities and their

attributes
– Different types of attributes
– Structural constraints on relationships
● ER diagrams represent E-R schemas