$5\mathrm{.}6$ LAB $-$ Implement independent entity $($Sakila$)$

Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram:

The phone entity appears on the right. The phone entity contains four attributes, each followed by cardinality information: phone$\_$id $1-1(1),$ country$\_$code M$-1(1),$ phone$\_$numer M$-1(1),$ and phone$\_$type M$-1(0).$ Three entities appear on the left: store, staff, and customer, connected to the phone entity by three identical relationships. The three relationships are named 'has' and have cardinality $1(0)$ on both sides.

The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names:

All lower case

Underscore separator between root and suffix

Foreign keys have the same name as referenced primary key

Write CREATE TABLE and ALTER TABLE statements that:

Implement the entity as a new phone table.

Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables.

Remove the existing phone column from the Sakila address table.

Step $2$ requires adding a foreign key constraint to an existing table. Ex:

ALTER TABLE customer

ADD FOREIGN KEY $($phone$\_$id$)$ REFERENCES phone$($phone$\_$id$)$

ON DELETE SET NULL

ON UPDATE CASCADE;

Specify data types as follows:

phone$\_$id$,$ phone$\_$number, and country$\_$code have data type INT.

phone$\_$type has date type VARCHAR$(12)$ and contains strings like 'Home', 'Mobile', and 'Other'.

Apply these constraints:

NOT NULL constraints correspond to cardinalities on the diagram above.

Foreign key actions are SET NULL for delete rules and CASCADE for update rules.

Specify a suitable column as the phone table primary key.

$535412\mathrm{.}1781354.$qx$3$zqy$7$

LAB ACTIVITY

$5\mathrm{.}6\mathrm{.}1$: LAB $-$ Implement independent entity $($Sakila$)$

$7/10$

Main.sql