A database exercise involving normalization. It is due in three hours

IST659 Spring 2017 Lab 4 Exercise Lab 4 Exercise – DUE SUNDAY NOON
1. Problem Description (1)
You are hired to develop a normalized data model starting with a sample document (see
the table below). The sample document is a utility bill summary sheet that Orange Nation
Company is currently using to track the utility usage for its customer accounts. Orange Nation
companyCompany wants to build a small database to store and retrieve this information.
Orange Nation Company
Utility Bill Summary
Customer Information
Customer No. _______________
First Name_____________________ Last Name ______________________
Apt number ________ Street ________ City ________
Phone No. _____________________
Account No: ______________ Account Start Date: _______________
Billing Period: Start Date __________________ End Date______________
Previous Balance _______________ Current Charge ___________________
Total Amount __________________ Due Date________________________
Last Payment Amount _____________ Last Payment Date ______________
Usage Information
Electric:
Meter No. _____________________ Unit Charge _____________________
Current Read _________________ Previous Read _____________________
Billed Usage (KWH) _______________ Billed Amount _________________
Gas:
Meter No. _____________________ Unit Charge _____________________
Current Read _________________ Previous Read _____________________
Billed Usage (CCF) _______________Billed Amount __________________
Water:
Meter No. _____________________ Unit Charge _____________________
Current Read _________________ Previous Read _____________________
Billed Usage (CGAL) _______________ Billed Amount__ ______________
1 IST659 Spring 2017 Lab 4 Exercise Orange Company follows these business rules:
a. Each customer must have at least one account.
b. Each account must have at least one of the connections, e.g., electric, gas, and water.
c. Electric meter number is not the same as gas meter number or water meter number
d. Each electric meter number is associated with one and only one account number.
e. Each gas meter number is associated with one and only one account number.
f. Each water meter number is associated with one and only one account number.
g. Current charge is the summation of electric, gas and water billed amounts for the billing
period. There are no adjustments from previous cycles. 2. Requirements You will use MS Access to finish this lab assignment. You will go through the steps that we
used to demonstrate normalization in the class. Basically, take all given data fields in a 1NF form
and then move forward with normalizing this data model to 2NF (second normal form) and 3NF
(third normal form). Make sure you demonstrate the intermediate steps before arriving at the
3NF i.e. show 2NF ERD and explain all the functional dependencies. Your final ERD must be in
the 3NF. Feel free to create new tables or attributes when necessary. NO NEED TO ENTER data
in the access tables, please simply SHOW the diagram of the relationships at different stages.
Here is the list of the items that you should turn in and their point distribution:
1) an Access table that is in 1NF (using Customer No., Account No and Start Date as a
composite PK)
2) the Access tables that conform to 2NF, and explanation of what partial dependencies you
have found and how you removed them
4) The data model that conforms to 3NF, and explanation of what transitive dependencies
you have found and how you removed them
6) Create necessary entities, and give appropriate names to them.
7) Set up primary key for each entity. 2 IST659 Spring 2017 Lab 4 Exercise 8) Establish relationships between entities. Give the relationships appropriate names (show
forward verb phrases only) and recognize the associations (foreign keys).
9) Mark correct cardinality of the relationships.
Submission instruction
Please submit your lab report (including the ERDs and screenshots) in one PDF file to Black
Board. Name your file in this format “IST659-Lab3-Lastname-Firstname.pdf”.
Due date
The lab report is due on Sunday, Noon
GRADING CRITERIA: Correct 1NF, 2NF, and 3NF ER-Diagrams – 2.5 pt.
Completed and correct dependency lists - 1.5pt
Complete Cardinality, correct identification of keys, no composite or multivalued attributes – 1pt 3

• 1493453131-Lab.doc

ComputerScienceExpert

(11)

Status NEW Posted 29 Apr 2017 08:04 AM My Price 8.00

-----------

Attachments

file 1493453131-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer