Normalization

Functional Dependency (FD)

restriction is derived from the meaning of attributes and relationships between attributes
There is a functional dependency from X to Y indicates that the value of attribute X determine the unique value of attribute Y
- Written as X --> Y
- Can be read as: determine the X or Y, Y is determined by the X or, there are functional dependency from X to Y
- FD is obtained from the fact that there is (obtained at the analysis system)

Example :

• Relation instance Teach potentially have on the functional dependency TEXT --> COURSE

• There is no FD TEACHER -->COURSE
• FD should apply to all lines in the relationship. To determine the FD must understand the semantics of data, consider the instance that there is now and will have in the future.

On the relations EMP_PROJ there are 3 FD:
• (SSN, PNUMBER) --> HOURS
• SSN --> ENAME
• PNUMBER --> PNAME, PLOCATION


Normalization
Normalization should be performed for:
- Optimization table structures
- Increase the speed
- Eliminate income data the same
- More efficient use of storage media
- Reduce redundancy
- Avoiding anomalies (insertion anomalies, deletion anomalies, update anomalies).
- Improved data integrity

• There are several normal forms based on a number of criteria:
- Primary keys (1NF, 2NF, 3NF)
- All Candidate Keys (2NF, 3NF, BCNF)
- Multivalued dependencies (4NF)
- Join dependencies (5NF)

Normalization should be done in order to design the database that produced good quality and meet the desired nature. Normalization in practice difficult to do if the restrictions of a basic normalization difficult to understand or difficult to detect. The database does not need to do a form of normalization to the highest, usually done to achieve 3NF or BCNF

Denormalisasi: reverse process of normalization, ie, combine several relations, brought to normal form of a lower

First Normal form (1NF)
First normal form (1NF) is part of the definition of relations.
• The form does not allow the normal first:
- Composite attributes,
- Multivalue attributes,
- Nested relations.

Second Normal form (2NF)
• Using the concept of FD and the primary key
• Definition:
- Full functional dependency: a FD X --> Y if such until one of the attribute of X removed, then the FD is not there anymore.
• Example:
- (SSN, PNUMBER) --> HOURS is a full FD, as there is no SSN --> HOURS, and PNUMBER --> HOURS
- (SSN, ENAME --> PNUMBER) is a partial dependency, not because there is a full FD dependency SSN ENAME
• A relation R is in second normal form (2NF) if R is in 1NF and every nonprime attribute A in relation R is functional dependent on the full primary key.
• A relation R can didekomposisi to-customer relationships that meet 2NF through the process of 2NF normalization

