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 Page 2 of 5 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 the 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

There are serious redundancy problems with the above database. Normalize the above database to 3NF.

Questions:

1. Discuss the business processes explained in the case study.

2. Apply the normalization process to normalize the database to 3NF, show each step taken that is, movement from one normal form to another and show the final normalized database.

3. Business rules for the new database - 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.

4. ERD for the new database - 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.

This is all the information there is

There is no sum involved in this question

Contract Contract_Number Description Division District Completed Claims Contract_Number Claim_Number * Bill_Number Bill_Description * Item_Number Item_Description Unit Rate 00 Items 7 Contract_Number Bill_Number Bill_Description 7 item_Number Item_Description Unit Rate Total Quantity * Place Quantity Date Claims Contract Number r r 1 $0.50 3 3 Claim Bill_N Numbe umbe Bill_Description 1 1 Desilting 1 Desilting 1 1 Desilting 1 1 Desilting 1 1 Desilting 1 1 Desilting 1 3 Gravelling 1 3 Gravelling 1 3 Gravelling 1 Gravelling 1 Gravelling 1 4 Construct new crossing 1 5 Upgrade Culvert Crossing 2 1 Desilting 2 1 Desilting 2 1 Desilting 2 1 Desilting 2 1 Desilting 2 2 Excavate Drain And Level Spoil On Site 2 2 Excavate Drain And Level Spoil On Site 2 4 Construct new crossing 2 5 Upgrade Culvert Crossing 2 Stone Pitching and General Excavation 2 Stone Pitching and General Excavation 3 2 Excavate Drain And Level Spoil On Site 3 2 Excavate Drain And Level Spoil On Site 3 5 Upgrade Culvert Crossing 3 7 Stone Pitching and General Excavation 3 Stone Pitching and General Excavation 4 2 Excavate Drain And Level Spoil On Site 4 2 Excavate Drain And Level Spoil On Site 5 2 Excavate Drain And Level Spoil On Site 5 2 Excavate Drain And Level Spoil On Site 5 2 Excavate Drain And Level Spoil On Site 5 5 Upgrade Culvert Crossing 1 6 Upgrade Doors And Components 4 6 Upgrade Doors And Components WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 20/20 WSC 20/20 Item_N umber Item_Description Unit Rate Place 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Cautata 1 Desilt and clean drain from one side and level spoil on site LM Dravo 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Koronivia West 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Lakena/Manoca/Buiduna 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Mokani 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Waikete 4 Gravelling M3 $15.00 Cautata 4 Gravelling M3 $15.00 Dravo 4 Gravelling | $15.00 Naila 4 Gravelling M3 $15.00 Naitalasese 4 Gravelling M3 $15.00 Waivou 1(b) Construct New Crossing LM $1,000.00 Naila 1(c) Upgrade culvert crossing LM $500.00 Buiduna 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Lakena/Manoca/Waituri 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Qaraniki/Naselai 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Qaraniki/Visama 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Qumqum 1 Desilt and clean drain from one side and level spoil on site LM $0.50 1 Excavate Drain And Level Spoil On Site IM3 $1.50 Qaraniki/Naselai 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 3(0) Construct Flap Gate LM $1,000.00 Noco 1(c) Upgrade culvert crossing LM $500.00 Naitasiri 3 Stone Pitching M3 $50.00 Waituri 4 General Excavation M3 $1.00 Waituri 1 Excavate Drain And Level Spoil On Site M3 $1.50 Noco 1 Excavate Drain And Level Spoil On Site M3 $1.50 Waituri 1(c) Upgrade culvert crossing LM $500.00 Noco 3 Stone Pitching M3 $50.00 Noco 4 General Excavation | $1.00 Waituri 1 Excavate Drain And Level Spoil On Site M3 $1.50 Naila 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 1 Excavate Drain And Level Spoil On Site M3 $1.50 Buiduna 1 Excavate Drain And Level Spoil On Site [3 $1.50 Naila 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 1(c) Upgrade culvert crossing LM $500.00 Naila 1(iii) Upgrade of Doors and Components SET $1,000.00 Dravo 1(iii) Upgrade of Doors and Components SET $1,000.00 Vuci Vuci Quantity Date 1006 2/1/20201 247 2/1/2020 38 2/1/2020 212 2/1/2020 1500 2/1/2020 2866 2/1/2020 50 2/1/2020 15 2/1/2020 44.5 2/1/2020 89 2/1/2020 29.6 2/1/2020 2.88 2/1/2020 2.88 2/1/2020 3662 4/18/2020 3000 4/18/2020 3321 4/18/2020 1556 4/18/2020 4568 4/18/2020 2268.33 4/18/2020 257.33 4/18/2020 3.1 4/18/2020 2.66 4/18/2020 5.68 4/18/2020 155 4/18/2020 300 4/19/2020 300 4/19/2020 2.26 4/20/2020 5 4/20/2020 20 4/20/2020 20 4/21/2020 365 4/21/2020 562 4/22/2020 1 4/22/2020 10 4/22/2020 2.55 4/22/2020 2 4/24/2020 1 4/25/2020 7 7 7 Items Contract_N umber WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 20/20 Bill_N umber Bill_Description 1 Desilting 2 Excavate Drain And Level Spoil On Site 3 Gravelling 4 Construct new crossing 4 Construct new crossing 5 Upgrade Culvert Crossing 7 Stone Pitching and General Excavation 7 Stone Pitching and General Excavation 6 Upgrade Doors And Components Item_N umber Item_Description 1 Desilt and clean drain from one side and level spoil on site 1 Excavate Drain And Level Spoil On Site 4 Gravelling 1(b) Construct New Crossing 3(c) Construct Flap Gate 1(c) Upgrade culvert crossing 3 Stone Pitching 4 General Excavation 1(iii) Upgrade of Doors and Components Total_Q uantity Unit Rate LM $0.50 180000 M3 $1.50 10000 M3 $15.00 3000 LM $1,000.00 14.64 LM $1,000.00 6.1 LM $500.00 14.64 M3 $50.00 25 M3 $1.00 30 SET $1,000.00 1 Contract Contract_Number Description Division WSC 19/20 Maintenance of completed drainage schemes Central WSC 20/20 Maintenance of completed drainage schemes Central District Nausori Suva Completed No No Contract Contract_Number Description Division District Completed Claims Contract_Number Claim_Number * Bill_Number Bill_Description * Item_Number Item_Description Unit Rate 00 Items 7 Contract_Number Bill_Number Bill_Description 7 item_Number Item_Description Unit Rate Total Quantity * Place Quantity Date Claims Contract Number r r 1 $0.50 3 3 Claim Bill_N Numbe umbe Bill_Description 1 1 Desilting 1 Desilting 1 1 Desilting 1 1 Desilting 1 1 Desilting 1 1 Desilting 1 3 Gravelling 1 3 Gravelling 1 3 Gravelling 1 Gravelling 1 Gravelling 1 4 Construct new crossing 1 5 Upgrade Culvert Crossing 2 1 Desilting 2 1 Desilting 2 1 Desilting 2 1 Desilting 2 1 Desilting 2 2 Excavate Drain And Level Spoil On Site 2 2 Excavate Drain And Level Spoil On Site 2 4 Construct new crossing 2 5 Upgrade Culvert Crossing 2 Stone Pitching and General Excavation 2 Stone Pitching and General Excavation 3 2 Excavate Drain And Level Spoil On Site 3 2 Excavate Drain And Level Spoil On Site 3 5 Upgrade Culvert Crossing 3 7 Stone Pitching and General Excavation 3 Stone Pitching and General Excavation 4 2 Excavate Drain And Level Spoil On Site 4 2 Excavate Drain And Level Spoil On Site 5 2 Excavate Drain And Level Spoil On Site 5 2 Excavate Drain And Level Spoil On Site 5 2 Excavate Drain And Level Spoil On Site 5 5 Upgrade Culvert Crossing 1 6 Upgrade Doors And Components 4 6 Upgrade Doors And Components WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 20/20 WSC 20/20 Item_N umber Item_Description Unit Rate Place 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Cautata 1 Desilt and clean drain from one side and level spoil on site LM Dravo 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Koronivia West 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Lakena/Manoca/Buiduna 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Mokani 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Waikete 4 Gravelling M3 $15.00 Cautata 4 Gravelling M3 $15.00 Dravo 4 Gravelling | $15.00 Naila 4 Gravelling M3 $15.00 Naitalasese 4 Gravelling M3 $15.00 Waivou 1(b) Construct New Crossing LM $1,000.00 Naila 1(c) Upgrade culvert crossing LM $500.00 Buiduna 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Lakena/Manoca/Waituri 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Qaraniki/Naselai 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Qaraniki/Visama 1 Desilt and clean drain from one side and level spoil on site LM $0.50 Qumqum 1 Desilt and clean drain from one side and level spoil on site LM $0.50 1 Excavate Drain And Level Spoil On Site IM3 $1.50 Qaraniki/Naselai 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 3(0) Construct Flap Gate LM $1,000.00 Noco 1(c) Upgrade culvert crossing LM $500.00 Naitasiri 3 Stone Pitching M3 $50.00 Waituri 4 General Excavation M3 $1.00 Waituri 1 Excavate Drain And Level Spoil On Site M3 $1.50 Noco 1 Excavate Drain And Level Spoil On Site M3 $1.50 Waituri 1(c) Upgrade culvert crossing LM $500.00 Noco 3 Stone Pitching M3 $50.00 Noco 4 General Excavation | $1.00 Waituri 1 Excavate Drain And Level Spoil On Site M3 $1.50 Naila 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 1 Excavate Drain And Level Spoil On Site M3 $1.50 Buiduna 1 Excavate Drain And Level Spoil On Site [3 $1.50 Naila 1 Excavate Drain And Level Spoil On Site M3 $1.50 Vuci 1(c) Upgrade culvert crossing LM $500.00 Naila 1(iii) Upgrade of Doors and Components SET $1,000.00 Dravo 1(iii) Upgrade of Doors and Components SET $1,000.00 Vuci Vuci Quantity Date 1006 2/1/20201 247 2/1/2020 38 2/1/2020 212 2/1/2020 1500 2/1/2020 2866 2/1/2020 50 2/1/2020 15 2/1/2020 44.5 2/1/2020 89 2/1/2020 29.6 2/1/2020 2.88 2/1/2020 2.88 2/1/2020 3662 4/18/2020 3000 4/18/2020 3321 4/18/2020 1556 4/18/2020 4568 4/18/2020 2268.33 4/18/2020 257.33 4/18/2020 3.1 4/18/2020 2.66 4/18/2020 5.68 4/18/2020 155 4/18/2020 300 4/19/2020 300 4/19/2020 2.26 4/20/2020 5 4/20/2020 20 4/20/2020 20 4/21/2020 365 4/21/2020 562 4/22/2020 1 4/22/2020 10 4/22/2020 2.55 4/22/2020 2 4/24/2020 1 4/25/2020 7 7 7 Items Contract_N umber WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 19/20 WSC 20/20 Bill_N umber Bill_Description 1 Desilting 2 Excavate Drain And Level Spoil On Site 3 Gravelling 4 Construct new crossing 4 Construct new crossing 5 Upgrade Culvert Crossing 7 Stone Pitching and General Excavation 7 Stone Pitching and General Excavation 6 Upgrade Doors And Components Item_N umber Item_Description 1 Desilt and clean drain from one side and level spoil on site 1 Excavate Drain And Level Spoil On Site 4 Gravelling 1(b) Construct New Crossing 3(c) Construct Flap Gate 1(c) Upgrade culvert crossing 3 Stone Pitching 4 General Excavation 1(iii) Upgrade of Doors and Components Total_Q uantity Unit Rate LM $0.50 180000 M3 $1.50 10000 M3 $15.00 3000 LM $1,000.00 14.64 LM $1,000.00 6.1 LM $500.00 14.64 M3 $50.00 25 M3 $1.00 30 SET $1,000.00 1 Contract Contract_Number Description Division WSC 19/20 Maintenance of completed drainage schemes Central WSC 20/20 Maintenance of completed drainage schemes Central District Nausori Suva Completed No No