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
