How to design a database Part 2

Improve the database plan

This stage will improve the database plan created earlier by breaking it down in a way that it will eliminate redundancy. Although decomposition can eliminate redundancy, it can lead to problems of its own and therefore should be used with caution. For example, to create the student information system,it will check whether redundant data is stored within the database.

Problems caused by Redundancy

Storing the same information redundantly can lead to several problems listed as follows.

• Redundant Storage: Some information is stored repeatedly.
• Updating problems: If one copy of repeated copies updated creates an inconsistency if it is not updated to all the copies.
• Inserting problems: it is not possible to store some information unless some other information is stored as well.
• Deleting problems: It is not possible to delete some information without losing some other information as well.

Problems related to Decomposition

When decomposing a relation schema, we have to consider,

• Whether we need to decompose a relation?
• The problems that a given decomposition causes?

For example when we create a student information system we have to think whether the table breakdown is correct or not. If we cannot retrieve the required data by using these tables, then there is a problem in the decomposition of the tables. If so, at this stage we have to eliminate these problems.

Design the database physically

This step will choose specific storage structures and access paths for the database files to achieve best benefit for the various database applications. The following criteria are used to select a physical database design option.

• Response time: this is the time between submitting a database transaction for execution and receiving a response.
• Space utilization: this will consider the amount of storage space used by the database files and their access path structures on disk, and will include indexes and other access paths.
• Transaction throughput: this is the average number of transactions that can be processed per minute.

Security Design

The three main objectives to consider while designing a secure database application are,

• Secrecy: Information should not be revealed to unauthorized users.
• Integrity: Only authorized users can modify the data which is stored in the database.
• Availability: Authorized users should not be denied access.

To achieve the above objectives there should be a clear and consistent security policy which will describe the security measures enforced, should emphasize which part of the data is to be protected, and which users get access to relevant portions of the data.