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

Draw an e_r model in oracle, Draw an E_R Model for the following: An or...

Draw an E_R Model for the following: An organization uses number of items of a equipment to generates goods. Each item is at one LOCATION, of one TYPE and has a DETAILED_DISCRI

OLAP CUBES, 1) Define a job scheduling strategy that will meet business req...

1) Define a job scheduling strategy that will meet business requirement of reporting availability by 6am CST for the following cubes? Show the job scheduling dependencies in a pict

Compare the nested-loop join and block-nested loop join, Compare the Nested...

Compare the Nested-loop join and block-nested loop join Ans: Nested  loop join - It is costly as worst case cost, no. of block accesses needed is n r * b s + b r   where b

Define the term database, QUESTION (a)(i) Define the term database. ...

QUESTION (a)(i) Define the term database. (ii) How is it different from a database management system? (b) What is the difference between an entity and an attribute? (c

Explanation on data sharing, Explanation on Data Sharing?

Explanation on Data Sharing?

E r diagram, how to make an e r diagram?

how to make an e r diagram?

What are the concurrency issues, Concurrency issues Data integrity...

Concurrency issues Data integrity: Threads accessing same object need to be synchronized, such as: banking account. Deadlock: One or more threads in system are perman

How can you terminate the mysql server, How can you terminate the MySQL Ser...

How can you terminate the MySQL Server? Typing the following in Command prompt will shut down the server completely as root user % mysqladmin -u root -p shutdown

How to define a structure with tag name car_table, QUESTION (a) Conside...

QUESTION (a) Consider a car database consisting of car model, car make, car origin, Engine capacity, color, cost.

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

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