Reference no: EM132694820

Database design and Implementation

Introduction:

In this project, you will design and implement a database for keeping track of customer information for a company called XYZ-Utility that provide utility service to customer at their addresses.

You will first design an ERD diagram for this company's database application. Then, you will need to map the ERD into a relational database schema and implement it on MySQL DBMS, load some data into the created database, and create some SQL queries to update and query the data.

The system description and requirements are given in the following section

System Description and requirements:

XYZ-Utility Company which provides Utility services to Customers at their addresses (You may think of Gas service as an example). Customers subscribe for the service by signing a contract with the company which contains the activation date, the meter number, the address, and the billing rate. The company bills the customer according to their consumption which is measured by Meter installed at their address. Each Meter is assigned a unique number, and the reading of the meter is recorded by a company employee who is assigned the reading task. Each Reader is assigned a list of addresses and Meter numbers to read on specific date. The Reader record the Meter reading and the date it was taken. Then customer consumption is calculated by subtracting the previous reading from the current reading. The cost is calculated by multiplying the customer consumption by the unit rate (specified in the contract). The customers are billed after each reading and given 30 days to pay their bill. A customer may have one or more addresses and consequently may have Multiple Contracts. One Meter is installed at each address. Each Contract is billed separately if the customer has multiple addresses (contracts). Customer can activate or deactivate the service at his address at any date, and these dates should be recorded. Meters are fixed at the installation addresses; however customers can change their addresses. Consequently, the same meter/address can be

assigned to multiple customers but not at the same time period, so the activation and deactivation dates are important to determine which customer is using which meter at any given time.

Currently XYZ-Utility uses traditional file-based system for its data where it keeps track of the Customers information, Meter readings, Bills, Payments, and Employee Reading assignments . The attached excel file has a sample of this file based system data. You need this data to test your database

You can see the current file-based system has all different kinds of update, delete, insert, issues. In addition, answering questions about customers, due dates, bills, and reading assignments is difficult.

Tasks:

Task 1-Your task is to use the description above and the given sample data excel file to design a database system for XYZ-Utility Company to avoid all possible update, insert and query issues in their current file- based system.

To prove the usefulness of your database-based system you should insert the sample data in your tables, add users with different roles (Manager, Reader, Customer service) to your database and assign them privileges according to their job needs (for example: Reader person should only see the list of addresses he needs to visit in order read their Meter, and not allowed to see customer names or other information. Customer service can see customer information and Bills, but not the employee who is assigned to read the customer meter. Manager can see all information and can assign/change the assignment of Meter readings to Reading employees).
Note that the sample data used in the file-based system are not normalized. Also, feel free to add some other attributes to any of the entities if you think it will be useful.

-Task 1 deliverables:
a- PDF file: Detailed Crow's foot ERD for your database system design
b- SQL script file: SQL DDL and Insert script to create the database you designed, with Normalized Tables and insert the sample data in the database
- Note that your design needs to avoid the duplication/redundancy of the data entries
c- SQL script file : SQL DCL to add the three types of users and assign them accesses as explained in the task description

Task 2-Write SQL queries to update the data and answer the following questions:

2.1 Update Queries: Write queries to

1- Update customers by adding new customers

2- Assign one of the Reader employees to do next reading of some customers (at least 2)

3- make one of the readers add a new reading from his assignments list. Note that when a new reading is completed and if it is not the first reading for that customer a new bill will be issued, so create a bill for the entered reading according to the previous reading.

4- deactivate the service for a customer at his current address. Note that deactivating the service will require a new reading to be scheduled on the date of termination request. And new bill to be issued after the reading.

2.2 Informational Queries: Write queries to answer

1- How many customers with balance over $100? And then List their names and balances

2- List all readings assigned to a specific employee with their dates, meter numbers, and addresses 3-List all readings, bills and payments made by a customer at all of his addresses.

4- How many readings have been made by employee x in the interval between any two specified dates (start_date, end_date)?

5- Given a customer bill number, find the readers of the 1st and last reading in that bill? 6-Who is the customer with highest consumption up to specific date?

7- What is the average of all customer consumptions in the interval between any two specified billing dates (start_date, end_date)?

8- Who are the customers whose consumptions is above average in previous query (Q7) during that interval?

Attachment:- Database design and Implementation.rar