1 The Relational Data Model - PowerPoint Presentation

Slide1

1

The Relational Data Model

David J. StuckiSlide2

Relational Model Concepts

2

Fundamental concept: the

relation

The Relational Model represents an entire database as a collection of

relations

Idea of a relation: A table of values

Each row is some collection of facts about an entity

Each column is a single attribute about the entities in the tableSlide3

Relational Model Concepts

3

Tuple

– one row of a relation

Attribute

– one column of a relation

Relation

– the whole tableDomain of an Attribute – all the values that attribute can haveSlide4

Domain

All the possible values an attribute can takeAtomicRemember your mathematics?Example domains:US Phone Numbers: the set of all 10-digit phone numbers

Local Phone Numbers: the set of all 7-digit phone numbers

Social Security Numbers: the set of all 9-digit numbers

Names: the set of all possible names

GPAs: the set of all possible values between 0.0 and 4.0

Data type: a format for a domainUS Phone Numbers: (ddd)

ddd-ddddGPAs: any real-valued number

4Slide5

Relation Schema

Denoted by R(A1,

A

2

, ...,

A

n)Consists of a relation name and a list of attributesDescription of what the relation should containSTUDENT relation:STUDENT(Name, SSN, Address, GPA)Can also include data type:

STUDENT(Name:String, SSN:Social_Security_Nums, Address: String, GPA: Real)Degree

(or arity) of a relation Number of attributes n of its relation schema

5Slide6

Relation

A particular set of tuples for a given relation schema (also known as a

relation state

)

Set of

n

-tuples r = {t1,

t2, ..., tm}The “state” of the relation is its current configuration (i.e. current contents)Each

tuple in the set is an ordered list of valuesEach element of the tuple corresponds to a particular

attribute

for the relation

6Slide7

Characteristics of Relations

Ordering of tuplesRelation is a set

Sets have no order

Relation is not sensitive to ordering of

tuples

Uniqueness of

tuplesNo duplicate tuples in a relation!Unknown values

NULL value – used when value can’t be known or does not existInterpretationRelation is an assertion of factsEach

tuple can be thought of as a fact about the worldOr as a predicate in first order logic

7Slide8

Characteristics of Relations

Order of attributes and values is not that important As long as correspondence between attributes and values maintainedAlternative definition of a relation Tuple considered as a set of (<attribute>, <value>) pairs

Each pair gives the value of the mapping from an attribute

A

i

to a value

vi from dom(Ai)Use the first definition of relation

Attributes and the values within tuples are orderedSimpler notationBut alternative has application in later formalismsSlide9

Characteristics of Relations

Values in tuplesEach value in a tuple is atomicFlat relational modelComposite and multivalued attributes not allowed

First normal form

assumption

Multivalued attributes

Must be represented by separate relations

Composite attributes Represented only by simple component attributes in basic relational modelSlide10

Relational Model Constraints

A constraint is a restriction on the values in a database stateImplicit constraints (model-based constraints)Inherent in the data model itselfE.g. no duplicate

tuples

in a relation

Explicit constraints (schema-based constraints)

Can be explicitly enforced/expressed in the schema

Application-based constraints (business rules)Derived from miniworld represented by databaseCannot be explicitly enforced by the schema

Must be enforced by application programs themselves10Slide11

Schema-based constraints

Domain constraintsData type constraintEach attribute in a tuple may only take on a value from the domain of that attribute

11Slide12

Schema-based constraints

Key constraintsRemember – each tuple in a relation must be uniqueNo duplicate tuples

!

This means that no two

tuples

have the same combination of attributes for

all of their attributesUsually there is a subset of attributes that control uniquenessWe call this subset a superkeyDefinition: Let SK be a subset of attributes of the relation R that form a

superkey. Then for any two distinct tuples t1 and t2 in a relation state r of R: t1[SK] != t2[SK]

12Slide13

Schema-based constraints

Superkeys can have redundant attributesEx: {First Name, Last Name, SSN} could be a superkey

Don’t really need First Name and Last Name to be unique – SSN is guaranteed to be unique

keys

A key is a minimal

superkey

Remove one attribute from a key and it is no longer a superkey!A key is always a

superkey, but not all superkeys are keysThere can be more than one key in a relationEx: {SSN} {Student ID}Common to identify one of these keys as

as a primary keyPrimary key uniquely identifies tuples in a relation to other relations and outside applications

13Slide14

Schema-based constraints

Constraints can apply not just to single relationsWe need to be able to talk about constraints that cross relationsMore Terminology!Relational Database SchemaSet of relation schemas

S =

{

R

1

, R2, ..., Rm} Set of

integrity constraints ICRelational Database StateSet of relation states for a relational database such that all integrity constraints are satisfiedInvalid state – state that violates an integrity constraint

14Slide15

Schema-based constraints

Entity integrity constraintNo primary key can have a NULL valueRemember – primary key uniquely identifies a tuple!Referential integrity constraint

Specified between two relations

A

tuple

in one relation that refers to a

tuple in a second relation MUST refer to an existing tupleYou can’t put in “placeholder” references – every reference must be resolvable when you make the reference

