You are part of a development team with Ace Software, Inc. who has recently been

Question

You are part of a development team with Ace Software, Inc. who has recently been contracted to develop various database capabilities for Online Vehicle Sales, Inc. (OVS). OVS is a startup “dotcom” with about 10 dealership locations in Maryland, Virginia and Washington, D.C. They sell new and used cars (compacts, midsizes and full-sizes), sport utility vehicles (SUVs) and light trucks. Currently their business is just based on customers visiting one of the 10 dealership locations in person, but soon they plan to move the bulk of their business the Internet. Initially they have expressed a desire to have a custom OLTP database, and a custom DSS database, designed and built by your company. Each dealership has a staff of salespersons who assist customers in the purchase of different types of vehicles for which various financing plans are available. New and used vehicles are provided to each dealership based on sales and inventory needs.

An ERD for a 3NF normalized online transaction processing (OLTP) relational database for this application is provided.

Using an SQL script file, create Oracle tables for the CUSTOMER, VEHICLE (i.e. SALE_VEHICLE), and SALE entities shown in the ERD. Use the plural form of the entity name for your table names (i.e. CUSTOMERS, VEHICLES, and SALES). Note that you are using the SALE_VEHICLE entity to create a 2

VEHICLES table. Ignore the TRADE_IN_VEHICLE entity. You will create tables for some of the other entities in upcoming assignments.

You must include all necessary integrity constraints including primary keys, foreign keys, CHECK constraints, UNIQUE constraints, and NOT NULL constraints. It is a good practice, but not required, to explicitly name your constraints.

You can create your database on Nova or any other Oracle system you wish, but you must use the Oracle RDBMS.

Populate the VEHICLES and CUSTOMERS tables with at least 10 (ten) rows each.

Populate your SALES table with at least 10 (ten) rows.

Run SELECT * statements on all three tables after they are populated to show their contents. To prevent excessive wrapping you might consider using the SQL*Plus LINESIZE and COLUMN commands.

Submit the following in either an SQL*Plus SPOOL file or screen snapshots of the output if using SQL Developer or another GUI.:

1) All of your DROP TABLE, CREATE TABLE, and ALTER TABLE SQL statements as they executed and the Oracle responses.

2) All of your INSERT SQL statements as they executed and the Oracle responses.

3) The contents of all tables from SELECT * FROM tablename; statements.

Do NOT submit your SQL script files. Only submit the output specified in Steps #1 though #3 above in a single SPOOL file or Word file.

You must include a SET ECHO ON SQL*Plus statement in your SQL script file to ensure that all the SQL that is executed is displayed in your SPOOL file.

Do NOT submit additional files as this only complicates the grading, and may result in lost points.

FINANCING PLAN min down ercentage OVC, Inc. Database ERD max loan a Loan type facilitates facilitated by SALE FINANCING oan_ter part of vehicle status os wherefrom sale ID sale first last date wholesale co make part of includes SALE name CUSTOMER SALE include part of VEHICLE comes from includes mileageincludes ade VIN address cus mes from street trade to state zip alesperson_ becomes city titie TRADE IN VEHICLE part of part of sale ID LEPERSON phone city ade in name works at street first belongs to last ire_date DISTRIBUTORaddress employs makes zip Xstate hone ate DEALERSHIP contains manag has made by ORDER SHIPMENT sq ft makes comes from ealer region dist ID p dat

Explanation / Answer

SET ECHO ON

DROP TABLE CUSTOMERS;
DROP TABLE VEHICLES;
DROP TABLE SALES;

CREATE TABLE CUSTOMERS(

cust_ID VARCHAR(20) NOT NULL,

name_first VARCHAR2(40) NOT NULL,

name_MI VARCHAR2(40),

name_last VARCHAR2(40) NOT NULL,

address_street VARCHAR2(40),

address_city VARCHAR2(40) NOT NULL,

address_state VARCHAR2(40) NOT NULL,

address_zip NUMBER(6) NOT NULL,

CONSTRAINT customer_pk PRIMARY KEY (cust_ID)
);

--SALE_VEHICLE table

CREATE TABLE VEHICLES(

VIN NUMBER(10) NOT NULL,

type VARCHAR2(10) NOT NULL,

model VARCHAR2(40) NOT NULL,

make VARCHAR2(20) NOT NULL,

wholesale_cost NUMBER(20),

wherefrom VARCHAR2(40),

trade_ID VARCHAR2(20),

CONSTRAINT vehicles_pk PRIMARY KEY (VIN)

);

-- SALE table

CREATE TABLE SALES(

sale_ID VARCHAR(10) NOT NULL,

gross_sale_price NUMBER(20) NOT NULL,

VIN NUMBER(10) NOT NULL,

salesperson_ID VARCHAR(20) NOT NULL,

cust_ID VARCHAR(20) NOT NULL,

mileage NUMBER(10),

sdate DATE,

vehicle_status VARCHAR(20),

CONSTRAINT sales_pk PRIMARY KEY (sale_ID),

CONSTRAINT fk_customers
FOREIGN KEY (cust_ID)
REFERENCES customers(cust_ID),
CONSTRAINT fk_vehicles
FOREIGN KEY (VIN)
REFERENCES VEHICLES(VIN)
);

