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

Determine the language that needs a user to specify the data, Determine the...

Determine the language that needs a user to specify the data to be retrieved with no specifying exactly how to get it is Ans: Non-Procedural DML

Explain the term high level dml, Explain the term High level DML? High...

Explain the term High level DML? High level DML A high-level or non-procedural DML permit the user to specify what data is needed without specifying how it is to be acquired.

Describe the association - design, Describe the Association: Design  ...

Describe the Association: Design  Before we describe association design let us define association "Association is set of links between two objects in the object model". It is

Shadow paging, Describe the shadow paging technique.

Describe the shadow paging technique.

In case of entity integrity the primary key may be declared, In case of ent...

In case of entity integrity, the primary key may be declared? In a case of entity integrity primary key may be not Null.

Conversion of er diagram to relational database, Conversion of entity sets:...

Conversion of entity sets: I)   For  every  strong  entity  type  E  in  the  ER  diagram,  we  make  a  relation  R containing all the easy attributes of E. The relation R pri

Illustrate system design of an object oriented approach, System Design: An ...

System Design: An Object Oriented Approach Any software systems always try to change and evolve as technology and business rules evolve. The development of information systems

Who will teach, Do you have experts of the dbms

Do you have experts of the dbms

Define clause in sql to specify query result must be sorted, The clause in ...

The clause in SQL which specifies that the query result should be sorted in ascending or descending order based upon the values of one or much more columns is Ans : Order by

Failure controlling methods, Failure Controlling Methods: Failures can be h...

Failure Controlling Methods: Failures can be handled using different recovery methods that are talk about later in the unit. But the first question is doing we really require recov

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