Chapter 3

Data Modeling

1
Outline:
• Database System Development Lifecycle
• The Relational Database Model
⁻ The Relational Model Concepts
⁻ The Relational Constraints and Relational Database Schemas
⁻ The Relational Operations
⁻ Views
• The Entity Relationship (ER) Model
• The high-level conceptual model
• Entities, Attributes, and Keys
• Relationship types, Associations, and Constraints
• Relationship Types, Relationship Sets, Roles, and Structural Constraints
• Weak Entity Types
• ER Diagrams, Naming Conventions, and Design Issues
• Mapping ER-models to relational tables

2
Database Design
▪ Database design is the process of coming up with different kinds of specification for the
data to be stored in the database.

• The database design part is one of the middle phases we have in information systems
development where the system uses a database approach.

• Design is the part on which we would be engaged to describe how the data should be
perceived at different levels and finally how it is going to be stored in a computer system.

3
Database Design (Cont’d)

• In developing a good design, one should answer such questions as:

• What are the relevant Entities for the Organization

• What are the important features of each Entity

• What are the important Relationships

• What are the important queries from the user

• What are the other requirements of the Organization and the Users

4
Database Design (Cont’d)

The database design phase is divided into three steps:

• conceptual database design

• logical database design

• physical database design

5
 Conceptual Design

• The process of constructing a model of the information used in an enterprise, independent

of all physical considerations.
Entirely independent of implementation details such as the target DBMS, application
programs, programming languages, hardware platform, performance issues, or any other
physical considerations.

• It is the source of information for the logical design phase.

• Mostly uses an Entity Relationship Model to describe the data at this level.

• After the completion of Conceptual Design one has to go for refinement of the schema,
which is verification of Entities, Attributes, and Relationships.
6
The key objectives of conceptual database design are as follows −
• Identify the entities and their attributes − Entities are objects or concepts that exist in the real world and
can be distinguished from each other. Attributes are the properties or characteristics of the entities. The first
objective of conceptual database design is to identify the entities and their attributes that are relevant to the
organization's business requirements.
• Define the relationships − Relationships are the associations between entities. The second objective of
conceptual database design is to define the relationships between the identified entities. Relationships can
be one-to-one, one-to-many, or many-to-many.
• Establish the constraints − Constraints are the rules that govern the relationships between entities. The
third objective of conceptual database design is to establish the constraints between entities, which ensure
data consistency and integrity.

7
Process of Conceptual Database Design
The process of conceptual database design involves the following steps −
• Requirements gathering − The first step in conceptual database design is to gather the
business requirements from the stakeholders. This involves identifying the data elements,
relationships, and constraints that are essential to the organization's business requirements.
• Entity-relationship modeling − The second step in conceptual database design is to create
an entity-relationship (ER) model, which represents the entities, attributes, and
relationships between the entities. The ER model is a graphical representation of the data
elements and their relationships.
• Review and feedback − The fourth step in conceptual database design is to review the ER
model with the stakeholders and incorporate their feedback into the design.
8
Conceptual Design (Cont’d)

• Conceptual design revolves around discovering and analyzing organizational and

user data requirements
• The important activities are to identify
• Entities
• Attributes
• Relationships
• Constraints
• And based on these components develop the ER model using ER diagrams

9
 Conceptual Design (Cont’d)
• Identify what are the entities/entity types
• Identify what are the attributes: - the information about entities and relationship
should we store in the database.
• Identify relationship types
• Identify what are the constraints/business rules that hold?
• Draw entity-relationship diagram: - representing the database in the ER model using
pictorial representation called ER diagram
• Check model for redundancy
• Validate conceptual data model against user transactions
• Review the conceptual data model with user

10
Entity Relationship (E-R) Model

• An entity-relationship (E-R) data model is a high-level conceptual model that describes data
as entities, attributes, and relationships.

• The E-R model is represented by E-R diagrams that show how data will be represented and
organized in the various components of the final database.

• However, the model diagrams do not specify the actual data, or even exactly how it is
stored.

• The users and applications will create the data content and the database management system
will create the database to store the content.

• Entity-Relationship modeling is used to represent conceptual view of the database.

11
Entity Relationship (E-R) Model

The main components of ER Modeling are:

• Entities
• Attributes
• Relationships
• Constraints

12
Main components of ER Modeling -Entities
• An entity (or entity class) is a person, place, thing, event, or concept about which data is
collected. In other words, entities are the real-world things in which we have sufficient
interest to capture and store data about in a database.
• Just about anything that can be named with a noun can be an entity
• Each entity shown in the conceptual model represents the entire class for that entity. For
example, the Customer entity represents the collection of all customers. The individual
customers are called instances of the entity.
• The name given to an entity should always be a singular noun descriptive of each item to
be stored in it. E.g.: student NOT students.
• Corresponds to entire table, not row
• Represented by Rectangle
13
 Main components of ER Modeling –Attributes,Relations,Constraints

Attributes
• Represents the property used to describe an entity or a relationship

• Attributes are the characteristics of an entity that we are interested in. An attribute is a
descriptor whose values are associated with individual entities of a specific type

• Represented by Oval
Relationships
• Represents the association that exist between entities

• Represented by Diamond
Constraints
• Represent the constraint in the data
14
Main components of ER Modeling –Attributes,Relations,Constraints (Cont’d)

• Before working on the conceptual design of the database, one has to know and answer the
following basic questions.

• What are the Entities and relationships in the enterprise?

• What information about these entities and relationships should we store in the database?

• What are the integrity constraints that hold? Constraints on each data with respect to
update, retrieval and store.

• Represent this information pictorially in ER diagrams , then map ER diagram into a

relational schema.

15
 Developing an E-R Diagram

• Designing conceptual model for the database is not a one linear process but an
iterative activity where the design is refined again and again.

• To identify the entities, attributes, relationships, and constraints on the data, there
are different set of methods used during the analysis phase. These include
information gathered by
• Interviewing end users individually and in a group
• Questionnaire survey
• Direct observation
• Examining different documents
16
 Developing an E-R Diagram (Cont’d)

• The basic E-R model is graphically depicted and presented for review. The process is
repeated until the end users and designers agree that the E-R diagram is a fair
representation of the organization’s activities and functions.