Uses the concept of a foreign key

15Slide16

Schema-based constraints

Where do referential integrity constraints come from?Connections in the data

16Slide17

Other constraints

Semantic integrity constraintsConstraints that come from outside the basic relationships between tuples“Business rules” “no employee can have a salary larger than their supervisor”

“no employee can log more than 60 hours of time in a week”

Usually modeled at the application level, but sometimes can be modeled in the database

“Triggers” – “when event X occurs perform action Y”

“Assertions” – “make sure that no matter what action X does, condition Y is always true”

17Slide18

Operations

The relational model has two types of operations:RetrievalsGetting information out of the databaseUpdatesAdding/changing information in the database

Different kinds of updates:

INSERT

Add new

tuple

to a relationDELETERemove a tuple from a relationUPDATE

Change an attribute value in a tuple in a relation18Slide19

Updates & Constraints

The DBMS must make sure that updates are not allowed to violate integrity constraintsCheck to make sure that attributes in an INSERT do not violate constraintsDELETE can cause referential constraint violationsRemoving a tuple

being referred to by another

tuple

UPDATE can cause referential constraint violations

Changing a

primary key can cause all sorts of referential problems for any tuple referring to the updated tuple

Changing a foreign key can only happen if the tuple the foreign key refers to already exists

19Slide20

ER-Relational Mapping

20Slide21

ER-Model to Relational Model

Once we have our ER-Model, we use it to come up with our Relational modelMust map elements of ER-model to elements of relational modelEntities, Relationships, Attributes, etc. must become Relations, Attributes, etc.

21Slide22

Strong Entity Types

Each strong entity type in an ER model becomes a relationEntity type E -> Relation RSimple attributes of E become attributes of RInclude only

the component attributes of a composite attribute

Choose one key of E to become the primary key of R

22

EMPLOYEE

Ssn

Address

Name

Fname

Lname

M

Ssn

Fname

M

Lname

Address

EMPLOYEESlide23

Weak Entity Types

Each weak entity type becomes a relationWeak entity W owned by entity EEntity E -> Relation R1Entity W -> Relation R2All simple attributes of W become attributes in R2

Include as a foreign key the primary key attribute of R1

Primary key of R2 will be that foreign key, plus the partial key of W

23

EMPLOYEE

Dependents_of

DEPENDENT

Sex

Relationship

Bdate

Name

N

1

Ssn

Fname

M

Lname

Address

EMPLOYEE

Essn

Name

Sex

Bdate

Relationship

DEPENDENTSlide24

Binary 1:1 Relationships

Three approaches:Foreign Key ApproachMerged relation ApproachCross-reference ApproachShould use “Foreign key approach” unless there is good reason not to

24Slide25

Binary 1:1 Relationships

Foreign key approachTwo entities in the relationship – E1 and E2Generate two relations R1 and R2 associated with E1 and E2Include in R1 a foreign key pointing at the primary key of R2

R1 should be an entity with Total Participation if possible

25

Ssn

Fname

M

Lname

Address

EMPLOYEE

Dname

Dnumber

MgrSsn

DEPARTMENTSlide26

Binary 1:1 relationships

Merged relation approachIf both entities have TOTAL participation in the relationship, you can merge them into a single entityEach table would have an exact one-to-one correspondence between rows, so they’re essentially the same entityCross-reference approach

Set up a third relation as a “lookup table” for the relationship

Required for many-to-many relationships

26Slide27

Binary 1:N Relationships

Use the foreign key approachIdentify which relation represents the entity on “many” side of the relationshipGive that relation a foreign key pointing at the primary key on the “one” side of the relationshipOr use cross-reference

27

Ssn

Fname

M

Lname

Address

Dno

EMPLOYEE

Dname

Dnumber

MgrSsn

DEPARTMENTSlide28

Binary M:N Relationships

Cross-reference approachFor Entities E1 and E2 connected by Relationship RCreate a new relation for RInclude as attributes foreign keys pointing at the primary keys of E1 and E2 – combination will be the primary key

Include any attributes tied to R

Think of this as a “lookup table

”

28

Ssn

Fname

M

Lname

Address

Dno

EMPLOYEE

Name

Pnumber

Location

PROJECT

Ssn

Pnumber

Hours

WORKS_ONSlide29

Multivalued Attributes

Each multi-valued attribute A in the entity E1 gets its own relationRelation includes the attribute A and a foreign key pointing at the primary key of the relation associated with E1Primary key of the new relation is a combination of A and the foreign key

29

Dname

Dnumber

MgrSsn

DEPARTMENT

Dno

Dlocation

DEPT_LOCATIONSSlide30

N-ary Relationships

N-ary relationships modeled using cross-reference approachEach n-ary relationship is made into a new relation

Attributes of this relation include foreign keys pointing at the primary keys of all the participating entity relations

Include all simple attributes as well

Primary key is usually a combination of all foreign keys

Be careful – cardinality constraints may mean that we need to leave some of these out

30Slide31

Summary of ER-to-Relational mapping

31