Physical Database Design

Database design lifecycle

• Requirements analysis
• User needs; what must database do?
• Conceptual design
• High-level description; often using E/R model
• Logical design
• Translate E/R model into relational schema
Normalization
• Schema refinement
• Check schema for redundancies and anomalies
• Physical design/tuning
• Consider typical workloads, and further optimise
2
Desain Database Fisik
• Model Data Relasional dan proses normalisasi database membantu
mengembangkan abstraksi data organisasi yang menangkap makna
data. Namun, notasi ini tidak menjelaskan bagaimana data akan diolah
atau disimpan.
• Desain Database Fisik akan menerjemahkan deskripsi logika dari data
ke dalam spesifikasi teknis untuk menyimpan, memproses, dan
mengambil data.
• Desain Database Fisik Membuat desain untuk menyimpan data yang
memberikan kinerja yang memadai dan memastikan integritas,
keamanan, dan pemulihan database. Memproduksi spesifikasi teknis
yang digunakan oleh pemrogram, administrator basis data, dan
sebagainya
Sebelum dapat memulai desain fisik, terlebih dahulu harus memiliki:
• desain basis data logis minimal bentuk normal ketiga
• Karakterisasi transaksi, seperti transaksi yang paling sering, transaksi
yang paling kompleks atau tidak peka terhadap sumber daya,
distribusi transaksi dari waktu ke waktu, kombinasi antara perintah
transaksi insert, update, delete, dan select
• Persyaratan kinerja
Tujuan Desain Database Fisik

• Meningkatkan kinerja sistem dengan mengurangi I / O disk dan

mengurangi penggabungan tabel
• menggabungkan aturan bisnis yang disematkan ke dalam desain basis
data, melalui default, aturan, batasan, prosedur tersimpan, atau
pemicu
• membuatnya dapat dipahami oleh pengguna, gunakan nama yang
bermakna dan indikatif untuk tabel dan kolom
Kegiatan Utama dari desain basis
data fisik
• Bagaimana memperkirakan jumlah (Analisis Volume Data) dari data yang pengguna akan
dibutuhkan dalam database
• Tentukan bagaimana data akan digunakan (Analisis Penggunaan Data)
• Tentang pilihan untuk menyimpan nilai atribut
• Bagaimana memilih di antara pilihan ini untuk mencapai efisiensi dan kualitas data.
• Bagaimana menerapkan pengukuran kualitas data
• Tabel yang dinormalisasi tidak selalu menjadi dasar untuk data fisik terbaik
• Bagaimana kita dapat mendenormalisasi data untuk meningkatkan kecepatan
pengambilan data.
• Penggunaan indeks, yang penting dalam mempercepat pengambilan data.

Sehingga desain basis data fisik pada aksesibilitas data, waktu respon, kualitas data,
keamanan, keramahan pengguna, dan faktor desain sistem informasi penting lainnya.
Aktifitas pada
Desain Database
Fisik
Keputusan-keputusan kunci dari Desain
Basis Data Fisik
• Memilih format penyimpanan (disebut tipe data) untuk setiap atribut dari model
data logis. Format dan parameter terkait dipilih untuk memaksimalkan integritas
data dan meminimalkan ruang penyimpanan.
• Memberikan panduan sistem manajemen basis data mengenai cara
mengelompokkan atribut dari model data logis ke dalam catatan fisik. Anda akan
menemukan bahwa meskipun kolom tabel relasional seperti yang ditentukan dalam
desain logis adalah definisi alami untuk konten catatan fisik, ini tidak selalu
membentuk fondasi untuk pengelompokan atribut yang paling diinginkan.
• Memberikan panduan sistem manajemen basis data mengenai bagaimana
menyusun catatan yang berstruktur serupa dalam memori sekunder (terutama hard
disk), menggunakan struktur (disebut organisasi file) sehingga catatan individu dan
kelompok dapat disimpan, diambil, dan diperbarui dengan cepat. Pertimbangan juga
harus diberikan untuk melindungi data dan memulihkan data jika ditemukan
kesalahan.
Keputusan-keputusan kunci dari Desain
Basis Data Fisik
• Memilih struktur (termasuk indeks dan arsitektur database secara
keseluruhan) untuk menyimpan dan menghubungkan file agar
pengambilan data terkait lebih efisien.
• Menyiapkan strategi untuk menangani query terhadap database yang
akan mengoptimalkan kinerja dan memanfaatkan organisasi file dan
indeks yang telah ditentukan. Struktur database yang efisien akan
bermanfaat hanya jika query dan sistem manajemen database yang
menangani query tersebut di-tuning untuk menggunakan struktur
tersebut dengan cerdas.
Sebelum desain database fisik dapat dilakukan,
penting untuk dilakukan memahami :

