sql database1

5. 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 (20 marks): a. Meter readers can only read a maximum of 5 meters in any given calendar month (please note this constraint is modified from Assignment 1 so that you will be able to test it). b. Customers who do not pay their bill within 60 days will have their water supply cut off. c. 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. 6. Provide all the SQL statements that are required for the following transactions to be executed (15 marks): Transaction 01: - A residential customer (Bob Builder) of 53 Drip Drive, Dripwater (post code: 1267) has an account with the water company at the same address. The meter was installed on 30th October 2016 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, 2017, 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, and at this reading, it was 123683 kL. The bill was created and sent on the same day. Transaction 03: - On 2nd August 2017, Bob Builder settled the account in full for consumption at 53 Drip Drive, Dripwater using PayPal. 7. Provide VIEWS for the following (views should be named as ViewA, ViewB etc.) (20 marks): ViewA: • The number of meters read 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). ViewB: • List the consumption history of the service address, 53 Drip Drive, Dripwater, 1267, from the beginning of 2017 (you will have to make up the readings yourself) until the September reading. ViewC: • 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). ViewD: • Calculate the current bills for all non-residential customers for September 2017. (Your sample data must include at least one non-residential customer who has a bill for September 2017). -