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

lab activity

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

open$\_$in$\_$newfullscreen$8/10$

Refer to the address, store, staff, and customer tables of the Sakila database. In this lab, these tables initially have the same columns as in Sakila, however some constraints are different.

The customer and staff tables have many common columns. Move these columns to a new supertype table person, as shown in the following 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 the diagram in three steps:

Step $1.$ Create a person supertype table:

Create a primary key person$\_$id with data type SMALLINT UNSIGNED.$$

Add the common columns of customer and staff$($refer to the diagram$).$ Specify the same data types and constraints as in customer and staff.

Implement the belongs$\_$to relationship as a foreign key constraint on address$\_$id$.$ Specify the same foreign key rules as in customer and staff.

Step $2.$ Drop common columns from customer and staff:

Drop columns that moved to person in step $1.$

Do not drop last$\_$update, as every Sakila table has this column.

Drop the foreign key constraint on address$\_$id before dropping this column. $$

Step $3.$ Implement IsA relationships in customer and staff:

Drop the existing primary key columns.

Add new primary keys person$\_$id with data type SMALLINT UNSIGNED.$$

Implement the IsA relationships as foreign key constraints on person$\_$id$.$ Specify CASCADE foreign key rules.$$

HINT: Use ALTER TABLE to add and drop foreign key constraints:

ALTER TABLE customer ADD FOREIGN KEY $($person$\_$id$)$ REFERENCES person$($person$\_$id$)$ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer DROP FOREIGN KEY customer$\_$ibfk$\_1$;$--$ customer$\_$ibfk$\_1$ is a symbol representing the first foreign key declared in customer