172 - Slide 5 - Relational Model

Download as pdf or txt
Download as pdf or txt
You are on page 1of 55

Slide 5

The Relational Data Model and Relational


Database Constraints

CSF2600700 - BASIS DATA


SEMESTER GANJIL 2018/2 019
Reference:
“Database System”, 7th edition, Elmasri/Navathe, 2011:
Chapter 5 The Relational Data Model and Relational
Database Constraints

01/02/2019 BASIS DATA GENAP 2016/2017


2
Outline
Relational Model Concept
Characteristics of Relations
Relational Model Notation
Relational Model Constraints
Update Operations, Transactions, and Dealing
with Constraint Violations

01/02/2019 BASIS DATA GENAP 2016/2017


3
The Relational Data Model
Relational model
◦ Introduced by Ted Codd of IBM Research in 1970
◦ The model uses the concept of a mathematical
relation
◦ First commercial implementations available in early
1980s by IBM and Oracle
◦ Has been implemented in a large number of
commercial system
◦ Popular Relational DBMS: Oracle, DB2, MySQL,
PostgreSQL
◦ Preceded by hierarchical and network models

01/02/2019 BASIS DATA GENAP 2016/2017


4
Relational Model Concepts
▪Represents database as a collection of relations
▪Each relation resembles a table of values
◦ Row
• Represents a collection of related data values
• Represents a fact that typically corresponds to a real-world
entity or relationship
◦ Table name and column names
• Interpret the meaning of the values in each row
• Formal Terminology
• Row → Tuple
• Column header → attribute
• Table → Relation

01/02/2019 BASIS DATA GENAP 2016/2017


5
Relational Model Concepts (cont’d.)

01/02/2019 BASIS DATA GENAP 2016/2017


6
Domains, Attributes, Tuples, and Relations
Domain D
◦Set of atomic values
◦Example:
◦ GPA: real number between 0 and 4
◦ Local_phone_numbers. The set of seven-digit phone numbers
valid within a particular area code
Atomic
◦Each value indivisible
Specifying a domain
◦Data type specified for each domain

01/02/2019 BASIS DATA GENAP 2016/2017


7
Domains, Attributes, Tuples, and Relations
(cont’d.)
 Relation schema R
 Denoted by R(A1, A2, ...,An)
 Made up of a relation name R and a list of attributes, A1,
A2, ..., An
 Example: STUDENT(Name, SSN, Home_phone,
Address, Office_phone, Age, Gpa)
 Attribute Ai
 Name of a role played by some domain D in the relation
schema R
 Degree (or arity) of a relation
 Number of attributes n of its relation schema
 STUDENT: a relation of degree 7

01/02/2019 BASIS DATA GENAP 2016/2017


8
Domains, Attributes, Tuples, and Relations
(cont’d.)
Relation (or relation state)
◦Set of n-tuples r = {t1, t2, ..., tm}
◦Each n-tuple t
• Ordered list of n values t =<v1, v2, ..., vn >
• Each value vi, 1 ≤ i ≤ n, is an element of dom(Ai) or is a special
NULL value
• Example:
• t = <‘Benyamin Bayer’, ‘305-61-2435’, …, 3.21>

01/02/2019 BASIS DATA GENAP 2016/2017


9
Domains, Attributes, Tuples, and Relations
(cont’d.)
Relation (or relation state) r(R)
◦Mathematical relation of degree n on the domains
dom(A1), dom(A2), ..., dom(An)
◦Subset of the Cartesian product of the domains that
define R:
• r(R) ⊆ (dom(A1) × dom(A2) × ... × dom(An))
• The Cartesian product specifies all possible
combinations of values from the underlying
domains.

01/02/2019 BASIS DATA GENAP 2016/2017


10
Example
Given relation schema R(A1, A2)
◦ dom(A1) = {0,1}
◦ dom(A2) = {a,b,c}
Cartesian product of the domain → dom (A1) X dom
(A2):
{<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> }
A state of R:
{<0,a> , <1,a>, <1,c> }

01/02/2019 BASIS DATA GENAP 2016/2017


11
Domains, Attributes, Tuples, and Relations
(cont’d.)
Cardinality
◦Total number of values in domain
Current relation state
◦Relation state at a given time
◦Reflects only the valid tuples that represent a
particular state of the real world
Attribute names
◦Indicate different roles, or interpretations, for the
domain

01/02/2019 BASIS DATA GENAP 2016/2017


12
Equivalent Terminology

Formal (Relational Model) Non Formal


Relation Table (File)
Tuple Row (Record)
Attribute Column Header (Field)
Domain All possible column values
Schema of a relation Table definition
State of the relation Populated table

