Corporation: id 15 17 20 Company: cid id 1 15 17 15 20 15 2 3 4 5 Assumptions: •id in Company references id in Corporation. • cid in Department references cid in Company Department: did cid 1 1 2 2 3 2 4 5 6 7 8 3 9 1 10 2 11 4 12 5 3 5 1 3 Each option starts from the original tables (given above) NOT from the tables modified by the previous option You don't need to copy the entire table. Just explain the action of each operation. (a) Suppose that all the foreign keys are declared using on delete restrict. What is the effect of the instruction delete from Company where cid=2? (b) Suppose that all the foreign keys are declared using on delete cascade. What is the effect of the instruction delete from Corporation where id-15?

Corporation: id 15 17 20 Company: cid id 1 15 17 15 20 15 2 3 4 5 Assumptions: •id in Company references id in Corporation. • cid in Department references cid in Company Department: did cid 1 1 2 2 3 2 4 5 6 7 8 3 9 1 10 2 11 4 12 5 3 5 1 3 Each option starts from the original tables (given above) NOT from the tables modified by the previous option You don't need to copy the entire table. Just explain the action of each operation. (a) Suppose that all the foreign keys are declared using on delete restrict. What is the effect of the instruction delete from Company where cid=2? (b) Suppose that all the foreign keys are declared using on delete cascade. What is the effect of the instruction delete from Corporation where id-15?

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

Q: THIS MODULE IS ABOUT SUBQUERIES, SO YOU MUST USE SUBQUERIES INSTEAD OF TABLE JOINS IN THIS…

A: In SQL we use the sub queries along with the IN clause to get the set of records for which we need…

Q: Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real…

A: Creating SQL views offers valuable advantages, such as simplifying data access, bolstering security,…

Q: If the functional dependencies are consistent with the above relation, put TRUE. If they are not,…

A: functionally dependent defines the relationship between attributes, how they are related. A-> B…

Q: Who has rented movies in Japanese language? Return only the first five distinct results in…

A: The INNER JOIN keyword chooses records from both tables whose values match. The SELECT clause just…

Q: Write a SELECT statement that joins the Instructors and Courses tables and returns these columns:…

A: To accomplish this task, you can use a SQL SELECT statement that performs a LEFT JOIN between the…

Q: Course • Courseld CourseCode CourseName Capacity Instructorld 3815 ENGL862 American Literature 75 2…

A: The question requires us to write a SQL query that will return the details of instructors who teach…

Q: Question 15 Suppose that you have the following table created as: CREATE TABLE TAB1 (NAME VARCHAR2…

A: For this question we can decompose the name by using parsename. Parsename will decompose a full name…

Q: 1) Write the SQL code that will create the table structure for a table named EMP. This table is a…

A: For auto generated id's we have to start from 101 and increment by 1 For checking JOB_CODE we can…

