Concepts of Database Design

Jan 24 • Engineering Sample Papers • 7870 Views • No Comments on Concepts of Database Design

We all know that the data is an important resource of an organisation and the database is the most fundamental component of an information system. Therefore, management and control of the corporate data and the corporate database is very important. Database design is a process of arranging the corporate data fields into an organised structure needed by one or more applications in the organisation. Database is need to be designed for controlling the storing, updating, and deleting data from the database. It is also useful to reduce the data redundancy from different relations. We have to keep eye on every data present in the database, so we need a error free database. So to design a redundant free database, we have to learn the concepts of database design. In this section, we will learn all the concepts of designing a database, including all the parameters, the different normal forms etc. Still if it contains any error please do let us know. You can give your feedback through the comments, any type of suggestions will be highly appreciated.

Database Design Concepts

Advantages of Database Structure

Data redundancy
Data independence
Application
Performance
Data security
Ease of programming

Database Development Life Cycle (DDLC) : The database development life cycle (DDLC) is a process of designing, implementing and maintaining a database system to meet strategic or operational information needs of an organisation or enterprise such as:

Database Development Life Cycle Model

Improved customer support and customer satisfaction
Better production management
Better inventory management
More accurate sales forecasting

Feasibility study and requirement analysis phase
In this phase of DDLC, a preliminary study is conducted of the existing business situation of an enterprise or organisation and how the information systems might help solve the problem. The business situation is then analysed to determine organisation’s needs and a functional specification document (FCD) is produced. Feasibility study and requirement analysis stage emphasizes the study of existing systems and procedures. It emphasizes on technological, operational and economical feasibilities of database. It is the scope of database system. This phase emphasizes information system as well as hardware and software requirements. It interfaces for various categories of users.

Different approaches to design of a database.
There are many approaches to the design of a database as given below:

Bottom-up database approach: This approach starts fundamental level of attributes, that is properties of the entities and relationships. It then combines or add to these abstractions, which are grouped into relations that represent types of entities and relationships between entities. New relations among entity types may be added as the design progresses.
Top-down database approach: This approach commences with the development of the data models that contains high level abstractions. Then the successive top-down refinement are applied to identify lower-level entities, relationships and the associated attributes. The Entity Relationship model is an example of top-down approach and is more suitable for the design of complex databases.
Inside-out database design approach: This approach begins with the identification of set of major entities and then spreading out to consider other entities, relationships and attributes associated with those first identified. The inside-out database design approach is special case of a bottom-up approach, where attention is focused at a central set of concepts that are most evident and then spreading outward by considering others in the vicinity of existing ones.
Mixed strategy database design approach: This approach uses both the bottom-up and top-down approach instead of following any particular approach for various parts of the data model before finally combining all parts together. In this case, the requirements are partitioned according to a top-down approach and part of the schema is each partition according to a bottom-up approach.

First Normal Form (1NF).
A relation is said to be in first NF, if it has atomic attribute, atomic attribute means, the attribute which cannot be subdivided. For e.g. Name attribute which can be further divided into subparts like first name, middle name and last name. Hence we can say Name attribute is not in 1NF. Therefore to make this relation 1NF, we have to make this relation as atomic.
In the figure, two tables TABLE_PRODUCT_PRICE and TABLE_PRODUCT_COLOR has shown. Now according to First normal form, the new relation can be made between these two tables.

Fig : Table with Redundancy

Fig. : Table After First Normal Form (1NF)

Boyce-Codd Normal Form.

The original definition of 3NF was inadequate in some situations. It was not satisfactory for the tables:
(a) That had multiple candidate keys
(b) Where the multiple candidate keys were composite
(c) Where the multiple candidate keys overlapped. Therefore, a new normal form, the Boyce-Codd Normal form was introduced. To convert a table to BCNF the guidelines are

Find and remove the overlapping candidate keys. Place the part of the candidate key and the attribute it is functionally dependent on, in a different table.
Group the remaining items into a table.

An Example of a relation schema JR and a set of dependencies such that R is in BCNF, but not in 4NF.
The relation schema R=(A, B, C, D, E) and the set of dependencies
A->->BC, B->->CD, E->->AD
Constitute a BCNF decomposition, however it is clearly not in 4NF.

Difference between BCNF and 3NF
Third normal form removes virtually all the redundant data which requires that there are no non trivial functional dependencies of non key attributes on something other than a super set of a candidate key. A relation is i 3NF if and only if , it is in 2NF and every non key attribute is non transitively dependent on the primary key i.e, there are no transitive functional dependencies. Anomalies can occur when a relation contains one or more transitive dependencies. The transitive functional dependencies arise when one non-key attribute is functionally dependent on another non-key attribute.

Fifth Normal Form (5NF)
In Fifth normal form (5NF) or projection join normal form (PJNF) there are no nontrivial join dependencies that don’t follow from the key constraints. A table is said to be in the 5NF if:
(a) It is in 4NF
(b) Every join dependency in it is implied by the candidate keys.

Sixth normal form (6NF)
A table is said to be in sixth normal form (6NF) or Domain key normal form (DKNF) if:
(a) It is in 5NF
(b) If simply enforcing the domain constraints and the key constraints can enforce all constraints and dependencies that should hold on a relation specified on the relation.

About the Author: Niroj Pahi

Niroj Kumar Pahi B-Tech Computer Science Engineering From Oxford College of Engineering and Management, Bhubaneswar, Odisha. Currently Working in Ruva Customer Services pvt. Ltd as IT promotional Consultant