Video summary

Perancangan Basis Data

Main summary

Key takeaways

Educational

Main ideas / lessons conveyed

The video continues a database normalization case study (Case Study 3), moving from 1NF/2NF into 2NF and then BCNF (Boyce–Codd Normal Form).

Key focus:

  • 2NF: separate single-value attributes (single data group) from multivalue/repeating attributes (multivalue group).
  • BCNF: remove transitive dependencies—dependencies where a non-key attribute depends on another non-key attribute (directly/indirectly through other attributes), which is not allowed under BCNF.

Final output: after applying 2NF and BCNF, the discussion produces 5 relational tables, grouped as:

  • 3 master tables (relatively stable)
  • 2 transaction tables (represent transactions such as invoices/sales and their line items)

Detailed method / step-by-step normalization instructions (as presented)

A) 2NF Normalization (Second Form)

  1. Identify which attributes are single-valued vs. multivalued

    • Single data group: attributes that appear only once per record/note (example list provided):
      • Invoice number
      • Buyer ID
      • Buyer name
      • DP (provided as “DP amount/DP”)
      • Due date
      • Cashier ID
      • Cashier name
    • Multivalue / repeated data group: attributes that can appear multiple times per invoice/note (example list provided):
      • Item code
      • Item name
      • Quantity (number of items)
      • Unit price

  2. Split into two tables

    • Table 1 (single data / header table)
      • Primary key: Invoice number
      • Other attributes: Buyer ID, Buyer name, DP, due date, cashier ID, cashier name (as described)
    • Table 2 (multivalue / detail table)
      • Formed using the primary key from Table 1 (Invoice number) plus the multivalue key attribute
      • Composite primary key: (Invoice number, Item code)
      • Other attributes: Item name, quantity, unit price

  3. Result

    • After 2NF: 2 tables exist.

B) BCNF Normalization (Eliminate Transitive Dependencies)

  1. Find and remove transitive dependencies in the 2NF tables

    • Transitive dependency described as: an attribute depends on a non-key attribute, which itself depends on the key.

  2. From the invoice/header side (first table in 2NF)

    • Buyer name depends on Buyer ID, while Buyer ID depends on Invoice number.
      • Remove buyer name from the invoice table
      • Create/ensure a separate buyer table keyed by Buyer ID
    • Cashier name depends on Cashier ID, while Cashier ID depends on Invoice number.
      • Remove cashier name from the invoice table
      • Create/ensure a separate cashier table keyed by Cashier ID
    • DP, due date, note date are described as depending on invoice number (kept as part of the invoice/sales transaction data).

  3. From the invoice detail side (second table in 2NF)

    • Product name depends on Product code, while Product code depends on Invoice number.
      • Remove product name from the invoice-detail table
      • Create/ensure a separate product/item table keyed by Item/Product code

  4. Result

    • After BCNF: 5 tables are produced.

Final schema outcome (5 relational tables) and how they’re grouped

The video states the BCNF result is five relational tables conceptually:

1) Master tables (3 tables; relatively stable)

  • Buyer table
    • attributes mentioned: Buyer ID, Buyer name
  • Cashier table
    • attributes mentioned: Cashier ID, Cashier name
  • Item/Product table
    • attributes mentioned: Product/Item code, Item/Product name

2) Transaction tables (2 tables; represent sales/invoices)

  • Invoice/Sales table
    • attributes mentioned: Invoice number (key), DP, note date, due date (as described)
  • Invoice Detail table
    • described as line items for transactions
    • relates invoice to purchased goods (composite key implied previously as Invoice number + Item code)
    • contains: item quantity and unit price (and item code)

The video also gives an example visualization of contents:

  • Buyers: “Dinda” and “Ahmad” (example)
  • Cashier: “Anggun” (example)
  • Items: four item entries (example), each with an item code and item name
  • Invoices: two invoices (example) with DP date and due date
  • Detail table: shows transaction details of items bought

Speakers / sources featured

  • No specific named external sources.
  • Main speaker: the course instructor/lecturer (not identified by name in the subtitles).

Original video