Phases of Database Design Databa Database se design design is made made up of three three main main phases phases,, namely namely concep conceptua tual, l, logical, and physical design.
Conceptual Conceptual database database design design – to build the conceptual representation of the the datab databas ase, e,wh whic ich h incl includ udes es iden identi tic cat atio ion n of the the impor importa tant nt enti entiti ties es,, relationships, and attributes. Logical Logical database database design design – to translate the conceptual representation to the logical structure of the database, which includes designing the relations. Physical Physical database database design design – to decide how the logical structure is to be phys physic ical ally ly impl implem emen ente ted d (as (as base base relat elatio ions ns)) in the the tar target get Data Databa base se Management System (DBMS).
Conceptual database design Concep Conceptua tuall he process of constructing a model of the data used in an database design enterprise, independent of all physical considerations. he rst phase of database design is called conceptual database design , and in!ol!es the creation of a conceptual data model of the part of the enterprise that we are interested in modeling. he data model is built using the the infor informa mati tion on docu docume ment nted ed in the the us user ers" s" re#ui e#uirremen ements ts sp spec eci ica cati tion on.. $oncep $onceptua tuall databas database e design design is entir entirely ely indepe independe ndent nt of imple implemen mentat tation ion deta detail ils s such uch as the the tar target get DBM DBMS sof softwar ware, appl applic icat atio ion n prog progrrams, ams, prog progra ramm mmin ing g langu anguag ages es,, har hardwar dware e plat platfo forrm, or any any othe otherr phys physic ical al considerations. hroughout the process of de!eloping a conceptual data model, the mode modell is test tested ed and and !ali !alida date ted d agai agains nstt the the us user ers" s" re#ui e#uirremen ements ts.. he he conceptual data model of the enterprise is a source of information for the ne%t phase, namely logical database design.
Conceptual database design Step & Build conceptual data model Step &.& 'dentify entity types Step &. 'dentify relationship types Step &. 'dentify and associate attributes with entity or relationship types Step &.* Determine attribute domains Step &.+ Determine candidate, primary, and alternate ey attributes Step &.- $onsider use of enhanced modeling concepts (optional step) Step &. $hec model for redundancy &
Step &./ 0alidate conceptual model against user transactions Step &.1 2e!iew conceptual data model with user
Logical Database Design Logical he process of constructing a model of the data used in an enterprise database based on a specic data model, but independent of a particular DBMS design and other physical considerations. he second phase of database design is called logical database design , which results in the creation of a logical data model of the part of the enterprise that we interested in modeling. he conceptual data model created in the pre!ious phase is rened and mapped on to a logical data model. he logical data model is based on the target data model for the database (for e%ample, the relational data model). 3hereas a conceptual data model is independent of all physical considerations, a logical model is deri!ed nowing the underlying data model of the target DBMS. 'n other words, we now that the DBMS is, for e%ample, relational, networ, hierarchical, or ob4ect oriented. 5owe!er, we ignore any other aspects of the chosen DBMS and, in particular,any physical details, such as storage structures or inde%es. hroughout the process of de!eloping a logical data model, the model is tested and !alidated against the users" re#uirements. he techni#ue of normalization is used to test the correctness of a logical data model. 6ormali7ation ensures that the relations deri!ed from the data model do not display data redundancy, which can cause update anomalies when implemented. he logical data model should also be e%amined to ensure that it supports the transactions specied by the users. he logical data model is a source of information for the ne%t phase, namely physical database design, pro!iding the physical database designer with a !ehicle for maing tradeo8s that are !ery important to e9cient database design. he logical model also ser!es an important role during the operational maintenance stage of the database system de!elopment lifecycle. :roperly maintained and ept up to date, the data model allows future changes to application programs or data to be accurately and e9ciently represented by the database.
Logical database design for the relational model Step Build and !alidate logical data model Step .& Deri!e relations for logical data model Step . 0alidate relations using normali7ation Step . 0alidate relations against user transactions
Step .* $hec integrity constraints Step .+ 2e!iew logical data model with user Step .- Merge logical data models into global model (optional step) Step . $hec for future growth
Physical database design Physical he process of producing a description of the implementation of the database database on secondary storage; it describes the base relations, le design organi7ations, and inde%es used to achie!e e9cient access to the data, and any associated integrity constraints and security measures. Physical database design is the third and nal phase of the database design process, during which the designer decides how the database is to be implemented. he pre!ious phase of database design in!ol!ed the de!elopment of a logical structure for the database, which describes relations and enterprise constraints. or the relational model, this in!ol!es? •
•
• •
• •
•
creating a set of relational tables and the constraints on these tables from the information presented in the logical data model; identifying the specic storage structures and access methods for the data to achie!e an optimum performance for the database system; designing security protection for the system. 'deally, conceptual and logical database design for larger systems should be separated from physical design for three main reasons? it deals with a di8erent sub4ect matter – the what, not the how; it is performed at a di8erent time – the what must be understood before the how can be determined; it re#uires di8erent sills, which are often found in di8erent people.
Database design is an iterati!e process, which has a starting point and an almost endless procession of renements. hey should be !iewed as learning
processes.
Physical database design for relational databases Step ranslate logical data model for target DBMS Step .& Design base relations Step . Design representation of deri!ed data Step . Design general constraints Step * Design le organi7ations and inde%es Step *.&
*
