A Complete Project of database on ATM System 2

A Complete Project of database on ATM System 2

            Physical database design

The process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures.

1)       Translate logical data model for target DBMS

This step is to produce a relational database schema from the logical data model that can be implemented in the target DBMS.

   3.1)  Design base relation

To decide how to represent the base relations identified in the logical data model in the target DBMS.

To start the physical design process, we first collate and assimilate the information about the relations produced during logical database design. The necessary information can be obtained from the data dictionary and the definition of the relations described using the Database Design Language (DBDL). For each relation identified in the logical data model, we have a definition consisting of:

�         The name of the relation;

�         A list of simple attributes in brackets;

�         The primary key and, where appropriate, alternate keys (AK) and foreign keys (FK);

�         Referential integrity constraints for any foreign keys identified.

�         From the data dictionary, we also have for each attribute:

�         Its domain, consisting of a data type, length, and any constraints on the domain;

�         An optional default value for the attribute;

�         Whether the attribute can hold nulls;

�         Whether the attribute is derived and, if so, how it should be computed.

3.2) Design representation of derived data

To decide how to represent any derived data present in the logical data model in the target DBMS.

From a physical database design perspective, whether a derived attribute is stored in the database or calculated every time it is needed is a tradeoff. The designer should calculate:

�         The additional cost to store the derived data and keep it consistent with operational data from which it is derived;

�         The cost to calculate it each time it is required

Document design of derived data

The design of derived data should be fully documented along with the reasons for selecting the proposed design. In particular, document the reasons for selecting one approach where many alternatives exist.

3.3) Design general constraints

This step is to design the general constraints for the target DBMS.

Updates to relations may be constrained by integrity constraints governing the �real world� transactions that are represented by the updates. In Step 3.1 we designed a number of integrity constraints: required data, domain constraints, and entity and referential integrity.

In this step we have to consider the remaining general constraints. The design of such constraints is again dependent on the choice of DBMS; some systems provide more facilities than others for defining general constraints. As in the previous step, if the system is compliant the SQL standard, some constraints may be easy to implement. For example, Administrator has a rule that prevents a member of staff from managing more than 5 ATM Account at the same time. We could design this constraint into the SQL CREATE

TABLE statement for account using the following clause:

//////////////////////////////////////////

USE ATMREPORT

CONSTRAINT ACCOUNTNO

CHECK( SELECT ACCOUNTNO

FROM ACCOUNT

GROUP BY ACCOUNTNO

HAVING COUNT(*)<5)

Document design of general constraints

The design of general constraints should be fully documented. In particular, document the reasons for selecting one approach where many alternatives exist.

2)       Design file organization & indexes

To determine the optimal file organizations to store the base relations and the indexes that are required to achieve acceptable performance, that is, the way in which relations and tuples will be held on secondary storage.

One of the main objectives of physical database design is to store and access data in an efficient way. While some storage structures are efficient for bulk loading data into the database, they may be inefficient after that. Thus, we may have to choose to use an efficient storage structure to set up the database and then choose another for operational use.

Again, the types of file organization available are dependent on the target DBMS; some systems provide more choice of storage structures than others. It is extremely important that the physical database designer fully understands the storage structures that are available, and how the target system uses these structures. This may require the designer to know how the system�s query optimizer functions. For example, there may be circumstances where the query optimizer would not use a secondary index, even if one were available. Thus, adding a secondary index would not improve the performance of the query, and the resultant overhead would be unjustified.

As with logical database design, physical database design must be guided by the nature of the data and its intended use. In particular, the database designer must understand the typical workload that the database must support. During the requirements collection and analysis stage there may have been requirements specified about how fast certain transactions must run or how many transactions must be processed per second. This information forms the basis for a number of decisions that will be made during this step.

  4.1) Analyze Transactions

  To understand the functionality of the transactions that will run on the database and to analyze the important transactions.

To carry out physical database design effectively, it is necessary to have knowledge of the transactions or queries that will run on the database. This includes both qualitative and quantitative information. In analyzing the transactions, we attempt to identify performance criteria, such as:

�         The transactions that run frequently and will have a significant impact on performance;