01/02/2019 BASIS DATA GENAP 2016/2017


13
Outline
The Relational Data Model
Characteristics of Relations
Relational Model Notation
Relational Model Constraints
Update Operations, Transactions, and Dealing
with Constraint Violations

01/02/2019 BASIS DATA GENAP 2016/2017


14
Characteristics of Relations
Ordering of tuples in a relation
◦Relation defined as a set of tuples
◦Elements have no order among them

01/02/2019 BASIS DATA GENAP 2016/2017


15
Characteristics of Relations
Ordering of values within a tuple
◦Order of attributes and values is not that important
◦As long as correspondence between attributes and
values maintained
Alternative definition of a relation
◦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)

01/02/2019 BASIS DATA GENAP 2016/2017


16
Characteristics of Relations (cont’d.)

Use the first definition of relation


◦Attributes and the values within tuples are ordered
◦Simpler notation

01/02/2019 BASIS DATA GENAP 2016/2017


17
Characteristics of Relations (cont’d.)
Values in tuples
◦Each value in a tuple is atomic
◦Flat relational model
• Composite and multivalued attributes not allowed
• First normal form assumption
◦Multivalued attributes
◦ Example: Favourite color = {red, green}
• Must be represented by separate relations
◦Composite attributes
◦ Example: Address can be divided into Street_address, City,
State, Zip.
• Represented only by simple component attributes in basic
relational model

01/02/2019 BASIS DATA GENAP 2016/2017


18
Characteristics of Relations (cont’d.)
NULL values
◦Represent the values of attributes that may be
unknown or may not apply to a tuple
◦Meanings for NULL values
• Value unknown
• Value exists but is not available
• Attribute does not apply to this tuple (also known as value
undefined)

01/02/2019 BASIS DATA GENAP 2016/2017


19
Exercise
From the following tables, which one is a relation in a
relational database?
R1 A B C D R2 A B C D
a2 {b1, b2} c1 d5 a2 b2 c6 d1
a2 b7 c9 d5 a2 b7 c9 d5
a2 b23 c22 d1 a2 b7 c9 d5
…... …...

R3 E# Ename AGE ADDRESS

E2 Diamond 45 1888 Buford Hyw.


E1 Smith 30 3302 Peachtree Rd., Atlanta, GA
E3 Evan null Baker Ct. Atlanta

01/02/2019 BASIS DATA GENAP 2016/2017


20
Outline
The Relational Data Model
Characteristics of Relations
Relational Model Notation
Relational Model Constraints
Update Operations, Transactions, and Dealing
with Constraint Violations

01/02/2019 BASIS DATA GENAP 2016/2017


21
Relational Model Notation
Relation schema R of degree n
◦Denoted by R(A1, A2, ..., An)
Uppercase letters Q, R, S
◦Denote relation names
Lowercase letters q, r, s
◦Denote relation states
Letters t, u, v
◦Denote tuples

01/02/2019 BASIS DATA GENAP 2016/2017


22
Relational Model Notation
Name of a relation schema: STUDENT
◦Indicates the current set of tuples in that relation
Notation: STUDENT(Name, Ssn, ...)
◦Refers only to relation schema
Attribute A can be qualified with the relation
name R to which it belongs
◦Using the dot notation R.A

01/02/2019 BASIS DATA GENAP 2016/2017


23
Relational Model Notation
n-tuple t in a relation r(R)
◦ Denoted by t = <v1, v2, ..., vn>
◦ vi is the value corresponding to attribute Ai

Component values of tuples:


◦ t[Ai] and t.Ai refer to the value vi in t for attribute Ai
◦ t[Au, Aw, ..., Az] and t.(Au, Aw, ..., Az) refer to the subtuple of
values <vu, vw, ..., vz> from t corresponding to the attributes
specified in the list

Example:
the tuple t = <‘Barbara Benson’, ‘533-69-1238’, ‘(817)839-
8461’, ‘7384 Fontana Lane’, NULL, 19, 3.25> from the STUDENT
relation
t[Name] = <‘Barbara Benson’>, and t[Ssn, Gpa, Age] = <‘533-69-
1238’, 3.25, 19>.

01/02/2019 BASIS DATA GENAP 2016/2017


24
Outline
The Relational Data Model
Characteristics of Relations
Relational Model Notation
Relational Model Constraints
Update Operations, Transactions, and Dealing
with Constraint Violations

01/02/2019 BASIS DATA GENAP 2016/2017


25
Relational Model Constraints
Constraints
◦ Restrictions on the actual values in a database state
◦ Derived from the rules in the miniworld that the database
represents
Constraints Categories:
◦ Inherent model-based constraints or implicit constraints
◦ Inherent in the data model
◦ Schema-based constraints or explicit constraints
◦ Can be directly expressed in schemas of the data model or in DDL
◦ Application-based or semantic constraints or business rules
◦ Cannot be directly expressed in schemas
◦ Expressed and enforced by application program