• Ukuran maksimum database.

Ukuran database adalah ditentukan oleh berapa banyak relasi dalam
database dan seberapa banyak data disimpan di setiap relasi. Jumlah
data dalam database akan menentukan berapa lama waktu yang
dibutuhkan untuk menjalankan operasi.
• Kegunaan dibuatnya database (ketika database di-query)
Sering kali harus dirancang untuk menjawab sejumlah besar pertanyaan
dengan cepat. Jika data sering ditambahkan ke database maka itu akan
terjadi harus dirancang untuk menangani banyak sisipan dengan cepat
Ukuran database akan menentukan beberapa
hal

• Ruang penyimpanan
Jumlah hard space yang dibutuhkan untuk menyimpan data.
• Waktu memproses
Waktu yang dibutuhkan untuk menjalankan kueri dan melakukan
pembaruan.
• Waktu Komunikasi
Waktu yang dibutuhkan untuk memindahkan data antar sistem
Ukuran database ditentukan oleh:

1. Jumlah relasi atau entitas dalam database.

2. Jumlah tupel di setiap relasi.
3. Ukuran tiap tupel dalam database.
4. Ruang penyimpanan tambahan yang dibutuhkan oleh DBMS untuk
informasi mendeskripsikan struktur database.
Penggunaan database adalah:

• Jumlah pembaruan untuk setiap entitas dalam database.

• Jumlah sisipan untuk setiap entitas dalam database.
• Jumlah penghapusan untuk setiap entitas dalam database.
• Jumlah query untuk setiap entitas dalam database.

Matriks CRUD digunakan untuk mengidentifikasi penggunaan atau database

access
• CREATE mengidentifikasi kapan data dibuat Tupel baru
• READ mengidentifikasikan kapan data dibaca melalui query
• UPDATE mengidentifikasikan kapan data yang ada diubah?
• DELETE mengidentifikasikan kapan data dihapus?
 DDL
(Data Definition Language)
Data Definition Language
(DDL)
Bahasa dalam DBMS yang digunakan untuk :
1. Membuat atau mendefinisikan basis data dan isinya
2. Menentukan struktur dan skema basisdata dan isinya
3. Memodifikasi basis data dan isinya
4. Memodifikasi struktur dan skema basisdata dan
isinya
Structured Query Language

• Structured Query Language

• Acronym: SQL
• Pronounced as “S-Q-L” [“Ess-Que-El”]
• Originally developed by IBM as the SEQUEL language in the 1970s
• SQL-92 is an ANSI national standard adopted in 1992.
• SQL:2011 is current standard.

3-16
SQL Defined
• SQL is not a programming language, but rather a data sublanguage.
• SQL is comprised of
• Data definition language (DDL)
• Used to define database structures
• Data manipulation language (DML)
• Data definition and updating
• Data retrieval (Queries)
• Data control language (DCL)
• Grant and revoke database permissions
• Transaction control language (TCL)
• Control transaction behavior
• SQL/Persistent Stored Modules (SQL/PSM)
• Procedural programming capabilities
SQL Data Definition Command

18
Perintah Dasar DDL
Data Dictionary (Kamus
Data)

• Hasil dari kompilasi perintah DDL berupa kumpulan tabel

yang disimpan dalam file khusus: Kamus Data(Data
Dictionary).
• Kamus data adalah suatu daftar data elemen yang
terorganisir dengan definisi yang tetap dan sesuai dengan
sistem, sehingga user dan analis sistem mempunyai
pengertian yang sama tentang input, output, dan komponen
data strore.
Yang harus diperhatikan dalam
perancangan database

Basis Data 2012

Tipe data yang akan
digunakan dalam tabel

