Homework-2 Emerging Electric wishes to create a database with the following enti

Question

Homework-2 Emerging Electric wishes to create a database with the following entities and attributes 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 Rate Per KWH 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 Homework 2 Solution Location Rate Customer Loc id Rate class Cust id Loc address Rate per kwh Cust address Has Street Street Has City State State Zipcode Zipcode Loc type Cust telephone Time-of-day Assumptions 1) Cust id is uique in Customer Loc id is unique in Location. 2) A location is owned by one customer a rate can be associated with multiple locations 3) Rate-class is unique each location is associated with multiple rates determined by rate-class depending on the time of day.

Explanation / Answer

Task-1
The relational schema tells you about the overall design of the database. The relational schema for given current E-R diagram is as follows:
The relational schema includes following tables with attributes:

i) Customer(Cust_id, Cust_address, Street, City, State, Zipcode, Cust_telephone)
ii) Location(Loc_id, Loc_address, Street, City, State, Zipcode, Loc_type)
iii) Rate(Rate_class, Rate_per_kwh)

Here in the above relation Cust_id, Loc_id and Rate_class is working as primary key.

Task-2

The various sql statements are as follows:

Table creation can be done as follows. For example sql query for customer table can be:

CREATE TABLE Customer
(Cust_address VARCHAR2(50),
Street VARCHAR2(30),
Cust_id NUMBER (10),
Zipcode NUMBER (10),
Cust_telephone NUMBER (10),
City VARCHAR2(12),
State VARCHAR2(12)
CONSTRAINT Cust_id PRIMARY KEY(isbn));

For Data insertaion we can use the following syntax:

INSERT INTO table_name (col1,col2,col3,...) VALUES (val1,val2,val3,...);

Here in above syntax you can replace table_name, column name and values.

The five query scenarios that may be useful are:

i) Select * from table_name (This query can be used to select all the data of table)

ii) delete from table_name (This query is for deleting a record from table)

iii) insert into table_name (This query is to insert into table)

iv) UPDATE table_name SET col1=val1,col2=val2,... WHERE some_col=some_val; (This query to update the table)

v) Select query with "Where" Clause (This query is to apply any condition)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.