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 nowThis is a popular solution!
Step by stepSolved 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;
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;
- StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.arrow_forwardGenerate the table creation DDL for the schema. Consider the keysprimary and foreign.arrow_forwardTask 5:The marketing team wants to celebrate the success of StayWell with a party. The team wants a table with the names of all residents and owners combined into single column named PARTICIPANT. You need to combine this information from the tables and send it back to the team. You do not need to create a new table in the database schema. I found the answer but it wroten on paper so its really hard to understand what it is, that's why I am posting this question again.arrow_forward
- In anticipation of a change to the database, you are asked to provide a list of all products that have 'Man-Made' or 'Man Made' in the the composition text. (HInt: It is easiest to use LIKE in this instance.) In addition, you are requested to replace either 'Man-Made' or 'Man Made' with the word 'Synthetic'. Only include ProductIDs between 1050 and 1060. Show the following in your answer: ProductID, ProductName, Composition, and the modified composition field as 'NewComposition'. (HINT: This problem is a little tricky because you need to replace both 'Man-Made' and 'Man Made'. The way to do this is to use a replace within a replace.)arrow_forwardIn the Testa EV database design described in the previous question, please complete a field specifications form for the field 'Model_Cost' of the table 'Model_T'. The field records the cost of producing a specific EV model. Because its data come from the cost data of specific parts used to build the model, its value can be entered later and editing is allowed (null is allowed). The field value is required. Only decimal numeric data are supported for the entered value. The field can store a maximum of 10 digits (including 2 decimal places). The user of the database (data analyst) is responsible for entering and maintaining the data. No specific range of values is specified for this field. You should enter 'N/A' (or leave it unchecked) for elements that are not applicable.arrow_forwardPage - 1 DBMS - TASK 1: Draw an ERD and Map it to relational schema. Please complete reading page 1 before going to page 2. In this task, you will be given a text that describes a company (in page 2). Each student should complete the steps below to fully solve the task Represent the company description as an ER-diagram, including all relevant constraints as the following steps: - Each student should download Lucidchart app Every student should use Lucidchart to draw the ERD Edit Lucidchart Blank diagram Select View Insert Arrange Help 80 What's New Saved Start a free trial to access unlimited shapes and documents! X BIUAT- 2 px None None 408 Liberation Sans ▾ 10 pt + Shapes O Standard × T Flowchart 000 ☐ ODOT ☐ ☐ ☐ ☐ ☐☐ )>0 × TU Xx 14 << ΔΟ ▼ Shapes ΠΑ ☐ ☐ Name B Present MA when you create After each entity ID print screen, your name include your user ID should be shown university ID number Department 0190334 Dep_ID Employee 0190334 Namearrow_forward
- Computer Networking: A Top-Down Approach (7th Edi...Computer EngineeringISBN:9780133594140Author:James Kurose, Keith RossPublisher:PEARSONComputer Organization and Design MIPS Edition, Fi...Computer EngineeringISBN:9780124077263Author:David A. Patterson, John L. HennessyPublisher:Elsevier ScienceNetwork+ Guide to Networks (MindTap Course List)Computer EngineeringISBN:9781337569330Author:Jill West, Tamara Dean, Jean AndrewsPublisher:Cengage Learning
- Concepts of Database ManagementComputer EngineeringISBN:9781337093422Author:Joy L. Starks, Philip J. Pratt, Mary Z. LastPublisher:Cengage LearningPrelude to ProgrammingComputer EngineeringISBN:9780133750423Author:VENIT, StewartPublisher:Pearson EducationSc Business Data Communications and Networking, T...Computer EngineeringISBN:9781119368830Author:FITZGERALDPublisher:WILEY