LAB - Implement supertype and subtype entities (Sakila)

Post by **answerhappygod** » Mon Jun 06, 2022 5:01 pm

: Lab Implement Supertype And Subtype Entities Sakila 1 (101.74 KiB) Viewed 34 times

: Lab Implement Supertype And Subtype Entities Sakila 2 (37.07 KiB) Viewed 34 times

Refer to the customer and staff tables of the Sakila database. These tables have many columns in common and represent similar entities. Convert the customer and staff entities into subtypes of a new supertype person: person address belongs to 1(1) M(0) person_id first_name last_name email active last_update works at M(0) store 1(1) customer person_id create_date 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 Implement supertype and subtype entities as person, customer, and staff tables with primary key person_id. Implement attributes as columns: staff • person_id picture username password

• All columns are NOT NULL. The person_id columns have 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 dependency relationships between subtype and supertype entities as foreign keys: • The person_id columns of customer and staff become foreign keys referring to person. ● Specify CASCADE actions for both relationships. Implement the belongs to and works_at relationships as foreign keys: ● belongs to becomes an address_id foreign key in person referring to address. • works_at becomes a store_id foreign key in staff referring to store. Specify RESTRICT actions for both relationships. The address and store tables, with primary keys address_id and store_id, are pre-defined in the zyLab environment. Foreign keys must have the same data types as the referenced primary keys: ● address_id has data type SMALLINT UNSIGNED. store_id has data type TINYINT UNSIGNED. 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: DROP TABLE customer, staff; 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;

LAB ACTIVITY 1 -- Your CREATE TABLE statements go here 2 Develop mode Submit mode 6.4.1: LAB - Implement supertype and subtype entities (Sakila) 0/10 Main.sql Load default template... 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.