You have been asked to develop a data model for the Drip Drip Water Company (DDWC). DDWC is the sole supplier of water to the citizens and businesses of the town of Drip Drip.
Each of DDWC’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 DDWC, 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 kl 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 the consumption since the last reading. They are charged 24c for the first 100 kl, 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 the 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.
What you have to do:
- Create an entity-relationship diagram showing the data requirements of the system. Your ERD should be able to be implemented in a relational DBMS. You should use theCrow’s Foot Notation, and should include a legend to explain the notation. You should include attributes in the ERD. The use of a drawing tool such as Visio will make this task easier. However, whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tools you have used.
- List and explain any assumptions you have made in creating the data model.
here is a series of questions that you can use to determine how well your ERD fulfills the requirements.
- LEGEND
- Is there one?
- Does it include all symbols used in the diagram?
- CUSTOMER
- Is it clear whether the customer is residential or non-residential?
- Is it possible for the different types to have different attributes
- Does the diagram clearly show that a customer has to be either residential or non-residential?
iii. Better to have Customer with Residential/Non-residential sub-types as there will be different attributes for both. The relationships Customer has with other entities are the same for both, and so the relationship should be with Customer and not with the sub-types.
- Can the customer have more than one service address?
- Must be able to tell if the customer has “bad” status because of late or non-payment.
- SERVICE ADDRESS
- Can the Service Address have more than one customer over the lifetime of the databse?
- Can the Service Address have more than one meter over the lifetime of the database?
- Is it clear that the Service Address can only have one meter at any one time?
- METER
- In order to be able to retire the meter after 5 years of service, is it clear when the meter came into service?
- In this system, a meter will only ever be installed at a single service address. Once its five years of service has elapsed, it will be retired and not reused.
- READER
- It is a requirement of the system that it is clear which reader made which reading.
- The system needs to be able to determine how many meters a reader has read in a month.
- Can this be calculated? It should not be listed in the ERD as an attribute
- There is no need for any further detail on the reader as payment etc is outside the scope of this assignment.
- READING
- A READER will make many READINGS
- A READING will apply to one and only one METER
- Need to know the date the reading was made and what the reading was.
- Storingpreviousreadings is poor design as it includes redundant data that can be found from existing data
- Similarly, consumption should not be an attribute as it is calculable
- BILL
- Best for Bill to be related to both READING and CUSTOMER
- In this way, the period and consumption can be calculated
- It is also possible to uniquely determine who the customer responsible at the time the Bill was raised was.
iii. Bill will include attributes that allow us to determine the following:
- Total amount owing
- Although this is calculated, if the rates change, there remains a record
- Paid and payment method
- Date the bill was raised in order to determine whether or not the bill is overdue.
- Some have broken this down to having a separate Payment entity. This is fine.
</pclass=”msonormal”>
