[ File # csp10909445, License # 3089529 ]
Licensed through http://www.canstockphoto.com in accordance with the End User License Agreement (http://www.canstockphoto.com/legal.php)
(c) Can Stock Photo Inc. / z_amir

Database Praktikum pertemuan 3


Sintaks (Syntax)

 

Create Table

CREATE TABLE  table_name

(

field1_name  datatype ( length ),

field2_name  datatype ( length ),

field3_name  datatype ( length ),

….

PRIMARY KEY ( field_name ),

FOREIGN KEY  ( field_name ) REFERENCES  table_name,

CONSTRAINT [constraint_name] CHECK (criteria)

)

 

Drop Table

DROP TABLE table_name

 

Alter Table

ALTER TABLEtable_name

ADD [ column_name datatype | CONSTRAINT constraint_name CHECK(criteria) | PRIMARY KEY(primary_key_name)]

 

ALTER TABLE table_name

DROP [COLUMN column_name | CONSTRAINT constraint_name]


Materi :

create database Salon

/create table [nama_table](
nama_variable tipe_data
)/
–tidak bisa di-run dua kali karena datanya sudah dicreate
–kalau di-run bagian database lain bisa karena beda database
–nchar-> tipe data yg seperti tulisan mandarin, jepang (n=nasional)
–decimal sama seperti float, float jarang digunakan
–10,2-> 2 angka di belakang koma, 10->sizenya
— 1 atribute dgn atribut lain dipisah dengan koma
–not null-> waktu insert data, datanya ga boleh kosong
— primary key ->kasih tau supaya 1row beda dengan yg lain
–Nim char(10)not null primary key, –> bisa
–alt + F1 -> seperti run tp lebih memberi tahu ttg detailnya , blok nama tabel baru alternate

create table a(
a1 int
)

create table Mahasiswa(

Nim char(10)not null,

Nama varchar(50),

TanggalLahir date,

Ipk decimal(10,2) ,

primary key(Nim)
)

SELECT *FROM Mahasiswa
Select * FROM Kelas

–insert into Mahasiswa values(NULL,’Andy’,’1995-12-05′,4.00)->tidak bisa karena Nim not null
insert into Mahasiswa(Nim, Nama,TanggalLahir,Ipk,IdJurusan)
values(‘1601234568′,’Andy’,’1995-12-05′,4.00,NULL)

insert into Kelas values(’04PLT’,’1601234570′)

–Cara 1 : drop tabel nama_tabel ->buat hapus dan ulang untuk input data –>cara sesat
–setelah hapus, kalau mau pakai atribut sebelumnya tinggal di run bagian tabel
drop table Mahasiswa

–Cara 2 : Alter tabel nama_tabel -> nambah tabel
alter table Mahasiswa
add IdJurusan char(2)default ’01’

delete from Mahasiswa –buat hapus

create table Kelas(
IdKelas char(5),
Nim char(10),
primary key(IdKelas, Nim), –perlu 2 primary key
foreign key (Nim) references Mahasiswa(Nim),
)

–constraint = batasan

alter table Mahasiswa — untuk 1 atribut saja
add constraint c1 check(Nim like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’)

alter table Mahasiswa
add constraint c2 check(LEN(Nama) between 4 and 10)

alter table Mahasiswa
drop c2 –drop constraint..tidak ada keyword constraint

alter table Mahasiswa
drop column Ipk

Select * from sys.tables where name like ‘c2’
SP_TABLES
SP_MSFOREACHTABLE ‘drop table?’


 

Tabel Relasional
Tabel Relasional

Soal (Case)

1. Buatlah tabel-tabel dibawah ini dengan ketentuan sebagai berikut :

Nama Tabel : MsCustomer

Primary Key : CustomerId

Nama Field Tipe Data Length Keterangan
CustomerId char 5 Harus diisi dan panjang = 5, harus diawali dengan ‘CU’ dan 3 digit terakhirnya berupa angka
CustomerName varchar 50 Tidak boleh kosong
CustomerGender varchar 10 Harus berupa ‘Male’ atau ‘Female’
CustomerPhone varchar 13
CustomerAddress varchar 100

 

create table MsCustomer(

CustomerId char(5),

CustomerName varchar(10) not NULL,

CustomerGender varchar(10),

CustomerPhone varchar(13),

CustomerAddress varchar(100),

Primary Key(CustomerId),

Constraint cek_CustomerId Check( CustomerId like ‘CU[0-9][0-9][0-9]’)

)

Nama Tabel : MsStaff

Primary Key : StaffId

Nama Field Tipe Data Length Keterangan
StaffId char 5 Harus diisi dan panjang = 5, harus diawali dengan ‘SF’ dan 3 digit terakhirnya berupa angka
StaffName varchar 50 Tidak boleh kosong
StaffGender varchar 10 Harus berupa ‘Male’ atau ‘Female’
StaffPhone varchar 13
StaffAddress varchar 100
StaffSalary numeric 11, 2
StaffPosition varchar 20

 

Create table MsStaff(

StaffId char(5),

StaffName varchar(50) not NULL,

StaffGender varchar(10),

StaffPhone varchar(13),

StaffAddress varchar(100),

StafSalary decimal(11,2),

StaffPosition varchar(20),

Primary Key(StaffId),

Constraint cek_StaffId Check( StaffId like ‘SF[0-9][0-9][0-9]’)

)

 

Nama Tabel : MsTreatmentType

Primary Key : TreatmentTypeId

Nama Field Tipe Data Length Keterangan
TreatmentTypeId char 5 Harus diisi dan panjang = 5, harus diawali dengan ‘TT’ dan 3 digit terakhirnya berupa angka
TreatmentTypeName varchar 50 Tidak boleh kosong

Create table MsTreatmentType(

TreatmentTypeId char(5),

TreatmentTypeName varchar(50) not NULL,

Primary Key (TreatmentTypeId),

Constraint cek_TreatmentTypeId Check(TreatmentTypeId like ‘IT[0-9][0-9][0-9]’)

)

 

Nama Tabel : MsTreatment

Primary Key : TreatmentId

Nama Field Tipe Data Length Keterangan
TreatmentId char 5 Harus diisi dan panjang = 5, harus diawali dengan ‘TM’ dan 3 digit terakhirnya berupa angka
TreatmentTypeId char 5 Tidak boleh kosong
TreamentName varchar 10 Tidak boleh kosong
Price numeric 11, 2

 

Cara 1 :

Create table MsTreatment(

TreatmentId char(5),

TreatmentTypeId char(5) not NULL,

TreatmentName varchar(10) not NULL,

Price decimal(11,2),

Primary Key( TreatmentId),

Foreign Key(TreatmentTypeId)  References MsTreatmentType (TreatmentTypeId) On Update     Cascade On delete Cascade,

Constraint cek_TreatmentId Check(TreatmentId like ‘TM[0-9][0-9][0-9]’)

)

 

Cara 2 :

Create table MsTreatment1(

TreatmentId char(5),

TreatmentTypeId char(5) not NULL References MsTreatmentType On Update Cascade On delete Cascade,

TreatmentName varchar(10) not NULL,

Price decimal(11,2),

Primary Key( TreatmentId),

Constraint cek_TreatmentId1 Check(TreatmentId like ‘TM[0-9][0-9][0-9]’)

)

 

Nama Tabel : HeaderSalonServices

Primary Key : TransactionId

Nama Field Tipe Data Length Keterangan
TransactionId char 5 Harus diisi dan panjang = 5, harus diawali dengan ‘TR’ dan 3 digit terakhirnya berupa angka
CustomerId char 5 Tidak boleh kosong
StaffId char 5 Tidak boleh kosong
TransactionDate date
PaymentType varchar 20

 

Cara 1 :

Create table HeaderSalonServices (

TransactionId char(5),

CustomerId char(5) not NULL,

StaffId char(5) not NULL,

TransactioDate date,

PaymentType varchar(20),

Primary Key(TransactionId),

Foreign Key(CustomerId)  References MsCustomer (CustomerId)

On Update Cascade On delete Cascade,

Foreign Key(StaffId)  References MsStaff (StaffId)

On Update Cascade On delete Cascade,

Constraint cek_TransactionId Check( TransactionId like ‘TR[0-9][0-9][0-9]’)

)

 

Cara 2 :

Create table HeaderSalonServices (

TransactionId char(5) Primary Key,

CustomerId char(5) not NULL References MsCustomer On Update Cascade On delete Cascade,

StaffId char(5) not NULL References MsStaff On Update Cascade On delete Cascade,

TransactioDate date,

PaymentType varchar(20),

Constraint cek_TransactionId Check( TransactionId like ‘TR[0-9][0-9][0-9]’)

)

Nama Tabel : DetailSalonServices

Primary Key : TransactionId, TreatmentId

Nama Field Tipe Data Length Keterangan
TransactionId char 5 Tidak boleh kosong
TreatmentId char 5 Tidak boleh kosong

 

Cara 1 :

Create table DetailSalonServices(

TransactionId char(5) not NULL References HeaderSalonServices

On Update Cascade On Delete Cascade

TreatmentId char(5) not NULL References MsTreatment

On Update Cascade On Delete Cascade,

Primary Key(TransactionId, TreatmentId)

)

 

Cara 2 :

Create table DetailSalonServices(

TransactionId char(5) not NULL,

TreatmentId char(5) not NULL,

Primary Key(TransactionId, TreatmentId),

Foreign Key(TransactionId) References HeaderSalonServices(TransactionId)

On Update Cascade On Delete Cascade,

Foreign Key(TreatmentId )References MsTreatment (TreatmentId)

On Update Cascade On Delete Cascade,

)

 

  1. Hapuslah tabel DetailSalonServices. (drop table)

DROP TABLE DetailSalonServices

 

  1. Buatlah tabel dengan ketentuan berikut :

Nama Tabel : DetailSalonServices

Nama Field Tipe Data Length Keterangan
TransactionId char 5 Tidak boleh kosong
TreatmentId char 5 Tidak boleh kosong

 

Kemudian dengan perintah alter, ubahlah field TransactionId dan TreatmentId sehingga menjadi primary key dari tabel tersebut. (create table, alter table, primary key)

Create table DetailSalonServices(

TransactionId char(5) not NULL

TreatmentId char(5) not NULL

)

Alter table DetilSalonServices

add Primary Key (TransactionId, TreatmentId)

 

  1. Tambahkan constraint untuk tabel MsStaff dimana panjang StaffName harus diantara 5 dan 20, data yang terdahulu tidak perlu di cek. Kemudian hapuslah constraint tersebut. (alter table, with nocheck, add constraint, drop)

Alter table MsStaff with nocheck

Add constraint C1 Check ( LEN(StaffName) between 5 and 20)

 

Alter table MsStaff

Drop C1

 

 

  1. Tambahkan kolom ‘Description’ pada tabel MsTreatment dengan tipe data varchar dengan panjang 100 karakter. Lalu hapuslah kolom tersebut.

(alter table, add, drop column)

 

Alter table MsTreatment

Add Description varchar (100)

 

Alter table MsTreatment

Drop column Description

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s