Third Normal form (3NF)
• A relation R is in third normal form (3NF) if R is in 2NF and no nonprime attribute A in R which has a transitive dependency on the primary key.
• A relation R can-decomposition relationships to meet the 3NF relations via the process of 3NF normalization
• Note:
- In the FD X -> Y and Y -> Z, with X as the primary key, we consider this as a problem only if Y is not a candidate key.
- If Y is a candidate key, there is no problem with this transitive dependency
- Example: Relations EMP (SSN, Emp #, Salary) does not violate 3NF because even though there are FD SSN à Emp # à Salary, Emp # is a candidate key

Normal is defined in the form of informal
• 1st normal form
- All attributes depend on the key
• 2nd normal form
- All attributes depend on the whole key
• 3rd normal form
- All attributes depend on nothing but the key

Boyce Codd Normal Form (BCNF)
• A relation scheme R is in BCNF with the requirement if there are X à FD on R, then X is superkey of R.
• Every BCNF definitely meet 3NF
• There are relationships that are in 3NF but not BCNF
• Goal to achieve normalization generally 3NF or BCNF

example
• There are 2 FD on the relation Teach:
- Fd1: (student, course) -> instructor
- Fd2: instructor -> course
• (student, course) is a candidate key to reach
- Relations are located in 3NF but not in BCNF
• BCNF relations that have not been able to achieve BCNF decomposition, but sometimes the FD can eliminate the existing

• There are 3 decomposition is possible to teach the relation:
- (Student, instructor) and (student, course)
- (Course, instructor) and (course, student)
- (Instructor, course) and (instructor, student)
• All is lost decomposition FD1
- All of the FD relations are kept as much as possible, but can also disappear in the normalization BCF
- However, the nature of lossless and non-additivity property after decomposition must remain guarded

Normal form of the fourth and fifth
• Relations in fourth normal form (NF 4) if the relation in BCNF and does not contain a lot of dependence 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

Database is a collection of data from a related one with the other stored in the computer and necessary software to manipulate. Or, organizing the collection of data related to each other to facilitate activities to obtain information.

DBMS (DATABASE MANAGEMENT SYSTEM)

System that is specifically created to make it easier to manage users in the database. The main purpose DBMS is to provide an environment that is efficient and easy to use, withdrawal and storage of data and information.

Advantages of DBMS:

• Data independence

• Access to the data of the Efficient

• Security and Data Integrity

• Data Administration

• Access Alongside and to the failure recovery

• Time of Application Development shortened.

ATRIBUTE
Entity is the object or objects in the real world is represented in the database. Entity objects that can be physically seen, such as cars, houses, employees, and can also form a physical concept is not visible, such as lectures, corporate, employment.

Attribute is a character or situation that is used to describe an entity.
For example, an employee has a name, SSN, address, gender, date of birth.

One entity will have specific values for each attribute. For example, employees have a certain name Wira, SSN: 1234567 Address: Klungkung Sex: Male Birthdate: 12 August1988
Each attribute has a set of values associated with it. This value is a set of data types, such as integer, string, and the other.

TYPE ATRIBUTE

• Simple / Single

Each entity has a single atomic value. Example: SSN, Sex.

• Composite

attribute consists of several components. Example: Address (Street, City, State, ZipCode, Country), Name (firstname, MiddleName, LastName). The composition can be a hierarchy where the attribute is also a component of composite attribute.

• Multi-valued

An entity can have multiple values. Example: color of a car, degree of employees, expressed in: (color), (title).

• Attributes composite multi-valued and can make a nest (nested). Example: (Previous Degrees (College, Year, Degree, Field)).

• PreviousDegrees attribute is a composite and multi-value.

Stored vs. derived Attibutes

- Stored: regular attribute

- Derived: attribute is derived / calculated from the stored attribute

Example: Birthdate vs Age

RECORD / TUPLE

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

KEY
In a collective entity, or entity type, between one entity with another entity must be distinguished. Make how an entity is unique? We need to find an attribute that can be deference.
For example, for the employee, SSN is unique because no two people have the same SSN. For the company, the name can be unique as there is no company that has the same name with another company.

For the payment slip, may have two attributes as pembeda, eg date and time.
Attribute whose value is called a unique key attribute .. An entity can have one key attribute, can also better than one

TYPE 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 (one to many) or many to many (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

DFD is a model of network functions that will be implemented by the system
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 associated 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 reviewed. 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 only have a relationship with one incident in which 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

- Rectangle represent the collective entity

- Circle represent the attributes

- rhomb is represent collective relationships

- Line as the link of relationships between the entity and the collective entity with collection of attribute

DATA FLOW DIAGRAM

To facilitate depiction of a system or the new system will be developed without considering the logic of the physical environment where the data flow or the physical environment where the data is saved, then we use the Data Flow Diagram (DFD). DFD is a tool that can describe the flow of data in a system with a structured and clear, that is why DAD is a tool the most important for a systems analyst. DAD can represent a system that automatically or manually by using the image in the network graph.

DFD consists of a context diagram and a detailed diagram (DFD Levelled). Context diagram map model work environment (describing the relationship between external entities, input and output system), which is represented with a single circle which represents the overall system. System is limited by Boundary (depicted by broken line), and at the time of presentment may not have storage (storage).

DFD describes the system as levelled networks between functions related to one another with the flow and storage of data, this model only makes the model system from the point of view function.

DFD will be levelled in a decline in the level where a decrease in the lower level must be able to represent the process in a clear specification of the process. So levelled in DFD can be started from the DFD level 0 and then go down to the DFD level 1 onwards. Each reduction is done only when necessary. The flow of incoming data and exit the process at a level x must be related to the flow of incoming data and exit the level x +1 in the process of defining the level of x is. Process that can not be revealed / told dirinci more primitive functional and the process referred to as primitive.

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. Specification process will be the guideline for the programmer to make this program (coding). Method used in the specification process: the process of disintegration in the form of a story, decision table, decision tree.

TERMINATOR COMPONENTS / FOREIGN ENTITY
Terminator represent external entities that communicate with the system that is currently being developed. Terminator usually known by the name of foreign entities (external entity).
There are two types of terminator:
1. Terminator Source (source): the terminator of a source.
2. Terminator Destination (sink) is the terminator that was the purpose of data / information system.

Terminator can be a person, group of people, organizations, departments within the organization, or the same company but outside the control system that is being made models.

Terminator can also be departments, divisions or systems outside the system that communicate with the system that is currently being developed.

Terminator components need to be given this name in accordance with the outside world that communicate with the system that is being made model, and usually use the noun, for example, Sales Section, Lecturer, Student.

There are three important issues that must be remembered about the terminator: Terminator is a part / the environment outside the system. Data flow that connects terminator system with different processes, the system shows the relationship with the outside world. Professional Systems reserve the right not to change the contents or the way work organization or procedures relating to the terminator. Relationship existing between the terminator with each other is not described in the DFD.

DATA FLOW

Is the place mengalirnya information. Depicted with the 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 flow between processes, data storage and data flow indicates that the form of data input for the system

* Guidelines of the name:
0 Name of the flow of data that consists of some words associated with the flow lines connect
0 No flow data for the same and the name should reflect its content
0 The flow of data that consists of several elements can be expressed with the group element
0 Avoid using the word 'data' and 'information' to give a name to the flow of data
0 Wherever possible the complete flow of data is written

* Other provisions
0 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
0 Data flow into or out of data storage does not need to be given a name when the flow of data simple and easy to understand and describe the data flow of all data items
0 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
Describe the process components of the system that transform input into output. The process is given a name to describe the process / activities that are / will be implemented. Giving the name of the process is done by using the transitive verb (the verb requires an object), such as Calculating Salaries, Printing KRS, Calculating Number of SKS. There are four possibilities that can occur in the process with respect to input and output:

There are a few things to note about the process:

0 The process must have input and output.
0 The process can be connected with the terminator component, process or store data through the data flow.
System / section / division / department that is being analyzed by the professional system with the described process components.

Here is wrong example of a process:

Generally, errors in the DFD process is:

1. The process does not have any input but the output. This error is called the black hole (black hole), because the data into the process and disappeared like to include in the black hole (see process 1).

2. The process of producing output, but did not receive input. This error is called a miracle (magic), because the output produced magically without ever receiving input (see process 2).


COMPONENT DATA STORE
This component is used to create a model set of data packets and given the name of the plural noun, such as Student. Data store is usually associated with the storage-storage, such as file or database associated with an in-store computer, such as a diskette file, files, hard disk, magnetic tape files. Also store data related to the storage manually, such as address books, file folders, and agenda.

A data store associated with the flow of data only on the component processes, not with the other components DFD. Data flows that connect the data store with an understanding of the process have the following:

Flow data from the data store, which means as the initialization or reading one single packet of data, more than one packet of data, part of a single packet of data, or part of more than one data packet to a process (see figure 2 (a)).

Flow of data to store data, which means as data updates, such as adding a new data packet or more, remove one or more packages, or change / modify one or more data packet (see figure 2 (b)).

In the first data store does not change, if a packet of data / information from a data store to a process. Instead of the second data store has changed as a result of the flow enters the data store. In other words, the process flow of data is responsible for the changes that occur in the data store.

DFD SYMBOLS

DICTIONARY OF DATA (CATALOG DATA)

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

* Load the data dictionary as follows:
0 Name of data flow: must note that readers who need further explanation about a flow of data can find it easily
0 Alias: the name of the data can be written when there is
0 Forms of data: used to segment the data dictionary to use when designing the system
0 Flow data: indicates from which data flows and where the data
0 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 / levels below
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
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:
0 There must be a balance between input and output of one level and next level
0 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
0 Name of the flow of data, data storage and terminals at each level must be the same if the same object


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