Answered step by step
Verified Expert Solution
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
CASUAL FURNITURE PLANO, TX
MOUNTAIN GALLERY BOULDER, CO
ORDER:
ORDERNO ORDERDATE CUSTOMERNO
MAR
MAR
MAR
MAR
PRODUCT:
PRODUCTID DESCRIPTION FINISH PRICE
WRITING DESK OAK NULL
DINING TABLE ASH
ENTERTAINMENT CENTER MAPLE
CHILDRENS DRESSER PINE
REQUEST:
ORDERNO PRODUCTID QUANTITY
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 characters long,
v each Address is at most characters long.
vi each Description is at most characters long,
vii each Finish is at most characters long, and
viii Quantity and Price are integer and float respectively.
Include the following custom constraints in your schema:
Each Price is a nonnegative value zero is allowed representing a price in dollars and cents that cannot exceed
Each Quantity is a positive whole number between and inclusive.
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
Display the contents of each table by adding four statements of the form SELECT FROM TABLENAME ; to the end of your script, where TABLENAME 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 Asql
B Primary Key
Consider the following table declaration:
Create Table Location
latitude float,
longitude float,
name varchar
;
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:
'Chicago';
'Chicago';
'Chicago'
iii Do any of the statements result in a violation? If yes, why? If no why?
Submit Bsql 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 primary key,
VIN number UNIQUE,
Color char
YearofMake number
Modelid number
foreign key modelid references Modelmodelid
;
It has the following data:
CarId VIN Color YearOfMake Modelid
Red
Blue
The Model table has the following specification:
CREATE TABLE Mod
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started