Course Database Design and Application Module Database Normalization

Course: Database Design and Application Module: Database Normalization Screen title: ID’s Note Database Normalization ID: RF Page 1

Course: Database Design and Application Module: Database Normalization ID: RF 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 3 NF Page 2

Course: Database Design and Application Module: Database Normalization 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 3

Course: Database Design and Application Module: Database Normalization 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 bottomup. Marisa Karsen, Subject Matter Expert ID: RF Page 4

Course: Database Design and Application Module: Database Normalization ID: RF 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 bottomup. ER Modeling Database Normalization Page 5

Course: Database Design and Application ID: RF Module: Database Normalization Screen title: ID’s Note Video: Tolong gambarnya dibuat ulang ya, sederhana saja. Terima kasih. Narasi: There are two types of Normalization approaches, which are top down and bottomup. BOTTOM-UP APPROACH Database Normalization Page 6

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 unnormalized table that we get from daily transaction in the operational level. Module: Database Normalization ID: RF Page 7

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 (1 NF) berkedip ketika disebut. • Kotak second normal form (2 NF) berkedip ketika disebut. • Kotak third normal form (3 NF) 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 Module: Database Normalization ID: RF Page 8

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. Module: Database Normalization ID: RF Page 9

Course: Database Design and Application Module: Database Normalization Screen title: ID’s Note Video: Narasi : The first step to start normalization process is define our table or form as an Unnormalized 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 10

ID: RF Module: Database Normalization Course: Database Design and Application Page 11 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. Narasi (VO): Here is Un-normalized Form (UNF) for Violet Baby Shop : Un-Normalize Form (UNF) Invoice Company. Name+Address+Invoice. No+Date+Staff. ID+Staff. Name+Cust. ID+Cust. Nam e+{No+Item. ID+Item. Name+Qty+Unit. Price+Amount}+Sub. Total+Discount+Total Invoice. No Date Staff. ID Staff. Name Cust. ID Cust. Name No Item. ID Item Name V 021 April, 4 th MK 09 Sheren CR 001 Crystal Marris 1 B 053 Disney Baby Bouncer 2016 2 3 4 5 Qty 1 Unit Price IDR 500, 000 Discount Total Amount Sub Total IDR 500, 000 IDR 3, 320, 000 S 302 Chiko Baby Stroller 1 IDR 2, 000, 000 B 001 Pigeon Baby Bottle Feeding 2 IDR 50, 000 IDR 100, 000 C 042 Baby Clothes (size 3 -6 months) 12 IDR 50, 000 IDR 600, 000 T 034 Baby Toys 3 IDR 40, 000 IDR 120, 000

Course: Database Design and Application Module: Database Normalization Screen title: ID’s Note Video: Narasi : After we identify Un. Normalized form, we continue to identify First Normal Form. In 1 NF, we Remove Repeating Group to create another table. First Normalize Form (1 NF) RULE: Remove repeating group ID: RF Page 12

ID: RF Module: Database Normalization Course: Database Design and Application Page 13 Screen title: ID’s Note Video: Tolong animasikan: 1. Kolom “Invoice. No” berkedip merah, 2. Muncul tabel Invoice Detail. 3. Setelah narasi selesai, kata “Invoice. No” yang dilingkari merah ditambahi underline. Narasi (VO) : In this case, we separate Invoice Header with Invoice Detail because there are repeating groups in invoice detail which are Item. ID, Item. Name, Quantity, and Unit. Price. We also identify primary key in Invoice Header, which is Invoice Number and add Invoice Number and Item. ID as primary key for Invoice Detail First Normalize Form (1 NF) Invoice Header Company. Name+Address+Invoice. No+Date+Staff. ID+Staff. Name+Cust. ID+Cust. Nam e Company. Name Violet Baby Shop Address Mall of Tangerang Ground Floor Blok. C No. 21. Tangerang Raya Street No. 51. Ph. (021 -7347575) Invoice. No Date Staff. ID V 021 April, 4 th MK 09 2016 Staff. Name Cust. ID Sheren CR 001 Cust. Name Crystal Marris Invoice Detail Invoice. No+Item. ID+Item. Name+Qty+Unit. Price Invoice. No V 021 Item. ID B 053 Item Name Disney Baby Bouncer S 302 Qty 1 Unit Price IDR 500, 000 Chiko Baby Stroller 1 IDR 2, 000 B 001 Pigeon Baby Bottle Feeding 2 IDR 50, 000 C 042 Baby Clothes (size 3 -6 months) 12 IDR 50, 000 T 034 Baby Toys 3 IDR 40, 000

Course: Database Design and Application Module: Database Normalization Screen title: ID’s Note Video: Narasi : After we create First Normal Form (1 NF), we continue to create Second Normal Form (2 NF). The Rule in Second Normal Form is is Remove Partial Dependency. SECOND NORMAL FORM (2 NF) RULE: Remove partial dependency ID: RF Page 14