1. Tipe Numeric
• Tipe data numerik digunakan untuk menyimpan data numeric (angka).
• Ciri utama data numeric adalah suatu data yang memungkinkan untuk dikenai
operasi aritmatika seperti pertambahan, pengurangan, perkalian dan
pembagian.
Basis Data
2
2. Tipe Date dan Time

• Tipe data date dan time digunakan untuk menyimpan data

tanggal dan waktu.
3. Tipe String (Text)

• Tipe data string digunakan untuk menyimpan data string

(text)

2 Basis Data 2012

5
4. Tipe BLOB (Biner)

• Tipe data blob digunakan untuk menyimpan data biner. Tipe ini biasanya
digunakan untuk menyimpan kode-kode biner dari suatu file atau object.
BLOB merupakan singkatan dari Binary Large Object.

2 Basis Data 2012

6
5. Tipe Data yang Lain

Basis Data 2012

DDL pada Database

1. Membuat Database

 Aturan penamaan sebuah database sama seperti aturan penamaan

sebuah variabel, dimana secara umum nama database boleh terdiri dari
huruf, angka dan under-score (_).

 Jika database yang akan dibuat sudah ada, maka akan muncul pesan
error. Namun jika ingin otomatis menghapus database yang lama jika
sudah ada, aktifkan option IF NOT EXISTS.
Perintahnya:

Contoh : create database if not exits akademik;

2
2. Menampilkan database yang sudah ada

3. Menggunakan database

Contoh : use akademik;

4. Menghapus basisdata

Contoh : drop database if exist akademik;

DDL pada Tabel dan Field

1. Membuat Tabel baru

• Untuk membuat table baru yang terletak pada sebuah basisdata haruslah ditentukan
terlebih dahulu nama kolom/atribut/field-nya serta tipe data kolom tersebut, dan
batasan jangkauan atau ketentuan yang lain data kolom tersebut.
• Kemudian yang tidak kalah pentingnya adalah harus ditentukan kunci datanya (key)
terutama kunci primer (primary key) dan kalau ada bisa dilengkapi dengan kunci tamu
(foreingn key).
Integrity Enhancement Feature

• Data yang dibutuhkan (Required data)

• Batasan domain (Domain constraints)
• Integritas entitas (Entity integrity)
• Integritas referensial (Referential integrity)
• Batasan enterprise (Enterprise constraints)
Required Data

• Beberapa kolom field harus memiliki nilai yang pasti (tidak

diperkenankan bernilai NULL).
• NULL digunakan untuk merepresentasikan data yang tidak ada/ tidak
tersedia, hilang atau tidak disertakan.
• Standar ISO menetapkan keyword NOT NULL untuk mengatasi hal
tersebut.
• Contoh format deklarasi :
position VARCHAR(10) NOT NULL
Domain Constraints

• Setiap kolom mempunyai domain, atau dengan kata lain himpunan

dari nilai-nilai yang benar.
• Misalkan dalam tabel Staff terdapat kolom Sex yang berisi nilai
karakter tunggal ‘M’ atau ‘F’, maka dapat dideklarasikan CHECK
(Search Condition);
• sehingga :
sex CHAR NOT NULL
CHECK (sex IN (‘M’, ‘F’));
• atau dieklarasikan secara eksplisit :
CREATE DOMAIN DomainName [AS] dataType
[CHECK (searchCondition)]
Sehingga :
CREATE DOMAIN SexType AS CHAR
CHECK (VALUE IN (‘M’, ‘F’));
DEFAULT <nilai>  artinya field tersebut jika tidak diisi nilainya, maka
nilai defaultnya adalah <nilai>
status varchar2(10) DEFAULT ‘Tidak kawin’
 Example : Creating a Table
create table product(Pname varchar(20) primary key,
price float NOT NULL,
category varchar(20) CHECK(category in("Gadget","Photography","Household")),
manufacturer varchar(20));

Attribute Data Type Constraints

Pname Varchar Primary Key
Price Float Not Null
Category Varchar Gadget, Photography, Household

Manufacturer Varchar
38
Referential integrity in SQL- example

create table account A foreign key

(branch_name char(15), is a column or
account_number char(10) not null, columns in a table
balance integer,
primary key(account_number),
that matches a primary
foreign key (branch_name) references branch(branch_name)); key in some table
in the database.
create table depositor
(customer_name char(20) not null, Are there any
account_number char(10) not null,
primary key (customer_name, account_number),
other foreign keys?
foreign key (account_number) references account(account_number),
foreign key (customer_name) references customer(customer_name));
Cascading Actions in SQL

