Overview
Your assignment is to design adatabase for a utility company to manage customer's monthly bills. You will design the database, insert some sample data, and implement a set of required features. Each feature will be implemented as one Oracle PL/SQL procedure (you can create sub-procedures that will be called by the main procedure for that feature). You do NOT need to write a graphic user interface. You also need to provide statements to execute your procedures. The instructor and TA will run the statements you provided to test your program.
Assumptions (Please read carefully)
You can make the following assumptions in this project.
1. The system needs to store data about customers (you can assume that each customer needs to register with the system) and customer's monthly bills.
2. The customer table stores customer's name, address (including street, city, and zip), phone number, email address, account number, and account balance.
3. The Monthly bill table stores monthly bills for each customer. The table stores account number, year and month, electricity usage (in kw), gas usage (in therm), and a total charge.
4. The system also stores a rate table that contains the unit charge for electricity ($ per kw) and gas ($ per therm) and a flat fee. These charges may change over time so you need to store the year and month of these charges.
5. You can assume that some rows have already been inserted into customer and rate table.
Required Features: please implement each of these features as a PL/SQL procedure. You should also test them by calling these procedures.
1. Generate monthly bill for customer. The input includes: account number, year, month, electricity usage, gas usage. Please first compute the total charge for that month (explained in assumption 4), then update the account balance (in the customer table) to the new total charge, and print out customer's name, address, account#, previous balance (before the update), electricity usage and charge, gas usage and charge, and total charge.
2. All a customer to pay for a monthly bill. The input includes: account number, year, month, the amount paid. Please update the customer table to compute a new balance as old balance - the amount paid.
3. Allow a customer to look up the monthly bill of a given year and month. The input includes account number, year, and month. Print out customer's name, address, account#, previous balance (before the update), electricity usage and charge, gas usage and charge, and total charge. Hint: the procedure is similar to feature 1 but you don't need to update or insert.
Deliverables
There will be 2 deliverables:
- 30%. Due 10/14. Design Document which includes the following:
o ER diagram of the database. You don't have to follow exact notations of ER diagram, but need to show tables, columns, primary keys, and foreign key links.
o SQL statements to create database tables and to insert some sample data (at least 5 rows per table).
o Specification for each required feature. The specification should include a description of input parameters and output (usually screen outputs). You don't need to implement any of these features at this point.
- 70% Due 12/17.
o Software code of your project, properly documented. The code should include:
- Drop table statements
- Create table statements
- Insert statements
- Create procedure statements (with code for the procedures). Each feature can be implemented as one PL/SQL procedure. Please include some comments in your code explaining the major steps.
o Demo script to show that all your features work correctly.
The script shall include:
- Drop table statements to drop tables if they exist (remember the order of drop should be inverse of create).
- Create table statements and insert statements.
- Some examples for each feature. This should include:
• PL/SQL script to call the appropriate PL/SQL procedure for this feature. E.g., exec procedure-name(parameter values)
• Explanation of what should be the correct output. The output could be updated tables (you can have some select statement to show the updated tables), some print out, etc.
- Make sure you have tested your examples from beginning to end. Remember that database tables may have been changed in the process. So you may need to start with a clean database (i.e., right after you execute all the drop table, create table, and insert statements).
Every deliverable is due midnight (Eastern time) on the specified date. Late submission will lead to 30% penalty in the first week and 50% penalty afterwards except for the last deliverable. No late submission is allowed for the last deliverable.
Grading Guidelines
What I look for while grading software code (deliverable 3):
1. Existence of code
2. Comments: Both descriptive and inline for every procedure/function
3. Software quality
a. Whether it is correct.
b. Whether it is complete and clear.
c. Efficiency of code. You shall not use too many SQL statements, and you shall put as much work as possible in SQL. For example, if you can do a join, do not use two select statements and then do a join in your program.
d. Whether it has considered all special cases such as a username already exists when registering a customer.