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

Similar entities have many common attributes and relationships. Similar entities are often converted into subtypes of a supertype entity, as illustrated in this lab.

In the Sakila database, the customer and staff tables have several common columns. Convert these tables into subtypes of person. Specifically, write CREATE TABLE statements for person, customer, and staff that implement this ER diagram:

The diagram has five entities and two relationships. The entities are person, staff, customer, address, and store. Person is a super type entity, with subtypes staff and customer. Staff appears above customer, within person. Address and store appear outside person. Person has attributes person$\_$id R U$,$ first$\_$name, last$\_$name, email, active, and last$\_$update. Staff has attributes person$\_$id R U$,$ picture, username, and password. Customer has attributes person$\_$id R U and create$\_$date. Address and store have no attributes. Relationship address$-$belongs$\_$to$-$person has cardinality one$-$many maximum and one$-$zero minimum. Relationship staff$-$works$\_$at$-$store has cardinality many$-$one maximum and zero$-$one minimum.

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 attributes 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 type SMALLINT UNSIGNED.

works$\_$at becomes a store$\_$id foreign key in staff with data type TINYINT UNSIGNED.

Specify RESTRICT actions for both foreign keys.

Required relationships become NOT NULL foreign keys.

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

The person$\_$id columns of customer 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 customer, staff, and all constraints that refer to these tables. Use the following statements with Sakila only, not in the zyLab environment:

ALTER TABLE payment

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

DROP CONSTRAINT fk$\_$payment$\_$staff;

ALTER TABLE rental

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

DROP CONSTRAINT fk$\_$rental$\_$staff;

ALTER TABLE store

DROP CONSTRAINT fk$\_$store$\_$staff;

DROP TABLE customer, staff;

$542416\mathrm{.}3037512.$qx$3$zqy$7$

LAB ACTIVITY

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

$0/10$

Main.sql

Load default template...

Develop mode

Submit mode

Explore the database and run your program as often as you'd like, before submitting for grading. Click Run program and observe the program's output in the second box.

Run program

Main.sql

$($Your program$)$

Output $($shown below$)$

Program output displayed here