DATABASE NORMALIZATION

DATABASE NORMALIZATION

Database Plan Process (Review)
• Gather user/business need
• Develop e-r model based on user/business need
• Conversion e-r model to relation collection (table)
• Relation normalization to cause the loss of anomaly
• Implementation to database with make table for every relation that normalization

Data Basis normalization

Normalization are process basis structure forming data so a considerable part ambiguity can be removed. Normalization phase is begun of lightest phase (1NF) until tightest (5NF) Just usually comes up 3NF's zoom or BCNF because was enough is equal to result qualified table good.
The importance for normalization:
- Since marks sense database structure that insufficiently lovely
- Saved same data at severally place (file or record)
- Disability to result particular information
- Information’s forfeit happening
- Happening marks sense redundancy (repeat) or data duplication so wastes storage room and wanting hard with while updating process data
- Mark sense NULL VALUE
Normalization process:

- Untied data in shaped table, hereafter analysis bases particular situating go to many zooms.
- If table examinee has qualified particular, therefore table that needs to be broken down becomes many table that simpler until optimal form pock.

To the effect Normalization:

- to remove duplicate data
- to reduce complexity
- to water down modification data

Normalization?

- Optimal is table's structures
- Increasing speed
- Removing same data inclusion
- More efficient in storage media purpose
- Reducing redundancy

- Avoiding anomaly ( insertion anomalies , deletion anomalies , anomaly’s update ).
- Increased data integrity

One table is said well (efficient) or normal if pock 3 criterions as follows:
- If there is decomposition (decomposition) table, therefore decomposition it shall be secured safe( Lossless Join Decomposition ). Its mean, after that table is untied or at decomposition becomes new tables, that new tables can result tables originally equally a hair's breadth.
- Its petted dependable functional at the moment data change (Dependency Preservation).
- Don't breach Boyce Code Form's Normal (BCNF)


Functional Dependency

To do normalization, shall can determine earlier Functional Dependency (FD) or Functional dependency, notably deep do database design decomposition.
Functional Dependency (FD) can symbol with:
A -> B: its mean b have dependency with A
Matter a. functionally determine B or b functionally depend on A.


• Functional Dependency figuring relationship attributes in one relationship
• An attribute is said functionally dependant on the other if we utilize to assess that attribute to determine the other attribute point.
• Symbol that is utilized is -> for representing functional dependency.
> read functionally determines
• Notation: A. > B
A. and b is attribute of one table. Matter functionally a. determines b or b cling to, if and only if available 2 data row with appreciative a. same, therefore point b also with
• Notation: A. > B or a. x > B
Are opposite of previous notation.
Normalization forms

While design database utilizes relational model, we often find severally alternative in definition relationship scheme gathering. Severally selection more comfortable disbanding option – other option for medley motive. Normalization order is declared for in normal shaped terminology. Normal form are a ruling one be put on on relationships in data basis and has to be accomplished by that relationship on normalization levels. A relationship is said in given normal form if given condition pocks.


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 is said present in normal form I if it doesn't present in form unnormalized table, where happen multiplication of a kind field and there possible field that null (empty)
• Forbidden of existence:
o Attribute that have multivalue (multivalued attribute).
o Composite attribute or combination of both.
• So: Value of domain attribute must be atomic value.

SECOND NORMAL FORM (second is Form's Normal 2NF) (1 )

Both of Normal Shaped definition (2 NF) are:
1 ). Shaped pock 1 NF (first normal).
2 ). Attribute doesn't key to have logistic ala dependent on main key /
primary goes to y. .
So to form normal both of every table / must file is determined keys its attribute. Attribute key has unique and get to represent attribute any other that as its member. On College Student table example that accomplishes first normal (1 NF) , visually that NIM constitute Primary Goes To y. (PK). NIM -> Name, Guardian lecturer .
Its mean is that Name attribute and Sponsor Lecturer hinges on NIM.
But NIM -> Lesson. Its mean is bahqa attribute Lesson not depends
on NIM.

For meeting normal both of, therefore on that college student table is broken down

DRD NORMAL FORM (Third is Form's Normal 3NF)

Normal Shaped definition drd (3 NF) are:
1 ). Shaped pock 2 NF (second normal).
2 ). Attribute doesn't key have no dependensi transitiving to keys
main / primary goes to y..
Following Samples relationship that accomplishes to form 2 NF, but doesn't accomplish to form 3
NF.



Attribute No. Order and No. Massage constitutes prime key, well code item and name
item has dependensi functional to that prime key.
On table upon, each code item with, therefore item's name point also with,
so pointing out marks sense dependensi two that attribute, but which that
determine, what is item's code hinges on item's name, or contrariwise? So
item's name has dependensi functionaling to item's Code.
On this relationship points out that item's name have no dependensi ala
directly to prime key (No. order and No. Massage). In other words Name
Item has dependensi transitive to prime key.
So for meeting form 3 NF, therefore relationship upon at decomposition becomes
two relationships as follows:




Boyce Codd's form Form's Normal (BNCF)

BCNF'S Shaped definition is:
1 ). Shaped pock 3 NF (drd normal).
2 ). All conditioner (determinant) are key candidate (attribute that gets unique character).
Each attribute shall hinge function on super key's attribute.
BCNF constitutes to form normal as remedial as to 3 NF. A relationship
that BCNF'S pock always accomplishes 3 NF, but don't for on the contrary. A
relationship that accomplishes 3 NF was obviously accomplishes BCNF. Since form 3 NF
still enable anomali's happening.
On this following example exists seminar table, prime key is number student +
seminar, with that savvy:
_ Student can take a or two seminar.
_ Each seminar needs 2 instructors.
_ Each student led by either one 2 seminar instructors.
_ Each instructor just may take one seminar just.

