$13\mathrm{.}11$ LAB$2$#$1-$ Implement strong entity $($Sakila$)$

This lab will be available until February $18$th$,11$:$59$ PM EST

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

The diagram has four entities and three relationships. The entities are phone, store, staff, and customer. Phone has attributes phone$\_$id R U$,$ country$\_$code R$,$ phone$\_$number R$,$ and phone$\_$type. Store, staff, and customer have no attributes. The relationships are store$-$has$-$phone, staff$-$has$-$phone, and customer$-$has$-$phone. All relationships have cardinality one$-$one maximum and zero$-$zero minimum.

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.Main.sql

$--$ Your CREATE TABLE and ALTER TABLE statements go here

CREATE TABLE phone $($

phone$\_$id INT PRIMARY KEY,

phone$\_$number INT NOT NULL,

country$\_$code INT NOT NULL,

phone$\_$type VARCHAR$(12)$ NULL

;

ALTER TABLE customer

ADD phone$\_$id INT,

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

ON DELETE SET NULL

ON UPDATE CASCADE;

ALTER TABLE staff

ADD phone$\_$id INT,

ADD FOREI$$N KEY $($phone$\_$id$)$ REFERENCES phone$($phone$\_$id$)$

ON DELETE SET NULL

ON UPDATE CASCADE;

ALTER TABLE store

ADD phone$\_$id INT,

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

ON DELETE SET NULL

ON UPDATE CASCADE;

ALTER TABLE address

DROP COLUMN phone;