Database Design - Essay Example

Database Design The database design is presented in three stages as identified by Gavin Powell (2005)i. These include Conceptual Design, Logical Design and the Physical Design as described below. Conceptual Design The conceptual design of a database being the element part of the overall development of the design forms a critical segment of the design process. The conceptual design stage of the database predominantly includes the description of the various entities and their relationship with regards to the envisaged business application at a strategic level. In the light of the aforementioned arguments an insight into the validations system design is as follows The primary entity in the validations information model is the meter assigned to the customer, as this is a unique entity for every customer. The meter assigned to the domestic customers in the market not only provides the flexibility for the organization to effectively identify the customer and his/her consumption but also provides the ability for the company to conduct regular maintenance on the meters so as to prevent any malfunctioning and abuse of the facility provided to the customers. Furthermore, the information model comprises of the customer and the details of the meter including the intricate technical details as well as the maintenance schedules apart from the measurements that quantify for billing. This is necessary for the effective management of the information system and conduct regular checks on the meters in order to prevent errors in the readings leading to unwanted reconciliations for the organization. The conceptual design of the database model also includes the use of the billing efficiently both during the period of operation for the meter as well as at the time of removal and installation of the new meter. This method although straight forward in words is actually a complex procedure as it not only involves the company and the customer but also the supplier for the meter that is installed at the customer site. The need for policy compliance and related issues make the process complex and the information model should address this. The conceptual design being the high level specification of the information model to be deployed without any system restrictions into account or technical considerations, the conceptual design also comprises of the following at a strategic level implementation 1. The billing section to be notified of any new changes in the metering for a given customer once the change is completed. 2. The information system should account for the price of the gas that is dynamic on a day-to-day basis. The gas price being fixed using a dynamic bidding process in the UK energy market should be accounted for whilst calculating the bills and any reconciliation to the customers. 3. The information model should also include the dynamic transportation charges for the gas from the source to the customer, which is additional to the gas pricing on a day-to-day basis for the quantity of gas being transported every day. Logical Design: The logical design is implemented using the Entity Relationship diagrams as follows Entities: The entities identified in the logical design of the application include 1. Customer 2. Validation 3. Meter 4. Supplier 5. Billing and Reconciliation Among the aforementioned the first four are the primary entities in the database with the Billing and Reconciliation forming the transaction in the system. This makes it clear that any trigger implemented with regards to the finance processing will reflect upon the transactions on billing and reconciliation entity only. The relationships between the entities identified are discussed below followed by the entity relationship diagram adhering to the UML standards. The customer being the first and important element of the system identified is placed central to establish the relationship with other entities based upon the customer. Customer - Meter: the relationship between the customer and the meter is deemed to be 1N. This is because of the fact that a customer can have more than one meter assigned to him/her. Although only one meter can be assigned to the domestic customers at a given time, the de-commissioning of a meter and installation of another meter apparently explains the aforementioned argument. Hence the relationship between the meter and the customer is 1N. Customer - Validation: From the arguments for the meter mentioned above, the relationship between the customer and the validation for the meter is also deemed as 1N. This is because a validation of the meter can fail which can be conducted again after maintenance to secure a pass. This makes it clear that more than one validation is possible for a given meter. Hence the relationship 1N is justified. Meter - Validation: From the above arguments it is further clear that a meter can have more than one validation thus making it clear that the relationship between the two entities is 1N (i.e.) a meter can have more than one validation. Supplier -Meter: The supplier although appears only in times of replacement of the meters, it is essential to note that a relationship is established between the supplier and the meter in order to avoid any duplication of data. Hence the supplier to meter relationship is deemed as 1-1. This is because a meter to a customer can have only one supplier whilst a supplier can supply more than one meter. Hence the overall relationship is derived the relationship between the customer - meter - supplier, which is deemed as 1N1. Billing and Reconciliation is the more complex relationship with the customer because of the following reasons. 1. A customer cannot be billed more than once for a given time period to a given meter and the fact that at a given instance only one meter exists further makes it clear that there is no room for duplication in the billing to the customer. Although this is a straight forward concept, the reconciliation to the bills already sent to the customer is a complex approach and can occur as many time as required in order to correct errors in the reading which will either benefit the customer or the company. 2. There can be more than one reconciliation to a given customer, which can comprise more than one meter making it clear that the duration of the reconciliation can include more than one billing period. Hence the relationship is broken in two sub-transaction entities ass follows Customer - Bill: This relationship is deemed as 1-1 relationship (one - to - one). Customer - Reconciliation: This relationship can be drilled down further with the presence of the bill in the middle making the entity relationship as customer - Bill- Reconciliation. This approach justifies the arguments presented above. Hence the relationship for the entities identified can be derived as 1-N-1. This makes it clear that the bill although identified as the central element is the transaction entity in the relationship explaining a customer can have more than one bill, a reconciliation can cover more than one bill sent to the customer thus making it clear that there cannot be a direct relationship between the customer and the reconciliation. The Entity Relationship diagrams for the aforementioned discussion are presented below The aforementioned diagram summarises the high-level entity relationship diagram for the designed database application. The attributes to the aforementioned entities are presented in the UML Relational database design later in the report. The use of Unified Modelling Language (UML) in the design and implementation of the system is argued as the effective method of database design by many researchers and authors including Gavin Powell (2005). This is not only because of the ability for a designer to replicate the database design virtually to the finest possible details but also the flexibility in the UML to treat the objects as entities irrespective of their data types or other attributes. This makes it clear that the implementation of the UML design is palpable even with the detail of the object attributes without actually signifying the attributes of the object. This form of pseudo design approach is the key for the successful design using UML. Using the object-oriented approach for the database design the object oriented UML database model is presented in Fig 2 below. The aforementioned entity database design is implemented using Oracle and Postgres the interpretation for which is presented in the next section. Object - Relational Analysis Kevin Loney (2004)ii argues that the object relational database implementation using Oracle 10g not only provide the flexibility in the operations of the database in case of editing or updating tables where the PL/SQL code sequence can be easily edited using the object reference rather than actually creating the procedures to replicate the table being worked upon. This approach actually creates a virtual database table relationship without actually affecting the tables in the database, which helps eliminate any errors in the data entered. This approach helps maintain the integrity in the data being entered as well as providing the flexibility to the designers. The object design relational codes for the database design presented are discussed below. Abstract Data types The abstract data types option of the Oracle 10g helps mirror the exact design of the table without altering the table itself. This approach helps the programmer to actually verify the validity of the information entered by the end user in comparison with the data type designed instead of actually checking every single field. The abstract data type creation for the customer entity is given below CREATE TYPE Customer_TY as object { cust_ID Varchar2(40), Cust_MeterID Varchar2 (40), Cust_Name Varchar2 (50), Cust_Address Varchar2 (150), Cust_PostCode Char (6), Cust_Region Varchar2 (40), Cust_BillingReference Varchar2 (40), Cust_DOB Date}; / The aforementioned abstract data type created can not only help in encapsulating the customer table in the database but also prevent the direct access to the table thus eliminating the locking of the table by a given user. The exclusive locking of a table in the databases has been a critical issue in the design of application as argued by Steven Feuerstein and Bill Pribyl (2004)iii. This makes it clear that the object relational approach is an effective method of encapsulating the tables from the users whilst enabling the presence of the tables for transactions in the front end. Postgres on the other hand deploys the object relational approach in a different manner by treating the table as the object and creating instances of the table design rather than creating an abstract data type. This approach although different in the initial level is exactly similar in operation to its Oracle counterpart making it clear that the object relational approach is predominantly to enable flexibility in coding and manipulation from the front end systems. In both the cases, the forms created for front end can access only objects replicating the tables in the databases instead of accessing the tables itself. This approach is efficient because of the fact that it not only eliminates the exclusive locking of the tables but also enables the effective trigger operations within the tables thus eliminating the issues of referential integrity and data access hierarchy to the tables. Although the documentation of the Postgres relational database application describes the aforementioned ability to deploy the object relational database, the actual application does not have the total application functionality described. The only capability for Postgres is the ability to inherit the table design to the objects, which again cannot be created for multiple instances. This makes it clear that the efficiency of the Postgres in the application development especially in the front-end back-end application proposed in this case is not effective as with the use of Oracle. The fact that the object relational database model is implemented as the key update in the Oracle 10g version providing utmost flexibility to the programmers justifies the use of Oracle as the database backend for the application. Alongside, the database design proposed for the objects in the Validations application further makes it clear that the object-relational implementation is not only necessary for the purpose of creating a mirror for the front end but also to enable the effective operations of the triggers for the tables. Triggers: The triggers for the database application proposed are discussed below The type of triggers used in this application is under the category of Data Manipulation Triggers as the information in the tables are the ones targeted by the triggers and not the design. Change of Meter to the customer The first trigger for the database is when the meter changes, the system should automatically generate the billing details for the meter to the customer and store it in the Billing Table. The code for the trigger is as follows CREATE TRIGGER Update_Bill AFTER UPDATE OF Cust_MeterID ON TblCustomer FOR TblBilling.Bill_EndDate Read More