RELATIONAL DATA STRUCTURES

(1)

RELATIONAL DATA STRUCTURES

Chapter 3 – part I

(2)

Relational Model Concepts

⚫ The concept of a Relation

◦ The strength of the relational approach to data management comes from the formal foundation provided by the theory of

relations

◦ A Relation is a mathematical concept

based on the ideas of sets

(3)

How is a Relational DB different from a File?

File Table Relational DB

File Table Relation

Record Row Tuple

Field Column Attribute

Records may be different types.

Field order matters Record order may

matter

No primary key or unique identifier field

(4)

Informal Definitions

⚫ A relation looks like a table of values.

◦ Tuple – one row of a relation

◦ Attribute – one column of a relation

◦ Relation – the whole table

◦ Domain of an Attribute – all the values that attribute can have

(5)

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

◦ Sometimes row-ids or sequential numbers are assigned as keys to identify the rows in a table

● Called artificial key or surrogate key

(6)

Informal Formal Translation

Informal Terms Formal Terms

Table Relation

Column Header Attribute

All possible Column Values Domain

Row Tuple

Table Definition Schema of a Relation

Populated Table State of the Relation

(7)

FORMAL TERMS

(8)

Domain

⚫ All the possible values an attribute can take

◦ Remember your mathematics?

⚫ A set of Atomic Values

⚫ Indivisible

⚫ Atomic Values have name, type, format

⚫ Name

⚫ “USA_phone_numbers”

⚫ Data type

⚫ the set of U.S. valid 10 digit phone numbers

⚫ Format

⚫ (ddd)ddd-dddd, where each d is a decimal digit.

(9)

Domain types

⚫ Simple Domain is a set of atomic values

⚫ Composite Domains

⚫ Cartesian product of simple domains

◦ Ex., Date = Month x Day x Year where Month = {1, 2, …, 12}

Day = {1, 2, … 31}

Year = { 1900, 1901, …}

E.g., 10 9 1999

(10)

Relational Database Schema

⚫ Informally, the table definition

⚫ COMPANY relational db schema

(11)

Relation Schema

⚫

Relation schema: R or R(A

₁

, A

₂

, …, A

_n

)

• Fixed set of attributes A

₁

, A

₂

, …, A

_n

• Each attribute A

_j

corresponds to exactly one of the underlying domains D

_j

(j = 1, 2, …, n),

• Not necessarily distinct D

_j

= domain( A

_j

)

EMP( EMPNO, NAME, DNO, JOB, MGR, SAL, COMMISSION)

Note: EMPNO and MGR take values from the same domain, the set of valid employee numbers

⚫

n = The number of attributes in the relation schema (degree of the relation)

Degree 1 Unary

Degree 2 Binary Degree 3 Ternary Degree n n-ary

(12)

Tuple

⚫ An “ordered” set of attributes

⚫ Values derived from an appropriate domain

◦ Tuple in the EMPLOYEE relation

◦ <"John”, “X”,“Smith", “000-99-9999”>

⚫ A relation is an unordered set of such tuples

◦ Concrete realization, i.e. printed table does have an order.

◦ Abstract construct does not.

◦ Much easier to understand effects of operations if

no ordering is specified.

(13)

Example – A relation STUDENT

Same state, but with different tuple ordering

(14)

Relation Instance

⚫ An ordered list of attributes

◦ R(A

₁

, A

₂

, …, A

_n

)

⚫ With a set of values

◦ Each tuple consists of attribute-value pairs (a

_j

, v

_j

), where j = 1, 2, …, n

◦ v

_j

is a value from the unique domain D

_j

that is associated with that attribute.

⚫ Mapped to a domain

◦ r =

^{t₁^{, t}₂^{, …, t}_m^}

◦ Each tuple t is a mapping from R → D

where, R = R(A₁, A₂, …, A_n) and

D = domain(A₁) ∪ domain(A₂) ∪ … ∪ domain(A_n)

(15)

Properties of Relations

⚫

Ordering of tuples

◦ Relation is a set

◦ Sets have no order

◦ Relation is not sensitive to ordering of tuples

⚫

Uniqueness of tuples

◦ No duplicate tuples in a relation!

⚫

Unknown values

◦ NULL value – used when value can’t be known or does not exist

⚫

Interpretation

◦ Relation is an assertion of facts

◦ Each tuple can be thought of as a fact about the world

● Or as a predicate in first order logic

⚫

Relations may not contain repeating groups.

◦ Such a relation is said to be normalized or in 1

^st

normal form.

(16)

Normalization of Repeating Groups

Before : S# (PQ)*

P# QTY S1 P1 300

P2 200 P3 400 S2 P1 300 P2 400 S3 P2 200

