Sprint 36% 9:25 PM reggienet.illinoisstate.edu The following are the tasks that

Question

Sprint 36% 9:25 PM reggienet.illinoisstate.edu The following are the tasks that you need to complete for the seconed phase of the group project. Some Ace Auto Repair table names may conflict with those fronm the JLDB database from the chapters. You can use the team Oracle account to avoid this issue, or use table names lik ORDERI and CUST-x. 1. Create the relational schema for the Ace Auto Repair database using the entity and attribute names in Phase 1 solution posted on ReggieNet. referentialLintegrity arrows Remember, this is the table schema that looks like this: CUSTOMER 2. Write SQL codes in Notepad++to create your tables. While creating your tables, please include the necessary constraints (e.g, PK, FK, NOT NULL), declare default values as necessary and select the data types appropriate for the data on pages 2-5. Whenever possible, your constraints should be created at the table level. Document your design in a data dictionary (see Module2 slide #28 as an example). 3. Run the SQL commands in Oracle to build the tables. After your 4. Use the INSERT command to populate your tables with the required data (see pp. 2-5). After your commands have executed, run the SELECT FROM tablename; command once for each table, take screenshots of the results, and paste the images into the same Word file. Multiple screenshots may be Note: The dates in the RepairOrder table are not p Oracle's default format (DD-MMM-YY). One option is to reformat them into the default format Submit the following three (3) files via ReggieNet. Please DO NOT create a zip file: 1. A Word file containing: a the relational schema b the data dictionary

Explanation / Answer

If you have any doubts, please give me comment...

Customer(CustNo, FirstName, LastName, Address, City, State, Zip, Phone);

Vehicle(VIN, YearMake, Model, LicenceNo, State, Cylinders, CustNo);

Part(PartNo, PartDesc, UnitsInStock, UnitPrice, UnitSize);

RepairOrder(OrderNo, VIN, Odometer, Date);

PartsUsed(OrderNo, PartNo, QtyUsed);

CREATE TABLE Customer(

CustNo INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,

FirstName VARCHAR(100),

LastName VARCHAR(100),

Address VARCHAR(100),

City VARCHAR(100),

State CHAR(2),

Zip CHAR(5),

Phone VARCHAR(15)

);

CREATE TABLE Vehicle(

VIN VARCHAR(10) NOT NULL PRIMARY KEY,

YearMake INTEGER,

Model VARCHAR(30),

LicenceNo VARCHAR(6),

State CHAR(2),

Cylinders INTEGER,

CustNo INTEGER,

FOREIGN KEY(CustNo) REFERENCES Customer(CustNo)

);

CREATE TABLE Part(

PartNo INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,

PartDesc VARCHAR(255),

UnitsInStock INTEGER,

UnitPrice REAL(5,2),

UnitSize VARCHAR(20)

);

CREATE TABLE RepairOrder(

OrderNo INTEGER NOT NULL PRIMARY KEY,

Odometer INTEGER,

Date DATE,

VIN VARCHAR(10),

FOREIGN KEY(VIN) REFERENCES Vehicle(VIN)

);

CREATE TABLE PartsUsed(

OrderNo INTEGER,

PartNo INTEGER,

QtyUsed INTEGER,

FOREIGN KEY(OrderNo) REFERENCES RepairOrder(OrderNo),

FOREIGN KEY(PartNo) REFERENCES Part(PartNo)

);

INSERT INTO Customer VALUES(1, 'Beth', 'Taylor', '2396 Rafter Rd', 'Seattle', 'WA', '98103', '(206) 221-9021');

INSERT INTO Customer VALUES(1, 'Betty', 'Wise', '4334 153rd NW', 'Seattle', 'WA', '98178', '(206) 445-6982');

INSERT INTO Vehicle Values('AZXS230187', 2001, 'Ford', 'Skylark', '145UKI', 'WA', 4, 10);

INSERT INTO Part Values(1, '10W-40 oil', 145, 1.00, 'quart');

INSERT INTO RepairOrder VALUES(1, 50000, '10/5/2005', 'AZXS230187');

INSERT INTO PartsUsed VALUES(1, 2, 1);

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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