Case
For Assignment 1, you designed a Relational Data Model for Emerging Electric. For Assignment 2, you are requested to design the Physical Data Model and build the database.
The input for the Physical Data Model are the Relational diagram you built for Assignment 1, the points from the Analysis phase below, and the additional requirements below.
Analysis Phase Requirements
After some initial analysis, it has been established that at a minimum the following entities and attributes are needed:
• Customer, with attributes Customer ID, Name, Address (Street, City, State, Zip Code), and Telephone
• Location, with attributes Location ID, Address (Street, City, State, Zip Code), and Type (values of Business or Residential)
• Rate, with attributes Rate Class and RatePerKWH
After interviews with the owners, you have come up with the following business rules:
• Customers can have one or more Locations.
• Each Location can have one or more rates, depending on the time of day.
• Customers are required to have service at least at one Location.
• Each Location is required to have at least one rate.
Additional Requirements
1. The Database will be named with your first name plus last name initial. Example. Aldo Gonzalez ALDOG
2. Appropriate data type for each column must be chosen. RatePerKWH is a number.
3. You must run the script and verify that it works before submitting it.
4. Write the script to build the database in T-SQL. That is the SQL version we are using in all the Lab scripts
5. The script must include create all tables, add all columns, create primary keys and foreign keys as needed.
Deliverables
1. A .sql file with the script to build the database in T-SQL.
