Software Analysis: Normalization of the Current Database Report

Executive Summary

In this report, normalization is discussed as well as applied to the current database. The results of this normalization are also discussed. In addition, an analysis of the future software is done, which is mainly a database analysis procedure. This report concludes that the IT-Inventory application should be based on a normalized database such as the one that results after normalizing the current database. The report also concludes that the database analysis done on the future software is effective for analyzing the IT-Inventory application.

Introduction

Normalization guarantees the future growth as well as expansion of a relational database. Normalization is a relational database design activity undertaken to eliminate three common anomalies associated with poor relational database design, namely, modification, insertion and deletion. The first anomaly results in erroneous data in the relational database due to lack of consistency in modification of the data. The second anomaly results in an inability to enter new and needed data into the relational database due to incorrect fields and faulty relationships among the database’s relations. The third anomaly results in unwanted loss of needed data through deletion due to poorly constructed relations.

Discussion

Normalizing the current database to the third normal form (3NF) results in a total of nine tables in the database, namely, Machine, Machine description, Employee, Company branch, Department, Purchase order, Supplier, Employee & Department and Employee & Machine. Since they are normalized to 3NF, the tables do not exhibit repeating groups, functional dependencies and transitive dependencies (Mendes, n.d, 1).

System ERD

An entity-relationship diagram (or ERD) shows the entities that make up a relational database, their attributes and the relationships between them (About.com).

Discussing the schema

Company branch

The Company branch table holds information on the company’s branches. The branch code field is the only candidate key and thus, it is also the primary key. This field captures the unique identification assigned to a branch of the company. The secondary key in this table is the branch name.

In the table, the primary key is Company branch code; this is the only candidate key since branch name is not guaranteed to be unique. There will be one branch name per branch code. This means that Branch Code functionally determines every other attribute in Company branch table; therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key, which is the primary key as well; therefore, no partial dependencies are possible. Branch name cannot be used to functionally determine any other attribute in the table since two company branches may have the same name; and thus the second normal form is met (Mendes, n.d, 1). There is only one secondary key in the table, thus, transitive dependencies do not exist and thus the table meets the 3NF.

Department

The Department table holds information on the company’s departments. The Department code field is the only candidate key and thus, it is also the primary key. This field captures the unique identification assigned to a department in any of the company’s branches. The secondary key in this table is the Department name. The foreign key in this table is the Branch Code field that links it with the Company branch table.

In the table, the primary key is Department code; this is the only candidate key since Department name is not guaranteed to be unique. There will be one department name per department code. This means that Department Code functionally determines every other attribute in the Department table; therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key, which is the primary key as well; therefore, no partial dependencies are possible. Department name cannot be used to functionally determine any other attribute in the table since two departments may have the same name; and thus the second normal form is met (Mendes, n.d, 1). There is only one secondary key in the table, thus, transitive dependencies do not exist and thus the table meets the 3NF.

Employee

The Employee table holds information on the company’s employees. The Employee ID field is the only candidate key and thus, it is also the primary key. This field captures the unique identification assigned to an employee. The secondary keys in this table are the employee name and user name. The foreign key in this table is the Department code field that links it with the Department table.

In the table, the primary key is Employee ID; this is the only candidate key since Employee name and User name are not guaranteed to be unique. There will be one employee name and user name per employee ID. This means that Employee ID functionally determines every other attribute in the Employee table; therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key, which is also the primary key; therefore, no partial dependencies are possible. Employee name and User name cannot be used to functionally determine any other attribute in the table since two employees may have the same name; and thus the second normal form is met (Mendes, n.d, 1). There is no transitive dependency between the secondary keys in the table, thus, table meets the 3NF.

Supplier

The Supplier table holds information on the company’s suppliers. The Supplier ID field is the only candidate key and thus, it is also the primary key. This field is introduced to capture the unique identification assigned to a supplier. The secondary key in this table is the Supplier name.