01/02/2019 BASIS DATA GENAP 2016/2017


26
Classification of Relational Integrity Constraints

Key Constraints

Main Entity Integrity Constraints

Referential Integrity Constraints

Domain Constraints

Others Transition Constraints


(Semantic)
Set Constraints

01/02/2019 BASIS DATA GENAP 2016/2017


27
Domain Constraints
Specify that within each tuple, the value of each attribute A
must be an atomic value from the domain dom(A)
Typically include:
◦ Numeric data types for integers and real numbers
◦ Characters
◦ Booleans
◦ Fixed-length strings
◦ Variable-length strings
◦ Date, time, timestamp
◦ Money
◦ Other special data types

01/02/2019 BASIS DATA GENAP 2016/2017


28
Key Constraints and Constraints on NULL
Values
Key Constraints
◦No two tuples can have the same combination of
values for all their attributes.
Superkey (SK)
◦SK: an attribute or set of attributes that guarantee
that no two distinct tuples in any state r of R can have
the same value for SK

01/02/2019 BASIS DATA GENAP 2016/2017


29
Key Constraints and Constraints on NULL
Values (cont’d.)
Key
◦Superkey of R
◦Removing any attribute A from K leaves a set of
attributes K that is not a superkey of R any more
Key satisfies two properties:
◦Two distinct tuples in any state of relation cannot
have identical values for (all) attributes in key
◦Minimal superkey
• Cannot remove any attributes and still have uniqueness
constraint in above condition hold

01/02/2019 BASIS DATA GENAP 2016/2017


30
Key Constraints and Constraints on NULL
Values (cont’d.)
Candidate key
◦Relation schema may have more than one key
Primary key of the relation
◦Designated among candidate keys
◦Underline attribute
Other candidate keys are designated as unique
keys / alternate keys

01/02/2019 BASIS DATA GENAP 2016/2017


31
Key Constraints and Constraints on NULL
Values (cont’d.)
Example
SSN FName LName BirthDate Sex Address

0606007800 Ahmad Zakky 10-4-87 L Jakarta

0607001123 Gede Saraswati 19-9-87 P Denpasar

0607120012 Bayu Wirawan 12-12-86 L Jimbaran

0607121023 Satya Wirawan 12-12-86 L Jimbaran

0607131240 Fira Bahira 1-3-87 P Jakarta

0607132222 Nayla Putri 1-9-86 P Depok

Super key:
SSN, Candidate key:
{SSN, Lname}, SSN,
{FName, BirthDate}, FName
{FName, Sex},

Alternate Key:
Primary Key: FName
SSN

BASIS DATA GENAP 2016/2017


Key Constraints and Constraints on NULL
Values (cont’d.)
Constraints on NULL Value
◦For an attribute, we can specify whether NULL
values are or are not permitted
◦For example: every STUDENT tuple must have a
valid, non-NULL value for the Name attribute →
then Name of STUDENT is constraint to be NOT
NULL

01/02/2019 BASIS DATA GENAP 2016/2017


34
Relational Databases and Relational
Database Schemas
Relational database schema S
◦Set of relation schemas S = {R1, R2, ..., Rm}
◦Set of integrity constraints IC
Relational database state
◦Set of relation states DB = {r1, r2, ..., rm}
◦Each ri is a state of Ri and such that the ri relation
states satisfy integrity constraints specified in IC

01/02/2019 BASIS DATA GENAP 2016/2017


35
Relational Databases and Relational
Database Schemas (cont’d.)

01/02/2019 BASIS DATA GENAP 2016/2017


36
01/02/2019 BASIS DATA GENAP 2016/2017
37
Relational Databases and Relational
Database Schemas (cont’d.)

Invalid state
◦Does not obey all the integrity constraints
Valid state
◦Satisfies all the constraints in the defined set of integrity
constraints IC

01/02/2019 BASIS DATA GENAP 2016/2017


38
Integrity, Referential Integrity,
and Foreign Keys
Entity integrity constraint
◦No primary key value can be NULL
Referential integrity constraint
◦Specified between two relations
◦Maintains consistency among tuples in two relations

01/02/2019 BASIS DATA GENAP 2016/2017


39
Integrity, Referential Integrity,
and Foreign Keys (cont’d.)

01/02/2019 BASIS DATA GENAP 2016/2017


