ICT285: Your Task is to Develop a Data Model for the Dripping Wet Water Company (DWWC): Databases Assignment, MU, Singapore

Databases

1. Create and submit an Entity Relationship Diagram (ERD) for this database base on the scenario

HINT: use crow’s feet notation –create at least 6 entities – Customer, Service Address, Meter, Reading, Meter Reader, and Bill. Indicate title, legend, and if appliable, add in assumptions as well for the ERD.

Your task is to develop a data model for the Dripping Wet Water Company (DWWC). DWWC is the sole supplier of water to the citizens and businesses of the town of RandomTown.

Each of DWWC’s customers is classified as being either residential or non-residential. Each customer has at least one water meter that measures the water consumed by the customer at a particular address. Water meters must be replaced when they have been in use for 5 years.

All customers are billed monthly. Meter readers, who are employed by DWWC, are paid per meter they read. They are each allocated up to 1000 meters to read each month.

Once the reader has read the meter, the reading is recorded and the bill for that customer is calculated based on the consumption since the last reading. For example, if the last meter reading was 123580 kilolitres (kl) and the next reading was 123683kl, the consumption for that period would be 103kl.

Residential customers’ bills are calculated as follows: for each residence, the customer is billed a flat rate of $25 supply charge. They are then billed a further 24c for each kI consumed since the last reading. There are no GST or other taxes imposed.

Non-residential customers’ bills are calculated as follows: there is no supply charge. They are charged only for consumption since the last reading. They are charged 24c for the first 100 kI, 30c for the second 100 kl, and 50c for each kl in excess of 200kl.

All customers are given 30 days from the billing date to pay their account. They can pay by cash, Paypal, or EFT. Customers who do not pay within 30 days will have an accounting charge of $5 added to the bill. After 45 days a further $10 accounting charge is added. At 60 days, the customer’s water supply is cut off. Once a customer has had their supply cut off, the person or business responsible for the bill will not be able to create a new account at a different address until their arrears have been cleared.

Each customer has the right to request a report that details the historical consumption of water at the address of their account, whether they were the customer at the time or not.

2. A one or two paragraph explanation as to the changes you have made to the ERD on the basis of your feedback from Assignment 1 or as a result of having to support the transactions and views below.

3. Create a data dictionary that lists at least each of the tables, the columns, their domains, and any other constraints that apply.

4. Provide an explanation (and any code you used) as to how you implemented (or, if you were not able to successfully implement, how you would have implemented) the following constraints:

• Meter readers can only read a maximum of 5 meters in any given calendar month
• Customers who do not pay their bill within 60 days will their water supply cut off.
• Once a customer has had their supply cut off, the person or business responsible for the bill will not be able to create a new account at a different service address until such time as their arrears have been cleared.

5. Provide all the SQL statements that are required for the following transactions to be executed:

Transaction 01:

• A residential customer (Bob Builder) of 53 Drip Drive, Dripwater (postcode: 1267) has an account with the water company at the same address. The meter was installed on 30^th October 2019 and has the serial number DDWC4763. This customer does not have any outstanding balance for the service at this address.

Transaction 02:

• On 23rd July 2020, the meter at 53 Drip Drive, Dripwater was read by Karen Karpenter (a meter reader). The reading the last time this meter was read was 123580 kL, at this reading, it was 123683 kL. The bill was created and sent on the same day.

Transaction 03:

• On 2^nd August 2020, Bob Builder settled the account in full for consumption at 53 Drip Drive, Dripwater using PayPal.

6. Provide VIEWS for the following (views should be named as ViewA, ViewB, etc):

View A:

• The number of meters reads in the current month by each of the meter readers (this view should be able to be used at any time without having to be changed to accommodate the date).

View B:

• List the consumption history of the service address, 53 Drip Drive, Dripwater, 1267, from the beginning of 2020 (you will have to make up the readings yourself) until the September reading.

View C:

• List the meters that are due for replacement in the next six months (this view should be able to work at any time in the future without needing to be changed to accommodate the date).

View D:

• Calculate the current bills for all non-residential customers for September 2020. (Your sample data must include at least one non-residential customer who has a bill for September 2020.