The Case of ABC Electric: ABC Electric wishes to create a database with the foll
ID: 674435 • Letter: T
Question
The Case of ABC Electric: ABC Electric wishes to create a database with the following entities and attributes: • Customer, with attributes Customer_ID, Customer_Name, Customer_Address (Street, City, State, Zip code), Customer_Type (Business or Residential) • Location, with attributes Location_ID, Location_Name, Location_County • Rate, with attributes Rate_Class, Rate_Per_ kwh
After interviews with the company employees and its customers, you have come up with the following business rules: • Locations are geographical areas such as cities, towns, etc. • Customers can have one or more locations. • Each location can have one or more rates, depending on the time of day.
Question 1 for the Case: Please use either Access notation (i.e., one—1, many—) or ER modeling notation (i.e., one— , many— )(see slide 3.28) to develop a logical database model for the company. You also need to (1) create associative entities (with attributes) to simplify many-to-many relationships whenever necessary, and
(2) use verbs to describe the relationships. Similar database model can be found in slide 7.12, which uses ER notation. The Draw function in Microsoft Word is preferred to illustrate the model. Question 2 for the Case: Write SQL statements to develop database tables for each of these entities in the model. Similar SQL statements can be found in slide 7.13.
Explanation / Answer
CREATE TABLE Customer (Customer_ID CHAR (20) NOT NULL,
Customer_Name CHAR (50) NOT NULL,
Street VARCHAR (100) NOT NULL,
City VARCHAR (100) NOT NULL,
State VARCHAR (5) NOT NULL,
Zip_Code VARCHAR (5) NOT NULL,
“Type” CHAR (20) CONSTANT Customer_Type_Check CHECK (“Type” IN ‘Business’, ‘Residential’)),
CONSTRAINT Customer_PK PRIMARY KEY (Customer_ID));
CREATE TABLE Location (Location _ID CHAR (20) NOT NULL,
Location_Name CHAR (50) NOT NULL,
Location_Country CHAR (50) NOT NULL,
Cust_ID INT references Customers (Customer_ID),
PRIMARY KEY (Location_ID)
);
CREATE TABLE Rate (Rate_Class CHAR (20) NOT NULL,
Rate_Per_Kwh CHAR (50) NOT NULL,
Location_Country CHAR (50) NOT NULL,
Loc_ID INT references Location (Location_ID)
PRIMARY KEY (Rate_Class)
);
CREATE TABLE Location_Rate (Rate_Date Date NOT NULL,
Location_Rate_ID CHAR (10) NOT NULL,
L_ID INT references Location (Location_ID),
R_Class INT references Rate (Rate_Class)
PRIMARY KEY (Rate_Date)
);