40
Integrity, Referential Integrity,
and Foreign Keys (cont’d.)
Foreign key rules:
◦The attributes in FK have the same domain(s) as the
primary key attributes PK
◦Value of FK in a tuple t1 of the current state r1(R1)
either occurs as a value of PK for some tuple t2 in the
current state r2(R2) or is NULL

01/02/2019 BASIS DATA GENAP 2016/2017


41
Integrity, Referential Integrity,
and Foreign Keys (cont’d.)
Diagrammatically display referential integrity
constraints
◦Directed arc from each foreign key to the relation it
references
All integrity constraints should be specified on
relational database schema

01/02/2019 BASIS DATA GENAP 2016/2017


42
Other Types of Constraints
Semantic integrity constraints
◦May have to be specified and enforced on a relational
database
◦Use triggers and assertions
◦More common to check for these types of constraints
within the application programs

01/02/2019 BASIS DATA GENAP 2016/2017


43
Latihan
Basis data yang memproses order (pemesanan) pada sebuah
perusahaan memiliki 6 relasi berikut:

CUSTOMER (Cust#, Cname, City)


ORDER (Order#, Odate, Cust#, Ord_Amt)
ORDER_ITEM (Order#, Item#, Qty)
ITEM (Item#, Unit_price)
SHIPMENT (Order#, Warehouse#, Ship_date)
WAREHOUSE (Warehouse#, City)

Ord_Amt mengacu pada jumlah harga pada satu kali order. Odate
menyatakan tanggal pemesanan dilakukan, Ship_date
menyatakan tanggal pengiriman barang yang dipesan customer
dari gudang. Asumsikan bahwa suatu order dapat mengambil
barang dari beberapa gudang (warehouse). Nyatakan foreign key
yang mungkin untuk skema basis data ini.

01/02/2019 BASIS DATA GENAP 2016/2017


45
Outline
The Relational Data Model
Characteristics of Relations
Relational Model Notation
Relational Model Constraints
Update Operations, Transactions, and
Dealing with Constraint Violations

01/02/2019 BASIS DATA GENAP 2016/2017


46
Update Operations, Transactions, and
Dealing with Constraint Violations
Operations of the relational model can be
categorized into retrievals and updates
Basic operations that change the states of
relations in the database:
◦Insert
◦Delete
◦Update (or Modify)

01/02/2019 BASIS DATA GENAP 2016/2017


47
01/02/2019 BASIS DATA GENAP 2016/2017
48
01/02/2019 BASIS DATA GENAP 2016/2017
49
The Insert Operation
Provides a list of attribute values for a new
tuple t that is to be inserted into a relation R
Can violate any of the four types of constraints
If an insertion violates one or more constraints
◦Default option is to reject the insertion

01/02/2019 BASIS DATA GENAP 2016/2017


50
The Delete Operation
Can violate only referential integrity
◦If tuple being deleted is referenced by foreign keys from
other tuples
◦Restrict
• Reject the deletion
◦Cascade
• Propagate the deletion by deleting tuples that reference the tuple that
is being deleted
◦Set null or set default
• Modify the referencing attribute values that cause the violation

01/02/2019 BASIS DATA GENAP 2016/2017


51
The Update Operation
Necessary to specify a condition on attributes
of relation
◦Select the tuple (or tuples) to be modified
If attribute not part of a primary key nor of a
foreign key
◦Usually causes no problems
Updating a primary/foreign key
◦Similar issues as with Insert/Delete

01/02/2019 BASIS DATA GENAP 2016/2017


52
The Transaction Concept
Transaction
◦Executing program
◦Includes some database operations
◦Must leave the database in a valid or consistent state
Online transaction processing (OLTP)
systems
◦Execute transactions at rates that reach several
hundred per second

01/02/2019 BASIS DATA GENAP 2016/2017


53
Latihan

Apakah ada constraints yang


dilanggar pada operasi
berikut?
1. Insert < 'ProductA', 4,
'Bellaire', 2 > into PROJECT.
2. Insert < '677678989', null,
'40.0' > into WORKS_ON.
3. Delete the WORKS_ON
tuples with ESSN=
'333445555'.
4. Delete the EMPLOYEE tuple
with SSN= '987654321'.
5. Modify the SUPERSSN
attribute of the EMPLOYEE
tuple with SSN= '999887777'
to '943775543'.

01/02/2019 BASIS DATA GENAP 2016/2017


54
Summary
Characteristics differentiate relations from
ordinary tables or files
Classify database constraints into:
◦Inherent model-based constraints, explicit schema-
based constraints, and application-based constraints
Modification operations on the relational
model:
◦Insert, Delete, and Update

01/02/2019 BASIS DATA GENAP 2016/2017


55
FINISH

01/02/2019 BASIS DATA GENAP 2016/2017


56

You might also like