Phases Of Database Design 4z5r5c

Phases of Database Design Database design is made up of three main phases, namely conceptual, logical, and physical design. Conceptual database design – to build the conceptual representation of the database,which includes identification of the important entities, relationships, and attributes. Logical database design – to translate the conceptual representation to the logical structure of the database, which includes deg the relations. Physical database design – to decide how the logical structure is to be physically implemented (as base relations) in the target Database Management System (DBMS).

Conceptual database design Conceptual The process of constructing a model of the data used in an database design enterprise, independent of all physical considerations. The first phase of database design is called conceptual database design, and involves the creation of a conceptual data model of the part of the enterprise that we are interested in modeling. The data model is built using the information documented in the s’ requirements specification. Conceptual database design is entirely independent of implementation details such as the target DBMS software, application programs, programming languages, hardware platform, or any other physical considerations. Throughout the process of developing a conceptual data model, the model is tested and validated against the s’ requirements. The conceptual data model of the enterprise is a source of information for the next phase, namely logical database design. Conceptual database design Step Step Step Step Step Step Step Step 1

1 Build conceptual data model 1.1 Identify entity types 1.2 Identify relationship types 1.3 Identify and associate attributes with entity or relationship types 1.4 Determine attribute domains 1.5 Determine candidate, primary, and alternate key attributes 1.6 Consider use of enhanced modeling concepts (optional step) 1.7 Check model for redundancy

Step 1.8 Validate conceptual model against transactions Step 1.9 Review conceptual data model with Logical Database Design Logical The process of constructing a model of the data used in an enterprise database based on a specific data model, but independent of a particular DBMS design and other physical considerations. The 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. The conceptual data model created in the previous phase is refined and mapped on to a logical data model. The logical data model is based on the target data model for the database (for example, the relational data model). Whereas a conceptual data model is independent of all physical considerations, a logical model is derived knowing the underlying data model of the target DBMS. In other words, we know that the DBMS is, for example, relational, network, hierarchical, or object oriented. However, we ignore any other aspects of the chosen DBMS and, in particular,any physical details, such as storage structures or indexes. Throughout the process of developing a logical data model, the model is tested and validated against the s’ requirements. The technique of normalization is used to test the correctness of a logical data model. Normalization ensures that the relations derived from the data model do not display data redundancy, which can cause update anomalies when implemented. The logical data model should also be examined to ensure that it s the transactions specified by the s. The logical data model is a source of information for the next phase, namely physical database design, providing the physical database designer with a vehicle for making tradeoffs that are very important to efficient database design. The logical model also serves an important role during the operational maintenance stage of the database system development lifecycle. Properly maintained and kept up to date, the data model allows future changes to application programs or data to be accurately and efficiently represented by the database. Logical database design for the relational model Step Step Step Step 2

2 Build and validate logical data model 2.1 Derive relations for logical data model 2.2 Validate relations using normalization 2.3 Validate relations against transactions

Step Step Step Step

2.4 2.5 2.6 2.7

Check integrity constraints Review logical data model with Merge logical data models into global model (optional step) Check for future growth

Physical database design Physical The process of producing a description of the implementation of the database database on secondary storage; it describes the base relations, file design organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures. Physical database design is the third and final phase of the database design process, during which the designer decides how the database is to be implemented. The previous phase of database design involved the development of a logical structure for the database, which describes relations and enterprise constraints. Although this structure is DBMSindependent, it is developed in accordance with a particular data model such as the relational, network, or hierarchic. However, in developing the physical database design, we must first identify the target DBMS. Therefore, physical design is tailored to a specific DBMS system. There is between physical and logical design, because decisions are taken during physical design for improving performance that may affect the structure of the logical data model. In general, the main aim of physical database design is to describe how we intend to physically implement the logical database design. For the relational model, this involves:       

creating a set of relational tables and the constraints on these tables from the information presented in the logical data model; identifying the specific storage structures and access methods for the data to achieve an optimum performance for the database system; deg security protection for the system. Ideally, conceptual and logical database design for larger systems should be separated from physical design for three main reasons: it deals with a different subject matter – the what, not the how; it is performed at a different time – the what must be understood before the how can be determined; it requires different skills, which are often found in different people.

Database design is an iterative process, which has a starting point and an almost endless procession of refinements. They should be viewed as learning 3

processes. As the designers come to understand the workings of the enterprise and the meanings of its data, and express that understanding in the selected data models, the information gained may well necessitate changes to other parts of the design. In particular, conceptual and logical database designs are critical to the overall success of the system. If the designs are not a true representation of the enterprise, it will be difficult, if not impossible, to define all the required views or to maintain database integrity. It may even prove difficult to define the physical implementation or to maintain acceptable system performance. On the other hand, the ability to adjust to change is one hallmark of good database design. Therefore, it is worthwhile spending the time and energy necessary to produce the best possible design.

Physical database design for relational databases Step Step Step Step Step Step Step Step Step Step Step Step Step

4

3 Translate logical data model for target DBMS 3.1 Design base relations 3.2 Design representation of derived data 3.3 Design general constraints 4 Design file organizations and indexes 4.1 Analyze transactions 4.2 Choose file organizations 4.3 Choose indexes 4.4 Estimate disk space requirements 5 Design views 6 Design security mechanisms 7 Consider the introduction of controlled redundancy 8 Monitor and tune the operational system