Sunday, April 26, 2009

Database Design process

Database Design process

  • Collecting user / business requirements
  • Developing E-R Model refer to user / business requirements
  • Converting E-R Model to relationship gatherings (table)
  • Normalizing relationship to remove anomalies
  • Implementation to database by make table for each relationship has been normalizatio


Database Normalization

  • Normalization is process to create database structure until a large of ambiguity can be removed.
  • Normalization phase is begin from lightest phase (1NF) until tightest (5NF)
  • Usually it’s just until 3NF level or BCNF because was enough is equal to result the good table qualified.

Why do normalization?

  • Optimizing table structures
  • Increase speed
  • Removing the same data input
  • More efficient in the use of storage media
  • Reduce redundancy
  • To avoid anomalies (insertion anomalies, deletion anomalies, update anomalies).
  • Increased data integrity

A table is called good (efficient) or normal if has 3 criteria as follows:

  1. If there is table decomposition, then its decomposition should be secured safe (Lossless Join Decomposition). It’s mean, after that table is untied / decomposition becomes new tables, which new tables can result original tables equal with previously.
  2. It’s petted dependable functional at the moment data change (Dependency Preservation).
  3. Don't break Boyce Code Normal Form (BCNF)

If third criteria (BCNF) can't be accomplished, and then at least that table not breach third phase of Normal Form (3rd Normal Form/ 3NF).


Functional Dependency

  • Functional Dependency describes attributes relationship in one relationship
  • An attribute is called functionally dependant on another one if we use that attribute to determine the value of other attribute.
Symbol that used is -> for representing functional dependency.
-> read: functionally determines

Notation: A -> B
A and b is attribute of one table. In functionally A determines B or B depend to A, if and only if there are 2 data row appropriate with A, then B also have the same value.


Notation: A.
-/-> B or A x -> B
Are opposite of the previous notation.










First Normal Form

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

Prohibited:

  • Multivalued attributes
  • Attributes composite or a combination of both.
And then:

Cost of the domain attribute must be atomic cost

Second Normal Form

  • Normal form 2NF met in a table if it meets the form of 1NF, and all the attributes except the primary key, have a full Functional Dependency on primary key.
  • A table does not meet 2NF, if there are attributes that it's dependency (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
  • Functional dependency X->Y is said full if it is remove an attribute A from X . It is means that Y is no functionally dependent.
  • Functional dependency X -> Y is said partial if it is remove an attribute A from X . It is means that Y is functionally dependent.
  • Relation scheme R in 2NF if every non-primary key attribute A E R functionally depend on the full primary key of R.
This table following 1NF but not 2NF







Boyce-Codd Normal Form

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

3NF Table


Seminat table is depend on one key attribute is not as super key as condition by BCNF. So
Seminar relations must be parsed into two namely:


Third Normal Form

Normal form 3NF met 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).

This table following 2NF but not 3NF


Fourth and Fifth Normal Form

