Picture of the author
Study Locus

Answered: SQL physical database design and implementation

Introduction

Physical database design phase begins once the logical database models have been merged into a finalized enterprise model. The physical database design phase is the last phase in the creation of a relational database. Unlike the conceptual and logical design phases, the physical design phase is dependent on a particular database management system (DBMS). Since the physical design phase targets a specific DBMS, the designer must ensure that the target DBMS offers functionalities that befit the finalized enterprise model. Effective implementation of the physical design phase takes several steps as discussed below.

Step one: Creating base relations for a target DBMS

To kick start the physical design process, the design collects and organizes all information that is the product of the logical design phase. Using relations derived from logical data model and the data dictionary, the designer creates relations in the target DBMS. Each relation from the logical data model should provide the following information;

1.    Relation name

2.    Relation attributes

3.    Primary key and any foreign keys

4.    Integrity constraints for any foreign keys

Key information obtained from the data dictionary should include

1.    Domain of each attribute

2.    Default values for each attribute where applicable

3.    The attribute’s value

Using the information from the data dictionary and the logical relations, the designer can use a database definition language (DBDL) to organize the data into a format that is identical to one used by data definition language (DDL).

Step two: Identifying and creating enterprise constraints for the target DBMS

The designer creates constraints that restrict the data that an attribute can receive. These constraints are in most cases derived from the business rules created for the enterprise in relation to the database. Constraints are DBMS specific.

Step three: Defining secondary indexes and determining when they are used

Defining a secondary index creates a set of keys that enhance efficient data retrieval from a relation while meeting certain conditions.

Step four: Denormalization

It maybe inefficient at times to implement a fully normalized database especially where data is updated infrequently or where full normalization contributes significantly to poor database performance. At this point the designer decides whether to denormalize the relations. Denormalization is achieved by duplication of attributes in relations or merger of relations to reduce the number of join operations in data fetching.

Step five: Creating user views

Since essentially a database has many different users, the designer provides a way for all kinds of users to access the database using user views that abstract database design details and prohibit direct access.

Step six: Designing database access rules

Stakeholders in any organization have access to different levels of information regarding the organization. Similarly, a database system is meant to serve multiple users or groups of users while limiting what kind of data each user has access to in the database. This is achieved by creating access rules that grant users certain privileges that limit their level of access.

Creating primary and foreign keys

Conclusion

The physical database is implemented using database definition language (DDL). DDL is a set of commands that are used in creating the physical database. These commands are used to achieve the following tasks

a.    Creating a relation using CREATE TABLE command

b.    Creating a schema using CREATE SCHEMA command

c.    Altering relations and views e.g. when adding a column to a relation using the ALTER command

d.    Dropping relations and views using the DROP command

Primary and foreign keys are essential components of a relational database. Primary keys are used to ensure that data in a specific column is unique. This helps in uniquely identifying any record in a relation. Primary keys solely or in combination with other candidate keys form the points of linkage between relations. Normalization often results in putting related data in different relations. Foreign keys are used to link this data between different relations.

Get unique answers on this homework or any other assignment. order here

Secure payment powered by PayPal
Type of Paper
Academic Level
Deadline (days)
Pages
0
0
Price: $0 Words: 0

Affordable Pricing

High School
From$10page
College
From$13page
University
From$14page