Question

• • Relational Schema

Create a relational database schema consisting of the four relation schemas representing various entities recorded by a furniture company.  

Write CREATE TABLE statements for the following four relation schemas. Define all necessary attributes, domains, and primary and foreign keys.

Customer(CustomerID, Name, Address)

FullOrder(OrderID, OrderDate, CustomerID)

Request(OrderID, ProductID, Quantity)

Product(ProductID, Description, Finish, Price)

You should assume the following: Each CustomerID is a number with at most three digits, each OrderID is a number with at most five digits, and each ProductID is a number with at most two digits. Each Name is at most 25 characters long, and each Address is at most 50 characters long. Each Description is at most 28 characters long, and each Finish is at most 10 characters long. Quantity and Price are integer and float respectively.

In this schema, primary and foreign keys are not marked. Identify primary and foreign keys and other custom constraints. Declare PK and FK in your CREATE TABLE statement and custom constraints as follows:

• Each Price is a non-negative value (zero is allowed) representing a price in dollars and cents that cannot exceed 999.99.
• Each Quantity is a positive whole number between 1 and 100, inclusive.

Populate the database as follows:

CUSTOMER:

CUSTOMERID NAME ADDRESS

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

2 CASUAL FURNITURE PLANO, TX

6 MOUNTAIN GALLERY BOULDER, CO

FULLORDER:

ORDERID ORDERDATE CUSTOMERID

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

1006 24-MAR-10 2

1007 25-MAR-10 6

1008 25-MAR-10 6

1009 26-MAR-10 2

PRODUCT:

PRODUCTID DESCRIPTION FINISH PRICE

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

10 WRITING DESK OAK 425

30 DINING TABLE ASH 600

40 ENTERTAINMENT CENTER MAPLE 650

70 CHILDRENS DRESSER PINE 300

REQUEST:

ORDERID PRODUCTID QUANTITY

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

1006 10 4

1006 30 2

1006 40 1

1007 40 3

1007 70 2

1008 70 1

1009 10 2

1009 40 1                  

Remember to insert the records containing primary key values before inserting the records containing the foreign keys that reference those values (or your insert statements will fail).

Display the contents of each table by adding four statements of the form SELECT * FROM TABLE_NAME ; to the end of your script, where TABLE_NAME is substituted by the four table names above.

In order to avoid conflicts, include DROP TABLE commands for all four tables before your CREATE TABLE statements. Either drop tables containing foreign keys before the tables containing the referenced primary keys, or use CASCADE CONSTRAINTS. Verify that the tables have been defined correctly before going on to the next step.

Run the complete script. Be sure that that the contents of the four tables are displayed correctly by your script.

Submit a Q1.sql file.

Note:

You must code this schema using ‘Create Table’ statements . You should not use any other tool except Oracle SQLDeveloper. No points will be given for auto-generated schemas using other 3^rd party tools or web-based editors.

Answer 1

/* Drop tables if exist */

DROP TABLE Customer;

DROP TABLE FullOrder;

DROP TABLE Request;

DROP TABLE Product;

/* Create required four tables */

CREATE TABLE Customer

(

CustomerID int CHECK(CustomerID BETWEEN 1 AND 999) NOT NULL PRIMARY KEY ,

Name varchar(25),

Address varchar(50)

);

CREATE TABLE FullOrder

(

OrderID int CHECK(OrderID BETWEEN 1 AND 99999) NOT NULL PRIMARY KEY ,

OrderDate date,

CustomerID int FOREIGN KEY REFERENCES Customer(CustomerID)

);

CREATE TABLE Request

(

OrderID int FOREIGN KEY REFERENCES FullOrder(OrderID) ,

ProductID int FOREIGN KEY REFERENCES Product(ProductID) ,

Quantity int CHECK(Quantity BETWEEN 1 AND 100)

);

CREATE TABLE Product

(

ProductID int CHECK(ProductID BETWEEN 1 AND 99) NOT NULL PRIMARY KEY,

Description varchar(28),

Finish varchar(10),

Price float CHECK(Price >=0 AND Price <= 999.99 )

);

/* Inserting data to the four tables */

INSERT INTO Customer

(

VALUES( 2, 'CASUAL' , ' FURNITURE PLANO, TX' ),

VALUES( 6, 'MOUNTAIN' , ' GALLERY BOULDER, CO' )

);

INSERT INTO FullOrder

(

VALUES( 1006, 24-MAR-10, 2 ),

VALUES( 1007, 25-MAR-10, 6 ),

VALUES( 1008, 25-MAR-10, 6 ),

VALUES( 1009, 26-MAR-10, 2 )

);

INSERT INTO Product

(

VALUES( 10 'WRITING DESK OAK', 425 ),

VALUES( 30, 'DINING TABLE ASH', 600 ),

VALUES( 40, 'ENTERTAINMENT CENTER MAPLE', 650 ),

VALUES( 70, 'CHILDRENS DRESSER PINE', 300 )

);

INSERT INTO Request

(

VALUES( 1006, 10, 4 ),

VALUES( 1006, 30, 2 ),

VALUES( 1006, 40, 1 ),

VALUES( 1007, 40, 3 ),

VALUES( 1007, 70, 2 ),

VALUES( 1008, 70, 1 ),

VALUES( 1009, 10, 2 ),

VALUES( 1009, 40, 1 )

);

/* Showing data present the four tables */

SELECT * FROM Customer;

SELECT * FROM FullOrder;

SELECT * FROM Product;

SELECT * FROM Request;