Relations in the fourth normal form (NF 4) if the relation in BCNF and dependency not 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 (the 5 NF also called PJNF (projection join normal form). The case is very rare and appear difficult to detect in practice.

Sunday, April 19, 2009

DATABASE AND ER-DIAGRAM

Database definition:

  • Database are collection of data that saved on magnetic disk, optical is disk or another secondary storage.
  • Coherent collection of mutually data of an enterprise (firm, government or private business)

Examples:

  • Manufacturing business: production planning data, current production data, ordering data significant, etc.
  • Hospital: patient data, doctor, nurse, etc.

DBMS (DATABASE MANAGEMENT SYSTEM)

  • A set of database with application software that base of database.
  • This application program are utilized to access and maintain the databases.
  • The main purpose of DBMS is provide an easy and efficient environment for data and information employing, pulling and saving.

Bit, Byte, Field

  • Bit constitutes a smallest part of data that valuably 0 or 1.
  • Byte is a set of bits that have a same type.
  • Field is a set of bytes that have a same type, in database it is called attribute.

Attribute/field

  • Attribute or field constitutes character or characteristic of an entity that provide detail explanations about those entity.
  • A relationship can also have attribute.

Example of Attribute:

COLLEGE STUDENT has attribute as: NIM, NAME, ADDRESS

CAR has attribute as: COLOR, TYPE, CC


Type of Attribute

  • Single vs Multi-valued

Single : just can be filled at most one value
Multi-valued : can be filled by more than one value with same type.

  • Atomic vs composite

Atomic : indiscrete into smaller attribute
Composite : constitute set of some attribute which smaller

  • Derived Attribute : attribute that has a value which gets from value of another attribute, for example : age is resulted by birth date attribute.
  • Null Value Attribute : Attribute that have no value for a record
  • Mandatory Value Attribute : Attribute that must has a value


Record/ Tuple

Constitute a data row in a relationship
Consist from a set of attribute where that attribute interactions to inform the entity or relationship comprehensively.


Entity/File

File constitutes set of record that have a same type, element and attribute but have a different value.

File type :

In application processing, file can be categorized as:

  • Parent file
  • Transactions file
  • Report file
  • History file
  • Protecting file
  • Job file


Domain

Domain constitutes set of values that can be in place on one or more attribute.
Each attribute in a relational database is defined as a domain.


Key of Data Element

Key is record element that is used to find record on access time or can also be used to identify each entity / record / row.


Key Type

  • Super Key:
Super key constitutes one or more attribute of a table that can be used to identify entity / record of table uniquely (are not all attribute can be super key).
  • Candidate Key:

Candidate Key is super key with minimal attribute. Candidate key may not contain attribute from the other table therefore candidate key is definitely super key but was obviously on the contrary.

  • Primary Key:

One of attribute from candidate key that can be used as primary key with three criteria as follows:

1. That key more natural to used as hint

2. That key is simple

3. The uniqueness of that key is guarantee.

  • Alternate Key:

Are attribute from candidate key that don't be chosen becomes primary key.

  • Foreign Key:

A foreign key also called a foreign keyword, in a database table is a key from another table that refers to a specific key, usually the primary key, in the table being used. A primary key ca be targeted by necessarily have to be the target of any foreign keys.

  • External Key:

Constitute a lexical attribute (or lexical gathering attribute) which its point identify one of instance object


Entity Relationship Diagram

ERD is a network model which use word structure that saved in a system abstractly.

The different between DFD and ERD:

DFD constitutes a logistic network model that will be performed by system

ERD constitutes data network model that emphasizes on structure and data relationship


Elements of ERD

  • Entity

On ER diagram Entity is figured with rectangle. Entity is something in real system and also abstract where data is saved or where data is exists.

  • Relationship

On ER diagram relationship can be figured by diamond.

  • Relationship Degree

Are total of entity who participates in one relationship. Degree that frequent being used in ERD.

  • Attribute

Are characteristic of every entity and also relationship.

  • Cardinality

Refer total of optimum tupel that relation with entity on another entity.


Relationship Degree

  • Unary Relationship

Are model relationships that occur between entities that come from same entity set.

  • Binary Relationship

Are model relationships that occur between 2 entities.

  • Ternary Relationship

Are relationships between instances of 3 entities types unilaterally.

Cardinality












There are three cardinality of relationships:

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 in contras.

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 look. For an incident on the first entity can have any relationship with many incident on the second entity, if the one incident on the the second entity can only have one relationship with the incident on the first entity.

Many To Many:
Occur if each event on one entity has many relationships with event on another entity

Notation of ERD

Notation of ERD:













Rectangles: declare entity sets

Circle: declare attribute of entity

Diamonds: declare relationship sets

Lines as connection between relationship sets with entity sets and entity sets with its attribute.

Sunday, April 5, 2009

Part 4 - DATA FLOW DIAGRAM

DATA FLOW DIAGRAM (DFD)
  • Figuring system division into smaller module
  • Facilitate user that insufficiently understand about computer area to understands system that will be worked
CONTEXT DIAGRAM
  • Consist of one process and figures scope of a system.
  • Constituting supreme level from DFD that figure all of input and output of system.
  • System limited by boundary (figured by dotted line)
  • Stored is may not there
ZERO DIAGRAMS
  • Figuring the process of DFD
  • Giving view thoroughly hit handled system, pointing out function or aught main process, data flow and external entity
  • This level is enabled existence of data storage
  • For the process that do not elaborated again on succeeding level, then increased *' or P' symbol at the end of process numbers
  • Balance of input and output between zero diagram and context diagram should be maintained
DIAGRAM DETAILED

Diagram detail is diagram that describes what the process that available in zero diagram or level on it.
Numbering level of DFD:







In one level is better has no more than seven processes and maximal processes are nine, if it has more then has to done the decomposition.

SPECIFICATION PROCESSES
  • Each process on DFD should have process specification.
  • On top level, method that used to figure the process is by use descriptive sentence
  • On more detail level which on lower process (functional primitive) need the specification that more structured.
  • Specification process will be guidance for the programmer in makes program (coding)
  • Method that used in specification process: Breakdown of process in shaped story, decision table, and decision tree.

EXTERNAL ENTITY
  • Something that lie at beyond of the system, but it gives data into the system or gives data of system.
  • Symbolized with notation box
  • External entity excluding the part of system
  • Naming:
1.Terminal name is substantive as.
2.Terminal may not have same name except the object is same.


DATA FLOW
  • Data flow is a place to make a flow information.
  • Figured by straight line that linking the component of system
  • Data Flow is pointed out with arrow and line is named by data flow that is steaming.
  • Data flow is streaming among the process, data storage and shows the data flow of data that form the input for the system.

Name Application guidance:
  • Data flow’s name which consists from several word streams is linked by connected line.
  • No data flow that have name equality and name application shall reflect its content.
  • Data flow that consisting of many elements gets to be declared for by element group.
  • Avoid employing word “data” and “information” if we want to give name of data flow.
  • As much as can data flow name is written completely

Other Rule:
  • Data flow’s name that turns in at a process may not same by the name of data flow that turns out from the process.
  • Data flow that turn in at a data storage or turns out from data storage not necessarily been given name, if:
  • Data flow are simple and comprehensible
  • Data flow figures all of data item
  • No data flow from terminal to data storage or in the contras, because terminal is not part of system, terminal relationship with data storage shall pass through the process.

PROCESS
  • Process constitutes what the system done.
  • Process can process data or input data flow become output data flow.
  • Process has a function to transform one or many input data become one or many output data appropriate to specification that needed.
  • Each process has one or many input and resulting one or many output
  • Often process is called bubble

Guidance of Process Naming
  • Name of processes consisting of verb and noun that reflect that process function.
  • Don't use "process" word as part of name a bubble.
  • May not there many process that have same name
  • Process should be numbered. Number thread as much as can follow the flow or process thread, but thus number thread doesn't mean absolutely constitute chronologically process thread.

DATA STORAGE
  • Data storage aught data repository in system
  • Epitomized by a couple of equal line or two lines with single-sided sideways exposed.
  • Process can take data of or gives data to the database.
  • Name application guidance:
  • Name shall reflect the data storage
  • If its name more than one word then has to be hyphened

DATA DICTIONARY

Data dictionary have function in helping system user to interpret application in detail and organize all of data element that utilized by the system exactly until user and system analyst have same interpretation about input, output, storage and process.
When analysis phase, data dictionary is used as communication device between system analysts. Meanwhile at the system scheme phase, data dictionary is used to design input, report and database.
Data flow on DAD has global character and more detail information one can be seen on data dictionary.
Data dictionary is containing these things:
  • Name of data flow: have to record to make reader that requires more explanation about a data current can look for it easily.
  • Alias: alias or besides name of data can be written if available
  • Data form: It used for clustering data dictionary into its purpose in time of design system.
  • Data Flow: point out of which data is streaming and where its aim.
  • Explanation: giving explanation about meaning of data flow.

BALANCING IN DFD
  • Data flow that turns in at and turn out from one process have to equal with data flow that turns in at and turn out from details of process on level under it.
  • Data flow's name which turn in at and turn out from one process have to same with the name of data flow that turns in at and turn out from details of that process.
  • Total and name of extern entities of a process has to equal with total and name of extern entities of details of that process.
  • Things who shall be noticed on DFD who between one level and the next level.
  • Shall available balancing input and output between one level and next level.
  • Balance between level 0 and level 1 is seen on input / output of data flow goes to or goes out from terminal on level 0, meanwhile balance between level 1 and level 2 are seen on input / output of data flow goes to / or goes out from process that pertinent.
  • Name of data flow, data storage and terminal at every level should be same.

INTERDICTION IN DFD
  • Data flow may not from extern entity directly to another extern entity without through a process.
  • Data flow may not from data storage directly to another extern entity without through a process.
  • Data flow may not from data storage directly to another data storage without through a process.
  • Data flow from one process directly to the other process without going through the process of data storage should be avoided.
The SYMBOL use in DFD