Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

A . Relational Model Implementation A furniture company has data pertaining to customers, orders, products, and requests, as shown below. CUSTOMER: CUSTOMERNO NAME ADDRESS -

A. Relational Model Implementation
A furniture company has data pertaining to customers, orders, products, and requests, as shown below.
CUSTOMER:
CUSTOMERNO NAME ADDRESS
------------------------------------------------------------------------
3 CASUAL FURNITURE PLANO, TX
8 MOUNTAIN GALLERY BOULDER, CO
ORDER:
ORDERNO ORDERDATE CUSTOMERNO
---------------------------------------------------------------------
100624-MAR-103
100725-MAR-108
100825-MAR-108
100926-MAR-103
PRODUCT:
PRODUCTID DESCRIPTION FINISH PRICE
------------------------------------------------------------------------
10 WRITING DESK OAK NULL
30 DINING TABLE ASH 425
40 ENTERTAINMENT CENTER MAPLE 650
70 CHILDRENS DRESSER PINE 300
REQUEST:
ORDERNO PRODUCTID QUANTITY
------------------------------------------------------------------
1006104
1006302
1006401
1007403
1007702
1008701
1009102
1009401
Create a relational database schema for the furniture company. Define all necessary attributes, domains, and primary and foreign keys.
You should assume the following:
(i) each CustomerID is a number with at most three digits,
(ii) each OrderID is a number with at most five digits,
(iii) each ProductID is a number with at most two digits,
(iv) each Name is at most 25 characters long,
(v) each Address is at most 50 characters long.
(vi) each Description is at most 30 characters long,
(vii) each Finish is at most 10 characters long, and
(viii) Quantity and Price are integer and float respectively.
2. Include the following custom constraints in your schema:
Each Price is a non-negative value (zero is allowed) representing a price in dollars and cents that cannot exceed 899.99.
Each Quantity is a positive whole number between 1 and 5, inclusive.
3. Write insert statements to insert the data.
Note: 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).
4. 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, also include DROP TABLE commands for all four tables. Decide where to place DROP TABLE commands: before or after Create Table commands. Remember to drop tables in the correct order.
Run the complete script. Be sure that after the execution, the contents of the tables are displayed by your script.
Submit A.sql
B. Primary Key
Consider the following table declaration:
Create Table Location (
latitude float,
longitude float,
name varchar(50)
);
What would be an appropriate primary key for this relation?
Based on your primary key provide insert statements for this relation based on the following data:
(41.881932,-87.623177, 'Chicago');
(42.881932,-87.623177,'Chicago');
(41.881932,-86.623177,'Chicago')
(iii) Do any of the statements result in a violation? If yes, why? If no, why?
Submit B.sql. Write your responses as a comment instead of using Word or PDF
C. Altering Schemas
The Cars table has the following specification.
CREATE TABLE Cars (
CarID number(5) primary key,
VIN number(10) UNIQUE,
Color char(15),
YearofMake number(4),
Model_id number(5),
foreign key model_id references Model(model_id)
);
It has the following data:
CarId VIN Color YearOfMake Model_id
1233456783412 Red 20101
2342876309034 Blue 20032
The Model table has the following specification:
CREATE TABLE Mod

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Design And SQL For DB2

Authors: James Cooper

1st Edition

1583473572, 978-1583473573

More Books

Students also viewed these Databases questions

Question

Explain the inverted U curve of conflict intensity and performance.

Answered: 1 week ago

Question

4. Label problematic uses of language and their remedies

Answered: 1 week ago