USA: +1 725-224-8795 
Sign in
Turn the attached ERD into Oracle database tables with foreign key relationships. You will insert records into your database tables and run queries against the tables using Structured Query Language (SQL).
Using Oracle, practice writing and testing Data Definition Language (DDL) and Data Manipulation Language (DML) SQL statements. You may use Oracle’s SQL Plus, SQL Developer, or Oracle Data Modeler tools. Create tables to fulfill the requirements of the ERD below. Populate the newly created tables withthree records of data and write database queries with the following requirements:
Scenario 2 Bute Electric – continued
A query (or queries) that lists all of the records in all of the tables.
A query that lists each customer by name with location addresses.
A query that lists the location type and the rates by time of day.
Expert Answer
- create table Customer(CustomerID number(10) primary key,Customer_Name varchar2(10),Street varchar2(10),City varchar2(10),Zip_Code number(5),Telephone number(11));
- create table Location(Location_ID number(5) primary key, Customer_ID number(10),Street varchar2(10),City varchar2(10),Zip_Code number(5),Location_type varchar2(10),Time_of_day timestamp, Foreign key(Customer_ID) references Customer(CustomerID));
- create table Rate(Rate_Class char(2) primary key,location_ID number(5),RatePerKWH number(3));
- To insert data to the tables use the insert query as below
Inserting data to the customer table.
- Insert into customer values(12345,’James’,’Street1′,’Toronto’,34567,19234556778);
- Insert into customer values(23456,’John’,’Street2′,’Ohio’,45678,14556778983);
- Insert into customer values(34567,’stephen’,’Street3′,’Mishigan’,56789,1567785678);
Inserting data to Location table
- Insert into Location values(5432,12345,lstreet,’Mishigan’,2345,’Business’,’19:10:30′);
- Insert into Location values(3254,34521,lstreet2,’Ohio’,4512,’Housing’,’14:30:30′);
- Insert into Location values(3452,23451,lstreet3,’Mishigan’,4532,’Industrial’,’24:11:20′);
Inserting data to Rate table
- Insert into Rate values(‘A’,5432,30);
- Insert into Rate values(‘C’,3254,10);
- Insert into Rate values(‘B’,3452,15);
Scenario 2:-
- A query (or queries) that lists all of the records in all of the tables.
Select * from customer;
Select * from location;
Select * from Rate;
- A query that lists each customer by name with location addresses.
Select Customer_ID,Customer_Name,l.Street,l.City,l.Zip_Code from Location l,customer where Customer_ID=CustomerID;
- A query that lists the location type and the rates by time of day.
Select Location_Type,Time_Of_Day,RatePerKWH from Location l,Rate where l.Location_ID=Rate.Location_ID;
