Perancangan Basis Data Understanding Normalization Establishing a Business

Perancangan Basis Data Understanding Normalization

Establishing a Business Model • • • What Is Normalization? 1 st Normal Form (1 NF) 2 nd Normal Form (2 NF) 3 rd Normal Form (3 NF) Beyond 3 rd Normal Form (3 NF) 12/30/2021 2

What Is Normalization? 12/30/2021 3

What Is Normalization? • In general, normalization removes duplication and minimizes redundant of data. • The problem with the academic approach to normalization is that it seems to insist on always expecting a designer to apply every Normal Form layer in every situation 12/30/2021 4

The Concept of Anomalies Insert anomaly • Caused when a record is added to a detail table, with no related record existing in a master table. kode_fakultas nama_fakultas kode_jurusan nama_jurusan kode_fakultas FAK 01 Teknik JUR 01 Sistem Informasi FAK 01 FAK 02 Ekonomi JUR 02 Teknik Informatika FAK 01 JUR 03 Akuntansi FAK 02 JUR 04 Ilmu Komunikasi FAK 03 12/30/2021 5

The Concept of Anomalies Delete anomaly • Caused when a record is deleted from a master table, without first deleting all sibling records, in a detail table. kode_fakultas nama_fakultas kode_jurusan nama_jurusan kode_fakultas FAK 01 Teknik JUR 01 Sistem Informasi FAK 01 FAK 02 Ekonomi JUR 02 Teknik Informatika FAK 01 JUR 03 Akuntansi FAK 02 ? 12/30/2021 6

Dependency, Determinants, and Other Jargon Functional dependency • Y is functionally dependent on X if the value of Y is determined by X. Determinants • The determinant in the description of functional dependency in the previous point is X because X determines the value Y, at least partially because 1 is added to X as well. kode_fakultas FAK 01 Determines that the faculty name is Teknik 12/30/2021 nama_fakultas FAK 01 Teknik FAK 02 Ekonomi is dependant on the code being FAK 02 7

Dependency, Determinants, and Other Jargon Transitive dependency • Z is transitively dependent on X when X determines Y and Y determines Z. Transitive dependence thus describes that Z is indirectly dependent on X through its relationship with Y. • Contoh : nim - > kode_jurusan -> kode_fakultas Transitive : nim - > kode_fakultas nim 12/30/2021 kode_jurusan kode_fakultas 10510202 05 1 10910760 09 1 20210121 02 2 8

Dependency, Determinants, and Other Jargon Full Functional dependency • This situation occurs where X determines Y, but X combined with Z does not determine Y. • In other words, Y depends on X and X alone. • If Y depends on X with anything else, there is not full functional dependence. 12/30/2021 9

Dependency, Determinants, and Other Jargon Multiple valued dependency • A commonly used example of a multi-valued dependency is a field containing a comma-delimited list or collection of some kind. 12/30/2021 10

Dependency, Determinants, and Other Jargon Cyclic dependency • The meaning of the word “cyclic” is a circular pattern, recurrent, closed ring, or a circular chain structure. • In other words, X relates to Y, Y relates to Z, and Z relates to X. 12/30/2021 11

Defining Normal Forms The Academic Way • The following are the precise academic definitions of Normal Forms : 1. 1 st Normal Form (1 NF) – atomic 2. 2 nd Normal Form (2 NF) – All non-key values must be fully functionally dependent on the primary key. 3. 3 rd Normal Form (3 NF) – Eliminate transitive dependencies, meaning that a field is indirectly determined by the primary key. 4. Boyce Code Normal Form (BCNF) – Every determinant in a table is a candidate key. If there is only one candidate key, 3 NF and BCNF are one and the same. 12/30/2021 12

Defining Normal Forms The Academic Way • The following are the precise academic definitions of Normal Forms : 5. 4 th Normal Form (4 NF) – Eliminate multiple sets of multivalued dependencies 6. 5 th Normal Form (5 NF) – Eliminate cyclic dependencies. 5 NF is also known as Projection Normal Form (PJNF). 7. Domain Key Normal Form (DKNF) – DKNF is the ultimate application of normalization and is more a measurement of conceptual state, as opposed to a transformation process in itself. 12/30/2021 13