DATABASE NORMALIZATION

• Database Normalization

Database Design Process (review)
- Gather user needs / business
- Develop an ER Model based on user / business
- Convert E-R model to the set of relations (tables)
- Normalisasikan relations, to remove anomalies
- Implemented to create a database with a table for each relationship that have to normalized

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 normalization is needed?
- 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 / in the decomposition into a new tables, the tables 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 describe a attributes connection in a relation
• A attribute said functionally dependant in other attribute if we use attribute value to determine other attribute value.
• Simbol that used is  for representing functional dependency.
 Read : functionally to determine

Notation: A  B
A and B attribute from a table. That's mean functionally A determine B or B depend on A, if and only if there 2 data lines which have a same value with A value , so B value also same.
Notation: A B or A  B
• Functional Dependency:
• NRP  Nama
• Mata_Kuliah, NRP Nilai
Non Functional Dependency:
• Mata_Kuliah  NRP
• NRP  Nilai
• Functional Dependency From Value Table
• Nrp  Nama
• because for every same Nrp value , so also same Nama value
• {Mata_kuliah, Nrp }  Value
• because Value attribute depend on Mata_kuliah and Nrp according to together. In other meaning for Mata_kuliah and same Nrp , so Value also same , because Mata_kuliah and Nrp is key (has unique).
• Mata_kuliah Nrp
• Nrp Value

• First Normal Form (1NF)
A table on the form said to be normal I if it's 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)

Is not allowed there:
- Many attributes of value (Multivalued attributes).
- Attributes composite or a combination of both
So:
Price is the domain attribute must be atomic rates

• Second Normal Form - 2NF
• Normal form 2NF has been fulfilled in a table if form 1NF has been fulfilled, and all attributes besides primary key, according to intact has functional dependency in primary key
• A table doesn't fulfil 2nf, if there is an attribute the dependence (functional dependency) has only partial (only depend on some of primary key)
• If found attribute doesn't has dependence towards primary key, so the attribute must moved or caused
• Functional dependence X  Y is said full if wipe off a A attribute from X that's mean Y not again depend on functional.
• Functional dependence X  Y is said partial if wipe off a A attribute from X that's mean Y still to depend on functional.
• R relation scheme in the form of 2NF if every attribute non primary key A  R depend on full according to fungsional in R primary key.
Example :
• Doesn't fulfil 2NF, because {NIM, KodeMk that assumed as primary key but:
o {NIM, KodeMk} NamaMhs
o {NIM, KodeMk}  Alamat
o {NIM, KodeMk}  Matakuliah
o {NIM, KodeMk}  Sks
o {NIM, KodeMk}  NilaiHuruf
• That’s table necessary decompositioned be several tables that up to standard 2NF
Example :
• That’s Functional dependency as follows:
o {NIM, KodeMk} NilaiHuruf (fd1)
o NIM  {NamaMhs, Alamat} (fd2)
o KodeMk  {Matakuliah, Sks} (fd3)
• Then :
o fd1 (NIM, KodeMk, NilaiHuruf)  Tabel Nilai
o fd2 (NIM, NamaMhs, Alamat)  Tabel Mahasiswa
o fd3 (KodeMk, Matakuliah, Sks)  Tabel MataKuliah

• Third Normal Form - 3NF
Normal form 3NF has been fulfilled if fulfil form 2NF, and if there is no non primary key attribute that has dependence towards non primary key attribute the other ( transitive dependence)
Example 1:
• Student university table follows up to standard 2NF, but doesn't fulfil 3NF
• Because still found non primary key attribute (that is Kota and Provinsi) has dependence towards other non primary key attribute (that is KodePos):
kodepos  {Kota, Provinsi}
Example 2 :
• so that the table necessary decompositioned be :
• Mahasiswa (NIM, NamaMhs, Jalan, KodePos)
o KodePos (Kodepos, Provinsi, Kota)

