Conceptual Data Modeling

DB specification independent of DB technology

Cannot be used to create a table directly

Chapter 2
Logical Data Modelin
Representing the database in
Immediately create tables
 Data Modeling
ting the database in terms of the databse technology used to implement the
ely create tables

Chapter 4
d to implement the DB.
Components of relational mo
Ò Data structure
É Tables (relations), rows, column
Ò Data manipulation
É Powerful SQL operations for retr
Ò Data integrity
É Mechanisms for implementing b
ional model

s, columns

ns for retrieving and modifying data

menting business rules that maintain in

ng data

maintain integrity of manipulated data

ated data
RELATION
Definition: A relation is a named, two-dimensional table of data.
Table: A table consists of rows (records) and columns (attribute or

ÒRequirements for a table to qualify as a relation:

ÉIt must have a unique name.
ÉEvery attribute value must be atomic (not multivalued, not com
ÉEvery row must be unique (can’t have two rows with exactly the
th
ÉAttributes (columns) in tables must have unique names.
ÉThe order of the columns must be irrelevant.
ÉThe order of the rows must be irrelevant.

Are these tables relations?

How to convert them to relations?
EMPLOYEE

Dept_ID Dept_Name
1 MIS
2 FIN
3 SCM

PK
Employee_ID
1
2
3
4
5
6
7
How to represent them?
PK Primary keys are unique identifiers of the relation. Examples include
How to represent?
FK Foreign keys are identifiers that enable a dependent relation (on the
How to represent?
Relationships: How to represent?

Two Ways

Graphical

EMPLOYEE
Employee_ID Employee_Name Employee_Age

DEPARTMENT
Dept_ID Dept_Name

EMPLOYEE SKILLS ASSIGNMENT both underlined bc it is an associative entity, both

Employee_ID Skill_ID

SKILLS
Skill_ID Skill_Name
mensional table of data.
and columns (attribute or field).

relation:

(not multivalued, not composite).

e two rows with exactly the same values for all their fields).
ave unique names.
levant.
elevant.

EMPLOYEE

Employee_Name Employee_Age Employee_Age

Mary 32 384
Sue 21 252
John 30 360
Smith 45 540
Joey 25 300
Smith 45 540

Employee_Name Employee_Age Employee_Age_Months

Mary 32 384
Sue 21 252
John 30 360
Smith 45 540
Joey 25 300
Smith 45 540
Smith 46 552

SKILLS when you have a multivalued attribute, turn it into a new t

Skill_ID Skill_Name
1 Data Entry
2 Dance
 3 Drama
4 Programming
5 Data analysis
6 Accounting

dentifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are u

s that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the rel

Employee_Age_Months Employee_Latitude Employee_Longitude

derlined bc it is an associative entity, both together create a composite primary key

 this is satisfied because at least one value is unique in the row

Employee Location Employee_skills

100,35 Data Entry, Dance, Drama
120,89 Programming
110, 56 Data analysis
100,36 Accounting
120,90 Data analysis
110, 57 Accounting

Employee_Latitude Employee_Longitude
100 35
120 89
110 56
100 36
120 90
110 57 even though they are the same they have diff primary key
110 57

valued attribute, turn it into a new table EMPLOYEE SKILL ASSIGNMENT

Employee_ID
1
1
 1
2
3
4
5
6

c. This guarantees that all rows are unique.

nt relation (on the one side of the relationship).

Dept_ID

arrows should always point the PK

arrow head should go from the FK to PK
 Correpondence to ER Models
ÒRelations (tables) correspond with entity types and with
ÒRows correspond with entity instances and with many-t
ÒColumns correspond with attributes.
ique in the row ÒNOTE: The word relation (in relational database) is NOT
NO

bad table

fixed table that is satisfied to be a relation

order of the columns are irrelevant
order of the rows are also irrelevant

re the same they have diff primary keys, so conditions satisfied

SSIGNMENT

Skill_ID
1
2
 3
4
5
6
4
5

Short-Text Statements (a different example)

EMPLOYEE (Employee_ID, Employee Name, Employee Location, Dept_ID)

DEPARTMENT (Dept_ID, Department Name)
EMPLOYEE SKILL ASSIGNMENT (Skill_ID, Employee_ID)
SKILL (Skill_ID, Skill_Name)
ntity types and with many-to-many relationship types.
es and with many-to-many relationship instances.

nal database) is NOT the same as the word relationship (in E-R model).
(in E-R model).
INTEGRITY CONSTRAINTS
Rules limiting acceptable values or actions, whose purpose is to facilitate mai
ÒDomain Constraints
ÉDomain: Allowable values for an attribute

ÒEntity Integrity
ÉNo primary key attribute may be null. All primary k
What about these two tables? Is entity integriy enforced?

EMPLOYEE EMPLOYEE SKILL ASSIGNMENT

Employee ID Employee Na Employee Age Skill ID Emp ID

1 Mary 32 1 NULL
NULL Sue 21 NULL 2
3 John 30 1 3

What is NULL?
A value that must be assigned to an attribute when no other value applies or when the applicable valu
 Examples?

ÒReferential Integrity
ÉRules that maintain consistency between the rows
 to facilitate maintaining the accuracy and integrity of the data in DB and to enforce b

Domain Constraint: All values that appear in a column of a relation m

CustomerID

All primary key fields MUST contain data values.

L ASSIGNMENT

when the applicable value is unknown.

en the rows of two related tables.
n DB and to enforce business rules

column of a relation must be from the same domain

During SQL Implementation

Implementation
REFERENTIAL INTEGRITY
Primary Key unique identifier of the relation This guarantees that all rows are unique.
Foreign Key are identifiers that enable a dependent relation (many side of the relationship) to refer to the parent s
Key can be simple or composite
Definition
Referential integrity requires that a foreign key must have a matching primary key or it must be

EMPLOYEE
PK
Employee ID Employee Na Employee Age
1 Mary 32
2 Sue 21
3 John 30
4 Smith 45
5 Smith 45
6 Joey 25

Which one is the PARENT RELATION?

Which one is the DEPENDENT RELATION?
What if we want to add a new employee named "Srikanth"?
 all rows are unique.
elationship) to refer to the parent side (one side of the relationship)

ng primary key or it must be null.

DEPARTMENT
FK PK
Dept ID Dept ID Dept Name
1 1 MIS
1 2 FIN
2 3 SCM
2 4 LEAD
3
3
How to depict referential Integrity?
In the relation diagrams of this chapter, arrows between foreign and pr

Refere
integrity co
are draw
arrows
dependent
tabl

In SQL, Referential integrity constraints are implemented with foreign

ween foreign and primary keys depict referential integrity constraints.

Referential
integrity constraints
are drawn via
arrows from
dependent to parent
table

mented with foreign key to primary key references.

constraints.
REFERENTIAL INTEGRITY - DELETE RULES
What happenes to the DEPENDENT RELATION when we delete a row in the PARENT RELATION?

ÐRestrict–don’t allow delete of “parent

ÐCascade–automatically delete “depend
ÐSet-to-Null–set the foreign key in the d

Revisiting referential integrity definition:foreign key must have a matching primary key o

Restrict

EMPLOYEE

Employee ID Employee Na Employee AgeEmployee Dept ID

1 Mary 32 1
2 Sue 21 1
3 John 30 2
4 Smith 45 2
4 Smith 45 3
6 Joey 25 3

Cascade

EMPLOYEE

Employee ID Employee Na Employee AgeEmployee Dept ID

1 Mary 32 1
2 Sue 21 1
3 John 30 2
4 Smith 45 2
4 Smith 45 3
6 Joey 25 3

Set-to-Null

EMPLOYEE

Employee ID Employee Na Employee AgeEmployee Dept ID

1 Mary 32 1
2 Sue 21 1
3 John 30 2
4 Smith 45 2
4 Smith 45 3
6 Joey 25 3
 of “parent” side if related rows exist in “dependent
ete “dependent” side rows that correspond with the
key in the dependent side to null if deleting from th

e a matching primary key or it must be null.

DEPARTMENT

Dept ID Dept Name

1 MIS
2 FIN
3 SCM

DEPARTMENT

Dept ID Dept Name

1 MIS
2 FIN
3 SCM

DEPARTMENT

Dept ID Dept Name

1 MIS
2 FIN
3 SCM
n “dependent” side
pond with the “parent” side row to be deleted
eting from the parent side à not allowed for weak e
deleted
wed for weak entities