�         The transactions that is critical to the operation of the business;

�         The times during the day/week when there will be a high demand made on the database.

4.2) Choose file organizations

This step is to determine an efficient file organization for each base relation.

One of the main objectives of physical database design is to store and access data in an efficient way. For example, if we want to retrieve staff tuples in alphabetical order of name, sorting the file by staff name is a good file organization. However, if we want to retrieve all staff whose salary is in a certain range, searching a file ordered by account name would not be particularly efficient. To complicate matters, some file organizations are: Efficient for bulk loading data into the database but inefficient after that. In other words, we may want to use an efficient storage structure to set up the database and then change it for normal operational use.

Document choice of file organizations

The choice of file organizations should be fully documented, along with the reasons for the choice. In particular, document the reasons for selecting one approach where many alternatives exist.

4.3) Choose indexes

To determine whether adding indexes will improve the performance of the system.

One approach to selecting an appropriate file organization for a relation is to keep the tuples unordered and create as many secondary indexes as necessary. Another approach is to order the tuples in the relation by specifying a primary or clustering index. In this case, choose the attribute for ordering or clustering the tuples as:

�         The attribute that is used most often for join operations, as this makes the join operation more efficient, or

�         The attribute that is used most often to access the tuples in a relation in order of that attribute.

If the ordering attribute chosen is a key of the relation, the index will be a primary index. if the ordering attribute is not a key, the index will be a clustering index. Remember that each relation can only have either a primary index or a clustering index.

Specifying indexes

For example, to create a primary index on the ATMCARD relation based on the ATMNO attribute, we might use the following SQL statement:

USE ATMREPORT

CREATE UNIQUE INDEX ATMNOInd ON ATMCARD (ATMNO);

To create a clustering index on the ATMCARD relation based on the ACCOUNTNO attribute, we will use the following statement

USE ATMREPORT

CREATE INDEX ACCOUNTNOInd ON BRANCH (ACCOUNTNO) /*CLUSTER*/;

Creating Secondary Indexes

USE ATMREPORT

SELECT ACCOUNTNO, COUNT(WTHDRAWAMNT)

FROM ACCOUNT_LEDGER

GROUP BY ACCOUNTNO;

4.4) Estimate disk space requirements

To estimate the amount of disk space that will be required by the database.

It may be a requirement that the physical database implementation can be handled by the current hardware configuration. Even if this is not the case, the designer still has to estimate the amount of disk space that is required to store the database, in the event that new hardware has to be procured. The objective of this step is to estimate the amount of disk space that is required to support the database implementation on secondary storage.

As with the previous steps, estimating the disk usage is highly dependent on the target DBMS and the hardware used to support the database. In general, the estimate is based on the size of each tuple and the number of tuples in the relation. The latter estimate should on the query transactions for the USERS view of ATMCARD.

3)       Design User Views

To design the user views that was identified during the requirements collection and analysis stage of the database system development lifecycle.

Following an analysis of the data requirements for these user views, we used the centralized approach to merge the requirements for the user views as follows:

�         Branch, consisting of the Administrator user views.

�         Staff, consisting of the Assistant user views.

Document design of user views

The design of the individual user views should be fully documented.

4)       Design Security Mechanisms

To design the security mechanisms for the database as specified by the users during the requirements and collection stage of the database system development lifecycle.

A database represents an essential corporate resource and so security of this resource is extremely important. During the requirements collection and analysis stage of the database system development lifecycle, specific security requirements should have been documented in the system requirements specification. The objective of this step is to decide how these security requirements will be realized. Some systems offer different security facilities than others. Again, the database designer must be aware of the facilities offered by the target DBMS. Relational DBMS generally provide two types of database security:

�       System security;

�       Data security.

System security covers access and use of the database at the system level, such as a user name and password.

Data security covers access and use of database objects (such as relations and views) and the actions that users can have on the objects. Again, the design of access rules is dependent on the target DBMS; some systems provide more facilities than others for designing access rules. We have previously discussed three particular ways to create access rules using the discretionary GRANT and REVOKE statements of the ISO SQL standard, Microsoft Office Access.

---

link download