Q: DROP TABLE IF EXISTS Worker; CREATE TABLE Worker ( WORKER_ID INTEGER NOT NULL PRIMARY KEY…

A: Answer in step 2

Q: Sales Database: Customers(custId, lastName, firstName, address, phone, creditLimit)…

A: A Sales Database consisting of three tables: Customers, Orders, and Items. The table structures and…

Q: 1. Write a SELECT statement that returns four columns from the Products table: ProductCode,…

A: “Since you have posted a question with multiple sub-parts, we will solve first three sub- parts for…

Q: CREATE TABLE Fielding( Player CHAR(25), CareerSpan VARCHAR(20), Matches INT, Innings INT, Catches…

A: SQL Query Use inner join to join the 3 tables and get the required result from query

Q: Below are some rows of the PROVIDERS table for a factory: PCODE NAME PHONE 10192 Hans, Inc.…

A: In this casе, wе arе working with a "PROVIDERS" tablе for a factory, and thе goal is to rеtriеvе…

Concept explainers

SQL

SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.

Queries

A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.

Question

Corporation:
id
15
17
20
instance:
Company:
cid
id
1
2
3
4
5
15
17
15
20
15
Assumptions:
id in Company references id in Corporation.
• cid in Department references cid in Company.
Department:
did cid
1
2
3
4
5
6
7
8
9
10
11
12 5
1
2
2
3
5
1
3
3
1
2
4
Each option starts from the original tables (given above) NOT from the tables
modified by the previous option
You don't need to copy the entire table. Just explain the action of each operation.
(a) Suppose that all the foreign keys are declared using on delete restrict. What is the
effect of the instruction delete from Company where cid=2?
(b) Suppose that all the foreign keys are declared using on delete cascade. What is the
effect of the instruction delete from Corporation where id-15?

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

SEE SOLUTION Check out a sample Q&A here

Step 1: Introduction

VIEW

Step 2: What are the resulting tables after performing given actions

VIEW

Solution

VIEW

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

SQL Query question
Task 3: The Car Maintenance team wants to insert and store the following maintenance types to the MAINTENANCE_TYPES table: ID: 1; Description: Tire Change; Price: 50 ID: 2; Description; Oil Change; Price: 45 ID: 3; Description; Full Cleaning; Price: 100 ID: 4; Description; Gas Pump Change; Price: 145 Add the four maintenance types to the MAINTENANCE_TYPES table.
Given the following tables, there is a university rule preventing a student from enrolling in a new class if there is an unpaid fine. Please write a table-level CHECK constraint to implement the rule. */ create table Course (CourseID int primary key, CourseName varchar(50), InstructorID int, AcademicYear int, Semester smallint); create table Student (StudentID int primary key, LastName varchar (50), FirstName varchar (50), Email varchar(30), PhoneNumber varchar (20)); create table Enrollment (CourseID int references Course(CourseID), StudentID int references Student(StudentID), RegisterDate date, primary key (CourseID, StudentID)); create table Fine (StudentID int references Student(StudentID), IssueDate date, Amount money, PaidDate date primary key (StudentID, IssueDate));
Using the Henry Books database, add the new title from Stephen King to the wrote table: Book code = 3578 Author number = 6 Edition = 1 You will insert 2 snips for this question. What construct did you use to add a row to the wrote table. Insert the snip of the construct used: Insert the snip of the wrote table. Be sure the new row is displayed:
Employee(empno, ename, street, city, mgrssn) work_project(eno, pno, number_of_hours) Write a query and optimize it to find employee name and project number in which they work and who live in "New_york". Identify the type of indexing to be applied on 'empno' in Employee Table.
Richardson Ski Racing (RSR) sells equipment needed for downhill ski racing. One of RSR's products is fencing used on downhill courses. The fence product comes in 150-foot rolls and sells for $215 per roll. However, RSR offers quantity discounts. The following table shows the price per roll depending on order size: Quantity Ordered To Price per Roll From 1 70 $215 71 140 $195 141 280 $175 281 and up $155 Click on the datafile logo to reference the data. DATA file (a) Use the VLOOKUP function with the preceding pricing table to determine the total revenue from these orders. (b) Use the COUNTIF function to determine the number of orders in each price bin. From Number of To Price per Roll 70 $215 !!! 140 $195 280 $175 and up $155 1 71 141 281 Orders 172
Q2: Design Database for the following scenario and Write SQL queries. Create an ERD for the following scenario. Suppose there is a grocery store near your house. Following can be considered for ERD: A grocery store may have more than one employee. A grocery store has exactly one manager. The manager has one or more sales men working under him. Grocery store has more than one portion for the products. • Each product has a barcode, name, expired date. Many customers can busy many products, but each product is bought by only one customer. Each customer will get an invoice for his /her purchase. The bill invoice has an id.
Refer to the film and inventory tables of the Sakila database. The tables in this lab have the same columns and data types but fewer rows. Write a query that lists the titles of films with the fewest rows in the inventory table. This query requires a subquery that computes the minimum of counts by film_id: SELECT MIN(count_film_id) FROM ( SELECT COUNT(film_id) AS count_film_id FROM inventory GROUP BY film_id ) AS temp_table; This subquery is provided in the template.
Question 5 Suppose that you have the following table: CREATE TABLE coupons ( coupon id INTEGER name VARCHAR2 (30) duration INTERVAL YEAR (3) TO MONTH Now, considers the following INSERT statement: INSERT INTO coupons (coupon id, name, duration) VALUES (4, 'BD1.5 Corn Flakes, INTERVAL 1- 3 YEAR TO MONTH) The above INSERT statement stores the following data: A. 4 BD1.5 Corn Flakes +000-14 4 BD1.5 Corn Flakes +014-00 4 BD1.5 Corn Flakes +014 None of the above
Task B. Import the data shown in pictures into the two tables created.
Below are some rows of the PROVIDERS table for a factory: PCODE 10192 98411 0489035 NAME Hans, Inc. Tools and More Motors Inc. PHONE 231 - 3092 231-1029 993 - 1821 All providers from the factory zone of the city start with 231. You want to get the providers that are in the factory zone of the city. Which of the following is more appropriate? O a. O b. SELECT * FROM PROVIDERS WHERE PHONE LIKE '% 231' O C. O d. SELECT * FROM PROVIDERS WHERE PHONE LIKE $231%' SELECT * FROM PROVIDERS WHERE PHONE LIKE 231 SELECT * FROM PROVIDERS WHERE PHONE LIKE '%231%'
List the names of all Staff members who hold a faculty position and have a salary of $45,000 or less. Show their names as their first name, followed by a space, and then their last name but sort the list alphabetically by last name and then first name. Hint: Check out the Position column in the Staff table. All staff members who do not hold a faculty position will get a bonus this year equal to 5% of their salary. List the names, current salary, and bonus amount for each Staff member (non-faculty) who is due a bonus. Show your list in order with the person receiving the highest bonus first.