Solved–Assignment #4: — Implementing a database in SQL*Plus 4 –Solution.

Description

For this assignment you will write CREATE TABLE and INSERT INTO statements in order to implement a five-table database in Oracle’s SQL*Plus. All the information you need about the database is contained in an MS Excel workbook comprising five worksheets (one worksheet for each table). The MS Excel file accompanies these instructions in the Assignment 4 item in Canvas.

More specifically, each worksheet includes the following information about its associated table:

• Column names (for example, the jr_order table contains the orderID, customerID, orderDate, orderStatus, and orderShippedDate columns);
• Column data types (for example, orderID is INT, orderStatus is VARCHAR2(2), etc.);
• Column constraints, if any (for example, orderID in the jr_order table is a primary key, supplierName is NOT NULL, etc.); and
• All values (i.e., the data).

Note that you must swap my initials (jr) with your initials for all table names. Thus, for Mike Smith, the jr_customer table becomes ms_customer, the jr_supplier table becomes ms_supplier, and so on. If your initials have already been taken, choose another set of initials.

Example code for INSERT INTO statements can be found below. Example CREATE TABLE code can be found in the SQL Code Part 1 file available on Canvas.

Things to remember

• Create parent tables first, then child tables. I recommend creating tables in the following order: customer; supplier; order; product; orderline. The orderline table must be last because it is a child of the order and product tables.

• After tables have been created, populate parent tables with data first, then populate child tables with data. I recommend populating tables in the same order as listed in #1.

1. Remember that you will need to write and execute one INSERT INTO statement for each and every record.

• Write and execute a COMMIT WORK statement after creating all the records for a given table. For example, after you have added the last record for the supplier table, write and execute this:COMMIT WORK ;

Example INSERT code for each table

Example INSERT code for the xx_customer table

INSERT INTO jr_customer (
customerID, customerLastName, customerFirstName,
customerCity, customerState, customerZIP, customerEmail,
jr_customerPhoneNum ) VALUES (1,’Alexander’,’Michael’,
‘Highland Heights’,’KY’,’41076′,’alexander@yahoo.com’,NULL) ;

Example INSERT code for the xx_supplier table

INSERT INTO jr_supplier (
supplierID, supplierName, supplierCity, supplierState, supplierContactName, supplierContactTitle, supplierContactEmail ) VALUES (1,’Reaper’,’Denton’,’TX’,
‘John Franklin’,’Product Marketing Manager’,’jfranklin@reaper.com’) ;

Example INSERT code for the xx_order table

INSERT INTO jr_order (
orderID, customerID, orderDate, orderStatus, orderShippedDate )
VALUES (1,1,TO_DATE(‘2018-03-01′,’yyyy-mm-dd’),’SP’,TO_DATE(‘2018-03-04′,’yyyy-mm-dd’) ) ;

Example INSERT code for the xx_product table

INSERT INTO jr_product (
productCode, supplierID, productName, suggestedPrice,
unitsInStock, unitsOnOrder, discontinued ) VALUES
(‘IM511′,2,’Cloaked Assassin’,4.95,4,1,’N’) ;

Example INSERT code for the xx_orderLine table

INSERT INTO jr_orderLine (
orderLineID, orderID, productCode, quantity, unitPrice )
VALUES (1,1,’IM511′,1,5.49) ;

Deliverable

For this assignment, you need only state the initials you used in the Comments portion of the assignment submission in Canvas.  If your name is Mike Smith, and you indicate that you used the initials ‘ms’, then I will look to see that all five tables (i.e., ms_customer, ms_supplier, ms_order, ms_product, and ms_orderLine) appear in SQL*Plus.  I will also write SELECT statements to make sure that you populated each table with all the data.

Sales Data Model