RELATIONAL DATA STRUCTURES

(1)

RELATIONAL DATA STRUCTURES

Chapter 3 – part I

1

(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

2

(3)

How is a Relational DB different from a File?

3

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

4

(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

5

(6)

Informal  Formal Translation

6

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

7

(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.

8

(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

9

(10)

Relational Database Schema

 Informally, the table definition

 COMPANY relational db schema

10

(11)

Relation Schema

11



Relation schema: R or R(A

1

, A

2

, …, A

n

)

• Fixed set of attributes A

1

, A

2

, …, 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

13

Same state, but with different tuple

ordering

(14)

Relation Instance

14

 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.

15

(16)

Normalization of Repeating Groups

Before: S# (PQ)*

P# QTY S1P1300

P2200 P3400 S2P1300 P2400 S3P2200

3 “records”

After: S# P#

QTY

S1P1300 S1P2200 S1P3400 S2P1300 S2P2400 S3P2200

6 “records”

16

(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]

17

(18)

SuperKey Example

18

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

19

(20)

Key Example

20

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)

21

(22)

CAR table

22

 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?

23

(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

ER to Relational Mapping 27

(28)

An ERD Example

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

NAME SALARY PHONE FIRST

MI LAST

DNAME GENDER J#

JNAME CITY

TOTQTY M

1

M N

1

M M

Regular entity?

Composite attribute?

Derived attribute?

Multi- valued attribute?

Weak entity?

M:1

relationshi p?

1:1

relationshi p?

M:N

relationshi

p?

(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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY

TOTQTY 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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY TOTQTY 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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY TOTQTY 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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY TOTQTY 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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY TOTQTY 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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY TOTQTY 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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY TOTQTY 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 also be added, if any

(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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY

TOTQTY 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#

EXPIRATION

(48)

An ERD Example – Summary

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY

TOTQTY 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

DEPARTMENT

EMPLOYEE

WORK_IN MANAGE

1

DEPENDENT EMP_DEP

PROJECT PROJ_MANAGE

PROJ_WORK DEPT#

CITY DNAME

EMP#

MI LAST

DNAME GENDER J#

JNAME CITY

TOTQTY 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), )

Quick Check 1 table / entity +1 table / n:m relationship

+1 table / multivalued attribute

All attributes mapped

(except derived attribute) All tables have PKs

Weak entity, M:N relationships and

multivalued attribute

tables have compound

PKs

(50)