Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: 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. Below is code that I have but I get this error code "ERROR 1072 (42000) at line 9: Key column 'phone_id' doesn't exist in table" Any help or a better code would be great CREATE TABLE phone ( phone_id INT NOT NULL, phone_number INT NOT NULL, country_code INT NOT NULL, phone_type VARCHAR(12) NOT NULL, PRIMARY KEY (phone_id) ); ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE staff ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE store ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE address DROP COLUMN phone;
![Q: [ slide-15 .png ] Q1. Please write SQL codes to create below member database table based on…A: A relational database contains tables. Tables are also known as relations and they contain rows and…](/questions-and-answers/slide-15-.png-q1.-please-write-sql-codes-to-create-below-member-database-table-based-on-the-previous/3b4446fb-5348-4dbb-944d-a99326b9259f){kind=link}
Implement a new independent entity phone in the Sakila
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.
Below is code that I have but I get this error code "ERROR 1072 (42000) at line 9: Key column 'phone_id' doesn't exist in table"
Any help or a better code would be great
CREATE TABLE phone (
phone_id INT NOT NULL,
phone_number INT NOT NULL,
country_code INT NOT NULL,
phone_type VARCHAR(12) NOT NULL,
PRIMARY KEY (phone_id)
);
ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE staff ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE store ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE address DROP COLUMN phone;
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
i am still receiving the following error
ERROR 1072 (42000) at line 9: Key column 'phone_id' doesn't exist in table
CREATE TABLE phone (
phone_id INT NOT NULL,
phone_number INT NOT NULL,
country_code INT NOT NULL,
phone_type VARCHAR(12) NOT NULL,
PRIMARY KEY (phone_id)
);
ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE staff ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE store ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE address ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;
by Bartleby Expert
