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

Explain the ansi sparc architecture, Explain the ANSI SPARC architecture ...

Explain the ANSI SPARC architecture The three-schema architecture is as well known as ANSI SPARC architecture. The aim of the three-schema architecture is to separate the user

Create a customer database and apply queries, For the question given below,...

For the question given below, you are required to submit design and implementation documentation as described over the page. Design, implement (in Java), test and document a set of

Back up files, what is the importance of back up files

what is the importance of back up files

Optimistic concurrency contents -write-set(t), write-set(T): Each data ite...

write-set(T): Each data items that are written by a transaction T

Create a database, The database will enable college administrators the abil...

The database will enable college administrators the ability to track students initial major. This will help college officials decide how best to prepare students for their goals an

Define signal, Define Signal. It allows methods to respond to events tr...

Define Signal. It allows methods to respond to events triggered by themselves or by other processes. Each signal corresponds to an exacting event. A signal is represented in Sy

What is called remapping of bad sectors, What is called remapping of bad se...

What is called remapping of bad sectors? If the controller detects that a sector is injured when the disk is initially formatted, or when an attempt is made to write the secto

Data model development and implementation, word limit no mentioned

word limit no mentioned

Describe integrity and security, Describe integrity and security? a)   ...

Describe integrity and security? a)      Domain constraint b)      Referential integrity A value that appears in one relation for a given set of attributes also appear fo

What is the meaning of data independence, What is the meaning of Data indep...

What is the meaning of Data independence? Data independence means a programs that are not dependent on the physical attributes of data and a programs that are not dependent on

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: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

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