create table account

…..
foreign key (branch-name) references branch (branch-name)
on delete cascade
on update cascade,
…)

• Due to the on delete cascade clauses, if a delete of a tuple in branch results in

referential-integrity constraint violation, the delete “cascades” to the account
relation, deleting the tuple that refers to the branch that was deleted.
• Cascading updates are similar.
Referential integrity in SQL- example
create table account
(branch_name char(15),
account_number char(10) not null,
balance integer,
primary key(account_number),
foreign key (branch_name) references branch(branch_name)
on delete cascade
on update cascade);

create table depositor

(customer_name char(20) not null,
account_number char(10) not null,
primary key (customer_name, account_number),
foreign key (account_number) references account(account_number)
on delete cascade
on update cascade,
foreign key (customer_name) references customer(customer_name)
on delete cascade
on update cascade);
Foreign Keys
• Foreign key – a (simple or composite) column which refers to the
primary key of some table in a database.
• Foreign and primary keys must be defined on same data type.
• A foreign key may be contained in a primary key or another foreign
key.
Referential Integrity
• Referential Integrity – a foreign key which identifies primary key of
table T must either be wholly null or match the value of the primary
key of some row in T
• Rationale for Referential Integrity :
• Any non-primary key column may be unknown or inapplicable (wholly null).
• An unmatched non-null foreign key identifies a non-existent object and is in
error
Referential Integrity Rules (Foreign Key Rules)

How is referential integrity maintained in a database?

Some operations that may cause a violation …
• Insert of PK values – no problem
• Update of PK values – what happens to matching foreign keys?
• Delete of PK values – what happens to matching foreign keys?
• Insert of FK values – disallowed unless matching primary key exists
• Update of FK values – disallowed unless matching primary key
exists
• Delete of FK values (FK Values set to NULL) – no problem as long
as NULL values are allowed in the FK

Bordoloi
Referential Integrity
Rules
So, for each FK in each table the database designer must specify:
• Whether or not NULLs allowed in the FK
• What should happen to the FK values should the related PK values in
the PK table are deleted or updated
Null Rule Alternatives
• Nulls allowed in foreign key columns (minimum cardinality 0)
• Nulls disallowed in foreign key columns (minimum cardinality 1)
Delete Rule Alternatives
• Delete of primary key cascades to foreign keys
• Delete of primary key nullifies foreign keys
• Delete of primary key is restricted if there are any matching foreign
keys
Update Rule Alternatives
• Update of primary key cascades to foreign keys
• Update of primary key nullifies foreign keys
• Update of primary key is restricted if there are any matching foreign
keys
Rule Alternatives: Meaning

On Delete/Update Cascade:
Any delete/update made to the PK table should be cascaded through to the FK
table.
NOTE:
If a PK value is deleted in the PK table then all the rows in the FK table with
matching FK values are also deleted in entirety.

On Delete/Update Set Null:

Any PK values that are deleted/updated in the PK table, cause affected FK values
in the FK table to be set to null.
Unlike Delete Cascade, the entire row is not deleted, only the affected FK values
are set to null.
Rule Alternatives:
Meaning
On Delete/Update Restrict:
Any updates made to the PK table that would delete or change a
primary key value will be rejected unless no foreign key references
that value in the FK table(s).
In other words, you are restricted to deleting or updating only
those PK values in the PK table which do NOT appear as FK values.
Cascading Actions in SQL
• If there is a chain of foreign-key dependencies across multiple relations, with on delete cascade
specified for each dependency, a deletion or update at one end of the chain can propagate across
the entire chain.

• If a cascading update or delete causes a constraint violation that cannot be handled by further
cascading operation, the system aborts the transaction. As a result, all the changes caused by the
transaction and its cascading actions are undone.
Entity Integrity

• Primary key dari suatu tabel harus berisi nilai yang unik, dan non-null
untuk setiap barisnya.
• Standard ISO menyediakan clause FOREIGN KEY pada perintah
CREATE dan ALTER TABLE :
PRIMARY KEY(staffNo)
PRIMARY KEY(clientNo, propertyNo)
->(Jika primary Key terdiri dari beberapa kolom)
Keys
• Keys : Minimal set of attributes which uniquely identify an instance of a entity
Many candidate keys choose one to be a primary keys