• Checking for Redundant Relationships in the ER Diagram. Relationships between entities

indicate access from one entity to another - it is therefore possible to access one entity
occurrence from another entity occurrence even if there are other entities and
relationships that separate them - this is often referred to as Navigation‘ of the ER
diagram.

• The last phase in ER modeling is validating an ER Model against requirement of the user.

17
Graphical Representations in ER Diagramming
• An ER diagram consists of entities, attributes, and relationships. Entities are the basic real-world
elements, attributes describe their characteristics, and relationships describe how entities interact with or
affect each other.

• ER diagrams depict the types of entities to be represented in the system and there will be many instances
of each entity type in the actual system when built. The ERD therefore also needs to represent
cardinality, or the possible number of each entity that can be associated with the number of another
entity.

• Cardinality is represented in an entity-relationship diagram by using notation to clarify the relationships

between entities.

• Two commonly used systems of notation are :

Chen notation

and

Crow’s Foot notation (to be discussed later) 18

Graphical Representations in ER Diagramming
Components of Chen notation
• Chen notation was introduced in 1976 by Peter Chen
• Chen’s notation system uses rectangles to represent entities and diamonds to represent
relationships

• To display cardinality, Chen notation uses the letter m to indicate many on one side of the
relationship. The m simply means any number greater or equal to one. It uses a 1 to indicate one and
only one.
19
Graphical Representations in ER Diagramming
Components of Chen notation

Example to show that a department can employ many employees

20
Graphical Representations in ER Diagramming
Components of Chen notation

Example to show a department can have many employees, but an

employee can only be employed by one department.

21
Graphical Representations in ER Diagramming
• Entity is represented by a RECTANGLE containing the name of the entity.

• Entities and their Attributes

• The basic object that the ER model represents is an entity, which is a "thing" in the real world
with an independent existence.
• An entity may be an object with a physical existence (for example, a particular person, car,
house, or Existence Dependency: the dependence of an entity on the existence of one or
more entities.

22
Entity Types
1. Strong entity types
2. Weak entity types

1. Strong entity types

• A regular entity type that doesn’t have a key attribute.
• A strong entity is an entity that exists independently and has its own primary key.
• It doesn't rely on another entity for its existence or identification
• It is independent Existence-it can exist without being related to any other entity.
• Removing a related entity doesn't affect the strong entity's existence.
• Has a Primary Key-it possesses one or more attributes that uniquely identify

• Typically depicted as a rectangle in an Entity-Relationship (ER) diagram.

23
Entity Types (Cont’d)
2. Weak entity
• Entity types that do not have key attributes of their own
• An entity that cannot exist without the entity with which it has a relationship
• Entities belonging to a weak entity type are identified by being related to specific entities
from another entity type in combination with some of their attribute values.
• We call this other entity type the identifying or owner entity type , and we call the
relationship type that relates a weak entity type to its owner the identifying relationship
of the weak entity type

24
 Strong vs Weak Entity Types
25

• In ER Diagram, weak entity types and their relationship have double borders

25
Strong vs Weak Entity Types
26

26
Main components of ER Modeling -Attributes

13
 Attributes
Represents the property used to describe an entity or a relationship
Are the characteristics of an entity that we are interested in
 Types of Attributes
Simple Attributes
Composite Attributes
Single-valued Attributes
Multi-valued Attributes
Derived Attributes

27
Types of Attributes
Simple (atomic) Vs Composite attributes

Simple: contains a single value (not divided into sub parts)

• Simple Attributes are represented by OVALS and are connected to the entity by a line
E.g. Age, gender

Composite: Divided into sub parts (composed of other attributes)

Eg:Name, address

28
 Types of Attributes(Cont’d)

Single-valued Vs multi-valued attributes

• Single-valued: have only single value (the value may change but has only one value at
one time)
E.g. Name, Sex, Id. No. color_of_eyes
• Multi-Valued
• have more than one value
• depicted by double ellipse
E.g. Address, dependent-name
Person may have several college
degrees

Multi-valued attribute
29
Types of Attributes(Cont’d)

Stored vs. Derived Attribute

• Stored : not possible to derive or compute
E.g. Name, Address
• Derived: The value may be derived (computed) from the values of other
attributes.
eg:
• Length of employment (current date- start date)
• Age (current year – year of birth)
• Profit (earning-cost)
• G.P.A (grade point/credit hours)
• A derived attribute is indicated by a dotted line. (……..).

30
Entity-Set and Keys
 Super Key(or Simply Key) is an attribute or collection of attributes that uniquely identifies
an entity15among entity set
For example, the ID of a student makes him/her identifiable among students
 Candidate Key – Candidate Key: a super key such that no proper subset of that collection
is a Super Key within the relation. A nominee for primary key field is known as candidate
key.
 An entity set may have more than one candidate keys
• A candidate key has two properties:
1. Uniqueness
2. Irreducibility
• If a super key is having only one attribute, it is automatically a Candidate key.

31
Keys (Cont’d)

 Primary Key - A primary key is one of the candidate keys chosen by the database
designer to uniquely identify the entity set
Value of a primary key is unique and cannot be null
Primary Keys are underlined

 Composite Key - If a table do not have any single column that qualifies for a Candidate
key, then you have to select two or more columns to make a row unique. Such keys are
called Composite Key.
 Foreign Key – an attribute that can only take the values which are present as values of other
attribute

32
Attribute Values - Null & Default Values

16
 Null Value
NULL applies to attributes which are not applicable or which do not have values

For such attributes, you may enter the value NA (meaning not applicable)

Value of a primary key attribute can not be null

 Default value
assumed value if no explicit value

33
Entity-Set and Keys

 For Student Entity  For Student_couse Entity

17 Candidate Key – {STUD_NO,
Candidate Key – COURSE_NO}
{STUD_NO} Primary Key – {STUD_NO, COURSE_NO} -
Primary Key - STUD_NO Also Composite keys
Foreign Key – STUD_NO

34
Main components of ER Modeling –Relations

18
 The association among entities is called a relationship
E.g., an employee works_at a department, a student enrolls in a
course. Here, Works_at and Enrolls are called relationships

A relationship should be named by a word or phrase which explains its function

Relationships are represented by DIAMOND shaped symbols

A relationship should be named by a word or phrase which explains its function

Strong Relationship is a relationship between two strong Entities

35
Main components of ER Modeling –Relations

• Strong Relationship

• Weak Relationship is a relationship between Weak and Strong

Entities

Weak relationship

36
Main components of ER Modeling –Relations

 Relationship Attributes: it is also possible for a relationship to have attributes

 Degree of Relationship: refers to the number of entities participating in a

relationship
Unary Relationship: a relationship involving only one entities
Binary Relationships: a relationship between two entities
Ternary Relationship: a relationship between three entities
N-ary Relationship: a relationship that participates more than three entities

37
Main components of ER Modeling –Relations

Binary Relationship type of degree 2

38
Main components of ER Modeling –Constraints
19
 Constraints
are the rules enforced on the data of a table
ensure the accuracy and reliability of the data in the
database
 For example, if a company has a rule that each employee must work for
exactly one department, then we would like to describe this constraint in the
schema
 We can distinguish two main types of relationship constraints: Cardinality
Ratio and Participation.
 Cardinality Ratios and Participation Constraints taken together are
called Structural Constraints
39
Main components of ER Modeling –Constraints (Cont’d)

Cardinality ratio
• Cardinality ratio for a binary relationship specifies the maximum number of the
relationship instances that an entity can participate in.

• Requirement Analysis:
Every employee works for exactly one department, a department can have many
employees and a new department need not have an employee.

40
Main components of ER Modeling –Constraints (Cont’d)

• Degree of a relationship: The number of entity instance that can participate in a

relationship
• In the above fig. the degree of the relationship is :2
• In the above fig.:
• the cardinality ratio of employee entity type is 1 since each employee instance can
participate in a maximum of one relationship instance
• the cardinality ratio of department entity type is N(many) since each department
instance can participate in a maximum of more than one relationship instances
41
Main components of ER Modeling –Constraints (Cont’d)

Types of Cardinality in ER-Model

• One-to-one (1:1) – When one entity in each entity set takes part at most once in the
relationship, the cardinality is one-to-one.
• One-to-many (1: N) – If entities in the first entity set take part in the relationship set at
most once and entities in the second entity set take part many times (at least twice), the
cardinality is said to be one-to-many.
• Many-to-one (N:1) – If entities in the first entity set take part in the relationship set many
times (at least twice), while entities in the second entity set take part at most once, the
cardinality is said to be many-to-one.
• Many-to-many (N: N) – The cardinality is said to be many to many if entities in both the
entity sets take part many times (at least twice) in the relationship set.

42
Representing cardinality ration on ER diagram

43
Cardinality Ratio Types
 ONE-TO-ONE (1:1) one tuple is associated with only one other tuple
21
E.g. a) Department-Employee: in manages relationship since one department has only one
manager and one manager manages only one department, this relationship is 1:1

