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

List the string operations supported by sql, List the string operations sup...

List the string operations supported by SQL? 1) Pattern matching Operation 2) Concatenation 3) Extracting character strings 4) Converting among uppercase and lower cas

Create table and insert data manufacturer-dbms, Create a table according to...

Create a table according to given data:  AmbulanceDriver(Driver_Number#, Name, Address, Birth_Date) AmbulanceDriverTeam(Team_Number#, Driver_Number#, Join_Date, Lea

Attributes, Attributes and its types explain?

Attributes and its types explain?

Explain strict two-phase locking, Explain Strict Two-Phase locking A tr...

Explain Strict Two-Phase locking A transaction T does not release any type of its exclusive (X) locks until that transaction commits or aborts. In this method no other transact

What is system catalog or catalog relation, What is system catalog or catal...

What is system catalog or catalog relation? How is better known as? A RDBMS maintains a explanation of all the data that it contains, information about every relation and index

What are deadlocks, What are deadlocks?  How can they be avoided?  Ans:...

What are deadlocks?  How can they be avoided?  Ans: Deadlock considers to a particular condition when two or more processes are every waiting for another to release a resource,

Explain dml with some examples, Explain DML with some examples? DML - D...

Explain DML with some examples? DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples: • SELECT - retrieve data from the a da

Explain the check clause, Explain the Check Clause The CHECK clause is ...

Explain the Check Clause The CHECK clause is employed to at the end of a CREATE TABLE statement specify table constraints. This is known as table-based constraint because it ap

How nulls are treated in comparison operator, How nulls are treated in comp...

How nulls are treated in comparison operator? Along with comparison (relational) operators, the null values are ignored as we cannot derive the relation along with the given op

Define the notation for classes and atrributes, Define the notation for cla...

Define the notation for classes and atrributes The notation for class, attribute, and operations are shown below:     Figure: Class with attributes and operations A

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