•Primary Key - a column or a set of columns that uniquely identify each row in a table

• Composite (compound) key

• Role is to enforce integrity - every table must have a primary key

• For every row the PK

: must have a non-null value
: the value must be unique
: the value must not change or become ‘null’ during the table lifetime
: columns with the above mentioned characteristics are candidate keys

•Foreign Key - column(s) in a table that serves as a PK of another table

Enforces referential integrity by completing an association between two tables
Data Integrity
• Refers to the accuracy and consistency of the data by applying
integrity constraint rules

Constraint
Constraint type
type Explanation
Explanation

Entity
Entity Integrity
Integrity No
No part
part of
of aa PK
PK can
can be
be NULL
NULL

Referential
Referential Integrity
Integrity AA FK
FK must
must match
match an
an existing
existing PK
PK value
value or
or else
else be
be NULL
NULL

Column
Column Integrity
Integrity AA column
column must
must contain
contain only
only values
values consistent
consistent with
with the
the
defined
defined data
data format
format of
of the
the column
column

User-defined
User-defined Integrity
Integrity The
The data
data stored
stored in
in the
the database
database must
must comply
comply with
with the
the
business
business rules
rules
Keys and Identifiers
• A key on FLIGHT# in FLIGHT-SCHEDULE will force all FLIGHT#’s
to be unique in FLIGHT-SCHEDULE
• Consider the following keys on DEPT-AIRPORT:

FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE

FLIGHT-SCHEDULE DEPT-AIRPORT

FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT# AIRPORT-CODE

101 delta mo 156 101 atl

545 american we 110 912 cph

912 scandinavian fr 450 545 lax

242 usair mo 231 242 bos

Integrity and Consistency
• Integrity: does the model reflect reality well?
• Consistency: is the model without internal conflicts?

• a FLIGHT# in FLIGHT-SCHEDULE cannot be null because it

models the existence of an entity in the real world
• a FLIGHT# in DEPT-AIRPORT must exist in FLIGHT-SCHEDULE
because it doesn’t make sense for a non-existing FLIGHT-
SCHEDULE entity to have a DEPT-AIRPORT
FLIGHT-SCHEDULE DEPT-AIRPORT

FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT# AIRPORT-CODE

101 delta mo 156 101 atl

545 american we 110 912 cph

912 scandinavian fr 450 545 lax

242 usair mo 231 242 bos

Triggers and Stored Procedures
• Triggers can be defined to enforce constraints on a database,
e.g.,

• DEFINE TRIGGER DELETE-FLIGHT-SCHEDULE

ON DELETE FROM FLIGHT-SCHEDULE WHERE FLIGHT#=‘X’
ACTION DELETE FROM DEPT-AIRPORT WHERE FLIGHT#=‘X’;

FLIGHT-SCHEDULE DEPT-AIRPORT

FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT# AIRPORT-CODE

101 delta mo 156 101 atl

545 american we 110 912 cph

912 scandinavian fr 450 545 lax

242 usair mo 231 242 bos

Null Values
CUSTOMER

CUSTOMER# NAME MAIDEN NAME DRAFT STATUS

123-45-6789 Lisa Smith Lisa Jones inapplicable
234-56-7890 George Foreman inapplicable drafted
345-67-8901 unknown Mary Blake inapplicable

 Null-value unknown reflects that the attribute does

apply, but the value is currently unknown. That’s ok!
 Null-value inapplicable indicates that the attribute does
not apply. That’s bad!
 Null-value inapplicable results from the direct use of
“catch all forms” in database design.
 “Catch all forms” are ok in reality, but detrimental in
database design.
Specifying Key Constraints
• PRIMARY KEY clause
• Specifies one or more attributes that make up the primary key of a relation
Dnumber INT NOT NULL PRIMARY KEY,
• Primary key attributes must be declared NOT NULL
• UNIQUE clause
• Specifies alternate (candidate) keys
Dname VARCHAR(15) UNIQUE;
• May or may not allow null values, depending on declaration
• If no key constraints, two or more tuples may be identical in all
columns.
• SQL deviates from pure relational model!
• Multiset (bag) behaviour
59
Referential Constraints
• FOREIGN KEY clause
FOREIGN KEY (Dept) REFERENCES DEPARTMENT (Dnum),
• Default operation: reject update on violation
• Attach referential triggered action clause in case referenced tuple is deleted
• Options include SET NULL, CASCADE, and SET DEFAULT
• Foreign key declaration must refer to a table already created

