Ringkasan Materi #5 - Physical Database Design and Performance

Chapter 5: Physical Database Design and Performance

Horizontal Partitioning: Mendistribusikan baris-baris dari relasi logis ke dalam beberapa tabel terpisah.

• Range partitioning : Partitions defined by range of field values Could result in unbalanced distribution of rowsLike-valued fields share partitions 
• Hash partitioning : Partitions defined via hash functions Will guarantee balanced distribution of rows Partition could contain widely varying valued fields
• List partitioning : Based on predefined lists of values for the partitioning key
  
• Composite partitioning : Combination of the other approaches 

Vertical Partitioning: Mendistribusikan kolom-kolom dari relasi logis ke dalam beberapa tabel fisik terpisah.

Indeks: tabel atau struktur data lain yang digunakan untuk menentukan dalam file lokasi catatan yang memenuhi beberapa kondisi, PK secara otomatis diindeks Bidang lain atau kombinasi bidang juga bisa diindeks; ini disebut kunci sekunder (atau kunci tidak unik).

Rules for Using Indexes

• Use on larger tables
• Index the primary key of each table
• Index search fields (fields frequently in WHERE clause)
• Fields in SQL ORDER BY and GROUP BY commands
• When there are >100 values but not when there are <30 values

POSTGRE SQL

Create Table Schema

CREATE TABLE enrollment.student (

student_id INT,

name VARCHAR(25),

major VARCHAR(15),

gpa DECIMAL(6,3),

PRIMARY KEY (student_id)

INSERT INTO namaschema.namatabel VALUES (?, ?, ?, ?);

INSERT INTO enrollment.student VALUES (101, 'Bill', 'CIS', 3.45);

SQL: SHOW THE DETAIL TABLE

SELECT * FROM enrollment.student;

MODIFYING TABLE DATA

1.Mengganti nama student

UPDATE enrollment.student SET name='Ding' WHERE

student_id=101;

2. Menghapus data student yang memiliki id =103

DELETE FROM enrollment.student WHERE student_id=103;

Memodifikasi Tabel Dengan Perintah Alter

1. Menambah KOLOM atau ATRIBUT pada Tabel

ALTER TABLE enrollment.student ADD Available CHAR(1);

2. Mengurangi KOLOM atau ATRIBUT pada Tabel

ALTER TABLE enrollment.student DROP Available;

3. Menambahkan kunci primer pada tabel

ALTER TABLE namaschema.namatabel ADD PRIMARY KEY

(namaattribut yg diijadikan kunci utama);

4. Menambahkan kunci tetangga pada tabel

ALTER TABLE namaschema.namatabel ADD FOREIGN KEY

(namaattribut yg diijadikan kunci tetangga);

5. Mengubah type data suatu KOLOM

Alter Table Change Column Type ()

ALTER TABLE enrollment.student ALTER COLUMN student_id

TYPE varchar(15);

6. Mengubah nama TABEL

ALTER TABLE nama_tabel_awal RENAME TO nama_tabel_baru;

ALTER TABLE enrollment.student RENAME TO mahasiswa;

7. Mengubah nama KOLOM

ALTER TABLE namaschema.namatabel RENAME COLUMN

nama_column_awal TO nama_kolom_baru;;

ALTER TABLE enrollment.student RENAME COLUMN student_id

TO NIM;

8. Menghapus PRIMARY KEY

ALTER TABLE namaschema.namatabel DROP

CONSTRAINT nama_constraint_primary_key;

9. Menghapus FOREIGN KEY

ALTER TABLE namaschema.namatabel DROP

CONSTRAINT nama_constraint_foreign_key;

10. Menghapus CONSTRAINT

ALTER TABLE namaschema.namatabel DROP

CONSTRAINT nama_constraint_yg_akan_dihpus;

11. Menghapus KOLOM

ALTER TABLE namaschema.namatabel DROP COLUMN

nama_kolom_yg_dihapus;

QUERIES

1.   SELECT * FROM enrollment.course LIMIT 3;

2.   SELECT * FROM enrollment.enroll WHERE grade='A';

3.   SELECT * FROM enrollment.student WHERE

4.   student.student_id=(SELECT

enroll.student_id FROM enrollment.enroll WHERE grade='A-');

5.   SELECT * FROM enrollment.student WHERE student.student_id IN (SELECT

enroll.student_id FROM enrollment.enroll WHERE grade='A');

6.   SELECT student.name FROM enrollment.student, enrollment.enroll WHERE

student.student_id=enroll.student_id AND enroll.grade='A';

SORTING AND GROUPING

1.   SELECT * FROM enrollment.enroll ORDER BY grade, course_id;

2.   SELECT major, max(gpa) FROM enrollment.student GROUP BY

3.   major, gpa HAVING max(gpa)>3.40;

4.   SELECT DISTINCT grade FROM enrollment.enroll;

JOINING TABLES

1.   SELECTstudent.name,enroll.course_id, enroll.grade FROM enrollment.student INNER JOIN enrollment.enroll ON student.student_id=enroll.student_id;

2.   SELECT * FROM enrollment.student LEFT JOIN enrollment.enroll

ON student.student_id=enroll.student_id;

3.   SELECT * FROM enrollment.student RIGHT JOIN

enrollment.enroll ON student.student_id=enroll.student_id;

Sumber: PPT Dosen Pengampu Mata Kuliah Teknik Basis Data 2019/2020