DIK-034 Database Berbasis Web Database Relasional

DIK-034 Database Berbasis Web
DIK-034 Database Berbasis Web
Database Relasional
Dr. Taufik Fuadi Abidin, M.Tech
Program D3 Manajemen Informatika
FMIPA UNIVERSITAS KUALA
DIK-034 Database Berbasis Web
Database
Database = kumpulan data dalam jumlah besar dan
saling terkait. Contoh:
data bank
reservasi airline
Database memodelkan data organisasi, enterprise,
universitas, dan lain-lain
Entities (students, courses)
Relationships (“Ali mengambil DBW T.A 2010/11”)
Perubahan organisasi berarti perubahan database
2
DIK-034 Database Berbasis Web
Tahapan Dalam Rancangan DB
Problem
Requirements Analysis
Data Requirements
Data Analysis, Conceptual Design
Conceptual Schema
Logical Database Design
Logical Schema
Physical Database Design
Physical Schema
3
DIK-034 Database Berbasis Web
Different Schemas, Different Concepts
Conceptual
Schema
Logical
Schema
ER:
Tables/Relations:
• Entities,
• column names/attributes
• Relationships, • rows/tuples
• Attributes
Physical
Schema
File organisation:
• File types
• Index structures
4
DIK-034 Database Berbasis Web
Table name
Tabel (Table)
Product:
Name
gizmo
Price
$19.99
Column names
Category
gadgets
Manufacturer
GizmoWorks
Power gizmo
$29.99
gadgets
SingleTouch
$149.99
photography
Canon
household
Hitachi
MultiTouch
$203.99
GizmoWorks
Rows
5
DIK-034 Database Berbasis Web
Relation Schemas
Relation schema
R(A1:D1, ..., An:Dn)
Terdiri atas
Nama, R
Nonemtpy set attributes, A1, ..., An
Domain, Di = dom(Ai), untuk setiap attribute Ai.
Contoh:
Product(Prodname: Name, Price: DollarPrice,
Category: Name, Manufacturer: Name)
6
DIK-034 Database Berbasis Web
Database Schema dan Instance
Database Schema
Set of relation schemas, e.g.,
Product (Productname, Price, Category, Manufacturer),
Vendor (Vendorname, Address, Phone), …
Database Instance
Set of relation instances
Perbedaan:
Database Schema = stable, jarang berubah
Database Instance = sering berubah
7
DIK-034 Database Berbasis Web
Why Relational DB?
Model sederhana
Cocok dengan cara kita memvisualisasikan data
Berbasis logik dan teori himpunan (set theory)
Abstract model pendukung SQL, bahasa yang
digunakan oleh banyak DBMS sekarang ini
8
DIK-034 Database Berbasis Web
Keys: Overview
Superkey
a set of attributes whose values together uniquely identify a
tuple in a relation
Candidate Key
a superkey for which no proper subset is a superkey:
a key that is minimal .
Can be more than one for a relation
Primary Key
a candidate key chosen to be the main key
One for each relation,
indicated by underlining the key attributes
Student(studno,name,tutor,year)
9
DIK-034 Database Berbasis Web
Example: Multiple Keys
Student (Lastname, Firstname,
MatriculationNo, Major )
Key
Key
(2 attributes)
Superkey
Note: There are alternate keys
Keys are
{Lastname, Firstname} and
{StudentID}
10
DIK-034 Database Berbasis Web
Foreign Key
A set of attributes in a relation that exactly matches the
(primary) key in another relation
The names of the attributes don’t have to be the
same but must be of the same domain
Student (studno, name, hons, tutor, year)
Staff (lecturer, roomno, appraiser)
Notation:
FK1: Student (tutor) references Staff (lecturer)
FK2: Staff (appraiser) references Staff (lecturer)
11
DIK-034 Database Berbasis Web
Satisfaction of Foreign Key
Constraints
“FK: R(A) references S(B)” is satisfied by an instance of R
and S if for every t1 in R there is a t2 in S such that
t1[A] = t2[B], provided t1[A] is not null
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
Foreign key constraints are also called
“referential integrity constraints.”
12
DIK-034 Database Berbasis Web
Insertions (1)
If the following tuple is inserted into Student,
what should happen? Why?
(s1, jones, cis, capon, 3)
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
13
DIK-034 Database Berbasis Web
Insertions (2)
If the following tuple is inserted into Student,
what should happen? Why?
(null, jones, cis, capon, 3)
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
14
DIK-034 Database Berbasis Web
Insertions (3)
If the following tuple is inserted into Student,
what should happen? Why?
(s7, jones, cis, null, 3)
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
15
DIK-034 Database Berbasis Web
Insertions (4)
If the following tuple is inserted into Student,
what should happen? Why?
(s7, jones, cis, calvanese, 3)
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
16
DIK-034 Database Berbasis Web
Deletions (1)
If the following tuple is deleted from Student,
is there a problem? And what should happen?
(s2, brown, cis, kahn, 2)
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
17
DIK-034 Database Berbasis Web
Deletions (2)
And if this one is deleted from Staff ?
(kahn, IT206, watson)
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
18
DIK-034 Database Berbasis Web
Modifications (1)
What if we change in Student
(s1, jones, ca, bush, 2)
to
(s1, jones, ca, watson, 2) ?
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
19
DIK-034 Database Berbasis Web
Modifications (2)
And what if we change in Student
(s2, brown, cis, kahn, 2)
to
(s1, jones, ca, bloggs, 2) ?
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
20
DIK-034 Database Berbasis Web
Modifications (3)
And what if we change in Staff
(lindsey, 2.10, woods)
to
(lindsay, 2.10, woods) ?
STUDENT
studno name
s1
jones
s2
brown
s3
smith
s4
bloggs
s5
jones
s6
peters
hons
ca
cis
cs
ca
cs
ca
tutor
bush
kahn
goble
goble
zobel
kahn
year
2
2
2
1
1
3
STAFF
lecturer
kahn
bush
goble
zobel
watson
woods
capon
lindsey
barringer
roomno
IT206
2.26
2.82
2.34
IT212
IT204
A14
2.10
2.125
appraiser
watson
capon
capon
watson
barringer
barringer
watson
woods
null
21