Eastern Illinois University



April 8-12 and April 15 to April 29

Student Name: _______________________________________

Note: Professional looking is a criterion for grading your project. That means you need to pay attention to details.

Chatoon Autos is a small car dealership located in the city of Chatoon, IL. Most of its customers are either students or households looking for a second car. Appendix A shows basic information needed in order to run the dealership.

Your Assignment

Part 1: Creating the DB Objects (Due 4/12/2013)

1) Create a folder called FirstLastProj (where FirstLast represents your first and last names) under the root of your flash drive so that the path to the folder is something like F:\JohnDoeProj. Then, copy the given project’s files found under Project in the Assignments section of the course Website to your FirstLastProj folder. Note that all your project files must be saved in the FirstLastProj folder for the application to work.

2) Normalize the tables shown in Appendix A in order to come up with four (4) tables that are in 3NF. You need to use the template given in Appendix C of this assignment to create the design of your tables. The tables must be called: ChatoonEmployees, ChatoonSales, ChatoonInventory, ChatoonCustomers, Make sure you have added the necessary fields for creating the relationships between the tables where applicable. A copy of your tables’ design (your Appendix C) must be typed and saved as a Word file to your FirstLastProj folder under the name tablestructure.doc.

3) Based on the ScriptExample.sql file found in Appendix B of this assignment, use Notepad to create a script file called projectscript.sql that will delete any existing table with the name ChatoonEmployees, ChatoonSales, ChatoonInventory, or ChatoonCustomers from your user schema. The script should also delete any constraints associated with the tables. Then, the script will create the four (4) tables you have identified in step 2 above, and insert the data into them. Save the script to your FirstLastProj folder.

4) Run the projectScript.sql script in SQL Plus to create and populate the tables.

Submission: upload your FirstLastProj folder to the Illia network folder at \\L7019MSDN\test$

Appendix A

Table 1

|Sale_ID |SaleDate |Inventory_ID |Make |Model |Body |Engine |Trans |

|S0001 |1/1/2013 |IV005 |Toyota |Camry SE |Sedan |V4 |Automatic |

|S0002 |2/1/2013 |IV009 |Chevy |Impala XE |Sedan |V4 |Automatic |

|S0003 |2/3/2013 |IV001 |Honda |Accord LE |Sedan |V4 |Manual |

|S0004 |1/7/2013 |IV010 |Chrysler |300M |Sedan |V4 |Automatic |

|S0005 |3/1/2013 |IV016 |Toyota |Camry XE |Sedan |V6 |Automatic |

|S0006 |1/28/2013 |IV003 |Ford |Escape-i |SUV |V6 |Manual |

|S0007 |1/20/2013 |IV004 |Ford |Explorer |SUV |V8 |Automatic |

Table 1 (continued)

|Year |Cost |SalePrice |CustomerFirst |CustomerLast |CustomerPhone |

|2009 |8500 |10250 |Albert |Knew |2173451111 |

|2010 |16000 |21000 |Dominique |Williams |2177818569 |

|2008 |5000 |8500 |Bill |Busch |3255669000 |

|2005 |5200 |9000 |Courtney |Wong |2175420010 |

|2003 |4000 |6000 |Steven |Simpson |2177453210 |

|2010 |11000 |15000 |Dayton |Simpson |2179504566 |

|2011 |24000 |28000 |Elizabeth |Bloom |4109581000 |

Table 1 (continued)

|CustomerAddress |CustomerCity |CustomerState |CustomerPhone |

|200 Lincoln Ave |Chatoon |IL |2173451111 |

|234 Elm Street |Worth |IL |2177818569 |

|128 Mckinley Ave |Salem |KY |3255669000 |

|41 Dawn Street |Rardin |IL |2175420010 |

|210 10th Street |Chatoon |IL |2177453210 |

|102 Ohio Rd |Decatur |IUL |2179504566 |

|74 Indian Road |Chester |KY |4109581000 |

**********************************************************************************

Table 2

|Employee_ID |FirstName |LastName |Title |Experience |

|E0001 |John |Longhorn |Salesperson |10 |

|E0002 |Audry |Brown |Secretary |5 |

|E0003 |Luc |Jambon |Salesperson |5 |

|E0005 |Ken |Williams |Mecanic |5 |

|E0006 |Diane |Lawson |Salesperson |3 |

|E0007 |Roberto |Gonzales |Salesperson |5 |

|E0008 |Steve |Johns |Manager |10 |

Table 2 (continued)

|Address |City |State |Phone |

|102 Ohio Street |Chatoon |IL |2175824102 |

|14 Lerna Road |Chatoon |IL |2178542100 |

|120 Jacki Lane |Chatoon |IL |2178542121 |

|128 Lincoln Avenue |Chatoon |IL |2175242100 |

|100 Main Street |Salem |IN |2178754100 |

|145 Neil Avenue |Champaign |IL |2173251000 |

|123 Prospect Avenue |Urbana |IL |2176251015 |

Appendix B : scriptExample.sql

Appendix C: tables’ structure

Table’s name: _________________________

|Column |Data type |Field size |Format |Default value |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

Note: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.

Table’s name: _________________________

|Column |Data type |Field size |Format |Default value |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

Note: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.

Table’s name: _________________________

|Column |Data type |Field size |Format |Default value |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

Note: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.

Table’s name: _________________________

|Column |Data type |Field size |Format |Default value |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

| | | | | |

Note: The table’s primary key must be bold and underlined. The foreign key (if any) must be listed last and must be bold.

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

[pic]

Developing the Chatoon Autos database application

--script to create Clearwater Traders database

-- revised 8/17/02 JM

-- modified 3/27/2013 AI

DROP TABLE item CASCADE CONSTRAINTS;

DROP TABLE category CASCADE CONSTRAINTS;

DROP TABLE orders CASCADE CONSTRAINTS;

DROP TABLE order_source CASCADE CONSTRAINTS;

DROP TABLE customer CASCADE CONSTRAINTS;

CREATE TABLE customer

(c_id NUMBER(5),

c_last VARCHAR2(30),

c_first VARCHAR2(30),

c_mi CHAR(1),

c_birthdate DATE,

c_address VARCHAR2(30),

c_city VARCHAR2(30),

c_state CHAR(2),

c_zip VARCHAR2(10),

c_dphone VARCHAR2(10),

c_ephone VARCHAR2(10),

c_userid VARCHAR2(50),

c_password VARCHAR2(15),

CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id));

CREATE TABLE order_source

(os_id NUMBER(3),

os_desc VARCHAR2(30),

CONSTRAINT order_source_os_id_pk PRIMARY KEY(os_id));

CREATE TABLE orders

(o_id NUMBER(8),

o_date DATE,

o_methpmt VARCHAR2(10),

c_id NUMBER(5),

os_id NUMBER(3),

CONSTRAINT orders_o_id_pk PRIMARY KEY (o_id),

CONSTRAINT orders_c_id_fk FOREIGN KEY (c_id) REFERENCES customer(c_id),

CONSTRAINT orders_os_id_fk FOREIGN KEY (os_id) REFERENCES order_source(os_id));

CREATE TABLE category

(cat_id NUMBER(2),

cat_desc VARCHAR2(20),

CONSTRAINT category_cat_id_pk PRIMARY KEY (cat_id));

CREATE TABLE item

(item_id NUMBER(8),

item_desc VARCHAR2(30),

cat_id NUMBER(2),

item_image BLOB,

CONSTRAINT item_item_id_pk PRIMARY KEY (item_id),

CONSTRAINT item_cat_id_fk FOREIGN KEY (cat_id) REFERENCES category(cat_id));

------------------- inserting records into CUSTOMER

INSERT INTO CUSTOMER VALUES

(1, 'Graham', 'Neal', 'R', to_date('12/10/1967', 'mm/dd/yyyy'), '9815

Circle Dr.', 'Tallahassee', 'FL', '32308', '9045551897', '904558599','grahamn', 'barbiecar');

INSERT INTO CUSTOMER VALUES

(2, 'Sanchez', 'Myra', 'T', to_date('08/14/1958', 'mm/dd/yyyy'), '172 Alto

Park', 'Seattle', 'WA','42180', '4185551791', '4185556643', 'sanchezmt', 'qwert5');

INSERT INTO CUSTOMER VALUES

(3, 'Smith', 'Lisa', 'M', to_date('04/12/1960', 'mm/dd/yyyy'), '850 East

Main', 'Santa Ana', 'CA', '51875', '3075557841', '3075559852', 'smithlm', 'joshua5');

INSERT INTO CUSTOMER VALUES

(4, 'Phelp', 'Paul', NULL, to_date('01/18/1981', 'mm/dd/yyyy'), '994

Kirkman Rd.', 'Northpoint', 'NY', '11795', '4825554788', '4825558219','phelpp', 'hold98er');

INSERT INTO CUSTOMER VALUES

(5, 'Lewis', 'Sheila', 'A', to_date('08/30/1978', 'mm/dd/yyyy'), '195 College Blvd.', 'Newton', 'GA', '37812', '3525554972', '3525551811', 'lewissa', '125pass');

INSERT INTO CUSTOMER VALUES

(6, 'James', 'Thomas', 'E', to_date('06/01/1973', 'mm/dd/yyyy'), '348 Rice Lane', 'Radcliff', 'WY', '87195', '7615553485', '7615553319', 'jamest', 'nok$tell');

------------------ inserting records into ORDER_SOURCE

INSERT INTO order_source VALUES (1, 'Winter 2005');

INSERT INTO order_source VALUES (2, 'Spring 2006');

INSERT INTO order_source VALUES (3, 'Summer 2006');

INSERT INTO order_source VALUES (4, 'Outdoor 2006');

INSERT INTO order_source VALUES (5, 'Children''s 2006');

INSERT INTO order_source VALUES (6, 'Web Site');

--------------------- inserting records into orders

INSERT INTO orders VALUES (1, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 1, 2);

INSERT INTO orders VALUES (2, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 5, 6);

INSERT INTO orders VALUES (3, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CHECK', 2, 2);

INSERT INTO orders VALUES (4, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CC', 3, 3);

INSERT INTO orders VALUES (5, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 6);

INSERT INTO orders VALUES (6, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 3);

------------------------ inserting records into CATEGORY

INSERT INTO category VALUES (1, 'Women''s Clothing');

INSERT INTO category VALUES (2, 'Children''s Clothing');

INSERT INTO category VALUES (3, 'Men''s Clothing');

INSERT INTO category VALUES (4, 'Outdoor Gear');

------------------------- inserting records into ITEM

INSERT INTO item VALUES (1, 'Men''s Expedition Parka', 3, EMPTY_BLOB());

INSERT INTO item VALUES (2, '3-Season Tent', 4, EMPTY_BLOB());

INSERT INTO item VALUES (3, 'Women''s Hiking Shorts', 1, EMPTY_BLOB());

INSERT INTO item VALUES (4, 'Women''s Fleece Pullover', 1, EMPTY_BLOB());

INSERT INTO item VALUES (5, 'Children''s Beachcomber Sandals', 2, EMPTY_BLOB());

INSERT INTO item VALUES (6, 'Boy''s Surf Shorts', 2, EMPTY_BLOB());

INSERT INTO item VALUES (7, 'Girl''s Soccer Tee', 2, EMPTY_BLOB());

COMMIT;

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download