In the table, the primary key is Supplier ID; this is the only candidate key since Supplier name is not guaranteed to be unique. There will be one supplier name per supplier ID. This means that Supplier ID functionally determines every other attribute in the Supplier table; therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key, which is the primary key; therefore, no partial dependencies are possible. Supplier name cannot be used to functionally determine any other attribute in the table since two suppliers may have the same name; and thus the second normal form is met (Mendes, n.d, 1). There is only one secondary key in the table, thus, transitive dependencies do not exist and hence the table meets the 3NF.

Purchase order

The Purchase order table holds information on the company’s purchase orders. The Purchase order number field is the only candidate key and thus, it is also the primary key. This field captures the unique identification assigned to a purchase order. The secondary keys in this table are the Purchase order date and Delivery date. The foreign key in this table is the Supplier ID field that links it with the Supplier table.

In the table, the primary key is Purchase order number; this is the only candidate key since Purchase order date and Delivery date are not guaranteed to be unique. There will be one purchase order date and delivery date per purchase order number. This means that Purchase order number functionally determines every other attribute in the Purchase order table; therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key, which is the primary key; therefore, no partial dependencies are possible. Purchase order date and Delivery date cannot be used to functionally determine any other attribute in the table since two employees may have the same name; and thus the second normal form is met (Mendes, n.d, 1). There is no transitive dependency between the secondary keys in the table, thus, table meets the 3NF.

Machine

The Machine table holds information on the machines bought by the company. The Machine label field is the only candidate key and thus, it is also the primary key. This field captures the unique identification assigned to a machine. The foreign key in this table is the Purchase order number field that links it with the Purchase order table.

In the table, the primary key is Machine label; this is the only candidate key since Purchase order date and Delivery date are not guaranteed to be unique. The Machine label field functionally determines every other attribute in this table; therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key, which is the primary key; therefore, no partial dependencies are possible. The secondary keys in the table cannot be used to functionally determine any other attribute in the table; and thus the second normal form is met (Mendes, n.d, 1). There is no transitive dependency between the secondary keys in the table, thus, table meets the 3NF.

Machine description

The Machine description table holds information on the description provided for a machine bought by the company. The Machine description code field is the only candidate key and thus, it is also the primary key. This field captures the unique identification assigned to a machine description. The secondary key in this table is the Machine description. The foreign key in this table is the Machine label field that links it with the Machine table.

In the table, the primary key is Machine description code; this is the only candidate key since Machine description is not guaranteed to be unique. There will be one machine description per machine description code. This means that Machine description code functionally determines every other attribute in the Machine description table; therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key, which is the primary key; therefore, no partial dependencies are possible. Machine description cannot be used to functionally determine any other attribute in the table since two departments may have the same description; and thus the second normal form is met (Mendes, n.d, 1). There is only one secondary key in the table, thus, transitive dependencies do not exist thus the table meets the 3NF.

Employee & Department and Employee & Machine

These two are junction tables that break up the many-to-many relationship that exists between the Employee and Department tables and the Employee and Machine tables respectively. Since they are junction tables, they only contain primary and foreign keys.

Use Case analysis

The following is a use case analysis for the IT-Inventory application that reveals the requirements of the system as well as identifies the information necessary to define processes in the system. There are eight steps in the analysis, which are realization, description analysis classes, responsibility, associations, behaviour, describe attributes and mechanisms.

Realization

For the IT-inventory application the main input data is:

1. Asset and asset description
2. Employee data
3. Company branch and department data
4. Purchases data
5. Supplier data

The main processes in the application are:

1. Tracking asset and asset description data
2. Tracking employee data
3. Tracking company branch and department data
4. Tracking purchases data
5. Tracking supplier data
6. Report generation
7. List/View generation

The main outputs of the application are:

1. Detailed reports on the assets purchased by the company
2. Summarized reports on the assets purchased by the company
3. List/views showing employee information

For the application the main actors are:

1. Company employees who are also the users of the system
2. Company’s senior management who are also users of the system
3. Suppliers who supply the company with assets

Description

One of the main processes of the system is report generation whose output is either a detailed or summarized report on the assets purchased by the company. For this process to be implemented successfully the application presents a user with menus and dialog boxes, which enable a user to view the report of his or her choice. Another main process in the application is list/view generation whose output is a view or list of specific data present in the database.

