1 Relational Database Model - BFH-TI / Organisation

Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Database Systems
Unit 3
Relational Database Model
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-1
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Learning Goals
• In this unit you will learn
– what the relational database model is
– what componentes are used in the relational model
– how entities are represented in relational databases
– how relationships are represented in relational databases
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-2
1
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Characteristics
• The relational model was proposed by Dr. E. F. Codd of IBM
in the paper "A Relational Model for Large Shared Data
Banks," Communications of the ACM, June 1970.
• The model proposed by Codd is a theoretical and formal
model, based on the ideas of sets.
• In the early 1980s, the first commercial implementations of a
relational DBMS were available, such as Oracle DBMS and
the SQL/DS system on the MVS operating system by IBM.
• Today, most of the actually commercially available DBMS are
based on the relational database model (RDM).
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-3
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Relation
Student
Id
Name
Vorname
BLANJ
Blanc
MEIEP1
PLZ
Ort
Jean-Pierre Ch. du Crêt 8
2503
Bienne 032 398 88 73
Meier
Peter
Unterstr. 12
2503
Biel
032 983 83 87
MUSTP
Muster
Hans
Oberstr. 25
2502
Biel
079 938 92 66
RENAJ
Renard
Joceline
Rue du Lac 2
2560
Nidau
DB / PRM1 / 06.03.2008
Adresse
db04_RelationalModel.ppt / Version 2.0
Tel
4-4
2
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Informal and Formal Terms
Informal Terms
Formal Terms
Table
Relation
Column, Attribute, Property
Attribute / Domain
Row, Record, Dataset
Tuple
Possible values in a column
Domain
Table Definition
Relation Schema
Database Definition
Relational Database Schema
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-5
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Domains
• A domain D is a set of atomic values, that is each
value in the domain is indivisible as far as the
relational model is concerned.
• A common method of specifying a domain is to
specify a data type.
• It is also useful to specify a name for the domain, to
help in interpreting values.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-6
3
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Domain: Formal Definition
The domain of an attribute is the set of the possible
values of this attribute.
•
•
•
•
•
A format is also specified for each domain.
A domain is given a name, data type and format.
It may have also have a format defined for it.
Furthermore, a domain may be limited by constraints.
An attribute designates a role or interpretations for the
domain: For example, the same domain local phone numbers
plays the role of the home phone or of the office phone.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-7
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Types of Domains
• We distinguish between two types of domains:
– Static: A static domain is a predefined set of values, where
the values are constant for a certain time period or forever.
– Dynamic: A dynamic domain is a set of time
- varying values.
The set grows or shrinks dynamically and is not constant!
• Domains are part of the database structure and their definitions
and should therefore be stored in the database system and not
being defined in applications and functions! However, some
DBMS only have limited possibilities to support this.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-8
4
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Relation Schema: Formal Definition
A relation schema R, denoted by R(A1, A2, ……, An)
is made up of a relation name R and a List of attributes
A1, A2,……, An
• Each attribute Ai is the name of a role played by some
domain D in the relation schema R.
• Di is called the domain of Ai, also denoted by dom(Ai)
• A relation schema R is used to describe a relation where R is
the name of the relation.
• The number of attributes n is called the degree of a relation.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4-9
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Relation Schema: Formal Definition
• Example:
STUDENT(ID, LastName, FirstName, StreetAddress,
CityNumber, CityName, HomePhone)
• Using the data type, the definition is sometimes
written:
STUDENT(ID: string, LastName: string, FirstName: string,
StreetAddress: string, CityNumber: integer, CityName: string,
HomePhone: string)
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 10
5
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Relation: Formal Definition
A relation r (or relation state r) of the relational schema
R(A1, A2,… An), also denoted by r(R), is a set of ntuples (or tuples) r = {t1, t2,… tm}
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 11
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Tuple: Formal Definition
A tuple is an (ordered) set of values
• Each tuple t is an ordered list of n values
• t = <v1, v2, …, vn>, where each value vi,1≤i≤n, is an
element of dom(Ai) or is a special NULL value.
• The i-th value of a tuple corresponds to the attribute Ai is
referred to as t[Ai] or shortly t[i] .
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 12
6
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
NULL Value
• Additionally to domain values, attributes may or may not
accept NULL values.
• A NULL value is not simply the number of 0 (or an empty
character string); it has to be distinguished clearly from "0".
• NULL values are hardly disputed in the professional world,
because their meaning (semantics) is not always very clear:
– Value not applicable for a given tuple
– Value not know (but may exist)
– Value known to exist, but not (yet) available
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 13
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Relational Database Schema: Formal Definition
A Relational Database Schema is a set S of relation schemas
Ri that belong to the same database and a set of integrity
constraints IC. S is the name of the database and written by
S = {R1, R2, ..., Rn}
„A relational database is a database that is perceived by the
user as a collection of time-varying, normalized relations of
assorted degrees!“
E. F. Codd
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 14
7
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Relational Database Schema: Formal Definition
A relational database state DB of S is a set of relation states
DB = {r1, r2, ..., rn} such that ri is a state of Ri such that the ri
relation state satisfy the integrity constraint specified in IC.
• A database state that does not obey all the integrity
constraints is called an invalid state, and
• a state that satisfies all the constraints is called a valid state.
• Each relational DBMS must have a data definition language
(DDL) for defining a relational database schema (mostly
SQL).
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 15
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Relational Integrity Constraints
•
•
•
Constraints are conditions that must hold on all valid
relation instances.
There are three main types of constraints:
1. Key constraints
2. Entity integrity constraints
3. Referential integrity constraints
Additionally, most DBMS allow to specify user defined
integrity constraints.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 16
8
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Key Constraints
• Superkey of R: A set of attributes SK of R such that no two
tuples in any valid relation instance r(R) will have the same
value for SK. That is, for any distinct tuples t1 and t2 in r(R),
t1[SK] ≠ t2[SK]
• Key of R: A "minimal" superkey; that is, a superkey K such
that removal of any attribute from K results in a set of
attributes that is not a superkey
• If a relation has several (candidate) keys, one is chosen
arbitrarily to be the primary key
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 17
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Entity Integrity
• Entity Integrity: The primary key attributes PK of each
relation schema R in S cannot have null values in any tuple
of r(R). This is because primary key values are used to
identify the individual tuples.
t[PK] ≠ null for any tuple t in r(R)
• Note: There may be other attributes of R may to be similarly
constrained to be not Null, even though they are not
members of the primary key.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 18
9
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Referential Integrity
• A constraint involving two relations
• Used to specify a relationship among tuples in two relations:
the referencing relation and the referenced relation
• Tuples in the referencing relation R1 have attributes FK
(called foreign key attributes) that reference the primary key
attributes PK of the referenced relation R2. A tuple t1 in R1 is
said to reference a tuple t2 in R2 if t1[FK] = t2[PK]
• A referential integrity constraint can be displayed in a
relational database schema as a directed arc from R1.FK to
R2 or R2.PK resp.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 19
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Referential Integrity Constraint
•
The value in the foreign key column (or columns) FK of the
the referencing relation R1 can be either:
1. a value of an existing primary key value of the
corresponding primary key PK in the referenced
relation R2,,
or
2. NULL
•
In case (2), the FK in R1 may not be a part of its own primary
key
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 20
10
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Graphical Representation: Database Schema Diagram
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 21
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Textual Representation
Employee (SSN, FName, Minit, Lname, …,
PK(SSN) )
Dependent (ESSN, Dependent_Name, Bdate, …,
PK(ESSN, Dependent_Name),
FK(ESSN) Æ Employee) )
• Additionally, we may specify for each attribute whether it
accepts NULL values or not (Ex: "N" or "NN")
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 22
11
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Update Operations
• The operations of the relational model can be categorized
into retrievals and updates.
• There are three update operations: insert, delete and modify.
• Insert is used to insert a new tuple or tuples in a relation.
• Delete is used to delete tuples.
• Update (or modify) is used to change the values of some
attributes in existing tuples.
• Whenever these operations are applied, the integrity
constraints specified on the relational database schema
should not be violated.
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 23
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Update Operations
Exercise:
• Which type of integrity constraints can be violated by the
update operations above?
DB / PRM1 / 06.03.2008
db04_RelationalModel.ppt / Version 2.0
4 - 24
12