NORMALIZATION

Process Design Database

- Gether user need/business
- Develop a needs-based ER Model user/business
- Convert E-R model to the set of relations (tables)
- Normalization to remove the anomaly relations
- Implemented to create a database with a table for each relationship that is normalization

Database normalization

Normalization process is the establishment of the database structure so that most of the ambiguity can be removed. Normalization stage, starting from the most mild (1NF) to most stringent (5NF). Usually only up to the level of 3NF or BCNF because already sufficient to generate the table-table good quality.

Why do normalization?
- Optimizing table structures
- Increase speed
- The income data is the same
- More efficient in the use of storage media
- Reduce redundancy
- Avoid anomalies (insertion anomalies, deletion anomalies, update anomalies)
- Improved data integrity

A table saying good (efficient) or if the normal 3 to meet the following criteria :
- If there is decomposition (decomposition) table, it must be guaranteed safe dekomposisinya (Lossless-Join Decomposition). That is, after the table is described / didekomposisi a new table-table, the table-table can generate a new table with the same exact.
- Maintain dependence on the functional changes in data (Dependency preservation)
- Does not violate Boyce-Code Normal Form (BCNF)

If the three criteria (BCNF) can not be met, then at least the table does not violate the Normal Form of the third stage (3rd Normal Form / 3NF)

Functional Dependency

Functional Dependency describes a relationship attributes in relation. An attribute said functionally dependant on the other, if we use the value attribute to determine the value of other attributes. Symbol that is used to represent the functional dependency that is readable in determining functional.

Notation : A --> B
A and B are attributes of a table. A means of determining the functional B or B depends on A, if and only if there are 2 rows of data with the same value of A, then B is also the same value.

Notation : A --> B or A x --> B
It is the opposite of the previous notation.

FUNCTIONAL DEPENDENCY (FD)



Functional Dependency
NRP --> Nama
Mata_Kuliah, NRP --> Nilai

Non Functional Dependency
Mata_Kuliah --> NRP
NRP --> Nilai

Functional Dependency of the table value
- NRP --> Nama
Karena untuk setiap nilai Nrp yang sama, maka nilai Nama juga sama.
- {Mata_kuliah, NRP} --> Nilai
Because the value of attributes depending on the NRP and Mata_kuliah together. In another sense Mata_kuliah for the NRP and the same, they also rated the same, because Mata_kuliah and the NRP is a key (a unique).
- Mata_kuliah --> NRP
- NRP --> Nilai

FIRST NORMAL FORM – 1NF

A table on the form said to be normal if I did not reside in the unnormalized form of a table, where there is a kind of field multiplication and field that allows a null (empty).

Not allowed the:
- Many attributes of value (Multivalued attributes).
- Attributes composite or a combination of both.

So the price should be the domain attribute price atomic

Ex Student Data as follows:

Or:

The table above does not meet the requirements 1NF

Example :

Dekomposisi menjadi :
- Table Student

- Table Hobby


SECOND NORMAL FORM

Normal form 2NF met in a table if it meets the form of 1NF, and all the attributes than the primary key, have a full Functional Dependency on primary key. A table does not meet 2NF, if there are attributes that ketergantungannya (Functional Dependency) are only partial (only depending on the part of the primary key). If there are attributes that have no dependence on the primary key, then the attributes must be moved or removed. Y is full if it is said to delete an attribute A from X means that Y is no longer dependent functional --> Functional dependency X. Y said if deleting a partial attribute A from X means that Y is functionally dependent --> Functional dependency X. Relation scheme R in the form 2NF if every non-primary key attribute A element R depends on the full primary key fungsioanl R.

Example :

The following table meet 1NF, including but not 2NF


Does not meet 2NF, because (NIM, KodeMk) is regarded as the primary key:
{NIM, KodeMk} --> NamaMhs
{NIM, KodeMk} --> Alamat
{NIM, KodeMk} --> Matakuliah
{NIM, KodeMk} --> Sks
{NIM, KodeMk} --> NilaiHuruf

Table needs to be some table decomposition eligible 2NF

