Turn the attached ERD into Oracle database tables with foreign key relationships

Question

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 with three 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.

CUSTOMER Customer ID Customer Name Customer Address (Street, City, State, Zip Code) Customer Telephone Has Location Time of Day LOCATION RATE Location ID Customer tt Rate class (FK) Location Address Location ID (Street, City, State, Zip Rate Per KWH Code) Is Charged Location Type

Explanation / Answer

Inorder to create tables in oracle database we need to use create table queries like in any other database but there are few changes in the data type naming convension and use. Below are the DDL commands which will create the above ER Diagram into relational tables in database.

Inserting data to the customer table.

Inserting data to Location table

Inserting data to Rate table

Scenario 2:-

Select * from customer;
Select * from location;
Select * from Rate;

Select Customer_ID,Customer_Name,l.Street,l.City,l.Zip_Code from Location l,customer where Customer_ID=CustomerID;

Select Location_Type,Time_Of_Day,RatePerKWH from Location l,Rate where l.Location_ID=Rate.Location_ID;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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