Answered: Implement a new independent entity…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

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

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.

store
staff
customer
1(0)
1(0)
1(0)
has
has
has
1(0)
phone
phone_id 1-1(1)
country_code M-1(1)
1(0)| phone_number M-1(1)
phone_type M-1(0)
1(0)

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions