The Basics Of Database Design Programming | 2019
The process of making a comprehensive model of a database is known as database design. The process of designing the general structure of a database entails transforming a logical data model into an actual physical database. There are two parts to the database design programming. One of them involves the logical design and the other the physical design of a database.
The logical design involves a decision on the database schema. It is independent of the database management system (DBMS). However, it is created on the basis of a data model.
The physical design of a database involves a decision on its physical layout. It involves producing a description of the implementation of the database on the secondary storage and also that of the storage structures and the methods required to access the data. It is during the physical database design process that the designer of the database takes a decision on its implementation.Logical design requires that the designer find a good collection of relation schemas. There are two decisions involved in this design. One of them is a business decision. It involves decision-making as to which attributes must be recorded in the database. The other is a purely computer science decision. The designer has to decide what relation schemas to have and also how to distribute the attributes among the various relation schemas.
To develop the physical database design, it becomes necessary to first identify the target DBMS. Therefore, it is tailored to suit a specific DBMS.
Designers of databases successfully create the physical database design by keeping in mind the database objects supported by the specific DBMS and the physical structures and the files required to support the objects. They do so by considering the manner in which the DBMS supports indexing, constraints, data types, referential constraints, and other features that provide strength to the functionality of the objects.
They keep in mind the obsolete and new features of specific releases or versions of the DBMS to be used. They also need to have knowledge of the DBMS configuration parameters and the data definition language (DDL) to convert the physical design of the database into actual objects.
The transformation of the logical data model into the physical one takes place when entities are converted into tables, attributes are converted into columns, and domains are converted into data types and constraints.
Each column of a table must be assigned to a data type with the maximum length of the field being also specified and a constraint applied to it. Further a primary key must be assigned for every entity in the logical data model. Every physical table of the database must have a primary key.
The principles of normalization are used to avoid data redundancy and objects are defined at the schema level during the physical modeling phase of database design programming. The physical database design so created must be scalable and the database administrator (DBA) must program the DBMS to ensure minimum downtime of the database.