3 “records”

After : S# P# QTY

S1 P1 300 S1 P2 200 S1 P3 400 S2 P1 300 S2 P2 400 S3 P2 200

6 “records”

(17)

SuperKey

⚫ A subset of the relation attributes where all tuple values must be

distinct.

1. SK is a set of attributes 2. t ₁ and t ₂ are tuples

3. Then, t ₁ [SK] ≠ t ₂ [SK]

(18)

SuperKey Example

EMP( EMPNO, NAME, DNO, JOB, MGR, SAL, COMMISSION) What are the SuperKey(s) of this relation?

NAME

EMPNO, NAME, DNO, JOB, MGR, SAL, COMMISSION EMPNO, NAME, DNO, JOB

EMPNO, NAME

EMPNO, NAME, SAL, COMMISSION

EMPNO

(19)

Key – formal definition

⚫ A SuperKey where no proper subset of the attributes is a SuperKey.

⚫ A SuperKey K such that removal of any attribute(s) from K results in a set of

attributes that is not a SuperKey …

⚫ i.e., does not possess the SuperKey

uniqueness property

(20)

Key Example

EMP( EMPNO, NAME, DNO, JOB, MGR, SAL, COMMISSION) What are the Key(s) of this relation?

NAME EMPNO

Why isn’t EMPNO, NAME a Key?

A Key is a minimal SuperKey.

(21)

Candidate and Primary Key

⚫ A Key is a minimal SuperKey.

⚫ A relation may have more than one Key, called Candidate Keys

⚫ A Primary Key is an arbitrarily chosen Candidate Key

◦ Generally the smallest of the candidate keys

(in terms of size)

(22)

CAR table

⚫ Two Candidate keys:

◦ {EngSerialNo} akin to a VIN

◦ {LicenseNo} contains State and Reg #

⚫ What would you choose for the Primary Key?

(23)

Summary

⚫ Any Key is a SuperKey (but not vice versa)

⚫ Any set of attributes that includes a key is a SuperKey

⚫ A minimal SuperKey is also a Key

⚫ Is every relation required to have a

Primary Key?

(24)

CHAPTER 9 OUTLINE

Relational Database Design Using ER-to-Relational Mapping

Mapping EER Model Constructs to Relations

(25)

Relational Database

Design by ER- and EER-to-Relational Mapping

⚫ Design a relational database schema

◦ Based on a conceptual schema design

⚫ Algorithm to convert the basic ER model constructs into relations

⚫ Additional steps for EER model

(26)

Relational Database Design Using

ER-to-Relational Mapping

(27)

E-R to Relational Mapping

1. Regular entity type

2. Weak entity type

3. Binary M:1 relationship

4. Binary 1:1 relationship

5. Binary M:N relationship

6. Multi-valued attributes

(28)

An ERD Example

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Regular entity?

Composite attribute?

Derived attribute?

Multi-value d attribute?

Weak entity?

M:1

relationship?

1:1

relationship?

M:N

(29)

ER-to-Relational Mapping Algorithm

⚫ Step 1: Mapping of Regular Entity Types

◦ For each regular entity type, create a relation R that includes all the simple attributes of E

◦ Called entity relations

• Each tuple represents an entity instance

(30)

Step 1: Regular Entities

⚫ Map each regular entity type into a relation.

◦ Each simple attribute of the entity type maps to an attribute of the relation.

◦ Each composite attribute will be broken into many simple attributes.

◦ We’ll treat Multi-valued attributes later.

◦ Derived attributes, by definition, can be derived, and therefore are not necessary to be represented.

◦ The primary key of the entity type maps to the primary

key of the relation.

(31)

An ERD Example – Step 1

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Primary keys are underlined.

