Database Design and Development

Database Design and Development

Introduction

An integrated database system is vital and essential for proper, reliable and effective service delivery. It is important to embark on research to identify the most appropriate, suitable and feasible approach to attain this goal. Data normalization approach is one of the most feasible approach that one may utilize in designing an integrated database system for robust and better deliverables, thus the need to use the approach in redesigning Lawrence Historical Book database.

Design

a).ER diagram

b). Contribution of Normalization to ER diagram

Data normalization is one of the most crucial and requisite practice that a database designer needs to prioritize before tabling tools to design and model the database. In precision, database normalizations refer to the technique of organizing the contents of the schema for the purposes of transacting database and the whole data warehouse. Normalization is therefore analytical process that involves in-depth analysis of data elements available to identify various attributes of the data for the sole reason of establishing the relationship among the elements. To attain the ER diagram, I had to eliminate repeated grouping data into various new tables where there is no longer repetition just to ensure atomicity is achieved, thus  first normalization form. Secondly, I embarked on identifying the various data element thus has partial dependency on the primary key. Having identified them, I establish separate tables to ensure atomicity and independent dependency on the primary key, thus attainment of second normalization form.Finally, to achieve the third normalization form, I had to identify and remove transitive functional dependency while maintaining second normalization form. By achieving the third normalization form, I was able to have an absolute blueprint of how the database design model should look like, thus the above ER diagram is the product of this conceptual and analytical process. 

c). Data Dictionary

This section provides detailed and explicit information about the firms’ data element attributes, that is, standard definitions of various data attributes, their meanings and allowable values and even default values if any.All these information is presented in tabular format as shown below:

Table1:

Customer_detail

Name

Data Type

Field size

Required

Default Value

Description

customer_Id

varchar

10

Yes

None

Unique customer identifier(primary key)

customer_name

varchar

100

Yes

None

The particular customer name

customer_telephone

Int

100

None

Customer telephone number

credit_limit

Int

50

Yes

None

Contain the credit limitation for every customer

Address

Text

Unlimited

Yes

None

Contain the customers physical address in form of street description

Table 2:

Want_list

Name

Data_type

Field Size

Required

Default  Value

Description

want_list_id

Int

10

yes

None

Unique identifier for the every request a customer has made(primary key and its auto incremental)

customer_id

Varchar

10

yes

None

Customer unique identifier(reference key in this table)

by_title

Varchar

255

yes

none

Title of the book requested

by_subject

Varchar

100

none

Subject of the book requested

by_author

Varchar

100

none

Author of the book requested by the customer

Notes

Text

Unlimited

none

Sample notes about the book requested

Staff_id

Int

10

yes

none

Staff identifier in this table.

Table 3:

Purchases

Name

Data type

Field  Size

Required

Default Value

Description

book_id

Varchar

60

Yes

none

Unique book identifier purchased books

Customer_id

Varchar

50

Yes

none

Identifier for the particular customer who has bought the book

Book_name

Varchar

255

Yes

none

Name of the book in question

subject

Varchar

255

Yes

none

The book subject line

Author(s)

Varchar

255

Yes

none

The author of the book purchased.

Table4:

List_of_staff

Name

Data Type

Field size

Required

Default Value

Description

Staff_id

Int

10

yes

none

The unique staff identifier(primary key and auto increment)

Staff_name

Varchar

80

yes

none

The staff name

specialism

Text

unlimited

yes

none

Area of specialization

2. DATA AND QUERIES

a).The result set has 6 records

b). The result set has 13 records

c).The result set has 3 records

d). The result set has 5 records

3.DERIVED DATA

The allocation stipend (100 UK pound sterling) is a derived data from the fact that a staff is allocated a request therefore it is depend on every unique request identifier. Therefore to gather the data for reporting purposes, I will design a schema (table) and name it ‘staff_allocation’. This table will include the following attributes:’allocation_id’,’want_list_id’, ‘staff_id’, ‘allocation_wage’. The table will be updated anytime an ‘allocation’ functionality of the system in use is triggered, that is the schema will be updated concurrently with the ‘want_list ‘schema. To report on the indicators of interest, the personnel concerned will fetch the data from the schema using SQL query and present it into two dimension array format; column will be the unique staff_id and rows being unique allocation id all of which are extracted from the database.

Outline to implement this: More often the derivative data is extracted from multiple sources just in any case one would need the most predictive model, of which is the ultimate goal of many businesses. Therefore to achieve this; both the ‘want list’, ‘list_of_staff’ and ‘staff allocation’ schemas will be used as the source of our most descriptive data for predictive model. To obtain this data, a query script will be created and executed on the database server to obtain the data. However, to obtain the total salariesa mathematical formula will be used to accumulative calculate the gross salary.

4. Evaluation

Having normalized the database with sample data provided, am persuaded beyond any reasonable doubt that the company requirements are met since, by implementing this normalization technique; accuracy and efficiency of data is enhanced. Not forgetting the main goals of normalization; data integrity and referential integrity, the company will certainly enjoy the optimization of its database system. That is the proper database design, is the baseline for efficient, robust and well-performing system.

The initial design of the company database made milestone in attempt of meeting the data storage capability of the system. However, the performance of the system in terms of data integrity and referential integrity enhancement were still not yet established. And basically that is core objective of the normalization. Besides, enhancing integrity normalization will also eliminate redundancy, which would translate to numerous duplicates thus inaccuracy data and slow performance of the system through hindering deletion and update anomalies.

The general evaluation of the system after normalization, indicates better performance index, that is, everything perhaps is closer to perfection. First and foremost, deployment of normalization technique in database design is one of most essential factor and considerate factor that overly ensure data organization. When data is appropriately organized in the database system therefore the end product is efficiency in almost all aspect of system performance. Secondly, database design model without due normalization, results to data redundancy and inaccuracy, deletion and update anomaly. Therefore to ensure data integrity in the company, it was a prudent decision made to overly redesign the database. This has resulted to attaining data integrity henceforth. In addition, normalization as technique also enhances concrete relationship among the various entities involved in the design, thus ensure referential integrity. Moreover, by normalizing the database, a reliable ER diagram is established which will be useful for reusability in future in case of maintenance or expansion of the database system.

Conclusion

In a database driven application, one of the crucial requisite and most essential that the developers need to give as much concern as possible is designing of database. Designing a database is never an easy task, as many developers have perceived, however well designed database system, which is normalized appropriately is much effective and efficient since it informs the performance metric of the entire system. For ensuring appropriate data arrangement and organization, data integrity and referential integrity then database normalization is a must technique to be deployed in database system design.

Related Essay Examples

Leave a Comment