• Boyce-Codd Normal Form (BNCF)
Boyce-Codd Normal Form has compulsion that stronger than third normal form. To be bncf, relation must in the first form of normal and every attribute is forced to depend on function in super attribute key.
In this example : found Seminar relation, primary key is NPM + Seminar.
Student may take one or two seminars. Every seminar need 2 guides and every student is guided by one of between 2 seminar guides. Every guide only may take one seminar. In this example is NPM and seminar shows a guide.
Seminar relation form is third normal form, but not BCNF Because seminar code stills to depend on function in guide, if every guide can teach only one seminar. Seminar depend on one attribute not super key like condition in bcnf.
• Normal Form Fourth And Fifth
Relation in the form of normal fourth (4NF) if relation in BCNF and not full multivalue dependence . To cause the loss of multivalue dependence from one relation, we divide relation be two new relations. Each full relation has two attributes that has multivalue connection.
Relation in the form of normal fifth (5NF) deal with property that called join without existence loses information (lossless join). Normal form fifth (5NF also called PJNF (Projection Join Normal Form). This case is very rare appears and difficult to detected according to practise.

DATABASE & ER-DIAGRAM

• DATABASE
The database is a set of data stored in the magnetic disk, optical disk or other secondary storage. Collection of integrated data-related data of an enterprise (company, government or private).
▪ Company a manufacturing a production planning data, actual production data,data ordering materials, etc.
▪ A hospital a patient data, doctor, nurse, etc.

• DATABASE MANAGEMENT SYSTEM
Database management system represent database aliance or corps with software of application being based on database. This application programs is used to access and look after database. Especial target of DBMS is to provide a efficient and easy environment for the usage of, to withdrawal and is depository of information and data.
• BIT, BYTE, FIELD
-Bit is the smallest pieces of data that contains the value 0 or 1
- Byte is a set of bit-bit similar
- Field is a set of byte-byte similar, in the database used the term attribute
• ATRIBUT/FIELD
It is the nature or characteristics of an entity that provides provide detail on these entities. A relationship can also have attributes.
Examples for attributes:
- STUDENTS: NIM, NAME, ADDRESS
- CAR: NOMOR_PLAT, COLOR, TYPE, CC
• TIPE-TIPE ATRIBUT
- Single vs multivalue
Single > 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’t be divided into the attributes of smaller
composition > is a combination of several attributes of a smaller
- Derived Attribute
attribute value can be derived from other attribute values, for example: age of the attributes generated from the date of birth.
- Null Value Attribute
Attributes that have no value to a record
- Mandatory Value Attribute
Attributes must have values

• RECORD / TUPLE
Record is a data line in a relationship. Record consists of a set of attributes where the attribute is an attribute-related entity or to inform the full relationship.
• ENTITY / FILE
Entity is the object or objects in a certain mini world represented in the database. Entity can be:
1. Things that have physical (people, cars, houses, etc.)
2. Think that there are conceptual (enterprise, employment, discipline, etc.)
File is a collection of similar records and have the same elements, the same attributes but different data values. In processing applications, files can categories with several types as follows:
- Master File
- Transaction Files
- File Reports
- 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 relasional is defined as a domain.
• KEY DATA ELEMENT
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
3. Key is guaranteed unique
- 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 or many to many. Foreign key is usually always put on the table that point 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 is a model of a network that uses word order is stored in the abstract system.
Differences between the DFD and ERD, namely:
1. DFD is a model of network functions that will be implemented by the system.
2. ERD is a model that emphasizes the network data on the structure and relationship data.
• ELEMENTS OF THE ERD
- Entity
In the ER Diagram Entity is described with the form of a rectangle. entity is something that exists in the real system and the abstract where the data stored or where there are 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 relationships.
- Relationship Degree
is the number of entities participating in a relationship. Degree which is often used in the ERD.
- Attribute
is the nature or characteristics of each entity and relationship
- Kardinalitas
tupel indicates the maximum number that can be berelasi with entities on the other entity
• RELATIONSHIP DEGREE
- Unary Relationship
model is the relationship between the entity originating from the same entity set.
- Binary Relationship
model is the relationship between 2 entities.
- Ternary Relationship
is a relationship between the instance of 3 types of entities are unilateral.
• KARDINALITAS
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 is viewed. For one incident in the first entity can have many relationships with the incident on the second entity, if the one incident in which two entities can have only one incident hubugan with the first entity.
- Many To Many:
if any incident occurs in an entity has many relationships with other entities in the incident.
• NOTATION (E-R DIAGRAM)
Symbolic notation in the ER diagram is:
1. Rectangle represent the collective entity
2. Circle represent the attributes
3. Rhomb represent collective relationships
4. Line as the set of relations between the Association and the collective entity with entity attributes

DATA FLOW DIAGRAM

DATA FLOW DIAGRAM
Data flow diagram(DFD) is to describe the system modules in a smaller and less easy for the user to understand to understand the field of computer systems that will be done.
DFD also serves to describe the existing system or the new system will be developed logically without considering the physical environment where the data flows, or where data are stored. DFD is a tool that is used in the system development methodology is structured (structured analysis and design). DFD can describe the flow of data within the system with a structured and clear.

CONTEXT DIAGRAM
Context diagram of a process and describe the scope of a system and is the highest level of the DFD that describes the entire input to the system and output of the system.


ZERO DIAGRAM
Zero diagram is a chart that describes the process of DFD. This diagram provides a view of the overall system shows that the main function of the process or the flow of data and the external entity. At this level there is a data storage.
For the process that is not explained in the next level then added to the symbol '*' or 'P' at the end of the process. Balance of input and output (balancing) between 0 to diagram context diagram should be maintained.

DETAILED DIAGRAM
Is a diagram that decipher what is the process in the diagram zero level or above. one level there should be no more than 7 units and the maximum of 9, when more should be done in the decomposition.


SPECIFICATION PROCESS
Each process in the DFD must have a specification process. At the top level method is used to describe the process you can use with descriptive sentences. At a more detailed level, namely on the bottom (functional primitive) require a more structured specification.


EXTERNAL ENTITY
Unit outside is something that is outside the system, but provide data in the system or to provide data from an external system. Entity not including part of the system, the symbols with the notation.


DATA FLOW
Data flow is the information flow is depicted with a straight line that connects the components of the system. Data flow direction is indicated with arrows and lines give the name on the flow of data flow. Flow data that flows between processes, data storage and data flow indicates that the form of data input to the system.
Guidelines of the name:
1. Name of the flow of data that consists of some words associated with the flow lines connect
2. No flow data for the same and the name should reflect its content
3. The flow of data that consists of several elements can be expressed with the group element
4. Avoid using the word 'data' and 'information' to give a name to the flow of data
5. Wherever possible the complete flow of data is written
Other provisions:
1. Name of the flow of data into a process may not be the same as the name of the data flow out of the process
2. Data flow into or out of data storage doesn’t need to be given a name if:
a. The flow of data simple and easy to understand
b. Describes the data flow of all data items
3. There can be no flow of data from the terminal to the data storage, or vice versa because the terminal is not part of the system, the relationship with the terminal data storage must be through a process


PROCESS
The process is what is done by the system, can process data streams or input data into output data stream. Each process has one or more inputs and produce one or more output.
Transform the process of working one or more of the input data into one or more of the output data in accordance with the desired specifications.
Guidelines of the process:
1. Name of the process consists of a verb and noun, which reflects the function of the process
2. Do not use the process as part of the name of a bubble
3. May not have some process that has the same name
4. The process should be given a number. Order number wherever possible to follow the flow of the process or sequence, but the sequence number doesn’t mean that the absolute is a process in chronological order


DATA STORAGE
Data storage is a storage place for data that exists in the system, which symbol with a pair of parallel lines or two lines with one of the side open. The process can retrieve data from or provide data to the database.
Guidelines of the name:
1. The name should reflect the data.
2. When his name more than one word must be marked with the numbers.


DATA DICTIONARY
Data dictionary is also called with a system data dictionary is a catalog of facts and data information needs of an information system. Working data dictionary to help the system to interpret the application in detail and organize all elements of the data used in the system precisely so that the system analyst and have a basic understanding of the same input, output, storage and processing.
In the analysis phase, the data dictionary is used as a means of communication between the systems analyst with the user. While in the system design phase, the data dictionary is used to design input, reports and databases.
Flow data on the global DAD, further details can be seen in the data dictionary. Load the data dictionary as follows:
1. Name of data flow: must note that readers who need further explanation about a flow of data can find it easily.
2. Alias: alias or other name of the data can be written if there is.
3. Forms of data: used to segment the data dictionary to use when designing the system.
4. Flow data: indicates from which data flows and where the data.
5. Description: to give an explanation of the meaning of the data flow.


BALANCING IN DFD
There are some balancing between input and output in the DFD, such as:
1. The flow of data into and out of a process must be the same as the flow of data into and out of the details of the process on the level / levels below it.
2. Name of the data flow into and out of the process must match the name of the flow of data into and out of the details of the process.
3. Number and the name of an entity outside the process must be equal to the number of names and entities outside of the details of the process.
4. The issues that must be considered in the DFD which have more than one level:
• There must be a balance between input and output of one level and the next level.
• Balance between level 0 and level 1 at the input / output of stream data to or from the terminal on level 0, while the balance between level 1 and level 2 is seen on the input / output of stream data to / from the process concerned.
• Name of the flow of data, data storage and terminals at each level must be the same if the object is same.

RESTRICTIONS IN DFD
1. Flow data may not be from outside the entity directly to other outside entities without going through a process
2. Flow data may not be from the savings directly to the data to outside entities without going through a process
3. Flow data may not be saving the data directly from the savings and other data without going through a process
4. Flow data from one process directly to the other without going through the process of saving data should be avoided as much as possible