60
Primary Key and Foreign Key

• Primary key attributes contain both a NOT NULL and a UNIQUE

specification
• RDBMS will automatically enforce referential integrity for foreign keys
• Command sequence ends with semicolon
• ANSI SQL allows use of following clauses to cover CASCADE, SET NULL,
or SET DEFAULT
• ON DELETE and ON UPDATE

61
Specifying Tuple Constraints

• Some constraints involve several columns

• CHECK clause at the end of a CREATE TABLE statement
• Apply to each tuple individually
• Example
• CHECK (Dept_create_date <= Mgr_start_date)

62
• Hanya dapat mempunyai 1 clause PRIMARY KEY untuk setiap table,
tetapi masih dapat memastikan pemasukkan nilai yang unik untuk
beberapa alternate key dengan menggunakan keyword UNIQUE:
UNIQUE(telNo)
Referential Integrity
• Foreign Key adalah kolom atau himpunan kolom yang menghubungkan setiap
baris dalam child table yang berisi Foreign Key dengan baris dari parent table
yang berisi Primary Key yang sesuai/match.
• Integritas referential berarti, jika FK berisi suatu nilai, maka nilai tersebut
harus mengacu kesuatu baris dalam parent table.
• Standard ISO menyediakan pendefinisian untuk FK dengan clause FOREIGN
KEY dalam CREATE dan ALTER TABLE:
FOREIGN KEY(branchNo) REFERENCES Branch
What happens if we change or alter the value of an attribute that is part
of a primary key ?

Aksi yang dilakukan yang berusaha untuk merubah / menghapus (update/delete) nilai
candidate key dalam parent table yang memiliki baris yang sesuai dalam child table
tergantung pada referential action yang ditetapkan dengan subclause ON UPDATE
dan ON DELETE. Terdapat 4 pilihan aksi, yaitu :

• CASCADE, menghapus baris dari parent table dan secara otomatis menghapus
baris yang sesuai dalam child table, jika baris yang dihapus tadi merupakan
candidate key yang digunakan sebagai foreign key pada tabel lainnya, maka
aturan foreign key untuk tabel ini dihilangkan.
• SET NULL, menghapus baris pada parent table dan menetapkan nilai foreign
key dalam child table menjadi NULL. Berlaku jika kolom foreign key mempunyai
qualifier NOT NULL.
• SET DEFAULT, menghapus baris dari parent table dan menetapkan setiap
komponen foreign key dari child table menjadi defaultyang telah ditetapkan.
Berlaku jika kolom foreign key memliki nilai DEFAULT.
• NO ACTION, menolak operasi penghapusan dari parent table. Merupakan
default jika aturan ON DELETE dihilangkan
Contoh

• FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL

• FOREIGN KEY (ownerNo) REFERENCES Owner ON UPDATE CASCADE
SQL Constraints
NOT NULL
• Ensures that column does not accept nulls

UNIQUE
• Ensures that all values in column are unique

DEFAULT
• Assigns value to attribute when a new row is added to table

CHECK
• Validates data when attribute value is entered
NULL Values

• Tuples in SQL relations can have NULL as a value for

one or more components.
• Meaning depends on context.
• Two common cases:
• Missing value : e.g., we know Joe’s Bar has some
address, but we don’t know what it is.
• Inapplicable : e.g., the value of attribute spouse for an
unmarried person.
SQL for Data Definition: CREATE with CONSTRAINT

• Creating database tables with PRIMARY KEY

constraints
• The SQL CREATE TABLE statement
• The SQL CONSTRAINT keyword

CREATE TABLE EMPLOYEE(

EmpID Integer NOT NULL,
EmpName Char(25) NOT NULL
CONSTRAINT Emp_PK PRIMARY KEY(EmpID)
);
SQL for Data Definition: CREATE with CONSTRAINT

• Creating database tables with composite primary keys using

