Physical Database Design

Sep 26 • Notes • 1044 Views • No Comments on Physical Database Design

As 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 data and database is very important. Database design is the process of arranging the data fields into an organizes structure needed by one or more applications in the organisation. Physical database design is one of the most important database design process in database management system. Physical database translates the logical statements into the SQL commands for performing various database operations.

Physical Database Design

Physical Database Design

Physical database design translates the logical data model into a set of SQL commands which will execute in the database management system. This helps to store, update and retrieve the data from database. For relational database system, it is easy to translate a logical data model into a physical database.

Rules for translation
There are certain rules for translation in physical database design. These are listed below.
(a)  In physical database, the entities become tables.
(b) The unique identifiers become columns in physical database. These columns have null values. These are referred as the primary keys in the physical database.
(c)  In physical database, the relationships become foreign keys.

Naming and Attribute rules
In physical database, certain rules are to be followed during naming. Spaces are not allowed in between entity names. (_) mark may be allowed. Because, these names must translate into SQL commands for create a table, table name should confirm to SQL naming rules. Primary key attributes is a data type that can be indexed. Primary keys are arbitrary because it is faster to search on numeric fields in many database engines.

Foreign key and relationship.
Adding foreign keys into one of the tables involved in the relationship. A foreign key is a unique identifier of the table on the other sides  of the relationship. We can say that it is the media or relationship between two or more tables. The most common relationship is the one-to-many relationship. This relationship is mapped by placing the primary key on the “one” side of the relationship into the table on the “many” side.

SQL command to create a table Customer with its columns include cusid and cusname.
The last remaining task is to translate the complete physical database into SQL. For example,
         Create table customer (cusid char(5), cusname(20));

Syntax of CREATE command to create a table with its columns name.
The syntax of CREATE command in SQL to create a table with its column name is
           CREATE table <table name>
( <column name1> <data type 1>,
<column name2> <data type 2>);

SQL command to show the use of primary key in database
The database designers create unique indices on the primary key to enforce uniqueness as follows
     Create table customer
(cusid char(3) not null,
cusname varchar2(20),
Primary key (cusid));
In the above SQL command, cusid is the primary key.

Uses of data model
Data models are normally used to make the database independent. These are used to make the database as much as used friendly and secure. The database design techniques and data models  are used not only in MySQL, but also in oracle, Sybase and any other relational database engine.

Difference between Weak and Strong entity
A strong entity set has a primary key. All tuples in the set are distinguishable by that key. A weak entity set has no primary key. Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity.

Why do we have weak entity sets?
We have weak entity sets for the following reasons.
(a) We want to avoid the data duplication.
(b) Weak entities reflect the logical structure of an entity being dependent on another entity.
(c) Weak entities can be deleted automatically when their strong entity is deleted.
(d) Weak entities can be stored physically with their strong entities.

Related Links

Important SQL sample paper with Practice Questions
Database Management System  is the best source of your learning

Tell us Your Queries, Suggestions and Feedback

Your email address will not be published.

« »