Praktikum III : Memanipulasi data dalam tabel pada PostgreSQL

Pada praktikum ini, digunakan query berupa SELECT, FROM, dan WHERE. Lebih lengkapnya, silakan klik disini.

Praktikum Basis Data II : Pembuatan tabel data dengan menggunakan pgAdmin

Praktikum Basis Data II memuat pembuatan tabel data dengan menggunakan pgAdmin. Dalam praktiknya, terdapat 3 buah tabel yang dibuat, yaitu: tabel accounts, tabel roles, dan tabel account_roles. Untuk membuat tabel tersebut dapat dilakukan beberapa langkah. Lebih lengkapnya, silakan klik disini.

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

Ringkasan Materi #4 - Logical Database Design and The Relational Model

Chapter 4: Logical Database Design and The Relational Model

Primary keys adalah pengidentifikasi unik dari hubungan tersebut. Contohnya termasuk nomor karyawan, nomor jaminan sosial, dll. Ini menjamin bahwa semua baris unik.

Foreign keys adalah pengidentifikasi yang memungkinkan hubungan dependen (di banyak sisi hubungan) untuk merujuk ke hubungan induknya (di satu sisi hubungan).

Relation is a named, two-dimensional table of data. A table consists of rows (records) and columns (attribute or field). Requirements for a table to qualify as a relation: a unique name. Every attribute value must be (not multivalued, not composite).Every row must be unique (can’t have two rows with exactly the same values for all their fields). NOTE: All relations are in  1st Normal form.

Referential Integrity– aturan menyatakan bahwa nilai FK apa pun (pada relasi dari banyak sisi) HARUS cocok dengan PK dalam relasi satu sisi. (Atau FK bisa nol). Integrity Constraints, For example: Delete Rules

Restrict–don’t allow delete of  “parent” side if related rows exist in “dependent” side

Cascade–automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted

Set-to-Null–set the foreign key in the dependent side to null if deleting from the parent side  not allowed for weak entities

Transforming EER Diagrams into Relations (cont.)

• Multivalued Attribute: Becomes a separate relation with a foreign key taken from the superior entity
• Mapping Weak Entities: Becomes a separate relation with a foreign key taken from the superior entity

Mapping Binary Relationships

• One-to-Many–Primary key on the one side becomes a foreign key on the many side

• Many-to-Many–Create a new relation with the primary keys of the two entities as its primary key

• One-to-One–Primary key on mandatory side becomes a foreign key on optional side

Mapping Associative Entities

Default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship). Kalau ada identifier PK di parents jadi FK di child, kalau tidak ada maka PK = PK

Mapping Unary Relationships: One-to-Many–Recursive foreign key in the same relation

Many-to-Many–Two relations: One for the entity type, One for an associative relation in which the primary key has two attributes, diambil dari entity

Mapping Ternary (and n-ary) Relationships:

One relation for each entity and one for the associative entity. Associative entity has foreign keys to each entity in the relationship. Remember that the primary key MUST be unique. This is why treatment date and time are included in the composite primary key.

Mapping Supertype/Subtype Relationships

• Well-Structured Relations A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies. Goal is to avoid anomalies.
• Insertion Anomaly–adding new rows forces user to create duplicate data.
• Deletion Anomaly–deleting rows may cause a loss of data that would be needed for other future rows.
• Modification Anomaly–changing data in a row forces changes to other rows because of duplication

First Normal Form Menghilangkan duplikasi kolom dari tabel yang sama, Buat tabel terpisah untuk masing-masing kelompok data terkait dan mengidentifikasi setiap baris dengan kolom yang unik (primary key).

Second Normal Form Menghapus beberapa subset data yang ada pada tabel dan menempatkan mereka pada tabel terpisah.|Menciptakan hubungan antara tabel baru dan tabel lama dengan menciptakan foreign key.|Tidak ada atribut dalam tabel yang secara fungsional bergantung pada candidate key tabel tersebut.

Third Normal Form Normalisasi database dalam bentuk 3NF bertujuan untuk menghilangkan seluruh atribut atau field yang tidak berhubungan dengan primary key. Dengan demikian tidak ada ketergantungan transitif pada setiap kandidat key. Syarat dari bentuk normal ketiga atau 3NF, yaitu: memenuhi semua persyaratan dari bentuk normal kedua dan menghapus kolom yang tidak tergantung pada primary key.

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