b) A customer is associated with at most one loan via the relationship borrower.A loan is
associated with at most one customer via borrower.

44
Cardinality Ratio Types

C) Relationship Manages between STAFF and BRANCH.

• The multiplicity of the relationship is:

• One branch can only have one manager.

• One employee could manage either one or no branches.

 ONE-TO-MANY (1:N) one tuple can be associated with many other tuples, but not the
reverse
E.g. Department-Student: as one department can have multiple students and one
student belongs to only one department, this relationship is 1:N

45
Cardinality Ratio Types

ONE-TO-MANY (1:N)

E.g.
a) Relationship Leads between STAFF and PROJECT. The multiplicity of the
relationship is:
• One staff may Lead one or more project(s)
• One project is Lead by one staff

46
b)In the one-to-many relationship a loan is associated with at most one customer via
Borrower , a customer is associated with several (including 0) loans via borrower.

47
Many to One (N:1)

• An entity in an entity type (A) is associated with an entity in another entity type (B), and an
entity in entity type (B) can be associated with any number of entities in entity type (A).

E.g. Employee – Department: as many employees belong to a single department

48
Many to Many (N:M)
• An entity in entity type (A) can be associated with any number of entities in another entity
type (B), and an entity in entity type (B) can be associated with any number of entities in
entity type (A).
 one tuple can be associated with more than one other tuple and vice versa

E.g.
a) Student – Course as a student can take many courses and a single course can be
attended by many students
b)Relationship Teaches between INSTRUCTOR and COURSE The multiplicity of the
relationship
• One Instructor Teaches one or more Course(s)
• One Course Thought by Zero or more Instructor(s)

49
Participation constraints and the existing dependency
• Participation constraint of a relationship is involved in identifying and setting the
mandatory or optional feature of an entity occurrence to take a role in a relationship.
• The participation constraint specifies whether the existence of an entity depends on its
being related to another entity via the relationship type.
• This constraint specifies the minimum number of relationship instances that each
entity can participate in, and is sometime called minimum cardinality constraint.
There are two types of participation constraints
• Total participation constraints
• Partial participation constraints
50
Total (or mandatory )Participation

• “The participation of an entity type E in a relationship type R is said to be total if every

entity in entity type E participates in at least one relationship in relationship type R”.

• Every tuple in the entity or relation participates in at least one relationship by taking a
role. This means, every tuple in a relation will be attached with at least one other tuple.

• The entity with total participation in a relationship will be connected to the relationship
using a double line.

51
• For example if a company policy states that every employee must work for a
department, then an employee entity can exist only if it participate in at least one
works for relationship instance. Thus the participation of employee in works
for relationship type is called total participation, meaning that every entity in “the
total set of” employee entities must be related to a department entity via works
for relationship type.

• Total participation is also called existence dependency.

52
53
Partial (or optional) Participation
• some tuple in the entity or relation may not participate in the relationship. This
means, there is at least one tuple from that Relation not taking any role in that
specific relationship. The entity with partial participation in a relationship will be
connected to the relationship using a single line.

• In some cases we do not expect every employee to manage a department, so the

participation of employee in the “manages” relationship type is partial, meaning
that some or “part of the set of” employee entities are related to some
department entity via manages, but not necessarily all. “If only some entities in
entity type E participate in relationship in relationship type R, the participation of
entity type E in relationship type R is said to be partial”
54
Graphical Representations in ER Diagram