PRIMARY KEY constraints
• The SQL CREATE TABLE statement
• The SQL CONSTRAINT keyword

CREATE TABLE EMP_SKILL(

EmpID Integer NOT NULL,
SkillID Integer NOT NULL,
SkillLevel Integer NULL,
CONSTRAINT EmpSkill_PK PRIMARY KEY
(EmpID, SkillID)
);
SQL for Data Definition: CREATE with CONSTRAINT

• Creating database tables using PRIMARY KEY and FOREIGN KEY

constraints
• The SQL CREATE TABLE statement
• The SQL CONSTRAINT keyword

CREATE TABLE EMP_SKILL(

EmpID Integer NOT NULL,
SkillID Integer NOT NULL,
SkillLevel Integer NULL,
CONSTRAINT EmpSkill_PK PRIMARY KEY
(EmpID, SkillID),
CONSTRAINT Emp_FK FOREIGN KEY(EmpID)
REFERENCES EMPLOYEE(EmpID),
CONSTRAINT Skill_FK FOREIGN KEY(SkillID)
REFERENCES SKILL(SkillID)
);
SQL for Data Definition: CREATE with CONSTRAINT
• Creating database tables using PRIMARY KEY and FOREIGN KEY constraints
• The SQL CREATE TABLE statement
• The SQL CONSTRAINT keyword
• ON UPDATE CASCADE and ON DELETE CASCADE
CREATE TABLE EMP_SKILL(
EmpID Integer NOT NULL,
SkillID Integer NOT NULL,
SkillLevel Integer NULL,
CONSTRAINT EmpSkill_PK PRIMARY KEY(EmpID, SkillID),
CONSTRAINT Emp_FK FOREIGN KEY(EmpID)
REFERENCES EMPLOYEE(EmpID)
ON DELETE CASCADE,
CONSTRAINT Skill_FK FOREIGN KEY(SkillID)
REFERENCES SKILL(SkillID)
ON UPDATE CASCADE
);
DDL Specifying Keys- Introduction
• Unique keyword is used to specify keys.
• This ensures that duplicate rows are not created in the database.
• Both Primary keys and Candidate Keys can be specified in the database.
• Once a set of columns has been declared unique any data entered that duplicates the
data in these columns is rejected.
• Specifying a single column as unique:
Example
CREATE TABLE Studios
(studio_id Number,
name char(20),
city varchar(50),
state char(2),
UNIQUE (name))
• Here the name column has been declared as a candidate key
74
DDL Specifying Keys- Multiple Columns

• Specifying multiple columns as unique:

Example:
CREATE TABLE Studios
(studio_id Number,
name char(20),
city varchar(50),
state char(2),
UNIQUE (name),
UNIQUE(city, state))
• Here both name & city/state combination are declared as candidate keys

75
DDL Specifying Keys- Primary Key

• Specifying multiple columns as unique:

• To specify the Primary Key the Primary Key clause is used

Example:
CREATE TABLE Studios
(studio_id Number,
name char(20),
city varchar(50),
state char(2),
PRIMARY KEY (studio_id),
UNIQUE (name),
UNIQUE(city, state)
)

76
DDL Specifying Keys- Single and
MultiColumn Keys
• Single column keys can be defined at the column level instead of at
the table level at the end of the field descriptions.
• MultiColumn keys still need to be defined separately at the table
level

CREATE TABLE Studios

(studio_id Number PRIMARY KEY,
name char(20) UNIQUE,
city varchar(50),
state char(2),
Unique(city, state))

• Note: Some databases require the use of Unique Index for

specification of keys.
77
DDL Specifying Keys- Foreign Keys
• References clause is used to create a relationship between a set of
columns in one table and a candidate key in the table that is being
referenced.

Example:

CREATE TABLE Movies

(movie_title varchar(40),
studio_id Number REFERENCES Studios(studio_id))

• Creates a relationship from the Movies table to the Studios table

78
DDL Constraints- Disallowing Null Values
Disallowing Null Values:
• Null values entered into a column means that the data in not known.
• These can cause problems in Querying the database.
• Specifying Primary Key automatically prevents null being entered in columns which
specify the primary key
• Not Null clause is used in preventing null values from being entered in a
column.
Example:
CREATE TABLE Studios
( studio_id number PRIMARY KEY,
name char(20) NOT NULL,
city varchar(50) NOT NULL,
state char(2) NOT NULL)

