User Account

All Pages

Normalization, Database Management System

Assignment Help:
Introduction
In large projects, we can have many different companies working together. Generally, the project or
contract is awarded to one company (Principal Contractor), which then hires many other companies,
called subcontractors, to complete specific tasks.
In this case study, we will attempt to design a database that will be used by the sub-contractors when
making their claims. In particular, we will look at designing a database to be used by a sub-contractor
in the business of drainage of rural areas. In an attempt to make sure that the drainage of rural areas
is up to standard and safe, the government gives out contracts to companies that do earthworks.
These companies are responsible for cleaning the old drains and digging of new drains. In addition to
this, the contractors are required to build and maintain structures, such as roads, flood (flap) gates,
and sea walls.
Each contract is typically divided into quarters. Each quarter lasts three months with the first quarter
starting in January. The contractors will be given a set of jobs, called Bills, to do in each quarter after
a contractor finishes some of the jobs he is required to make a claim. This claim would show how
much work was done and the total amount spent by the contractor. The claim is printed out, and a
hardcopy is submitted to the Principal Contractor. In our case, the Principal Contractor could either
be a government department or a private company. To understand each claim, we need to look at
the structure of a contract.
Contract
A contract has a number, description, division (northern, eastern, etc.), and district (Navua, Rewa,
etc.). Each contract has a set of Bills. A Bill is basically the type of job. For example, gravelling of roads
is one type of job, and digging of new drains is another. Therefore, we will have a Bill for Gravelling
and another Bill for Digging New Drains. Each Bill has a number and a description. Furthermore, each
Bill is divided into Items. For example, assume that BILL 1 is “Gravelling of
Roads”. Under BILL 1 there could be ITEM 1 – Gravelling of new road and ITEM 2 –
Gravelling of the existing road. Each Item has a number, description, unit of measurement [LM (linear
meters for distance), M3 (Cubic Meters for volume), etc.], Rate (in Fiji dollars; this is rate per unit),
and Quantity (total contract quantity that must be completed by the contractor). Each contract has
its own set of Bills and Items.
Preliminary Work
A database designer was hired to design a database for the above process. The database designer
did not produce any documents but implemented some tables in an MS Access. The schema of the
database is shown below, along with a sample of the data populated in the tables. Study
these tables and answer the questions that follow
Page 2 of 5
Claims
Contract_
Number
Claim_
Numbe
r
Bill_N
umbe
r Bill_Description
Item_N
umber Item_Description Unit Rate Place Quantity Date
WSC 19/20 1 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Cautata 1006 2/1/2020
WSC 19/20 1 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Dravo 247 2/1/2020
WSC 19/20 1 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Koronivia West 38 2/1/2020
WSC 19/20 1 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Lakena/Manoca/Buiduna 212 2/1/2020
WSC 19/20 1 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Mokani 1500 2/1/2020
WSC 19/20 1 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Waikete 2866 2/1/2020
WSC 19/20 1 3 Gravelling 4 Gravelling M3 $15.00 Cautata 50 2/1/2020
WSC 19/20 1 3 Gravelling 4 Gravelling M3 $15.00 Dravo 15 2/1/2020
WSC 19/20 1 3 Gravelling 4 Gravelling M3 $15.00 Naila 44.5 2/1/2020
WSC 19/20 1 3 Gravelling 4 Gravelling M3 $15.00 Naitalasese 89 2/1/2020
WSC 19/20 1 3 Gravelling 4 Gravelling M3 $15.00 Waivou 29.6 2/1/2020
WSC 19/20 1 4 Construct new crossing 1(b) Construct New Crossing L M $1,000.00 Naila 2.88 2/1/2020
WSC 19/20 1 5 Upgrade Culvert Crossing 1(c) Upgrade culvert crossing L M $500.00 Buiduna 2.88 2/1/2020
WSC 19/20 2 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Lakena/Manoca/Waituri 3662 4/18/2020
WSC 19/20 2 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Qaraniki/Naselai 3000 4/18/2020
WSC 19/20 2 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Qaraniki/Visama 3321 4/18/2020
WSC 19/20 2 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Qumqum 1556 4/18/2020
WSC 19/20 2 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 Vuci 4568 4/18/2020
WSC 19/20 2 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Qaraniki/Naselai 2268.33 4/18/2020
WSC 19/20 2 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 257.33 4/18/2020
WSC 19/20 2 4 Construct new crossing 3(c) Construct Flap Gate L M $1,000.00 Noco 3.1 4/18/2020
WSC 19/20 2 5 Upgrade Culvert Crossing 1(c) Upgrade culvert crossing L M $500.00 Naitasiri 2.66 4/18/2020
WSC 19/20 2 7 Stone Pitching and General Excavation 3 Stone Pitching M3 $50.00 Waituri 5.68 4/18/2020
WSC 19/20 2 7 Stone Pitching and General Excavation 4 General Excavation M3 $1.00 Waituri 155 4/18/2020
WSC 19/20 3 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Noco 300 4/19/2020
WSC 19/20 3 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Waituri 300 4/19/2020
WSC 19/20 3 5 Upgrade Culvert Crossing 1(c) Upgrade culvert crossing L M $500.00 Noco 2.26 4/20/2020
WSC 19/20 3 7 Stone Pitching and General Excavation 3 Stone Pitching M3 $50.00 Noco 5 4/20/2020
WSC 19/20 3 7 Stone Pitching and General Excavation 4 General Excavation M3 $1.00 Waituri 20 4/20/2020
WSC 19/20 4 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Naila 20 4/21/2020
WSC 19/20 4 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 365 4/21/2020
WSC 19/20 5 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Buiduna 562 4/22/2020
WSC 19/20 5 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Naila 1 4/22/2020
WSC 19/20 5 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 10 4/22/2020
WSC 19/20 5 5 Upgrade Culvert Crossing 1(c) Upgrade culvert crossing L M $500.00 Naila 2.55 4/22/2020
WSC 20/20 1 6 Upgrade Doors And Components 1(iii) Upgrade of Doors and Components SET $1,000.00 Dravo 2 4/24/2020
WSC 20/20 4 6 Upgrade Doors And Components 1(iii) Upgrade of Doors and Components SET $1,000.00 Vuci 1 4/25/2020
Page 3 of 5
Contract
Contract_Number Description Division District Completed
WSC 19/20 Maintenance of completed drainage schemes Central Nausori No
WSC 20/20 Maintenance of completed drainage schemes Central Suva No
Items
Contract_N
umber
Bill_N
umber Bill_Description
Item_N
umber
Item_Description Unit Rate
Total_Q
uantity
WSC 19/20 1 Desilting 1 Desilt and clean drain from one side and level spoil on site L M $0.50 180000
WSC 19/20 2 Excavate Drain And Level Spoil On Site 1 Excavate Drain And Level Spoil On Site M3 $1.50 10000
WSC 19/20 3 Gravelling 4 Gravelling M3 $15.00 3000
WSC 19/20 4 Construct new crossing 1(b) Construct New Crossing L M $1,000.00 14.64
WSC 19/20 4 Construct new crossing 3(c) Construct Flap Gate L M $1,000.00 6.1
WSC 19/20 5 Upgrade Culvert Crossing 1(c) Upgrade culvert crossing L M $500.00 14.64
WSC 19/20 7 Stone Pitching and General Excavation 3 Stone Pitching M3 $50.00 25
WSC 19/20 7 Stone Pitching and General Excavation 4 General Excavation M3 $1.00 30
WSC 20/20 6 Upgrade Doors And Components 1(iii) Upgrade of Doors and Components SET $1,000.00 1
Page 4 of 5
What you need to do
__________________________________________________________________________
There are serious redundancy problems with the above database. Normalize the above database to
3NF.
1. After normalizing the database, create a Database Design Report. Include the following in the
report:
a. Cover page
i. Write Student ID number, name.
b. Table of Contents
c. Introduction
i. Discuss the business processes explained in the case study.
ii. Discuss the problems of the existing database design and the consequences
of the design.
iii. Discuss ways of improving the database design
d. Normalization Process
i. Apply the normalization process to normalize the database to 3NF.
ii. Show each step taken that is, movement from one normal form to another.
iii. Show the final normalized database.
Page 5 of 5
e. Business rules for the new database.
i. Briefly explain each business rule. The explanation will help the reader
understand the reason for having the business rule in the report and how it
relates to the case study.
f. ERD for the new database.
i. Provide a table that explains the reason/purpose of each table. You can explain
the kind of data that will be stored in the table at a very high level, that is, do
not list the attributes.
g. Conclusion
i. explain the achievements and challenges that you faced when doing the
assignment.

