$4\mathrm{.}14$ LAB $-$ Implement supertype and subtype entities $($Sakila$)$

Similar entities have many common attributes and relationships. Similar entities are often converted imo aublypes of a Bupertype entity, as illustrated in this lab

In the Sakila database, the customer and staff tables have several common columne. Convert these tables into subtypses of person. Specifically, write CREATE TABL.E statements for porson, oust omer, and st a $f$ that imploment thio ER diagram:

Follow Sakila conventions for table and column names:

All lower case

Underscore separator between root and suffix

Foreign keys have the same name as referenced primary key Implement atributes as columns:

The primary key of all three tables is person$\_$id with data type SMALLINT UNSIGNED.

The last$\_$update and create date columns have data type TIMESTAMP.

The picture column has data type BLOB.

All other columns have data type VARCHAR $(20)$

Implement the belongs$\_$to and works$\_$at relationships as foreign keys;

belongs$\_$to becomes an address$\_$id foreign key in person with data lype SMALiLINT UNSIGNED.

works$\_$at becomes a store id foreign key in sta f with data type TINY INT UNSIGNED.

Specify RESTRICT actions for both foreign keys.

Required relationships become NOT NULL foreign keys.

Subtype entities have an Is A relationship to the supertype. Implement these relationships as foreign keys:

The person$\_$id columns of custamer and staff become foreign keys referring to person.

Specify CASCADE actions for both foreign keys.

NOTE: If you execute your solution with the Sakila database, you must first drop cust comer, sta $1,$ and all constraints that refer to these iables. Use the following statements with Sakila only, not in the zyLab environment:

ALTER TABLE payment

DROP CONSTRAINT fk$\_$payment$\_$customer,

DROP CONSTRAINT EK$\_$Payment staFE:

ALTER TABLE rental

DROP CONSTRAINT Ek$\_$rental$\_$customer,

DROP CONSTRAINT Ek$\_$rental$\_$stafe;

ALTER TABLE StOre

DROP CONSTRAINT EK gLONe stafE;

DROP TABLE customer, stafr;