Functional dependency:
- {NIM, KodeMk} --> NilaiHuruf (fd1)
- NIM --> {NamaMhs, Alamat} (fd2)
- KodeMk --> {Matakuliah, Sks} (fd3)

So:
- fd1 (NIM, KodeMk, NilaiHuruf) --> Tabel Nilai
- fd2 (NIM, NamaMhs, Alamat) --> Tabel Mahasiswa
- fd3 (KodeMk, Matakuliah, Sks) --> Tabel MataKuliah

THIRD NORMAL FORM – 3NF

Normal form 3NF fulfilled if the form meets 2NF, and if there are no non-primary key attribute that has a dependence on non-primary key attributes of the other (transitive dependencies).

Example :

Table following students eligible 2NF, 3NF, but does not meet :


Because there are non-primary key attribute (ie, City and Provincial), which has a dependence on non-primary key attributes of the other (is KodePos):
KodePos --> {Kota, Provinsi}

So that the table should be decomposition :
Students (NIM, NamaMhs, Jalan, KodePos)
- KodePos (KodePos, Provinsi, Kota)

Boyce-Codd Normal Form (BNCF)

Boyce-Codd Normal Form constraint has a stronger form of the Normal third. To be BNCF, relations must be in the form of Normal Kesatu and forced each of the attributes depends on the function in the super key attributes.

In the example below there is a relationship seminar, is the Primary Key NPM + Seminar.
Students may take one or two seminars. Each seminar requires 2 each of the students and led by one of the 2 seminar. Each leader can only take one seminar course. NPM in this example indicates a seminar and teacher.




Relations Seminar is a form of Third Normal, but not BCNF
Seminar Code because the function depends on the Pembimbing, if any Pembimbing can only teach a seminar. Seminar depend on one key attribute is not as super disayaratakan by BCNF.

Normal Form Of The Fourth And Fifth

