P$0704$ Best Deals Stores

Starting with the relational data model below, create P$0704.$sql and perform the following:

$$ Create the database from the relational model.

$$ Determine the data types and lengths.

$$ Use bd$\_$ as a prefix to each table name.

Add Database Constraints

$$ NOT NULL $($column optionality$)$ constraints at the column level, except store id and city.

$$ Add DEFAULT constraints at the column level.

$$ Create ALTER TABLE statements for PKs$,$ FKs$,$ and UKs.

$$ Use the ALTER TABLE statement to add the NOT NULL constraint for the store id and city columns

in the bd$\_$stores table.

$$ Add CHECK constrains as follows:

o Store id in the range $1199$ inclusive.

o Department id must be in the range $300399$ inclusive.

o The birth date must be $1980$ or later.

o The Social Security Number must be in the range $1999999999$ inclusive.

o Gender must be $\text{'}$F$\text{'}$ or $\text{'}$M$\text{'}.$

o Pension Contribution must be defined as a Boolean with a default value of $0$ and

contain a value of $1$ or $0.$

o The hire date has a default value of the current date.

o The hire date must be later than the birth date.

o The department ID has a default value of $300.$

o The job class has a default value of $\text{'}$T$\text{'}.$

o The job class must be $\text{'}$T$\text{'},\text{'}$J$\text{'},\text{'}$C$\text{'},$ or $\text{'}$M$\text{'}.$

o The job level must be in the range $19$ inclusive.

o Salary must be less than or equal to $125,000\mathrm{.}00.$

o Salary must be greater than commission.

o Bonus$/$Commission $$ Must have a value for bonus or commission, but not both. Both

bonus and commission cannot be zero.

o Commission must be less than $8\mathrm{.}5\%$ of salary.Relational Data Model

Foreign Key Relationships

$128$

STORES.store$\_$mgr$\_$id $$ EMPLOYEES.employee$\_$id

EMPLOYEES.coach$\_$id $$ EMPLOYEES.employee$\_$id

EMPLOYEES.store$\_$id $$ STORES.store$\_$id

EMPLOYEES.department$\_$id $$ DEPARTMENTS.department$\_$id

STORE$\_$DEPARTMENTS.store$\_$id $$ STORES.store$\_$id

STORE$\_$DEPARTMENTS.department$\_$id $$ DEPARTMENTS.department$\_$id