• Null clause can be used to explicitly allow null values in a column also

79
DDL Constraints- Value Constraints
Value Constraints:
• Allows value inserted in the column to be checked condition in the column constraint.
• Check clause is used to create a constraint in SQL

Example:
CREATE TABLE Movies
(movie_title varchar(40) PRIMARY KEY,
studio_id Number,
budget Number check (budget > 50000)
)

• Table level constraints can also be defined using the Constraint keyword

Example:
CREATE TABLE Movies
(movie_title varchar(40) PRIMARY KEY,
studio_id Number,
budget Number check (budget > 50000),
release_date Date,
CONSTRAINT release_date_constraint Check (release_date between ’01-Jan-1980’ and ’31-dec-1989))

• Such constraints can be activated and deactivated as required.

80
DDL Constraints- Default Value
Default Value:
• A default value can be inserted in any column by using the Default keyword.
• Example:
CREATE TABLE Movies (
movie_title varchar(40) NOT NULL,
release_date date DEFAULT sysdate NULL,
genre varchar(20) DEFAULT ‘Comedy’ Check genre In (‘Comedy’, ‘Horror’,
‘Drama’)
)

• Table level constraints can also be defined using the Constraint keyword
• release_date defaults to the current date, however Null value is enabled in the column which
will need to be added explicitly when data is added.
• Note: Any valid expression can be used while specifying constraints

81
2. Melihat table yang ada pada suatu basisdata

Contoh : show tables from akademik;

3. Melihat kolom/field yang ada pada suatu table

Contoh : show fields from matakuliah; atau

show columns from matakuliah;
82
Basis Data 2012
4. Melihat struktur table

• DESC merupakan singkatan dari DESCRIBE (dalam query bisa ditulis

lengkap atau hanya 4 karakter pertama) untuk melihat struktur
suatu table.

• Contoh : desc matakuliah;

Basis Data 2012

5. Menghapus table

Contoh : drop matakuliah;

6. Memodifikasi Tabel
a. Mengubah nama table

84 Basis Data 2012

b. Menambah field pada table

Contoh : alter table matakuliah

add pengampu_mk varchar (50) not null;
c. Menambah primary key pada table

Contoh : alter table matakuliah

add primary key (kode);
85 Basis Data 2012
d. Menghapus field pada table

Contoh : alter table matakuliah drop pengampu_mk;

e. Mengubah nama field

Contoh : alter table matakuliah change kode kode_mk;

SQL 8  alter table matakuliah rename kode to kode_mk;
86
Example
• Recall Employee example:

87
Membuat database
CREATE DATABASE IF NOT EXIST COMPANY;
USE COMPANY;
Membuat tabel tabel
CREATE TABLE EMPLOYEE(
Fname VARCHAR(30) NOT NULL,
Minit VARCHAR(30) NOT NULL,
Lname VARCHAR(30) NOT NULL,
SSN CHAR(9) NOT NULL,
Bdate DATE NOT NULL,
Address VARCHAR(100) NOT NULL,
Sex CHAR NOT NULL CHECK (Sex IN(‘M’,’F’)),
Salary INT NOT NULL,
Dno INTEGER DEFAULT 1,
SUPER_SSN CHAR(9),
PRIMARY KEY (SSN),
FOREIGN KEY (Dno) REFERENCES DEPARTMENT (Dnumber)
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (SUPER_SSN) REFERENCES EMPLOYEE (SSN)
ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE DEPARTMENT (
Dname VARCHAR(10) NOT NULL,
Dnumber INTEGER NOT NULL,
MGR_SSN CHAR(9),
Mgr_start_date CHAR(9),
PRIMARY KEY (Dnumber),
UNIQUE (Dname),
FOREIGN KEY (MGR_SSN) REFERENCES EMPLOYEE
ON DELETE SET DEFAULT ON UPDATE CASCADE);

CREATE TABLE DEPT_LOCATIONS (

Dnumber INTEGER NOT NULL,
Dlocation VARCHAR(20) NOT NULL,
PRIMARY KEY (Dnumber,Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT
ON DELETE CASCADE ON UPDATE CASCADE);
91
 _T

_T

_T

_T