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