Related Discussions:- Normalization

Develop network database ipad app, I want an iPad app that is a simple 1 pa...

I want an iPad app that is a simple 1 page form to complete with text. The Objective is to allow a few dozen people of the same organization (they're all on iPad) to open the app,

Physical datastructure, what is physical data structure

what is physical data structure

Managing databases, Code an Oracle Database trigger to enforce the constrai...

Code an Oracle Database trigger to enforce the constraint that an employee can never change his or her department.

What are the objectives of advance object design, What are the Objectives o...

What are the Objectives of Advance object design After this unit, you shall be able to: combine three OOAD models to obtain operations on the classes; develop algo

Er diagram for accounts, how draw er diagram

how draw er diagram

What is an object id, What is an object ID? Each class-derived table ha...

What is an object ID? Each class-derived table has an ID for primary key, one or more object IDs form primary key for association derived tables. An object ID is equivalent dat

Define the terms - physical schema and logical schema, Define the terms  1)...

Define the terms  1) physical schema 2) logical schema.  Physical schema:  The physical schema explains the database design at the physical level, which  is  the  lowest  lev

Dependency Diagram, A Structure of a dependency diagram

A Structure of a dependency diagram

Which of the operations constitute a basic set of operations, Which of the ...

Which of the operations constitute a basic set of operations for manipulating relational data? Relational algebra operations constitute a basic set of operations for manipulati

Define average seek time, Define average seek time. The average seek ti...

Define average seek time. The average seek time is the average of the seek times, calculated over a sequence of random requests.

Write Your Message!

Captcha
Order Assignment

Featured Services

Order Now

Popular Subjects

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd