Transcript Relational Model Powerpoint

The Relational Data Model
David J. Stucki
1
Relational Model Concepts

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 table
2
Relational Model Concepts




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
3
Domain

All the possible values an attribute can take



Atomic
Remember 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 domain


US Phone Numbers: (ddd)ddd-dddd
GPAs: any real-valued number
4
Relation Schema

Denoted by R(A1, A2, ...,An)



Consists of a relation name and a list of attributes
Description of what the relation should contain
STUDENT 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
5
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 values

Each element of the tuple corresponds to a particular attribute
for the relation
6
Characteristics of Relations

Ordering of tuples




Uniqueness of tuples


No duplicate tuples in a relation!
Unknown values


Relation is a set
Sets have no order
Relation is not sensitive to ordering of tuples
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
7
Characteristics of Relations

Order of attributes and values is not that
important


Alternative definition of a relation



As long as correspondence between attributes and values
maintained
Tuple considered as a set of (<attribute>, <value>) pairs
Each pair gives the value of the mapping from an attribute Ai to a
value vi from dom(Ai)
Use the first definition of relation



Attributes and the values within tuples are ordered
Simpler notation
But alternative has application in later formalisms
Characteristics of Relations

Values in tuples


Each value in a tuple is atomic
Flat relational model
•
•

Multivalued attributes
•

Composite and multivalued attributes not allowed
First normal form assumption
Must be represented by separate relations
Composite attributes
•
Represented only by simple component attributes in basic
relational model
Relational Model Constraints

A constraint is a restriction on the values in a
database state

Implicit constraints (model-based constraints)

Inherent in the data model itself


Explicit constraints (schema-based constraints)


E.g. no duplicate tuples in a relation
Can be explicitly enforced/expressed in the schema
Application-based constraints (business rules)



Derived from miniworld represented by database
Cannot be explicitly enforced by the schema
Must be enforced by application programs themselves
10
Schema-based constraints

Domain constraints


Data type constraint
Each attribute in a tuple may only take on a value from
the domain of that attribute
11
Schema-based constraints

Key constraints

Remember – each tuple in a relation must be unique



No duplicate tuples!
This means that no two tuples have the same combination of
attributes for all of their attributes
Usually there is a subset of attributes that control
uniqueness


We call this subset a superkey
Definition: 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]
12
Schema-based constraints

Superkeys can have redundant attributes



Ex: {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 keys
There can be more than one key in a relation


Ex: {SSN} {Student ID}
Common to identify one of these keys as as a primary key

Primary key uniquely identifies tuples in a relation to other
relations and outside applications
13
Schema-based constraints

Constraints can apply not just to single relations



Relational Database Schema



We need to be able to talk about constraints that cross
relations
More Terminology!
Set of relation schemas S = {R1, R2, ..., Rm}
Set of integrity constraints IC
Relational Database State

Set of relation states for a relational database such
that all integrity constraints are satisfied

Invalid state – state that violates an integrity constraint
14
Schema-based constraints

Entity integrity constraint



No primary key can have a NULL value
Remember – 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 tuple


You can’t put in “placeholder” references – every reference
must be resolvable when you make the reference
Uses the concept of a foreign key
15
Schema-based constraints

Where do referential integrity constraints come from?

Connections in the data
16
Other constraints

Semantic integrity constraints

Constraints 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”
17
Operations

The relational model has two types of
operations:

Retrievals


Getting information out of the database
Updates


Adding/changing information in the database
Different kinds of updates:



INSERT

Add new tuple to a relation
DELETE

Remove a tuple from a relation
UPDATE

Change an attribute value in a tuple in a relation
18
Updates & Constraints

The DBMS must make sure that updates are not
allowed to violate integrity constraints


Check to make sure that attributes in an INSERT do
not violate constraints
DELETE can cause referential constraint violations


Removing 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
19
ER-Relational Mapping
20
ER-Model to Relational Model

Once we have our ER-Model, we use it to come
up with our Relational model

Must map elements of ER-model to elements of
relational model

Entities, Relationships, Attributes, etc. must become
Relations, Attributes, etc.
21
Strong Entity Types
Each strong entity type in an ER model becomes
a relation





Entity type E -> Relation R
Simple attributes of E become attributes of R
Include only the component attributes of a composite
attribute
Choose one key of E to become the primary key of R
Fname
M
Lname
EMPLOYEE
Ssn
Ssn
Name
EMPLOYEE
Fname
M
Lname
Address
Address
22
Weak Entity Types

Each weak entity type becomes a relation

Weak entity W owned by entity E



Entity E -> Relation R1
Entity W -> Relation R2
All 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
EMPLOYEE
Ssn
1
EMPLOYEE
Fname
M
Lname
Address
Dependents_of
DEPENDENT
N
Sex
Essn
Name
Sex
Bdate
Relationship
DEPENDENT
Bdate
Name
Relationship
23
Binary 1:1 Relationships

Three approaches:




Foreign Key Approach
Merged relation Approach
Cross-reference Approach
Should use “Foreign key approach” unless there is
good reason not to
24
Binary 1:1 Relationships

Foreign key approach

Two entities in the relationship – E1 and E2
1.
2.
Generate two relations R1 and R2 associated with E1 and E2
Include in R1 a foreign key pointing at the primary key of R2

R1 should be an entity with Total Participation if possible
EMPLOYEE
Ssn
Fname
M
Lname
Address
DEPARTMENT
Dname
Dnumber
MgrSsn
25
Binary 1:1 relationships

Merged relation approach

If both entities have TOTAL participation in the
relationship, you can merge them into a single entity


Each table would have an exact one-to-one correspondence
between rows, so they’re essentially the same entity
Cross-reference approach

Set up a third relation as a “lookup table” for the
relationship

Required for many-to-many relationships
26
Binary 1:N Relationships

Use the foreign key approach



Identify which relation represents the entity on “many” side of the
relationship
Give that relation a foreign key pointing at the primary key on the
“one” side of the relationship
Or use cross-reference
EMPLOYEE
Ssn
Fname
M
Lname
Address
Dno
DEPARTMENT
Dname
Dnumber
MgrSsn
27
Binary M:N Relationships

Cross-reference approach


For Entities E1 and E2 connected by Relationship R
Create a new relation for R

Include 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”

EMPLOYEE
Ssn
Fname
M
Lname
Address
Dno
WORKS_ON
Ssn
Pnumber
Hours
PROJECT
Name
Pnumber
Location
28
Multivalued Attributes

Each multi-valued attribute A in the entity E1 gets its own
relation


Relation includes the attribute A and a foreign key pointing at the
primary key of the relation associated with E1
Primary key of the new relation is a combination of A and the
foreign key
DEPARTMENT
Dname
Dnumber
MgrSsn
DEPT_LOCATIONS
Dno
Dlocation
29
N-ary Relationships

N-ary relationships modeled using crossreference approach

Each 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
30
Summary of ER-to-Relational
mapping
31