Participation
34 Constraints

55
Alternative way of representing both cardinality ratio and participation constraints
(Structural Constraints)on ER-diagram
using (min,max)-cardinalities
• Min cardinality shows participation constraint
• Max cardinality shows cardinality ratio constraint
Eg:

56
Participation Constraints Example
23

 Total Participation Example

If a company policy states that every employee must work for a department, then
an employee entity can exist only if it participates in at least one WORKS_ FOR
relationship instance

◼ Thus, the participation of EMPLOYEE in WORKS_FOR is called total

participation, meaning that every entity in employee entities must be related to
a department entity via WORKS_FOR.

57
Participation Constraints Example

 Partial Participation Example

We do not expect every employee to manage a department, so the

participation of EMPLOYEE in the MANAGES relationship type is partial,
meaning that some employee entities are related to some department entity via
MANAGES, but not necessarily all

58
Example identify the participation constraint between the following entities on the
relation
1. Employee belongs to a department
2.Employee manages the department

59
Summary of the notation for ER diagrams

60
Summary of the notation for ER diagrams(Cont’d)

61
Summary of the notation for ER diagrams (Cont’d)

62
Crow’s Foot Notation

Symbols associated with the crow's foot notation

Attributes
Entity

63
 Crow’s Foot Notation

Relationship Between Entities

Symbols associated with the crow's foot notation
• we use lines to connect them. But these lines have notations (indicators) on them to specify the type of
relationship that exists between two entities.

Zero

One

Many

One to Many

One and only one 64

Example: The minimum number of courses a teacher can take up is one, and the maximum is
also one(one and only one).

Example: One course can be taught by one or many teachers (one or many) .

65
Example: a pizza can be ordered by none (optional) or many customers and a
customer can order zero or many pizzas (many to man)

66
Example #1:Build an E-R Diagram for the following information:

A student record management system will have the following two basic data
object categories with their own features or properties: Students will have an Id,
Name, Dept, Age, GPA and Course will have an Id, Name, Credit
Hours. Whenever a student enroll in a course in a specific Academic Year and
Semester, the Student will have a grade for the course.

67
E-R Diagram

68
Example ER Diagram for a University Database

69
Example #2:Build an ER Diagram for the following information:

A Personnel record management system will have the following two basic data
object categories with their own features or properties: Employee will have an Id,
Name, DoB, Age, Tel and Department will have an Id, Name, Location. Whenever
an Employee is assigned in one Department, the duration of his stay in the
respective department should be registered.

70
ER Diagram Examples
35

71
72
73
Relational Data Model
• A Relation is a mathematical concept based on the ideas of sets
• The model was first proposed by Dr. E.F. Codd of IBM Research in 1970 in the
following paper:
• "A Relational Model for Large Shared Data Banks," Communications of the ACM,
June 1970
• The above paper caused a major revolution in the field of database management and
earned Dr. Codd the coveted ACM Turing Award

74
Informal Definitions

• Informally, a relation looks like a table of values.

• A relation typically contains a set of rows.

• The data elements in each row represent certain facts that correspond to a real-world
entity or relationship
• In the formal model, rows are called tuples

• Each column has a column header that gives an indication of the meaning of the data
items in that column
• In the formal model, the column header is called an attribute name (or just attribute)

Slide 5- 75
Example of a Relation

Slide 5- 76
Informal Definitions

• Key of a Relation:
• Each row has a value of a data item (or set of items) that uniquely identifies
that row in the table
• Called the key
• In the STUDENT table, SSN is the key

• Sometimes row-ids or sequential numbers are assigned as keys to identify the

rows in a table
• Called artificial key or surrogate key

Slide 5- 77
Formal Definitions - Schema

• The Schema (or description) of a Relation:

• Denoted by R(A1, A2, .....An)
• R is the name of the relation
• The attributes of the relation are A1, A2, ..., An
• Example:
CUSTOMER (Cust-id, Cust-name, Address, Phone#)
• CUSTOMER is the relation name
• Defined over the four attributes: Cust-id, Cust-name, Address, Phone#
• Each attribute has a domain or a set of valid values.
• For example, the domain of Cust-id is 6 digit numbers.

Slide 5- 78
Formal Definitions - Tuple

• A tuple is an ordered set of values (enclosed in angled brackets ‘< … >’)

• Each value is derived from an appropriate domain.
• A row in the CUSTOMER relation is a 4-tuple and would consist of four values,
for example:
• <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-
2000">
• This is called a 4-tuple as it has 4 values
• A tuple (row) in the CUSTOMER relation.
• A relation is a set of such tuples (rows)

Slide 5- 79
Formal Definitions - Domain
◼ A domain has a logical definition:
◼ Example: “USA_phone_numbers” are the set of 10 digit phone numbers valid
in the U.S.
◼ A domain also has a data-type or a format defined for it.
◼ The USA_phone_numbers may have a format: (ddd)ddd-dddd where each d is
a decimal digit.
◼ Dates have various formats such as year, month, date formatted as yyyy-mm-
dd, or as dd mm,yyyy etc.

◼ The attribute name designates the role played by a domain in a relation:

◼ Used to interpret the meaning of the data elements corresponding to that
attribute
◼ Example: The domain Date may be used to define two attributes named
“Invoice-date” and “Payment-date” with different meanings 80
Formal Definitions - State

• The relation state is a subset of the Cartesian product of the domains

of its attributes
• each domain contains the set of all possible values the attribute can take.
• Example: attribute Cust-name is defined over the domain of character
strings of maximum length 25
• dom(Cust-name) is varchar(25)
• The role these strings play in the CUSTOMER relation is that of the
name of a customer.

Slide 5- 81
 Formal Definitions - Summary
• Formally,
• Given R(A1, A2, .........., An)
• r(R)  dom (A1) X dom (A2) X ....X dom(An)
• R(A1, A2, …, An) is the schema of the relation
• R is the name of the relation
• A1, A2, …, An are the attributes of the relation
• r(R): a specific state (or "value" or “population”) of relation R – this is a set of
tuples (rows)
• r(R) = {t1, t2, …, tn} where each ti is an n-tuple
• ti = <v1, v2, …, vn> where each vj element-of dom(Aj)

Slide 5- 82
Formal Definitions - Example
• Let R(A1, A2) be a relation schema:
• Let dom(A1) = {0,1}
• Let dom(A2) = {a,b,c}
• Then: dom(A1) X dom(A2) is all possible combinations:
{<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> }

• The relation state r(R)  dom(A1) X dom(A2)

• For example: r(R) could be {<0,a> , <0,b> , <1,c> }
• this is one possible state (or “population” or “extension”) r of the relation R, defined
over A1 and A2.
• It has three 2-tuples: <0,a> , <0,b> , <1,c>

Slide 5- 83
Definition Summary
Informal Terms Formal Terms

Table Relation
Column Header Attribute
All possible Column Domain
Values
Row Tuple

Table Definition Schema of a Relation

Populated Table State of the Relation
Slide 5- 84
Example – A relation STUDENT

Slide 5- 85
Relational Data Model(Cont’d)

The building blocks of the relational data model are:

1. Relation: A relation is nothing but a table with rows and columns

2. Attributes: Properties used to describe each Entity or real world object.They
define a relation.
3. Tuple: Each row in a relation is called Tuple
4. Domain: A domain is a unique set of values permitted for an attribute in a table.
5. Degree: The number of attributes in a relation
6. Cardinality: The number of tuples in a relation

86
87
88
Types of Relations

• Base Relation (Base Table)

• A Named Relation corresponding to an entity in the conceptual schema, whose tuples are
physically stored in the database

• View (Unnamed Relation)

• A View is the dynamic result of one or more relational operations operating on the base
relations to produce another virtual relation that does not actually exist as presented
• produced upon request by a particular user at the time of
request
• can be created from single or different relations by extracting some attributes and records
with or without conditions

89
Purpose of View
• Hides unnecessary information from users: since only part of the base relation (Some
collection of attributes, not necessarily all) are to be included in the view

• Provide powerful flexibility and security: since unnecessary information will be hidden
from the user there will be some sort of data security.

• Provide customized view of the database for users: each user is going to be interfaced with
their own preferred data set and format by making use of the Views

90
Relation

Characteristics Of Relations Unique Relation Name

Atomic Value
Each Row Unique
Each Column Unique
Tuples are unordered
• Not all tables qualify as relations Requirements: Order of Rows
Every row has key

1. Each relation (or table) in a database has a unique name.

2. An entry at the intersection of each row and column is atomic (or single valued). There
can be only one value associated with each attribute on a specific row of a table; no
multivalued attributes are allowed in a relation.

3. Each row (or tuple) is unique; no two rows in a relation can be identical.

4. Each attribute (or column) within a table has a unique name.

91
Relation

Characteristics Of Relations
5. Ordering of tuples in a relation r(R):
The tuples are not considered to be ordered, even though they appear to be in
the tabular form.
6. Ordering of attributes in a relation schema R (and of values within each
tuple):
We will consider the attributes in R(A1, A2, ..., An) and the values in t=<v1, v2,
..., vn> to be ordered .
(However, a more general alternative definition of relation does not require this
ordering).
7. Key: Every table must have a unique key to identify each row.

92
Same state as previous Figure (but with different order of
tuples)

Slide 5- 93
Relation

Characteristics Of Relations
8. Each value in a tuple must be from the domain of the attribute for that column
◼ If tuple t = <v1, v2, …, vn> is a tuple (row) in the relation state r of R(A1, A2, …, An)
◼ Then each vi must be a value from dom(Ai)

◼ A special null value is used to represent values that are unknown or inapplicable to
certain tuples.

94
Correspondence with ER Model

• Relations (tables) correspond with entity types and with many-to-many relationship
types

• Rows correspond with entity instances and with many- to-many relationship instances
• Columns correspond with attributes
• NOTE: The word relation (in relational database) is NOT the same the word
relationship (in ER model)

95
 Relational Keys

Key
• A key is an attribute or sets of attributes which helps un uniquely identify the rows
of a table. It also helps in establishing relationship among tables.
Types of Keys
1. Super Key
2. Candidate Key
3. Primary Key
4. Foreign Key
5. Composite Key

96
Relational Keys
• Superkey of R:
• Is a set of attributes SK of R with the following condition:
• No two tuples in any valid relation state r(R) will have the same value for
SK
• That is, for any distinct tuples t1 and t2 in r(R), t1[SK]  t2[SK]
• This condition must hold in any valid state r(R)

Slide 5- 97
Super Key
Relational Keys
• Key of R:
• A "minimal" superkey
• That is, a key is a superkey K such that removal of any attribute from K results
in a set of attributes that is not a superkey (does not possess the superkey
uniqueness property)
◼ Example: Consider the CAR relation schema:
◼ CAR(State, Reg#, SerialNo, Make, Model, Year)

◼ CAR has two keys:

◼ Key1 = {State, Reg#}

◼ Key2 = {SerialNo}

◼ Both are also superkeys of CAR

◼ {SerialNo, Make} is a superkey but not a key.

◼ In general:
◼ Any key is a superkey (but not vice versa)
Slide 5- 99
◼ Any set of attributes that includes a key is a superkey
Candidate Key
Primary Key

• Primary key is a column that uniquely identify every row in that table.
Examples include employee numbers, social security numbers, etc.

• The Primary Key can’t be a duplicate meaning the same value can’t appear
more than once in the table. A table cannot have more than one primary key.

101
Alternate/Secondary Key
Foreign key

103
Composite key
Figure - Schema for four relations (Pine Valley Furniture)
105
Relation Schema
Relation Schema: Basic Information Describing a Table

106
Relational Database Schema
• Relational Database Schema:
• A set S of relation schemas that belong to the same database.
• S is the name of the whole database schema
• S = {R1, R2, ..., Rn}
• R1, R2, …, Rn are the names of the individual relation schemas within the
database S
• Following slide shows a COMPANY database schema with 6 relation
schemas

Slide 5- 107
COMPANY Database Schema

Slide 5- 108
 Integrity Constraints
• Constraints are conditions that must hold on all valid relation states.
• These are the rules or constraints applied to the database to keep data stable, accurate or
consistent.

Types of Integrity Constraints

1. Domain Constraint

2. Entity Constraint

3. Referential Integrity Constraint

4. Key Constraint

5. Enterprise Integrity Constraints:

109
 Integrity Constraints (Cont'd)
• These are the rules or constraints applied to the database to keep data stable, accurate or
consistent.
Domain Constraints
• A domain is a set of allowable values for an attribute. It acts like a data type combined
with additional rules or restrictions.
• No value of the attribute should be beyond the allowable limits
For example:
• An attribute "Age" might have a domain of integers between 0 and 150.
• An attribute "Email" might have a domain of strings that match a valid email format
(e.g., containing "@" and a domain name).
• An attribute "Gender" might have a domain restricted to values like "M", "F", or
"Other".
110
 Integrity Constraints (Cont'd)
Entity Integrity

• Entity integrity is a rule that states that no primary key value in a relational table
can be null, and all primary key values must be unique. This ensures that every
row in a table can be distinctly identified and accessed.
• Primary Key: A primary key is a column (or a set of columns) chosen to
uniquely identify each row in a table.
• Null Values: The primary key cannot contain null values because a null
value represents the absence of data, which would prevent the unique
identification of a row.
• Uniqueness: Every value in the primary key column(s) must be unique
across all rows in the table. This prevents duplicate rows, ensuring that each
entity (e.g., a customer, product, or transaction) is represented only once.

Example:
• In a Students table, student_id is the primary key. No two students can have the
same ID, and every student must have an ID. 111
Integrity Constraints (Cont'd)
Integrity Constraints (Cont'd)

Referential Integrity – rule that states that any foreign key value (on the relation of the
many side) MUST match a primary key value in the relation of the one side. (Or the foreign

key can be null)

• A foreign key in one table must either be NULL or match an existing primary key value in
the referenced table.

• Ensures relationships between tables remain valid via foreign keys.

113
Integrity Constraints (Cont'd)
Customer

CustomerID (PK) Name

1 Alice
2 Bob
Order

OrderID (PK) CustomerID (FK) Amount

101 1 50
102 2 75

You can’t insert an order with CustomerID = 3 into Orders because no customer with
CustomerID = 3 exists in Customers.

114
 Integrity Constraints (Cont'd)

Enforcement Mechanism of Referential Integrity

Insertion: When adding a row to the child table (e.g., Orders), the foreign key value
must already exist in the parent table (e.g., Customers), or the insert is rejected.

115
 Integrity Constraints (Cont'd)
Enforcement Mechanism of Referential Integrity
Deletion/Update in the Parent Table: If you try to delete or modify a primary key value
in the parent table (e.g., Customers) that’s referenced elsewhere, the database can
respond in several ways, depending on the constraint’s configuration:
• Restrict: Prevent the deletion or update (e.g., can’t delete CustomerID = 1 if it’s
in Orders).
• Cascade: Propagate the change to the child table (e.g., delete all orders for
CustomerID = 1 or update CustomerID in Orders if it changes in Customers).
• Set Null: Set the foreign key to null in the child table (e.g., set CustomerID to null
in Orders), if nulls are allowed.
116
Displaying a relational database schema and its constraints

• Each relation schema can be displayed as a row of attribute names

• The name of the relation is written above the attribute names
• The primary key attribute (or attributes) will be underlined
• A foreign key (referential integrity) constraints is displayed as a directed arc
(arrow) from the foreign key attributes to the referenced table
• Can also point the the primary key of the referenced relation for clarity
• Next slide shows the COMPANY relational schema diagram

Slide 5- 117
Referential Integrity Constraints for COMPANY database

Slide 5- 118
Integrity Constraints (Cont'd)

Key Constraint (or Uniqueness Constraint)

• This constraint ensures that a designated key (typically the primary key or a candidate
key) contains unique values across all rows in a table.
• A candidate key is any attribute (or set of attributes) that could uniquely identify rows,
even if it’s not chosen as the primary key.
• Unlike entity integrity, which also includes the "no nulls" rule for primary keys, the key
constraint focuses purely on uniqueness and can apply to candidate keys or other unique
columns.

119
Integrity Constraints (Cont'd)

Example:

EmpID (PK) SSN (Unique) Name

1 123-45-6789 Alice
2 987-65-4321 Bob

SSN (Social Security Number) is a candidate key with a uniqueness constraint, even
though EmpID is the primary key. Duplicate SSNs are not allowed.

120
 Integrity Constraints (Cont'd)

• Enterprise Integrity Constraints:

• Additional rules specified by the users or database administrators of a database are

incorporated

Here are some examples.

• A class can have a maximum of 30 students.

• A teacher can teach a maximum of four classes per semester.

• An employee cannot take part in more than five projects.

• The salary of an employee cannot exceed the salary of the employee’s manager.

121
Populated database state

• Each relation will have many tuples in its current relation state
• The relational database state is a union of all the individual relation states
• Whenever the database is changed, a new state arises
• Basic operations for changing the database:
• INSERT a new tuple in a relation
• DELETE an existing tuple from a relation
• MODIFY an attribute of an existing tuple
• Next slide shows an example state for the COMPANY database

Slide 5- 122
Populated database state for COMPANY

Slide 5- 123
Update Operations on Relations
• INSERT a tuple.
• DELETE a tuple.
• MODIFY a tuple.
• Integrity constraints should not be violated by the update operations.
• Several update operations may have to be grouped together.
• Updates may propagate to cause other updates automatically. This may be
necessary to maintain integrity constraints.

Slide 5- 124
Update Operations on Relations
• In case of integrity violation, several actions can be taken:

• Cancel the operation that causes the violation (RESTRICT or REJECT option)

• Perform the operation but inform the user of the violation

• Trigger additional updates so the violation is corrected (CASCADE option,

SET NULL option)

• Execute a user-specified error-correction routine

Slide 5- 125
Possible violations for each operation
• INSERT may violate any of the constraints:
• Domain constraint:
• if one of the attribute values provided for the new tuple is not of the specified
attribute domain
• Key constraint:
• if the value of a key attribute in the new tuple already exists in another tuple in
the relation
• Referential integrity:
• if a foreign key value in the new tuple references a primary key value that does
not exist in the referenced relation
• Entity integrity:
• if the primary key value is null in the new tuple

Slide 5- 126
Possible violations for each operation
• DELETE may violate only referential integrity:
• If the primary key value of the tuple being deleted is referenced from other tuples in the
database
• Can be remedied by several actions: RESTRICT, CASCADE, SET NULL (see Chapter 8 for
more details)
• RESTRICT option: reject the deletion
• CASCADE option: propagate the new primary key value into the foreign keys of the referencing
tuples
• SET NULL option: set the foreign keys of the referencing tuples to NULL

• One of the above options must be specified during database design for each foreign key
constraint
Slide 5- 127
Possible violations for each operation
• UPDATE may violate domain constraint and NOT NULL constraint on an
attribute being modified
• Any of the other constraints may also be violated, depending on the attribute being
updated:
• Updating the primary key (PK):
• Similar to a DELETE followed by an INSERT
• Need to specify similar options to DELETE
• Updating a foreign key (FK):
• May violate referential integrity
• Updating an ordinary attribute (neither PK nor FK):
• Can only violate domain constraints

Slide 5- 128
Exercise
Consider the following relations for a database that keeps track of student
enrollment in courses and the books adopted for each course:
STUDENT(SSN, Name, Major, Bdate)
COURSE(Course#, Cname, Dept)
ENROLL(SSN, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_ISBN)
TEXT(Book_ISBN, Book_Title, Publisher, Author)
Draw a relational schema diagram specifying the foreign keys for this
schema.

Slide 5- 129
Logical database design
• Logical design is the process of constructing a model of the information used in an
enterprise based on a specific data model (e.g. relational, hierarchical or network or
object), but independent of a particular DBMS and other physical considerations.

• is the process of transforming (or mapping) a conceptual schema of the application

domain into a schema for the data model underlying a particular DBMS, such as the
relational or object-oriented data model.

130
Activities during logical database design
1. Mapping Conceptual Model to Relational Schema

• Entity to Table Mapping: Each entity in the conceptual model (ER diagram) is
transformed into a table in the relational schema. Attributes of the entity become
columns of the table.

• Derive relations for logical data model

2. Validate relations using normalization

• Organize tables to eliminate redundancy via normal forms (1NF, 2NF, 3NF, BCNF).

• Analyze functional dependencies to ensure non-key attributes depend fully on primary

keys.

131
 Activities during logical database design (Cont’d)

3. Review and Refine

• Review the resulting logical schema to ensure it accurately represents the data
requirements and business rules.
• Stakeholder Review: Share the schema with stakeholders (users, developers, database
administrators) for feedback and validation. Ensure the model aligns with user
requirements and covers all entities/relationships from the conceptual model.
• Iterate and Refine: Based on feedback and analysis, iterate on the design, making
necessary adjustments to the tables, columns, relationships, and constraints.
• Review logical data model with user
• Validate relations against user transactions
4. Define integrity constraints

132
Converting ER Diagram to Relational Tables
• The first step before applying the rules in relational data model is converting the conceptual
design to a form suitable for relational logical model, which is in a form of tables .

Mapping ER Conceptual Database Design to Relational Model

1. Mapping of Strong Entity Types
2. Mapping of Weak Entity Types
3. Mapping of 1:1 Relationship Types
4. Mapping of 1:N Relationship Types
5. Mapping of M:N Relationship Type
6. Mapping of Multi-valued Attributes
7. Mapping of N-ary Relationship Types
133
Example: ER Diagram Schema for Company Database

134
Step 1 Mapping Regular Entities to Relations
• For each strong entity type E in the ER schema, create a relation R that includes all the simple
attributes of E.
• Entity Names will automatically be table names

• Choose one of the key attributes of E as primary key for R

• Atomic or single-valued or derived or stored attributes will be columns
• Composite attributes: the parent attribute will be ignored and the decomposed attributes (child
attributes) will be columns of the table.
• Multi-valued Attribute - will be mapped to a new table where the primary key of the main table will
be posted for cross referencing.

135
 Mapping regular entity :EMPLOYEE entity type with simple attributes

Name Sex Address

Salary

SSN Employee
Tel

Bdate

There will be Employee table with ssn, Fname, Lname, Salary,sex, bdate,address being the columns. The composite
attribute Name will be ignored as its decomposed attributes (FName and LName) are columns in the Employee Table.
(Assume there is Tel attribute in the ERD for the employee entity, though not shown on the original ERD).
The Tel attribute will be a new table as it is multi-valued.
EMPLOYEE SSN Fname Minit Lname Sex Address Salary Salary

Telephone SSN Tel

136
Mapping regular entity : PROJECT entity type with simple attributes

• There will be Project table with Number, and Name being the columns.
• .

Number Name

Project Number Name Location

Project

137
Mapping regular entity DEPARTMENT entity type with simple attributes

There will be Department t table with Number, Name , and location being the columns

The location attribute will be a new table as it is multi-valued

Name Location

Department Number Name Location Number

Department
Location Number Location

138
Step 2: Mapping of Weak Entity Types

• For each weak entity type W in the ER schema with owner entity type E, create a relation R and
include all simple attributes of W as attributes of R and also include primary key attributes of
the E as foreign key attributes W

• The primary key of W is the combination of the primary keys of E and the partial key of W

• Weak Entities Becomes a separate relation with a foreign key taken from the superior entity

• Primary key composed of:

• Partial identifier of weak entity

• Primary key of identifying relation (strong entity)

139
Example : Mapping of Weak Entity Type

Name Sex Address

Salary

Employee Name Sex

SSN

DEPENDEN
Bdate TS_OF
1
DEPENDENT

BDate
Relationship
EMPLOYEE
SSN Fname Minit Lname Sex Address Salary Salary

DEPENDENT SSN Name Bdate Relationship

140
Step 3: Mapping Binary Relationships

• One-to-One - Primary key on the mandatory side becomes a foreign key on the optional
side

• One-to-Many - Primary key on the one side becomes a foreign key on the many side

• Many-to-Many - Create a new relation with the primary keys of the two entities as its
primary key

141
Step 3.1 Mapping of 1:1 Relationship Types

• For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that
correspond to the entity types participating in R. There are three possible approaches:

1. the foreign key approach,

2. the merged relationship approach, and

3. the cross-reference or relationship relation approach.

142
Foreign key approach:

• Choose one of the relations say, S and include the primary key of T to the relation S as
a foreign key in R .

• It is better to choose an entity type with total participation in R in the role of S.

• Include all the simple attributes (or simple components of composite attributes) of the
1:1 relationship type R as attributes of S.

143
1:1 Relationship between Employee and Department in manages relationship type

Address
Name Sex
Number Name
Salary

SSN
1 1
Employee Manages Department

Bdate

Startdat Location
e

144
 1:1 Relationship between Employee and Department in manages relationship type

Relation Schema Approach 1

EMPLOYEE

SSN Fname Minit Lname Sex Address Salary Salary

DEPARTMENT
Number Name Mgr_ssn MgrStart
Date

145
1:1 Relationship between Employee and Department in manages relationship type

• In our example, we map the 1:1 relationship type MANAGES by choosing the
participating entity type DEPARTMENT to serve in the role of S, because its
participation in the MANAGES relationship type is total (every department has
a manager).

• We include the primary key of the EMPLOYEE relation as foreign key in the
DEPARTMENT relation and rename it MGRSSN.

• We also include the simple attribute STARTDATE of the MANAGES

relationship type in the DEPARTMENT relation and rename it
MGRSTARTDATE.
146
• Merged relation option:
• An alternative mapping of a 1:1 relationship type is possible by merging the two entity
types and the relationship into a single relation.

• This may be appropriate when both participations are total.

• Cross-reference or relationship relation option:

• The third alternative is to set up a third relation R for the purpose of cross-referencing.
The new table contains the primary keys of the two relations S and T representing the
entity types.

147
Step 4:Mapping of Binary 1:N Relationship Types

• For each regular binary 1:N relationship type R, identify the relation S that
represents the participating entity type at the N-side of the relationship type.

• Include as foreign key in S the primary key of the relation T that represents the
other entity type participating in R

• Include any simple attributes (or simple components of composite attributes) of

the 1:N relationship type as attributes of S.

Eg: For WORKS_FOR we include the primary key of the DEPARTMENT

relation as foreign key in the EMPLOYEE relation and call it DNO.

148
1:N Relationship between Employee and Department in works_for relationship type

Address
Name Sex
Number Name
Salary

SSN
N 1
Employee Works_for Department

Bdate

Startdat Location
e

149
 Relation Schemas

EMPLOYEE SSN Fname Minit Lname Sex Address Salary dnumber

Number Name Mgr_ssn MgrStartDat

DEPARTMENT e

150
Step 5:Mapping of Binary M:N Relationship Types
• For each binary M:N relationship type R, create a new relation S to represent R.

• Include as foreign key attributes in S the primary keys of the relations that represent the
participating entity types; their combination will form the primary key of S.

• Also include any simple attributes of the M:N relationship type (or simple components of
composite attributes) as attributes of S.
• The same approach should be used for relationships with degree greater than
binary.

151
Step 5:Mapping of Binary M:N Relationship Types(Cont..)

• In our example, we map the M:N relationship type WORKS_ON by creating the relation

WORKS_ON.

• We include the primary keys of the PROJECT and EMPLOYEE relations as foreign keys in

WORKS_ON and rename them PNO and ESSN, respectively.

• We also include an attribute HOURS in WORKS_ON to represent the HOURS attribute of

the relationship type. The primary key of the WORKS_ON relation is the combination of

the foreign key attributes {ESSN, PNO}.

152
Example :N:M Relationship between Employee and Project in works On
relationship type

153
Step 6: Mapping of Multivalued Attributes
• For each multivalued attribute A, create a new relation R.

• This relation R will include an attribute corresponding to A, plus the primary key
attribute K-as a foreign key in R-of the relation that represents the entity type or
relationship type that has A as an attribute.

• The primary key of R is the combination of A and K. If the multivalued attribute is

composite, we include its simple components.

154
Step 6: Mapping of Multivalued Attributes (Cont’d)

Eg:

• We create a relation DEPT_LOCATIONS.

• The attribute DLOCATION represents the multivalued attribute LOCATIONS of

DEPARTMENT, while DNUMBER-as foreign key represents the primary key of the
DEPARTMENT relation.

• The primary key of DEPT_LOCATIONS is the combination of {DNUMBER,

DLOCATION}.

155
Example Mapping multivalued valued attribute,department Location attribute

Address
Name Sex
Number Name
Salary

SSN
N 1
Employee Works_for Department

Bdate

Startdat Location
e

156
Example : Mapping multivalued valued attribute,department Location attribute (Cont..)

EMPLOYEE Number Name Mgr_ssn MgrStartDate

DEPT_LOCATION
dlocation dnumber

157
Step 7: Mapping of N-ary Relationship Types.

• For each n-ary relationship type R, where n > 2, create a new relation S to represent R.
• Include as foreign key attributes in S the primary keys of the relations that represent the
participating entity types.

• Also include any simple attributes of the n-ary relationship type (or simple components of
composite attributes) as attributes of S.

• The primary key of S is usually a combination of all the foreign keys that reference the
relations representing the participating entity types.

158
Example : Mapping a ternary relationship
(Next Slide)

159
 Supplier_Id quantity Project_Name
Supplier_
Name

Supplier supply Project

PartNo

Part
SUPPLIER
Supplier_Id Supplier_Name
PROJECT
Project_Name … …
PART
PartNo … …

SUPPLY Supplier_Id Project_Name PartNo quantity

160
• After converting the ER diagram in to table forms, the next phase is implementing the
process of normalization, which is a collection of rules each table should satisfy.

161
More Examples
Converting 1-1 Relationship

162
Mapping a regular entity

(a) CUSTOMER entity type with simple attributes

(b) CUSTOMER relation

163
Mapping a composite attribute

164
Mapping Weak Entity

165
Example of mapping an M:N relationship

166
Three Resulting Relations

167
Mapping a ternary relationship

168
Mapping a ternary relationship

169