Relations in the fourth normal form (NF 4) if the relation in BCNF and dependency tdak contain many values. To remove the dependency of many values from a relation, we divide the relationship into two new relations. Each relation contains two attributes that have a lot of relationship value. Relations in fifth normal form (5NF) deal with the property called the join without any loss of information (lossless join). Fifth normal form (also called the 5 NF PJNF (projection join normal form). The case is very rare and appear difficult to detect in practice.

DATABASE & ER-DIAGRAM

Definition of Database

Database is a set of data stored in the magnetic disc, optical disc, or other secondary storage. Integrated collection of related data from an enterprise (companies, goverment agecies or private).
- Manufacturing company : production planning data, actual production data, data ordering materials, etc.
- Hospital : patient data, doctor, nurse, etc.

Database Management System

- Collection/database combined with software applications that are based database.
- This application program used to access and maintain database.
- The main purpose DBMS (Database Management System) is to provide an environment that is efficient and easy to use, withdrawal and storage of data and information.

Bit, Byte, Field

- Bit is the smallest pieces of data that contains the value 0 or 1.
- Byte is a collection of bits-a bit like.
- Field is a set of byte-byte similar, in the database used the term attribute.

Attribute/Field

Attribute is the nature or characteristics of an entity that provides provide detail on these entities. A relationship can also have attributes.
Sample attributes :
- STUDENTS : NIM, NAME, ADDRESS
- CAR : PLAT_NUMBER, COLOR, TYPE, CC

Type Attribute

Single vs Multivalue
- Single typically can only be filled at most one value.
- Multivalue can be filled with more than one value with the same type of.
Atomic vs Composition
- Atomic can not be divided into the attributes of smaller.
- The composition is a combination of several attributes of a smaller.
Devired Attribute
Attribute value can be derived from other attribute values, Suppose that the age attribute of the date of birth.
Null Value Attribute
Attributes that have no value to a record
Mandatory Value Attribute
Attributes must have values

Record/Tuple

It is a line of data in a relationship, and consists of a set of attributes where the attribute is to inform each other entity/relationship fully.

Entity/File

File is a collection of records, and similar elements have the same, same but different attribute data value.

File Type :
In processing applications, files can be categorized as follows:
- Master File
- Transaction File
- File Report
- File History
- File Protection
- File Work

Domain

Domain is the set of values that are allowed to reside in one or more attributes. Each attribute in a database relational is defined as a domain.

Element Key Data

Key elements of record which is used to find these records at the time of access, or can also be used to identify each entity/record/line.

Species of Key

Superkey is one or more attributes of a table that can be used to identify entityty/record of the table are unique (not all attributes can be superkey).
Cadidate Key is a super key with minimal attributes. Candidate must not contain a key attribute of the table so that the other candidate key is certain superkey but not necessarily vice versa.

Primary Key
One of the key attributes of the candidate can be selected / specified a primary key with the three following criteria:
1. Key is more natural to use as reference.
2. Key is more simple.

Alternate Key is an attribute of the candidate key is not selected to be primary key.
Foreign Key is any attribute that points to the primary key in another table. Foreign key will be going on a relationship that has kardinalitas one to many (one to many) or many to many (many to many). Foreign key is usually always put on the table that lead to many.
External Key is a lexical attribute (or set of lexical attributes) that values are always identify an object instance.

ERD (Entity Relationship Diagram)

ERD (Entity Relationship Diagram) is a model of a network that uses word order is stored in the system of abstract.

Differences between the DFD (Data Flow Diagram) and ERD (Entitiy Relationship Diagram).
- DFD (Data Flow Diagram) is a model of network functions that will be implemented by the system.
- ERD (Entitiy Relationship Diagram) is a model that emphasizes the network data on the structure and relationship data.

Elements of the ERD (Entitiy Relationship Diagram)

- Entity
In the ER Diagram Entity is described with the form of a rectangle. Rntity is something that exists in the real system and the abstract where the data stored or where the data.

- Relationship
ER diagram on the relationship can be described with a lozenge. Relationship is a natural relationship that occurs between entities. In general, the name given to the verb base making it easier to do readings relations.

- Relationship Degree
Is the number of entities participating in a relationship. Degree which is often used in the ERD (Entitiy Relationship Diagram).

- Attribute
Is the nature or characteristics of each entity and relationship.

- Cardinalitation
Tupel indicates the maximum number that can be relationships with entities on the other entity.

Degree of Relationship

- Unary Relationship is the relationship model between the entity originating from the same entity set.
- Binary Relationship is a relationship model between 2 entities.
- Ternary Relationship is a relationship between the instance of 3 types of entities are unilateral.

Cardinalitation

There are 3 kardinalitas relations, namely:
- One to One : Level one to one relationship with the one stated in the entity's first event, only had one relationship with one incident in which the two entities and vice versa.
- One to Many or Many to One : Level one to many relationship is the same as the one to many depending on the direction from which the relationship views. For an incident on the first entity can have any relationship with banyaj incident on the second entity, if the one incident in which two entities can only have one connection with incident on the first entity.
- Many to Many : If any incident occurs in an entity many have relationships with other entities in the event.

DATA FLOW DIAGRAM (DFD)

Data Flow Diagram

Describe the system distribution in the small module. Make easy use less realize the computer area for an understand system to worked.

Context Diagram

Consist of one process and describe the scop from any system. And then is a high level from the Data Flow Diagram to describe all input to the system and output from the system. The context diagram usually limitted by Boundary. And then not to be any storage.

Zero Diagram

Describe the process from the Data Flow Diagram. Give opinion according to comprehensive hit the system to handled, show the fuction or important system exiting, data flow and external entity. In this level maked existence the data storage. For the process not be detailed againt in the next level so to added the symbol ‘*’ or ‘P’ in the end of process number. Balancing of the input and output between zero diagram with context diagram must to take care.

Detail of Diagram

That is a diagram to decomposing the process what is there in the zero diagram or on the level.

Level numberizatio in the Data Flow Diagram:
- Level name is 0, diagram name is context, and there is not the number of process.
- Level name is 1, diagram neme is 0 (zero) diagram, and the number of process is 1.0, 2.0, 3.0, etc.
- Level name is 2, diagram name is 1.0, and the number of process is 1.1, 1.2, 1.3, etc.
- Level name is 3, diagram name is 1.1, and the number of process is 1.1.1, 1.1.2, etc.

In the one level must be not found more from 7 process and maximal is 9, when be more so must done decomposition.

Process Spesification

Every process in the Data Flow Diagram must heving the process spesification. In the top level method to used for describe the process can by using descriptive sentence. In the level to more detail is in the most under process (functional primitive) need the more structured spesification. Process spesification will be guide for programer in the make the program (coding). Method to used in the process spesification is process explanation in the form of story, decision table, and decision tree.

Unitary Outside

Certain beyond the system, but he give the data to in the system or give the data from the system. Usually symbolized with notation box. External entity not to belong part from system. Nomenclature is :
- The terminal name is shaped noun.
- The terminal can be not have same the name except the object really same.

Data Current

Data current is a place currences information. Described with straight line tto connect the component from the system. Data current showed by arrow direction and the line given the name on data current to flow. Data current to flow in the process, data storage and to show the data current from data to shaped the input for system. Guide the gift of the name is :
- Name of data flow consist of many word flow to connected with line continues.
- There may not be any data flow it’s the same of the name and gived the name must to reflect the contents.
- Data flow consist of many element can to asked with element group.
- Avoid to use any word is ‘data’ and ‘information’ for give the name in the data flow.
- The name of flow can be to written complete.

Rule other:
- Name data flow to come in a process not to be same by name of data flow to out from the process.
- Data flow to come in or out from the data storage can be not given the name when :
a. The simple of data flow and easy understood.
b. Data flow to describe all data item.
- There may not be any data flow from terminal to data storage or on the contrary because the terminal not a part from system, the connection of the terminal by data storage must pass the process.

Process

Process is a what to working by system. Process can to cultivate the data or data flow come to be out of data flow. The function of process is transformation the one or many input data be one or many output data appropriated with spesification to desirable. Any process have a one or many input and produce one or many output. The process often also called as bubble.

Guide the gift of the name process is :
- Name of process consist of many verb and noun to reflect function of the process.
- Don’t use process word as part from name a bubble.
- There may not be any process to have the same of the name.
- The process must given the number. Sequence the number can be to follow the flow or sequence the process, but such be sequence the number meaningless absolutely is the sequence of process according to chronological.

Data Storage

Data storage is a data repository in the system. Symbolized with a pair parallel line or two line with one of the side opened. The process can be take the data from or give the data to datebase. Guide the gift of the name is :
- The name must be reflect the data storage
- When the name be more from one word so must to take sign continues

Data Dictionary

The function of data dictionary to help a system user for interprate the aplication to detail and to unite all data element to used the system be exacttly so that user and system analyst have a same base explanation about input, output, storage, and process. In analysis stage, data dictionary to use as a communication tools that is analys system with user. In system planning stage, data dictionary to use for plan the input, report, and database. Data current in the Data Flow Diagram has global, more detail of explanation can be look in the data dictionary.

Data dictionary hold matter as follow is :
- Name of data current : must be registerred so that reader need a furthe more explanation about of a data current can be find so easy.
- Data form : to use for unite the data dictionary to use timer the system planning.
- Data current : show whence a data to flow and where a data to go.
- Explanation : give explanation about meaning from data current.

Balancing in the Data Flow Diagram

Data flow to come in and out from one process must be same with data flow to come in and out from detail of process in the level or stage under. Name of data flow come in and out from one process must be same with name of data flow to come in and out from detail of the process. Total and outside of entitas name a process must be same with total and outside of entitas name from detail of the process. Matter that must be played a Data Flow Diagram to have more one level is :
- Must found balancing input and output the one level and other level.
- Balancing the 0 level and 1 level can be look in the input or output from data flow to and or from terminal in the 0 level, while be balancing the 1 level and 2 level can be look in the input or output from data flow to or from process cornerned.
- Name of data flow, data storage, and terminal in the every level must be same if same object.

Prohibition in the Data Flow Diagram

- Data current can be not from direct outside of entitas to go to other outside of entitas without pass a process.
- Data current can be not from direct data saving to go to outside of entitas without pass a process.
- Data current can be not from direct data saving to go to other data saving without pass a process.
- Data currrent from one direct process to go to other process to pass a data saving can be may a void.