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