Basis Data I Pertemuan Ke-4 & ke-5 (Model Relasional – Part 1) Noor Ifada
[email protected]
1
Sub Pokok Bahasan Model Relasional SQL (Structured Query Language) Integrity Constraint Transformasi ER ke Relasional
2
Model Relasional Model (basisdata) Relasional: suatu basisdata dimodelkan sebagai kumpulan relasi Relasi merupakan Tabel/Table/Relation/File yang terdiri dari: Baris/Row/Tuple/Record (masing-masing baris tidak boleh ada yang sama) Kolom/Column/Attribute/Field Contoh skema tabel/relasi: Students(sid: string, name: string, login: string, age: integer, gpa: real)
Students
3
SQL (Structured Query Language)
Adalah bahasa standar yang digunakan untuk memanipulasi basisdata relasional Dikembangkan oleh IBM pada tahun 1970-an Standar SQL: SQL-86 SQL-89 (minor revision) SQL-92 (major revision) SQL-99 (major extensions, current standard) Terdiri dari: Data Definition Language (DDL): CREATE tables, indexes, views, Establish primary / foreign keys, DROP / ALTER tables .... Etc Data Manipulation Language (DML): INSERT / UPDATE / DELETE, SELECT .... etc. Data Control Language (DCL): COMMIT / ROLLBACK work, GRANT / REVOKE .... etc
4
Integrity Constraint (IC) Merupakan kondisi yang dispesifikasikan dalam skema basisdata dan harus dipatuhi oleh setiap tabel/relasi dalam basisdata tersebut Didefinisikan di dalam skema tabel/relasi Pengecekan dilakukan setiap kali tabel/relasi dimodifikasi Macam-macam IC: Domain constraint Primary key constraints Foreign Key (referential integrity)
5
Domain Constraint
6
Primary Key (PK) Constraint Primary key constraint: tidak boleh ada baris yang memiliki nilai yang sama dalam kolom PK PK tidak boleh bernilai NULL Boleh jadi ada beberapa/banyak candidate keys (didefinisikan dengan perintah UNIQUE), lalu salah satunya/sebagian dijadikan PK Penggunaan PK yang salah, akan menyebabkan IC menolak penyimpanan data
Students
CREATE TABLE Students (sid CHAR(20), name CHAR(30), login CHAR(20), age INTEGER, gpa REAL, UNIQUE (name, age), CONSTRAINT StudentsKey PRIMARY KEY (sid) )
7
Foreign Key (FK) Constraint Foreign Key (referential integrity): kolom dalam suatu relasi yang digunakan dengan mengambil referensi dari relasi yang lain FK dari relasi pertama (referencing relation) harus sama dengan PK dari relasi kedua (referenced relation) → jumlah kolomnya dan kesesuaian tipe data (nama kolom boleh beda) CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students )
?
CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid,grade), FOREIGN KEY (sid) REFERENCES Students )) 8
Foreign Key (FK) Constraint (contd) Penerapan Referential integrity dalam SQL: Default adalah NO ACTION (delete/update is rejected) CASCADE (delete all tuples that refer to deleted tuple) SET NULL/SET DEFAULT (sets foreign key value of referencing tuple) CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20), grade CHAR(10), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE SET DEFAULT)
9
Transformasi: ER ke Relasional Entity Set Relationship set (tanpa constraint) Relationship set dengan key constraint (konektifitas) Relationship set dengan participation constraint (eksistansi) Weak entity set
10
Entity Set
CREATE TABLE Employees (ssn CHAR(11), name CHAR(30) , lot INTEGER, PRIMARY KEY (ssn) )
11
Relationship Set (tanpa constraint)
CREATE TABLE Works_In2 (ssn CHAR(11), did INTEGER, address CHAR(20) , since DATE, PRIMARY KEY (ssn, did, address), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (address) REFERENCES Locations, FOREIGN KEY (did) REFERENCES Departments)
12
Relationship Set (tanpa constraint)
CREATE TABLE Reports_To (Supervisor_ssn CHAR (11), Subordinate_ssn CHAR (11) , PRIMARY KEY (supervisor_ssn, subordinate_ssn), FOREIGN KEY (supervisor_ssn) REFERENCES Employees(ssn), FOREIGN KEY (subordinate_ssn) REFERENCES Employees(ssn) ) 13
Relationship Set + Key Constraint
CREATE TABLE Manages (ssn CHAR (11) , did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)
?
CREATE TABLE Dept_Mgr (did INTEGER, dname CHAR(20), budget REAL, ssn CHAR (11) NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees)
14
Relationship Set + Participation Constraint
CREATE TABLE Dept_Mgr (did INTEGER, dname CHAR(20) , budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE NO ACTION)
15
Weak Entity Set
CREATE TABLE Dep_Policy (pname CHAR(20) , age INTEGER, cost REAL, ssn CHAR (11) , PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE )
16