Department (Dept#, Dname, City)

Employee (Emp#, First, MI, Last, Salary) Project (J#, Jname, City)

What happened to Name?

How about Phone, and Totqty?

(32)

ER-to-Relational Mapping Algorithm

⚫ Step 2: Mapping of Weak Entity Types

◦ For each weak entity type, create a relation R and include all simple attributes of the entity type as attributes of R

◦ Include primary key attribute of owner as

foreign key attributes of R

(33)

Step 2: Weak Entities

⚫ The identifying relationship of a weak entity is of course a Many-to-One relationship,

⚫ The weak entity type must “fully” participate in its identifying relationship.

⚫ Each weak entity type maps into a relation.

◦ Attributes are treated as usual.

◦ Introduce a foreign key that references the primary key of its identifying relation.

◦ The primary key is the primary key of its

identifying relation plus its own partial key .

(34)

An ERD Example – Step 2

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M M

1

M M

Dependent (Emp#, Dname#, Gender)

Foreign key Emp# References Employee

(35)

ER-to-Relational Mapping Algorithm

⚫ Step 3: Mapping of Binary 1:N Relationship Types

◦ For each regular binary 1: N relationship type

• Identify relation that represents participating entity type at N-side of relationship type

• Include primary key of other entity type as foreign key in S

• Include simple attributes of 1:N relationship type as

attributes of S

(36)

Step 3: M:1 Relationships

⚫ Foreign Key approach

⚫ Each regular Many-to-One relationship type,

◦ Add key attribute from “1” side as a foreign key to the relation on the

“many” side

⚫ Do not need to introduce a

separate relation.

(37)

An ERD Example – Step 3

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ_

MAN AGE PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Employee (Emp#, First, MI, Last, Salary, Dept#) Foreign key Dept# references Department Project (J#, Jname, City, Manager#)

Foreign key Manager# references Employee

Foreign keys don’t have to have the same name as their PK

(38)

ER-to-Relational Mapping Algorithm

⚫ Step 4: Mapping of Binary 1:1 Relationship Types

◦ For each binary 1:1 relationship type

• Identify relations that correspond to entity types participating in R

◦ Possible approaches:

• Foreign key approach

• Merged relationship approach

• Cross-reference or relationship relation

approach

(39)

Step 4: 1:1 Relationships

⚫ Foreign Key approach

◦ Similarly to Many-to-One relationship.

● Partial participation side is like Many side of relationship

● Key attribute from partial participation side is added as foreign key to full participation side.

⚫ Merged approach

◦ Combine into one relation / table

⚫ Relationship relation (cross reference)

⚫

Create a new table of foreign keys to relate the

two tables

(40)

An ERD Example – Step 4

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Department (Dept#, Dname, City, Manager#)

Foreign key Manager# references Employee

Foreign Key approach

(41)

An ERD Example – Step 4

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Department (Dept#, Dname, City, Mgr#, First, MI, Last, Salary)

All Manager information is now a part of Department

Merged approach

Is this a desirable solution?

(42)

An ERD Example – Step 4

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

DeptMgr (Dept#, Manager#)

New relation has a compound key that refers (FK) to both relations.

Cross Reference approach

(43)

ER-to-Relational Mapping Algorithm

⚫ Step 5: Mapping of Binary M:N Relationship Types

◦ For each binary M : N relationship type

• Create a new relation S

• Include primary key of participating entity types as foreign key attributes in S

• Include any simple attributes of M:N relationship

type

(44)

An ERD Example – Step 5

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Proj_Work (Emp#, J#)

Foreign key Emp# References Employee Foreign key J# References Project

Attributes of Proj_Work would

(45)

ER-to-Relational Mapping Algorithm

⚫ Step 6: Mapping of Multivalued Attributes

◦ For each multivalued attribute

• Create a new relation

• If the multivalued attribute is composite, include its simple components

• Create foreign key to relationship or entity it belongs to

• The primary key is the combination of all the attributes.

• Or some of the attributes, if compound.

(46)

An ERD Example – Step 6

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Emp_Phone (Phone, Emp#)

Foreign key Emp# References Employee

(47)

Step 6: Another example

Compound Multivalued Attribute

Customer (Cust#, name)

Credit_Cards (Cust#, Card#, Expiration)

Foreign key Cust# References Customer

CUSTOMER Cust#

NAME CREDIT_

CARDS CARD

# EXPIRA

TION

(48)

An ERD Example – Summary

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M N

1

M M

Employee (Emp#, First, MI, Last, Salary, Dept# ^(FK)) Project (J#, Jname, City, Manager# ^(FK))

Department (Dept#, Dname, City, Manager#^(FK)) Dependent (Emp#^(FK), Dname#, Gender)

Proj_Work (Emp# ^(FK), J# ^(FK)) Emp_Phone (Phone, Emp# ^(FK), )

(49)

An ERD Example – Summary

DEPARTME NT

EMPLOYEE WOR

K_IN MAN

AGE

1

DEPENDEN T EMP_

DEP

PROJECT PROJ

_MA NAG

E PROJ _WO RK DEPT

#

CITY DNA ME

EMP#

NAM E SALA

RY PHO

NE FIRST

MI LAST

DNA ME

GEN DER J#

JNAM E CITY

TOT QTY M

1

M M

1

M M

Employee (Emp#, First, MI, Last, Salary, Dept# ^(FK)) Project (J#, Jname, City, Manager# ^(FK))

Department (Dept#, Dname, City, Manager#^(FK)) Dependent (Emp#^(FK), Dname#, Gender)

Proj_Work (Emp# ^(FK), J# ^(FK)) Emp_Phone (Phone, Emp# ^(FK), )