dik-034 database berbasiswebdik-034 database berbasis web dik-034 database berbasisweb database...

DIK-034 Database Berbasis Web


Database Relasional

Dr. Taufik Fuadi Abidin, M.Tech

Program D3 Manajemen InformatikaFMIPA UNIVERSITAS KUALA


Database

2

Database = kumpulan data dalam jumlah besar dansaling terkait. Contoh:

data bankreservasi 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


Tahapan Dalam Rancangan DB

3

Requirements Analysis

Data Analysis, Conceptual Design

Logical Database Design

Physical Database Design

Problem

Data Requirements

Conceptual Schema

Logical Schema

Physical Schema


4

Different Schemas, Different Concepts

Conceptual Schema

Logical Schema

PhysicalSchema

ER:• Entities, • Relationships,• Attributes

Tables/Relations:• column names/attributes• rows/tuples

File organisation:• File types• Index structures


5

Name Price Category Manufacturer

gizmo $19.99 gadgets GizmoWorks

Power gizmo $29.99 gadgets GizmoWorks

SingleTouch $149.99 photography Canon

MultiTouch $203.99 household Hitachi

Rows

Column namesTable name

Product:

Tabel (Table)


6

Relation SchemasRelation 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)


7

Database Schema dan InstanceDatabase Schema

Set of relation schemas, e.g.,Product (Productname, Price, Category, Manufacturer),

Vendor (Vendorname, Address, Phone), …

Database InstanceSet of relation instances

Perbedaan:� Database Schema = stable, jarang berubah� Database Instance = sering berubah


8

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


9

� 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)

Keys: Overview


10

Keys are

{Lastname, Firstname} and

{StudentID}

Student (Lastname, Firstname, MatriculationNo, Major )

Key Key

(2 attributes)

Superkey

Note: There are alternate keys

Example: Multiple Keys


11

� 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)

Foreign Key

Notation:FK1: Student (tutor) references Staff (lecturer)FK2: Staff (appraiser) references Staff (lecturer)


12

STUDENTstudno name hons tutor years1 jones ca bush 2s2 brown cis kahn 2s3 smith cs goble 2s4 bloggs ca goble 1s5 jones cs zobel 1s6 peters ca kahn 3

STAFF lecturer roomno appraiser kahn IT206 watson bush 2.26 capon goble 2.82 capon zobel 2.34 watson watson IT212 barringer woods IT204 barringer capon A14 watson lindsey 2.10 woods barringer 2.125 null

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

Foreign key constraints are also called“referential integrity constraints.”


13

If the following tuple is inserted into Student,what should happen? Why?

(s1, jones, cis, capon, 3)

Insertions (1)




14


(null, jones, cis, capon, 3)

Insertions (2)




15


(s7, jones, cis, null, 3)

Insertions (3)




16


(s7, jones, cis, calvanese, 3)

Insertions (4)




17

If the following tuple is deleted from Student,is there a problem? And what should happen?

(s2, brown, cis, kahn, 2)

Deletions (1)




18

And if this one is deleted from Staff ?

(kahn, IT206, watson)

Deletions (2)




19

What if we change in Student

(s1, jones, ca, bush, 2)to

(s1, jones, ca, watson, 2) ?

Modifications (1)




20

And what if we change in Student

(s2, brown, cis, kahn, 2)to

(s1, jones, ca, bloggs, 2) ?

Modifications (2)




21

And what if we change in Staff

(lindsey, 2.10, woods)to

(lindsay, 2.10, woods) ?

Modifications (3)



dik-034 database berbasiswebdik-034 database berbasis web dik-034 database berbasisweb database...

Documents