INSERT INTO CUSTOMERS VALUES('1001','Michael','','Smith','first street','chicago','IL',60007);
INSERT INTO CUSTOMERS VALUES('1002','Henry','','Ford','second street','chicago','IL',60009);
INSERT INTO CUSTOMERS VALUES('1003','Will','','King','third street','chicago','IL',60008);
INSERT INTO CUSTOMERS VALUES('1004','Lee','','Ziu','third street','chicago','IL',60008);
INSERT INTO CUSTOMERS VALUES('1005','Mike','','Taylor','main street','chicago','IL',60011);
INSERT INTO CUSTOMERS VALUES('1006','Demi','','Moore','main street','chicago','IL',60012);
INSERT INTO CUSTOMERS VALUES('1007','Lisa','','Curtis','alpha street','chicago','IL',60009);
INSERT INTO CUSTOMERS VALUES('1008','Vivek','','Singh','beta street','chicago','IL',60006);
INSERT INTO CUSTOMERS VALUES('1009','Allan','','Donald','alpha street','chicago','IL',60009);
INSERT INTO CUSTOMERS VALUES('1010','Jean','','Paul','gamma street','chicago','IL',60007);

INSERT INTO VEHICLES VALUES(101,'type1','model1','make1',20000,'New York','0000');
INSERT INTO VEHICLES VALUES(102,'type2','model2','make2',15000,'Dundee','0000');
INSERT INTO VEHICLES VALUES(103,'type3','model3','make3',22000,'Washington','0000');
INSERT INTO VEHICLES VALUES(104,'type4','model4','make4',12000,'Washington','0000');
INSERT INTO VEHICLES VALUES(105,'type5','model5','make5',21000,'Dallas','0000');
INSERT INTO VEHICLES VALUES(106,'type6','model6','make6',11000,'Atlanta','0000');
INSERT INTO VEHICLES VALUES(107,'type7','model7','make7',11000,'Atlanta','0000');
INSERT INTO VEHICLES VALUES(108,'type8','model8','make8',14000,'Colorado Springs','0000');
INSERT INTO VEHICLES VALUES(109,'type9','model9','make9',13000,'Springfield','0000');
INSERT INTO VEHICLES VALUES(110,'type10','model10','make10',14000,'Yorkshire','0000');

INSERT INTO SALES VALUES('S1001',10000,101,'000','1001',30,date '2017-01-01','new');
INSERT INTO SALES VALUES('S1002',12000,102,'000','1002',30,date '2017-01-02','new');
INSERT INTO SALES VALUES('S1003',15000,103,'000','1003',20,date '2017-03-01','new');
INSERT INTO SALES VALUES('S1004',5000,104,'000','1004',30,date '2017-01-06','new');
INSERT INTO SALES VALUES('S1005',10000,105,'000','1005',40,date '2017-01-01','new');
INSERT INTO SALES VALUES('S1006',10000,106,'000','1006',30,date '2016-11-11','new');
INSERT INTO SALES VALUES('S1007',12000,107,'000','1007',30,date '2017-01-01','new');
INSERT INTO SALES VALUES('S1008',10000,108,'000','1008',30,date '2017-01-01','new');
INSERT INTO SALES VALUES('S1009',13000,109,'000','1009',20,date '2016-01-01','new');
INSERT INTO SALES VALUES('S1010',8000,110,'000','1010',20,date '2017-06-01','new');

SELECT * FROM CUSTOMERS;
SELECT * FROM VEHICLES;
SELECT * FROM SALES;

Output results on execution:-

------------------------------------

CUST_ID,NAME_FIRST,NAME_MI,NAME_LAST,ADDRESS_STREET,ADDRESS_CITY,ADDRESS_STATE,ADDRESS_ZIP
1001,Michael, - ,Smith,first street,chicago,IL,60007
1002,Henry, - ,Ford,second street,chicago,IL,60009
1003,Will, - ,King,third street,chicago,IL,60008
1004,Lee, - ,Ziu,third street,chicago,IL,60008
1005,Mike, - ,Taylor,main street,chicago,IL,60011
1006,Demi, - ,Moore,main street,chicago,IL,60012
1007,Lisa, - ,Curtis,alpha street,chicago,IL,60009
1008,Vivek, - ,Singh,beta street,chicago,IL,60006
1009,Allan, - ,Donald,alpha street,chicago,IL,60009
1010,Jean, - ,Paul,gamma street,chicago,IL,60007

VIN,TYPE,MODEL,MAKE,WHOLESALE_COST,WHEREFROM,TRADE_ID
101,type1,model1,make1,20000,New York,0000
102,type2,model2,make2,15000,Dundee,0000
103,type3,model3,make3,22000,Washington,0000
104,type4,model4,make4,12000,Washington,0000
105,type5,model5,make5,21000,Dallas,0000
106,type6,model6,make6,11000,Atlanta,0000
107,type7,model7,make7,11000,Atlanta,0000
108,type8,model8,make8,14000,Colorado Springs,0000
109,type9,model9,make9,13000,Springfield,0000
110,type10,model10,make10,14000,Yorkshire,0000

SALE_ID,GROSS_SALE_PRICE,VIN,SALESPERSON_ID,CUST_ID,MILEAGE,SDATE,VEHICLE_STATUS
S1001,10000,101,000,1001,30,01-JAN-17,new
S1002,12000,102,000,1002,30,02-JAN-17,new
S1003,15000,103,000,1003,20,01-MAR-17,new
S1004,5000,104,000,1004,30,06-JAN-17,new
S1005,10000,105,000,1005,40,01-JAN-17,new
S1006,10000,106,000,1006,30,11-NOV-16,new
S1007,12000,107,000,1007,30,01-JAN-17,new
S1008,10000,108,000,1008,30,01-JAN-17,new
S1009,13000,109,000,1009,20,01-JAN-16,new
S1010,8000,110,000,1010,20,01-JUN-17,new

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

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