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\mathrm{.}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\mathrm{.}881932,-87\mathrm{.}623177,$ 'Chicago'$)$;

$(42\mathrm{.}881932,-87\mathrm{.}623177,$'Chicago'$)$;

$(41\mathrm{.}881932,-86\mathrm{.}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