IS222 – Database Management Systems Assignment 3 Semester 1 2020

 

Looking for IS222 – Database Management Systems Assignment 3 Answers? assignmenthelpaus.io has a team of more than MBA/PhD expert writers that make future of million students from Australia. Be it your online assignment help, No1 assignment writers or assignment writing services Australia, assignmenthelpaus.io is the one-stop solution for all.

 

order-now

 

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.

 

Preliminary Work

 

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 LM $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 LM $1,000.00 14.64
WSC 19/20 4 Construct new crossing 3(c) Construct Flap Gate LM $1,000.00 6.1
WSC 19/20 5 Upgrade Culvert Crossing 1(c) Upgrade culvert crossing LM $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

 

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

 

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

(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

(ii). Show each step taken that is, movement from one normal form to

(iii). Show the final normalized

 

(e).  Business rules for the new

(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

 

(f).  ERD for the new

(i). Provide a table that explains the reason/purpose of each 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.

 

 

Looking for Homework Answers? Improve your academic grade with Assignment Help AUS. World’s No.1 Assignment Answers Provide in Australia, UK, USA, Singapore and Many Countries at students friendly price.