Second Normal Form(2NF) in DBMS

A table is said to be in Second Normal Form (2NF) when:

• It is in 1NF. and

• It includes no partial dependencies; 

that is, no attribute is dependent on only a portion of the primary key.

(Note that it is still possible for a table in 2NF to exhibit transitive dependency; that is, the primary key may rely on one or more nonprime attributes to functionally determine other nonprime attributes, as is indicated by a functional dependence among the nonprime attributes.)

Read more ...

Third Normal Form (3NF)

A table is said to be in Third Normal Form (3NF) when:

It is in 2NF and It contains no Transitive Dependencies.

In the following Figure, it shows a transitive dependency, which can generate anomalies. 

For example, if the charge per hour changes for a job classification held by many employees, that change must be made for each of those employees. 

Read more ...

Boyce-Codd Normal Form (BCNF) in DBMS

A table is in Boyce-Codd normal form (BCNF) when every determinant in the table is a candidate key.

That a candidate key has the same characteristics as a primary key, but for some reason, it was not chosen to be the primary key. Clearly, when a table contains only one candidate key, the 3NF and the BCNF are equivalent. Putting that proposition another way, BCNF can be violated only when the table contains more than one candidate key.

Read more ...

Fourth Normal Form (4NF)

A table is in Fourth Normal Form (4NF) when it is in 3NF and has no multivalued dependencies.

You might encounter poorly designed databases, or you might be asked to convert spreadsheets into a database format in which multiple multivalued attributes exist. For example, consider the possibility that an employee can have multiple assignments and can also be involved in multiple service organizations. Suppose employee 10123 does volunteer work for the Red Cross and United Way. In addition, the same employee might be assigned to work on three projects: 1, 3, and 4.

Read more ...

Systems Development Life Cycle (SDLC)

The Systems Development Life Cycle (SDLC) traces the history (life cycle) of an information system. Perhaps more important to the system designer, the SDLC provides the big picture within which the database design and application development can be mapped out and evaluated.

The traditional SDLC is divided into five phases: planning, analysis, detailed systems design, implementation, and maintenance. The SDLC is an iterative rather than a sequential process. For example, the details of the feasibility study might help refine the initial assessment, and the details discovered during the user requirements portion of the SDLC might help refine the feasibility study.

Read more ...

The Database Life Cycle (DBLC)

The Database Life Cycle (DBLC) contains six phases, as shown in the following Figure: database initial study, database design, implementation and loading, testing and evaluation, operation, and maintenance and evolution.

Read more ...

Conceptual Design in Database Design Process

Conceptual design is the first stage in the database design process. The goal at this stage is to design a database that is independent of database software and physical details. The output of this process is a conceptual data model that describes the main data entities, attributes, relationships, and constraints of a given problem domain. This design is descriptive and narrative in form. Keep in mind the following minimal data rule:

"All that is needed is there, and all that is there is needed".

In other words, make sure that all data needed are in the model and that all data in the model are needed. All data elements required by the database transactions must be defined in the model, and all data elements defined in the model must be used by at least one database transaction. The conceptual design has four steps, which are as follows.

Read more ...

Logical Design: Database Design Stages

Logical design is the second stage in the database design process. The logical design goal is to design an enterprise-wide database based on a specific data model but independent of physical-level details. Logical design requires that all objects in the conceptual model be mapped to the specific constructs used by the selected database model. For example, the logical design for a relational DBMS includes the specifications for the relations (tables), relationships, and constraints (i.e., domain definitions, data validations, and security views).

The logical design is generally performed in four steps, which are as follows.

Read more ...