To generate views or lists a user is presented with a form from which he or she can enter the specifications of query. Queries are the main means of extracting data from the database. Lastly, the other main process in the application is tracking data pertaining to assets, employees, company branches and departments, purchases orders and suppliers. The process of tracking data constitutes three process addition, deletion and modification of data. To track data the application provides a user with forms from which he or she can add, view or modify the data of his or her choice.

Analysis Classes

The basis for the application is the relational database model in which data is stored in structure called a relation (Garcia-Molina et al, 2000, 15). The relational database model and the entities that make up the IT-Inventory application are the classes identified in this use case analysis. The relational database model is a toolkit that facilitates the development and implementation of relational databases. Creation of a relation in the relational database model is done such that it is representative of an entity that is fundamental in the creation of the database. The main entities that make up the IT-inventory application are Asset (Machine), Asset description (Machine description), Employee, Company branch, Department, Purchase order, and Supplier. For each of these entities a relation is created with the entity’s name.

Responsibilities

The asset relation is created to hold information on the assets purchased by the company e.g. device name, type and description. The employee relation is created to hold information on the employees of the company. The Asset relation is created to hold information on the assets of the company. The company branch and department relations are created to hold information relating to the company branches and departments respectively. The Supplier relation is created to hold information on the suppliers of the company. The purchase order relation is created to hold information on the purchase orders placed by the company.

Associations

The association between relations in these application is defined by the entity relationship model. Thus, at any given moment the relationship between any two relations in the IT-Inventory application is either one-to-one, one-many or many-to-many. For example the relationship between employee and asset relations is many-to-many implying that one record in the employee table is related to many records in the asset table. This is so when you consider in real-life that one employee can be assigned multiple assets and multiple employees can share one asset. An example of a one-to-many association is the relationship between the supplier and purchase order relations where one record in the supplier relation is associated to many records in the purchase order relation.

Behaviour

The IT-Inventory application is implemented using the Microsoft Access Relational Database Management System (RDBMS) and specifically. In this implementation, the relations listed above become RDBMS tables. Built-in within the RDBMS is a means for coordinating and managing these tables. Generally, the tables are the basic objects for holding data and allow for it to be accessed by other RDBMS objects such as queries.

Describe attributes

An entity-relationship diagram (or ERD) shows the entities that make up a relational database, their attributes and the relationships between them (About.com).

Mechanisms

To solve the problem it was created for, the main mechanisms in the IT-Inventory application are a relational database, RDBMS and programmed modules/macros. The relational database is the basis of the application and it requires to be normalized to the furthest extent possible. The RDBMS is means for implementing the design of the relational database. It is also the means for administrating the database once it has been implemented. Programmed modules or macros provide a means of automating processes within the application such as generation of views and reports at certain time intervals.

Use case diagram

From the use case diagram constructed for the IT-Inventory application it can be seen that: the company places an order for assets, with the order placed, an interested supplier goes through its contents. If the supplier is capable of servicing the order he or she confirms it and gives his or her quotation. The company goes through the quotation given by the interested supplier. If it is interested in it, it approves it and enters into a business transaction in which the supplier supplies the assets required by the company within the desired time duration.

Meanwhile, an employee submits a request to use a given asset. The employee’s request is confirmed and assessed. This leads to either approval or disapproval of the employee’s request. When the request is approved the employee is assigned the particular asset he or she had requested for. Once the asset is assigned, the employee uses it accordingly. The company, if required, is responsible for the maintenance of the assets it assigns to the various employees.

Software Analysis

Considering that, the IT-Inventory software is mainly a database application it is appropriate to analyze it using a database analysis procedure. The analysis procedure thus comprises of six steps, namely, database study, database design, implementation and loading, testing and evaluation, operation, and maintenance and evaluation.

Database study

From the use case analysis done above, the processes and requirements of the IT-Inventory application have been identified. This leads to the following statement of purpose that captures the functional requirements and scope of the IT-Inventory database “The IT-Inventory database tracks data on assets, employee data, company branches and departments, purchases, and suppliers. It can be used to modify information on existing assets as well as in the generation of reports and views”. A minor limitation of the IT-Inventory application is that its use requires prior knowledge of Microsoft Access, the RDBMS system through which it is implemented. However, to deal with this limitation the application provides a handful of menus and dialog boxes that make the application user friendly, thus the limitation is not a worry at all.

