Database Schema: branch ( branch_name, branch_city, assets ) customer ( ID, customer_name, customer_street, customer_city ) loan ( loan_number, branch_name, amount ) borrower ( ID, loan_number ) account ( account_number, branch_name, balance ) depositor ( ID, account_number ) Write a (postgresql) "trigger function" and "create a trigger" to carry out the following action: On delete of an account, for each customer-owner of the account, check if the owner has any remaining accounts, and if she does not, delete her from the depositor relation.

Database Schema: branch ( branch_name, branch_city, assets ) customer ( ID, customer_name, customer_street, customer_city ) loan ( loan_number, branch_name, amount ) borrower ( ID, loan_number ) account ( account_number, branch_name, balance ) depositor ( ID, account_number ) Write a (postgresql) "trigger function" and "create a trigger" to carry out the following action: On delete of an account, for each customer-owner of the account, check if the owner has any remaining accounts, and if she does not, delete her from the depositor relation.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 11RQ

See similar textbooks

Similar questions

Which of the following database operations should be handled with extra care?
1.14) Update anomalies are known as insertion anomalies, deletion anomalies, and modification anomaliesTrue False 1.15) A well-designed database can handle both OLTP and OLAP operations efficientlyTrue False 1.16) Database engine uses statistics to generate estimated execution plans then picks the best oneTrue False 
17.1 (Books Database) Write a Python script, perform each of the following tasks on the books database (books.db) from Section 17.2: a) Select all authors’ last names from the authors table in descending order. Print the result. b) Select all book titles from the titles table in ascending order. Print the result. c) Use an INNER JOIN to select all the books for a specific author. Include the title, copyright year and ISBN. Order the information alphabetically by title. Print the result. d) Insert a new author into the authors table. Print the result. e) Insert a new title for an author. Remember that the book must have an entry in the author_ISBN table and an entry in the titles table. Print the result.
Answer the given question with a proper explanation and step-by-step solution. . Complete the following: • Create a view called gl_stdV1 that returns the required data • Create an anonymous block that: o Accesses gl_stdV1
Referring to Appendix A below, code a MySQL statement to display the sales order number and customer name for all sales orders. APPENDIX A – Table definitions and initializations-- MySQL script to initialize a MySQL database and four tables:-- customer, detail, item, salesorder--DROP DATABASE IF EXISTS finaldb;CREATE DATABASE finaldb;USE finaldb;CREATE TABLE customer ( c_num varchar(16), c_name varchar(128));CREATE TABLE detail ( d_onum varchar(16), d_inum varchar(16), d_qty int);CREATE TABLE item ( i_num varchar(16), i_name varchar(128), i_price decimal(9,2), i_qty int);CREATE TABLE salesorder ( s_num varchar(16), s_cnum varchar(16), s_date date);INSERT INTO customer VALUES ('c101','Sam Paint'), ('c102','Ramon Hardware'), ('c103','Li Auto');--INSERT INTO detail VALUES ('s201','i301',21), ('s201','i302',22), ('s202','i302',23), ('s202','i303',24), ('s203','i303',25), ('s203','i304',26);--INSERT INTO item VALUES…
Database Systems Each branch of the Jaja restaurant has a mainstay menu that is very popular with customers, so information on the availability of the number of servings is needed to be able to serve orders. Here are two transactions that are ordering a food menu at the same time. T1: Start the transaction at time t1, read 10 servings of food stock at t2. Because ordering one portion, the stock is reduced by 1 at t3. On t6, update the stock count, and commit to t7. T2: Transaction T2 starts a transaction on t2, reads the number of stocks in t3 as many as 10. At t4 orders 3 portions. On t5 it updates the stock to 7 and then on t6 commits it. As a result of the T1 and T2 transactions above, there was an error in the amount of food stocks. Make a solution with 2PL. What kinds of problems happened? Give a reason why choosing this type of problem! How much food is still available?
2. DEL (30 %): Transactions:The following transaction schedule is given. A,B,C are the addresses of the recordsin the database and T1, T2 and T3 are the transactions.T1 R(A)W(A) R(C) R(B)W(B)C-----------------------------------------------------------------------------------------------------------------T2 R(B) W(B) R(A) W(A)C-----------------------------------------------------------------------------------------------------------------T3 R(C) R(B)W(B) Ca) Show an example of a strict 2PL implementation that leads to a deadlockb) For a) draw a »wait-for« graphc) Avoid the previously drawn deadlock with a »wait-die« policyd) Give an example of another schedule that would be conflict-equivalent to thegiven schedule of transactions
This is my attempt at DBDL statements trying to plan a database in MySQL. Employers: CREATE TABLE Employers ( EmployerID INT PRIMARY KEY, CompanyName VARCHAR(255), Location VARCHAR(255), ContactInfo VARCHAR(255), ); Customers: CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, Name VARCHAR(255), ContactInfo VARCHAR(255), Education VARCHAR(255), Experience INT ); Reference: CREATE TABLE References ( ReferenceID INT PRIMARY KEY, Name VARCHAR(255), ContactInfo VARCHAR(255), RelationshipToCustomer VARCHAR(255) ); Skills: CREATE TABLE Skills ( SkillID INT PRIMARY KEY, SkillName VARCHAR(255), ProficiencyLevel VARCHAR(255) ); EmploymentPositions: CREATE TABLE EmploymentPositions ( PositionID INT PRIMARY KEY, PostionTitle VARCHAR(255), EmployerID INT, Duration VARCHAR(255), FOREIGN KEY (EmployerID) REFERENCES Employers(EmployerID) ); Interviews: CREATE TABLE Interviews ( InterviewID INT PRIMARY KEY, InterviewDate DATE, Interviewer VARCHAR(255), Feedback VARCHAR(255) ); HiringStatus:…
Given the following relational database schema (primary keys are bold and underlined). Answer questions 4.1 to 4.2: branch(branch_id, branch_name, branch_city, assets) customer(customer_id, customer_name, customer_street, customer_city, customer_st, customer_zip) loan(loan_number, branch_id, amount) borrower(customer_id, loan_number) account(account_number, branch_id, balance) depositor(customer_id, account_number) 4.1 For each of the following queries, write a relational algebra expression to answer the query: a) Find the names of all customers who live in Plymouth and have the name Bronson. b) Find the names and zip codes of all customers who have loans. c) Find the names, cities of residence, and state for all depositors who have accounts with balance that is less than $5000 4.2 For each of the following relational algebra expressions, explain the output of the expression in words:
Referring to Appendix A below, code a MySQL statement to display the item number and name for all items. APPENDIX A – Table definitions and initializations-- MySQL script to initialize a MySQL database and four tables:-- customer, detail, item, salesorder--DROP DATABASE IF EXISTS finaldb;CREATE DATABASE finaldb;USE finaldb;CREATE TABLE customer ( c_num varchar(16), c_name varchar(128));CREATE TABLE detail ( d_onum varchar(16), d_inum varchar(16), d_qty int);CREATE TABLE item ( i_num varchar(16), i_name varchar(128), i_price decimal(9,2), i_qty int);CREATE TABLE salesorder ( s_num varchar(16), s_cnum varchar(16), s_date date);INSERT INTO customer VALUES ('c101','Sam Paint'), ('c102','Ramon Hardware'), ('c103','Li Auto');--INSERT INTO detail VALUES ('s201','i301',21), ('s201','i302',22), ('s202','i302',23), ('s202','i303',24), ('s203','i303',25), ('s203','i304',26);--INSERT INTO item VALUES ('i301','Doormat',6.00,80),…
You may choose to respond to one or both of the statements below: It is impossible to generate useful information from faulty data sources.However complex the application programmes may be, it is hard to employ good application programmes to overcome the disadvantages of poor database architecture due to the nature of the data.
What exactly is the index? What are the many sorts of indices?