Physical Design
The main purpose of the physical design is to actually implement the logical design that is, show the structure of the database along with all the columns & their data types, rows, relations, relationships among data & clearly define how relations are related to each other.
Following are the steps taken in physical design
Step 1: Entities are converted into tables or relations that consist of their properties (attributes)
Step 2: Apply integrity constraints: establish foreign key, unique key, and composite key relationships among the data. And apply various constraints.
Step 3: Entity names are converted into table names, property names are translated into attribute names, and so on.
Step 4: Apply normalization & modify as per the requirements.
Step 5: Final Schemes are defined based on the entities & attributes derived in logical design.
Database Design in DBMS
Database Design can be defined as a set of procedures or collection of tasks involving various steps taken to implement a database. Following are some critical points to keep in mind to achieve a good database design:
- Data consistency and integrity must be maintained.
- Low Redundancy
- Faster searching through indices
- Security measures should be taken by enforcing various integrity constraints.
- Data should be stored in fragmented bits of information in the most atomic format possible.
However, depending on specific requirements above criteria might change. But these are the most common things that ensure a good database design.