Course: Database Design and Application ID: RF Module: Database Normalization Screen title: ID’s Note Video: Tolong animasikan: 1. Kolom Item. ID berubah merah dan lingkaran merah di Invoice. No 2. Muncul tulisan item 3. Lalu muncul tulisan Item. ID+Item. Name+Unit. P rice 4. tulisan item. ID di invoice detail berubah jadi #Item. ID 5. Kolom item Name dan unit price pindah dari tabel invoice detail ke tabel item 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 Item. ID as the primary key, and also identified Item. ID in Invoice Detail as foreign key (animasi no. 4 -5 mulai). At the same time, we move Item. Name and Unit Price from Invoice Header to Item table. SECOND Normalize Form (2 NF) Invoice Detail Invoice. No+Item. ID+Item. Name+Qty+Unit. Price Invoice. No V 021 Item. ID B 053 Item Name Disney Baby Bouncer S 302 Qty 1 Unit Price IDR 500, 000 Chiko Baby Stroller 1 IDR 2, 000 B 001 Pigeon Baby Bottle Feeding 2 IDR 50, 000 C 042 Baby Clothes (size 3 -6 months) 12 IDR 50, 000 T 034 Baby Toys 3 IDR 40, 000 Item. ID+Item. Name+Unit. Price Item. ID Item Name Unit Price B 053 Disney Baby Bouncer IDR 500, 000 S 302 Chiko Baby Stroller B 001 Pigeon Baby Bottle Feeding IDR 50, 000 C 042 Baby Clothes (size 3 -6 months) IDR 50, 000 T 034 Baby Toys IDR 40, 000 IDR 2, 000 Page 15

Course: Database Design and Application Module: Database Normalization Screen title: ID’s Note Video: Tampilan tabelnya nanti terakhir seperti ini. SECOND Normalize Form (2 NF) Invoice Detail Invoice. No+#Item. ID+Qty Narasi (VO) : Invoice. No Item. ID Qty V 021 B 053 1 S 302 1 B 001 2 C 042 12 T 034 3 Item. ID+Item. Name+Unit. Price Item. ID Item Name Unit Price B 053 Disney Baby Bouncer IDR 500, 000 S 302 Chiko Baby Stroller B 001 Pigeon Baby Bottle Feeding IDR 50, 000 C 042 Baby Clothes (size 3 -6 months) IDR 50, 000 T 034 Baby Toys IDR 40, 000 IDR 2, 000 ID: RF Page 16

Course: Database Design and Application Module: Database Normalization Screen title: ID’s Note Video: Tolong animasikan: • Muncul tabel item ketika disebut Narasi (VO) : After we create Second Normal Form (2 NF), we continue to create Third Normal Form (3 NF). The rule in 3 NF is Remove 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 (3 NF) RULE: Remove Transitive Dependency ID: RF Page 17

ID: RF Module: Database Normalization Course: Database Design and Application Page 18 Screen title: ID’s Note Video: Tolong animasikan: 1. Kolom Staff. ID dan Staff. Name berkedip merah. 2. Kolom Cust. ID dan Cust. Name 3. Tabel baru “Staff” dan “Customer” muncul 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 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 3 NF, (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. THIRD Normalize Form (3 NF) Invoice Header Company. Name+Address+Invoice. No+Date+Staff. ID+Staff. Name+Cust. ID+Cust. Nam e Company. Name Violet Baby Shop Address Mall of Tangerang Ground Floor Blok. C No. 21. Tangerang Raya Street No. 51. Ph. (021 -7347575) Staff. ID+Staff. Name Staff. ID MK 09 Staff. Name Sheren Invoice. No Date Staff. ID V 021 April, 4 th MK 09 2016 Staff. Name Sheren Customer Cust. ID+Cust. Name Cust. ID Cust. Name CR 001 Crystal Marris Cust. ID CR 001 Cust. Name Crystal Marris

ID: RF 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 (3 NF) Invoice Header Company. Name+Address+Invoice. No+Date+#Staff. ID+#Cust. ID Company. Name Narasi (VO) : At the same time, we move Staff. Name and Cust. Name from Invoice Header table to the new tables, and identify another foreign key which are Staff. ID and Cust. ID. Address Invoice. No Mall of Tangerang Ground Floor Blok. C No. 21. Tangerang Violet Baby Shop V 021 Raya Street No. 51. Ph. (0217347575) Staff. ID+Staff. Name Staff. ID MK 09 Staff. Name Sheren Date April, 4 th 2016 Staff. ID MK 09 Customer Cust. ID+Cust. Name Cust. ID Cust. Name CR 001 Crystal Marris Cust. ID CR 001 Page 19

Course: Database Design and Application ID: RF Module: Database Normalization Screen title: ID’s Note Video: Tolong animasikan: • Muncul tabel item ketika disebut DATABASE NORMALIZATION RESULT Invoice Header Company. Name+Address+Invoice. No+Date+#Staff. ID+#Cust. ID Company. Name Narasi (VO) : So this is the end result of database normalization process until Third Normal Form (3 NF) and there are total 5 tables have created. Address Invoice. No Mall of Tangerang Ground Floor Blok. C No. 21. Tangerang Violet Baby Shop V 021 Raya Street No. 51. Ph. (0217347575) Invoice Detail Invoice. No+#Item. ID+Qty Date April, 4 th 2016 Staff. ID MK 09 Cust. ID CR 001 Item. ID+Item. Name+Unit. Price Invoice. No Item. ID Qty Item. ID V 021 B 053 Disney Baby Bouncer S 302 1 S 302 Chiko Baby Stroller B 001 2 B 001 Pigeon Baby Bottle Feeding IDR 50, 000 C 042 12 C 042 Baby Clothes (size 3 -6 months) IDR 50, 000 T 034 3 T 034 Baby Toys IDR 40, 000 Customer Cust. ID+Cust. Name Item Name Staff. ID+Staff. Name Cust. ID Cust. Name CR 001 Crystal Marris Staff. ID Staff. Name MK 09 Sheren Unit Price IDR 500, 000 IDR 2, 000 Page 20

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! Module: Database Normalization ID: RF Page 21

Course: Database Design and Application Module: Database Normalization Screen title: ID’s Note Subject Matter Expert Instructional Designer Multimedia Developer : Marisa Karsen : Riefni Riftianingrum : ID: RF Page 22