Database design

The database design process of the IT-inventory application involves close collaboration with Mr. Husain Abu Asba, one of the support team members in the company. The design of the database involves the following steps, designing tables, data normalization, enforcing data integrity and documentation. In the designing tables step, a study of the current software in place at the company reveals fields required in the IT-Inventory database. From these fields relevant tables are derived. In the data normalization stage, the tables derived from the designing tables step undergo normalization.

The third step in the database design is enforcing data integrity. Normalization was the first step in ensuring the integrity of the data in the IT-Inventory database, however, in this step plans are laid out on how to enforce referential integrity in the database during the implementation stage. The final step in the design of the It-inventory database is documentation in which documentation of the design process was prepared.

Implementation and loading

In this stage, the database design is implemented using the Microsoft Access RDBMS. Ms Access provides tools for both design and administration of the database. In addition to these, it is suitable for developing the IT-Inventory database for the reason that it contains an engine for compiling and running Visual Basic Code. Microsoft Access allows the enforcement of referential integrity, a feature that is critical in enhancing and maintaining high levels of data integrity in the database. Present in the RDMS are tools for creating other important database objects such as forms, queries and reports. Forms are particularly useful for navigating through the records of a table and in entry of new data. Queries extract information from the tables of the database. These are particularly useful in the creation of reports and views. Queries are a suitable basis for basing reports. Loading of data into the database is done with the aid of menus and dialog boxes present in the application. These menus and dialog boxes lead user’s to forms for entering data into the database.

Testing and evaluation

In this stage, the robustness of the IT-Inventory application is determined through a series of tests. The results of these tests enlighten on whether there are potential failures that can occur during the operation of the IT-Inventory application. A schedule of tests is developed in which each test is allocated a certain maximum duration of time within which it is to be carried out. One of the tests that are appropriate for the IT-Inventory application determines if the inserting new record feature of the application is functioning properly. This test can be allocated a maximum time of one day. Another important test determines if the generating report feature of the application is functioning properly. This test can be allocated a maximum time of three days due to the reason that there are a number of reports that have to be generated. Another important test determines if the query feature for extracting information from the database is functioning properly. This test can be allocated a maximum time of one day.

Operation

The operation of the IT-Inventory application needs to be closely monitored to some extent by the database administrator. The database administrator is a very important individual in the operation of the application as he or she possesses comprehensive knowledge on the design and implementation of the database. The monitoring is important because it establishes whether failures still occur in the system that was not identified during the testing and evaluation stage. If a failure occurs, the foremost step should be determining its severity and causes. After this step solutions to the failure should be developed. Operational failures in the IT-Inventory application can result from faults in the design of the database or from Ms Access, the RDBMS used in its implementation. An important aid in fixing failures arising from the design of the database is the design documentation prepared during the database design stage. If during the monitoring process no failures occur, the application can be assumed to be functioning properly.

Maintenance and evolution

Maintenance of the IT-Inventory application is the task of the database administrator. Evolution or future expansion of the database is also the responsibility of the database administrator. This is because the database administrator possesses structural, design and administrative knowledge of the database. Maintenance of the IT-Inventory application does not require any structural changes to the database, however, it may necessitate administrative changes. Evolution or future expansion of the database necessitates both structural and administrative changes. Structural changes made to the IT-Inventory application should be documented and included as part of the database design documentation.

Conclusion

By basing the IT-Inventory application on the database derived after normalizing the current database to the third normal form, the robustness of the application is greatly enhanced. Additionally, the software analysis process discussed in this report is effective for analyzing the IT-inventory application as it is a mix of software analysis techniques and database analysis techniques.

Works Cited

“Entity-relationship diagram.” About.com. n.p. (2011) Web.

Garcia_Molina, Hector, Ullman, D. Jeffrey, And Widom, Jennifer. Database System Implementation. Singapore: Addison Wesley Longman, 2000. Print.

Mendes Emilia. Normalization Rules: 1NF, 2NF, 3NF. Web.