1 / 20

Database Normalization

Database Normalization. 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. Video: Narasi :

gormanl
Download Presentation

Database Normalization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Normalization

  2. 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

  3. 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

  4. 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

  5. Video: Tolongdiagramnyadibuatulangtapisederhanaajadantulisannyaagakbesar. Animasi: Setelahnarasiselesai, table tersebutmengecildankeatas. 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.

  6. Video: Tolongdiagramnyadibuatulangtapisederhanaajadantulisannyaagakbesar. Animasi : Kotak first normal form (1NF) berkedipketikadisebut. Kotak second normal form (2NF) berkedipketikadisebut. Kotak third normal form (3NF) berkedipketikadisebut. 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

  7. 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.

  8. 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)

  9. Video: Mohontabeldibuatulangdenganmenggunakanwarnawarni yang lebihmenarik, dan diperbesar font nya (secukupnyaaja) Kalautidakmuat, tidakapa-apatapitolongdi zoom in (darikirikekanan) selama 3 detikbarulanjutke slide berikutnya. Narasi (VO): Here is Un-normalized Form (UNF) for Violet Baby Shop : Un-Normalize Form (UNF) Invoice CompanyName+Address+InvoiceNo+Date+StaffID+StaffName+CustID+CustName+{No+ItemID+ItemName+Qty+UnitPrice+Amount}+SubTotal+Discount+Total

  10. 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

  11. Video: Tolonganimasikan: Kolom “InvoiceNo” berkedipmerah, Muncultabel Invoice Detail. Setelahnarasiselesai, kata “InvoiceNo” yang dilingkarimerahditambahi underline. 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 First Normalize Form (1NF) Invoice Header CompanyName+Address+InvoiceNo+Date+StaffID+StaffName+CustID+CustName Invoice Detail InvoiceNo+ItemID+ItemName+Qty+UnitPrice

  12. Video: Narasi : After we create First Normal Form (1NF), we continue to create Second Normal Form (2NF). The Rule in Second Normal Form is isRemove Partial Dependency. SECOND NORMAL FORM (2NF) RULE: Remove partial dependency

  13. Video: Tolonganimasikan: KolomItemIDberubahmerahdanlingkaranmerahdiInvoiceNo Muncultulisan item LalumuncultulisanItemID+ItemName+UnitPrice tulisanitemIDdi invoice detail berubahjadi #ItemID Kolom item Name dan unit price pindahdaritabel invoice detail ketabel 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 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 . SECOND Normalize Form (2NF) Invoice Detail InvoiceNo+ItemID+ItemName+Qty+UnitPrice Item ItemID+ItemName+UnitPrice

  14. Video: Tampilantabelnyanantiterakhirsepertiini. Narasi (VO) : SECOND Normalize Form (2NF) Invoice Detail InvoiceNo+#ItemID+Qty Item ItemID+ItemName+UnitPrice

  15. Video: Tolonganimasikan: Muncultabel item ketikadisebut 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 (indonesianyaadalahatributpadatabeltidaktergantungsecaralangsungdengan table utamanya – Invoice) THIRD NORMAL FORM (3NF) RULE: Remove Transitive Dependency

  16. Video: Tolonganimasikan: KolomStaffIDdanStaffNameberkedipmerah. KolomCustIDdanCustName Tabelbaru “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 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. THIRD Normalize Form (3NF) Invoice Header CompanyName+Address+InvoiceNo+Date+StaffID+StaffName+CustID+CustName Staff StaffID+StaffName Customer CustID+CustName

  17. Video: Tolonganimasikan: Tabel Invoice Header menjadisepertiini. 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. THIRD Normalize Form (3NF) Invoice Header CompanyName+Address+InvoiceNo+Date+#StaffID+#CustID Staff StaffID+StaffName Customer CustID+CustName

  18. Video: Tolonganimasikan: Muncultabel item ketikadisebut 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. DATABASE NORMALIZATION RESULT Invoice Header CompanyName+Address+InvoiceNo+Date+#StaffID+#CustID Invoice Detail InvoiceNo+#ItemID+Qty Item ItemID+ItemName+UnitPrice Customer CustID+CustName Staff StaffID+StaffName

  19. Video: Silahkanpakaitabelinilangsung. Narasi (VO): After we learned about database normalization process, it’s time for you to do it by using this case. Good luck!

  20. Subject Matter Expert : Marisa Karsen Instructional Designer : Riefni Riftianingrum Multimedia Developer :

More Related