Database Normalization: Why and How, Learning Objectives, and Video Narratives
This content delves into the importance of normalization in database design, covering topics such as database efficiency, data redundancy reduction, and normalization methodologies like top-down and bottom-up approaches. Through images and narrations, it explains the significance of accurate data, successful normalization completion objectives, and practical ER modeling examples. Dive into the world of database normalization with this educational material.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
ID: RF Page 1 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Database Normalization
ID: RF Page 2 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Learning Objective On successful completion of this Material, students will be able to: Explain about Normalization and why database need to be normalized Perform normalization from UNF until 3NF
ID: RF Page 3 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Narasi: In developing database, it s important to have accurate data to maximize the efficiency of a database. It means there is no data redundancy in our database and make it easier to manage. Marisa Karsen, Subject Matter Expert
ID: RF Page 4 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Narasi: Normalization is a methodology to optimally design a database to reduce redundant data. Without normalization, database systems can be inaccurate, slow, and inefficient, and they might not produce the data you expected. There are two types of Normalization approaches, which are top down and bottom- up. Marisa Karsen, Subject Matter Expert
ID: RF Page 5 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Silahkan langsung pakai diagram ER Modeling dan tabel tersebut TOP-DOWN APPROACH Management Narasi: There are two types of Normalization approaches, which are top down and bottom- up. ER Modeling Database Normalization
ID: RF Page 6 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong gambarnya dibuat ulang ya, sederhana saja. Terima kasih. BOTTOM-UP APPROACH Narasi: There are two types of Normalization approaches, which are top down and bottom- up. Database Normalization
ID: RF Page 7 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong diagramnya dibuat ulang tapi sederhana aja dan tulisannya agak besar. Animasi: Setelah narasi selesai, table tersebut mengecil dan keatas. Narasi (VO) : In this module, we more concern to explain bottom-up normalization process, which is information gathered from transaction form or un- normalized table that we get from daily transaction in the operational level.
ID: RF Page 8 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong diagramnya dibuat ulang tapi sederhana aja dan tulisannya agak besar. Animasi : Kotak first normal form (1NF) berkedip ketika disebut. Kotak second normal form (2NF) berkedip ketika disebut. Kotak third normal form (3NF) berkedip ketika disebut. Narasi (VO) : Commonly, there are three steps in normalization processes which are first normal form, second normal form and third normal form. Here is the example of normalization process
ID: RF Page 9 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Narasi (VO) : Violet Baby shop is a shop that sells babies equipment and accessories. In this case we perform normalization process where a customer bought some items and the shop create invoice.
ID: RF Page 10 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Narasi : The first step to start normalization process is define our table or form as an Un- normalized Form (UNF) which means there are several data redundancies if we add, delete, or modify data into this form. Un-Normalize Form (UNF)
ID: RF Page 11 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Mohon tabel dibuat ulang dengan menggunakan warna warni yang lebih menarik, dan diperbesar font nya (secukupnya aja) Kalau tidak muat, tidak apa-apa tapi tolong di zoom in (dari kiri ke kanan) selama 3 detik baru lanjut ke slide berikutnya. Un-Normalize Form (UNF) Invoice CompanyName+Address+InvoiceNo+Date+StaffID+StaffName+CustID+CustName +{No+ItemID+ItemName+Qty+UnitPrice+Amount}+SubTotal+Discount+Total No ItemID 1 B053 Disney Baby Bouncer Sub Total IDR 3,320,000 Discount Total IDR 0 IDR 3,320,000 InvoiceNo V021 Date StaffID MK09 StaffName Sheren CustID CR001 CustName Crystal Marris Item Name Qty 1 Unit Price IDR 500,000 Amount IDR 500,000 April, 4th 2016 S302 Chiko Baby Stroller B001 Pigeon Baby Bottle Feeding C042 Baby Clothes (size 3-6 months) 12 1 2 IDR 2,000,000 IDR 2,000,000 IDR 50,000 IDR 50,000 2 3 IDR 100,000 IDR 600,000 4 5 T034 Baby Toys 3 IDR 40,000 IDR 120,000 Narasi (VO): Here is Un-normalized Form (UNF) for Violet Baby Shop :
ID: RF Page 12 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Narasi : After we identify Un- Normalized form, we continue to identify First Normal Form. In 1NF, we Remove Repeating Group to create another table. First Normalize Form (1NF) RULE: Remove repeating group
ID: RF Page 13 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong animasikan: 1. Kolom InvoiceNo berkedip merah, 2. Muncul tabel Invoice Detail. 3. Setelah narasi selesai, kata InvoiceNo yang dilingkari merah ditambahi underline. First Normalize Form (1NF) Invoice Header CompanyName+Address+InvoiceNo+Date+StaffID+StaffName+CustID+CustName CompanyName Address InvoiceNo Date StaffID StaffName CustID CustName Mall of Tangerang Ground Floor Blok. C No. 21.Tangerang Raya Street No.51.Ph. (021- 7347575) April, 4th 2016 Violet Baby Shop Crystal Marris V021 MK09 Sheren CR001 Narasi (VO) : In this case, we separate Invoice Header with Invoice Detail because there are repeating groups in invoice detail which are ItemID, ItemName, Quantity, and UnitPrice. We also identify primary key in Invoice Header, which is Invoice Number and add Invoice Number and ItemID as primary key for Invoice Detail Invoice Detail InvoiceNo+ItemID+ItemName+Qty+UnitPrice ItemID InvoiceNo V021 Item Name Qty 1 Unit Price IDR 500,000 B053 Disney Baby Bouncer S302 Chiko Baby Stroller 1 IDR 2,000,000 B001 Pigeon Baby Bottle Feeding 2 IDR 50,000 C042 Baby Clothes (size 3-6 months) 12 IDR 50,000 T034 Baby Toys 3 IDR 40,000
ID: RF Page 14 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Narasi : After we create First Normal Form (1NF), we continue to create Second Normal Form (2NF). The Rule in Second Normal Form is is Remove Partial Dependency. SECOND NORMAL FORM (2NF) RULE: Remove partial dependency
ID: RF Page 15 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong animasikan: 1.Kolom ItemID berubah merah dan lingkaran merah di InvoiceNo 2.Muncul tulisan item 3.Lalu muncul tulisan ItemID+ItemName+UnitP rice 4.tulisan itemID di invoice detail berubah jadi #ItemID 5.Kolom item Name dan unit price pindah dari tabel invoice detail ke tabel item SECOND Normalize Form (2NF) Invoice Detail InvoiceNo+ItemID+ItemName+Qty+UnitPrice ItemID InvoiceNo V021 Item Name Qty 1 Unit Price IDR 500,000 B053 Disney Baby Bouncer S302 Chiko Baby Stroller 1 IDR 2,000,000 B001 Pigeon Baby Bottle Feeding 2 IDR 50,000 C042 Baby Clothes (size 3-6 months) 12 IDR 50,000 T034 Baby Toys 3 IDR 40,000 Item ItemID+ItemName+UnitPrice Narasi (VO) : (animasi no.1 mulai) In this case, Item Table has partial dependency with Invoice Table because Item is the main component in sales . Based on this dependency, (animasi no.2-3 mulai) we create Item table with ItemID as the primary key, and also identified ItemID in Invoice Detail as foreign key (animasi no.4-5mulai) . At the same time, we move ItemName and Unit Price from Invoice Header to Item table . ItemID Item Name Unit Price B053 Disney Baby Bouncer IDR 500,000 S302 Chiko Baby Stroller IDR 2,000,000 B001 Pigeon Baby Bottle Feeding IDR 50,000 C042 Baby Clothes (size 3-6 months) IDR 50,000 T034 Baby Toys IDR 40,000
ID: RF Page 16 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tampilan tabelnya nanti terakhir seperti ini. SECOND Normalize Form (2NF) Invoice Detail InvoiceNo+#ItemID+Qty Narasi (VO) : InvoiceNo ItemID Qty V021 B053 1 S302 1 B001 2 C042 12 T034 3 Item ItemID+ItemName+UnitPrice ItemID Item Name Unit Price B053 Disney Baby Bouncer IDR 500,000 S302 Chiko Baby Stroller IDR 2,000,000 B001 Pigeon Baby Bottle Feeding IDR 50,000 C042 Baby Clothes (size 3-6 months) IDR 50,000 T034 Baby Toys IDR 40,000
ID: RF Page 17 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong animasikan: Muncul tabel item ketika disebut Narasi (VO) : After we create Second Normal Form (2NF), we continue to create Third Normal Form (3NF). The rule in 3NF is Remove Transitive Dependency. Transitive Dependency means that the attribute of the table are indirectly dependence with the main table (indonesianya adalah atribut pada tabel tidak tergantung secara langsung dengan table utamanya Invoice) THIRD NORMAL FORM (3NF) RULE: Remove Transitive Dependency
ID: RF Page 18 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong animasikan: 1. Kolom StaffID dan StaffName berkedip merah. 2. Kolom CustID dan CustName 3. Tabel baru Staff dan Customer muncul THIRD Normalize Form (3NF) Invoice Header CompanyName+Address+InvoiceNo+Date+StaffID+StaffName+CustID+CustName CompanyName Address InvoiceNo Date StaffID StaffName CustID CustName Mall of Tangerang Ground Floor Blok. C No. 21.Tangerang Raya Street No.51.Ph. (021- 7347575) April, 4th 2016 Violet Baby Shop Crystal Marris V021 MK09 Sheren CR001 Narasi (VO) : In this case, (1) Staff has Staff ID and Staff Name and also (2) Customer has Customer ID and Customer Name in Invoice Header table. That s mean there are another 2 tables that have transitive dependency with invoice table because we need to input Staff ID and Customer ID to get the information about staff name and customer name. So, in 3NF, (3) we create 2 new tables which are Staff table with Staff ID as Primary Key and Customer table with Customer ID as Primary Key. Staff StaffID+StaffName Customer CustID+CustName StaffID MK09 StaffName Sheren CustID CustName CR001 Crystal Marris
ID: RF Page 19 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong animasikan: Tabel Invoice Header menjadi seperti ini. THIRD Normalize Form (3NF) Invoice Header CompanyName+Address+InvoiceNo+Date+#StaffID+#CustID CompanyName Address InvoiceNo Date StaffID CustID Mall of Tangerang Ground Floor Blok. C No. 21.Tangerang Raya Street No.51.Ph. (021- 7347575) Narasi (VO) : At the same time, we move StaffName and CustName from Invoice Header table to the new tables, and identify another foreign key which are StaffID and CustID. April, 4th 2016 Violet Baby Shop V021 MK09 CR001 Staff StaffID+StaffName Customer CustID+CustName StaffID MK09 StaffName Sheren CustID CustName CR001 Crystal Marris
ID: RF Page 20 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Tolong animasikan: Muncul tabel item ketika disebut DATABASE NORMALIZATION RESULT Invoice Header CompanyName+Address+InvoiceNo+Date+#StaffID+#CustID CompanyName Address InvoiceNo Date StaffID CustID Mall of Tangerang Ground Floor Blok. C No. 21.Tangerang Raya Street No.51.Ph. (021- 7347575) April, 4th 2016 Narasi (VO) : So this is the end result of database normalization process until Third Normal Form (3NF) and there are total 5 tables have created. Violet Baby Shop V021 MK09 CR001 Invoice Detail InvoiceNo+#ItemID+Qty Item ItemID+ItemName+UnitPrice InvoiceNo ItemID Qty ItemID Item Name Unit Price V021 B053 1 B053 Disney Baby Bouncer IDR 500,000 S302 1 S302 Chiko Baby Stroller IDR 2,000,000 B001 2 B001 Pigeon Baby Bottle Feeding IDR 50,000 C042 12 C042 Baby Clothes (size 3-6 months) IDR 50,000 T034 3 T034 Baby Toys IDR 40,000 Customer CustID+CustName Staff StaffID+StaffName StaffID StaffName CustID CustName MK09 Sheren CR001 Crystal Marris
ID: RF Page 21 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Video: Silahkan pakai tabel ini langsung. Narasi (VO): After we learned about database normalization process, it s time for you to do it by using this case. Good luck!
ID: RF Page 22 Module: Database Normalization Course: Database Design and Application Screen title: ID s Note Subject Matter Expert Instructional Designer Multimedia Developer : Marisa Karsen : Riefni Riftianingrum :