On this example, number student and seminar points out an instructor.




Tabular seminar is accomplish forms DRD normal (3 NF), but don't
BCNF because seminar number is still hinge function on instructor, if each
instructor can teach at only one seminar. Logistic dependent seminar
on one attribute is not super key as one presupposed by BCNF.
Therefore seminar table shall at decomposition becomes two tables, which is instructor table
and seminar_instructor, as following as this:



Fourth normal form and to five
• Relationship in shaped fourth normal (4 NF) if relationship in BCNF and tdak contains dependency a lot of point. To remove dependency there are many point of one relationship, we divide relationship become two new relationships. Each relationship contains two attributes that have relationship a lot of points.
• Relationship in shaped normal to five (5NF) get business with property is join the so called without marks sense information loss (lossless join). Form normaling to five (5 NF also so-called PJNF (projection join is form's normal). This case very rare appearance and hard to be detected practical ala.

DATABASE AND ER-DIAGRAM

DATABASE AND ER-DIAGRAM

Database is a set of data stored in the magnetic disk, optical disk or other secondary storage. User and administrator can inserting, deleting and updating data. The different between user and administrator is in the accessing right. Collection of integrated data-related data of an enterprise (company, government or private). for example:
1. Company data : production planning, accounting data, company organizing, actual production data, data ordering materials, staff data etc.
2. Government : amount of resident data, government planning, nation income data, etc.
3. Private : private document, foto, etc.
DATABASE MANAGEMENT SYSTEM
. DBMS is a collection of databases or a combination of software-based database applications. DBMS is a software designed to assist in the maintenance and utility data collection in large numbers. Application programs are used to access and maintain databases. The main purpose DBMS is to provide an environment that is efficient and easy to use, withdrawal and storage of data and information.
BIT, BYTE, FIELD
1. Bit is the smallest pieces of data that contains the value 0 or 1
2. Byte is a set of bit-bit similar
3. Field is a set of byte-byte similar, in the database used the term attribute
ATTRIBUT/FIELD
Attribute is the nature or characteristics of an entity that provides provide detail on these entities. A relationship can also have attributes. Example attributes:
1. STUDENTS: NIM, NAME, ADDRESS
2. 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 that have relationship, for example: age of the attributes generated from the date of birth, zodiac of attributes generated from the date of birth, etc.

Null Value Attribute
Attributes that have no value to a record or the attributes have no generate. For example phone number in x’self data.
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.
ENTITAS/FILE
File is a collection of similar records and have the same elements, the same attributes but different data value.
File Type
File is a collection of notes and similar elements have the same, the same attributes but different data values. In the process of application, the file can be in the category with some of the following types
1. Master File
2. Transaction File
3. File Report
4. File History
5. File Protection
6. 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
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.


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 attribute from candidate key can be choose/determined to be primary key with three criterias as follows:
1. That key more natural to be used as reference
2. That key is simpler
3. That key well guaranted the uniqueness
• Alternate key is attribute from candidate key that not chosen be primary key.
• Foreign key is just any attribute that indicate to primary key in other table. Foreign key will happen at one particular relation that has cardinality one to many or many to many. Foreign key usually always put in table that aim to many.
• External key is a lexical attribute (or collection lexical attribute) that the values always identify one object instance.

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 network model that use wording that kept in system according to abstract.
• Difference between DFD and ERD :
o DFD is a function network model that would carried out by the system
o ERD is a data network model that emphasized in structure and relationship data

ELEMENTS OF ERD
• Entity
In ER Diagram entity described with long square form. Entity is something that there in real system also or abstract where does stored data or where does found data.
• Relationship
in ER Diagram relationship can be described with a trapezoid. relationship is a natural connection that happen between entity. in general given name with verb base so that make easy to do the relation reading.
• Relationship degree
Is a total entity that participate in one relationship. degree often worn in ERD.
• Attribute
Is a character or characteristics from every entity and also relationship
• Cardinality
Show optimum tupel that can be related with entity in another entity

RELATIONSHIP DEGREE
• Unary Relationship
Is a relationship model that happen between entity that come from same set entity.
• Binary Relationship
Is a relationship model that between 2 entity.
• Ternary Relationship
Is a relationship between instance from 3 types of entity unilaterally.

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 ER-DIAGRAM
Symbolic notation in diagram er is :
1. Long square that declares collection of entity
2. Circle that declares attribute
3. Trapezoid declares relation collection
4. Line as liaison between relation collection with entity collection

Data flow Diagram

DATA FLOW DIAGRAM
Data Flow Diagram (DFD) is a diagram using the notation-notation to describe the flow of data from the system, which is very helpful to understand the logic of the system, and tersruktur 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.
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 top-level specification process . method that is used to describe the process can use a sentence with descriptive, and on a more detailed level, namely on the bottom (functional primitive) require a more structured specification, process specification will be the guidelines for a programmer to create the program.
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 functions to help the system to interpret the application in detail and manage all elements of the data used in the system right so that the system analyst and have a basic understanding of the same input, output, storage and processing.
At analysis, the data dictionary is used as a means of communication between the systems analyst with the user. At the system design, data dictionary is used to design input, reports and databases.
Data dictionary contains the following:
1. Name of data flow: must note that readers who need further explanation about a flow of data can find it easily
2. Alias: initials or other name of the data can be written when 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
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 or levels below it. 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.
The issues that must be considered in the DFD which have more than one level:
1. There must be a balance between input and output of one level and next level
2. Balance between level 0 and level 1 at input output of the flow of 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 and from the process concerned
3. Name of the flow of data, data storage and terminals at each level must be the same if the same object
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