Perform the following operation in sql for insertion, updating and deletion using the referential integrity constraints Form 1Title: The Joy of E-R Modelling Published_In 2013ISBN: 939757Author Id: AU505Author Name : MossAuthor Id:Author Name :CopyNo C1034Shelf Number C100DateAcquired: 12-APR-16Borrower IdBorrower NameBorrower Dept Loan DateReturn DateA12B34BloggsGreenacreCIS02-OCT-1613-OCT-16Business13-OCT-1627-ОСТ-16Form 2Title: Normalisation is Fun and Easy Published_In 2010 ISBN: 549950Author Id AU1911Author Id AU505Author Name :BollingerAuthor Name : MossCopyNo C2399Shelf Number: C101 DateAcquired: 22-APR-2011Borrower IdBorrower NameBorrower Dept Loan DateReturn DateB34GreenacreFranksBusiness04-OCT-1618-OCT-16L67Leisure22-ОСТ-1629-OCT-16Form 3Title: Normalisation is Fun and Easy Published_In 2010 ISBN: 549950Author Id AU1911Author Name : BollingerAuthor Id AU505Author Name : MossCopyNo C2400Shelf Number: C101 DateAcquired: 25-JUN-2011Borrower IdBorrower NameBorrower Dept Loan DateReturn Date25 ОСТ-16BloggsFranksCIS04-OCT-16L67Leisure02-SEP-1609-SEP-16 Figure 2: List of AssumptionsThe library acquires one or many copies of books, these are physical copies of ai.particularpublication.A publication has a unique ISBN (an international code for all published works); atitle andii.the date it was published.Each copy has a unique copy number and has a particular shelf number to which it isiii.|located.2 |3A publication is always written by either one or two authors, and an author maywrite many publications.Only authors who have written at least one publication will be stored.iv.V.vi.A borrower may have more than one copy on loan at any time.vii. A borrower may yet to take out a loanviii. Over time a copy may be out on loan many times or it may never be loaned out.ix|A loan is made by a single borrower for one copy of a publication.

Four tables are created using the below table scripts. The table design it already taking the…

Answered: Perform the following operation in sql…

Engineering

Computer Engineering

Perform the following operation in sql for insertion, updating and deletion using the referential integrity constraints

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Q: Display the list of students who are involved in the IT club Display the details of all the…

A: First get the studid from Club table and then get those Student details This can be done using inner…

Q: How does the COALESCE function compare with the ISNULL function when handling NULL values in SQL

A: The COALESCE and ISNULL functions are both used to handle NULL values in SQL. However, they have…

Q: How can the OVER clause enhance the flexibility of window functions in SQL?

A: Window functions in SQL enable users to carry out calculations across a set of table rows related to…

Q: 1. Run an update query that converts the uppercase names in the database into normal case (i.e.…

A: To convert the FirstName and LastName to proper case UPDATE query with StrConv can be used. The…

Q: Please written by computer source 1. For this project, you need to create the company database and…

A: Step 1: Create the Company Database Open the Oracle SQL PLUS environment and create a new database…

Q: simplifying conditional logic in SQL?

A: The CHOOSE function is a powerful tool in SQL that simplifies conditional logic by allowing…

Q: 13 The find() method in MongoDB can accept a query object and a projection object. 14 In MongoDB a…

A: In MongoDB, the find() method allows querying documents with a query object and specifying fields to…

Q: Write a simple PL/SQL function which has the following definition. Refer to the "EMP" below for…

A: PL/SQL which refers to the one it is the purpose of PL/SQL in order to combine database language…

Q: Create a PL/SQL function to increment the salary of all employees of an organization by 15%, and for…

A: Creating a PL/SQL function to increment the salary of all employees of an organization by 15%, and…

Q: Write and execute a SQL query to list all patients whose information about race or gender has…

A: Answer: SQL Query: Method 1: select vvs.*, lag(race) over (partition by user order by age) as…

Q: Write a query to display the first name, last name, and email address of employees hired from…

A: In this question we have to write a query to display the first name, last name, and email address of…

Q: Write PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit…

A: Actually, SQL stands for Structured Query Language.

Q: 5 3 EID john bob ENAME DID HIREDATE 2 09/01/2006 55000 01/01/2000 90000 1 jeff 01/01/2005 73000 2 2…

A: Introduction to PL/SQL:PL/SQL, which stands for Procedural Language/Structured Query Language, is…

Q: What is the significance of the ROW_NUMBER function when dealing with duplicate rows in SQL?

A: SQL stands for Structured Query Language which is used to communicate with a database. It is the…

Q: Please answer the following in SQL: Find all pairs of customers who have purchased the exact same…

A: Initially join all the tables as we have to basically need goods. csv table to check the flavor at…

Q: Create a PL/SQL database programming block with DECLARE BEGIN END; In Declare section define…

A: Below is the PLSQL block code:

Q: Write and execute a SQL query to list all patient numbers and encounter ids which do not have any…

A: Actually, SQL stands for Structured Query Language.

Q: How can the CAST function be combined with other functions to enhance data type compatibility in SQL…

A: The CAST function can be combined with other functions to enhance data type compatibility in SQL…

Q: What advantages does the ISNULL function offer when dealing with missing data in SQL?

A: In the database management , handling NULL values is a challenge that SQL developers encounter…

Question

Perform the following operation in sql for insertion, updating and deletion using the referential integrity constraints

Form 1
Title: The Joy of E-R Modelling Published_In 2013
ISBN: 939757
Author Id: AU505
Author Name : Moss
Author Id:
Author Name :
CopyNo C1034
Shelf Number C100
DateAcquired: 12-APR-16
Borrower Id
Borrower Name
Borrower Dept Loan Date
Return Date
A12
B34
Bloggs
Greenacre
CIS
02-OCT-16
13-OCT-16
Business
13-OCT-16
27-ОСТ-16
Form 2
Title: Normalisation is Fun and Easy Published_In 2010 ISBN: 549950
Author Id AU1911
Author Id AU505
Author Name :Bollinger
Author Name : Moss
CopyNo C2399
Shelf Number: C101 DateAcquired: 22-APR-2011
Borrower Id
Borrower Name
Borrower Dept Loan Date
Return Date
B34
Greenacre
Franks
Business
04-OCT-16
18-OCT-16
L67
Leisure
22-ОСТ-16
29-OCT-16
Form 3
Title: Normalisation is Fun and Easy Published_In 2010 ISBN: 549950
Author Id AU1911
Author Name : Bollinger
Author Id AU505
Author Name : Moss
CopyNo C2400
Shelf Number: C101 DateAcquired: 25-JUN-2011
Borrower Id
Borrower Name
Borrower Dept Loan Date
Return Date
25 ОСТ-16
Bloggs
Franks
CIS
04-OCT-16
L67
Leisure
02-SEP-16
09-SEP-16

Figure 2: List of Assumptions
The library acquires one or many copies of books, these are physical copies of a
i.
particular
publication.
A publication has a unique ISBN (an international code for all published works); a
title and
ii.
the date it was published.
Each copy has a unique copy number and has a particular shelf number to which it is
iii.
|located.
2 |3
A publication is always written by either one or two authors, and an author may
write many publications.
Only authors who have written at least one publication will be stored.
iv.
V.
vi.
A borrower may have more than one copy on loan at any time.
vii. A borrower may yet to take out a loan
viii. Over time a copy may be out on loan many times or it may never be loaned out.
ix
|A loan is made by a single borrower for one copy of a publication.

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

Step 2

Step 3

Step 4

Step by step

Solved in 4 steps with 4 images

SEE SOLUTION Check out a sample Q&A here

Similar questions

Translate the following SQL query to a pandas statement in Python so that it can be executed on the pandas DataFrame. SELECT COUNT(*) FROM sqldata WHERE cases > 1000 AND date = "2020-10-10";
How would I create a Update statement in SQL Lite for the following question: The student ‘100004’ gave his phone to student ‘100003’. Make the phone ‘14896572315’ become the alternate phone of student ‘100003’.
PLZ help with the following: Using Oracle SQL Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriatedata types for each attribute and include primary key and foreign key constraints, Check and NotNull constraints.
1) Assume the EMPLOYEE table has the following rowsID DEPT SALARY NAME100 SALES 40000 Smith101 RD 38000 Terry102 HR 60000 David103 SALES 58000 Ellie104 RD 70000 Judya) Provide the complete PL/SQL code to implement a Virtual Private Database suchthat an employee could only view the records for employees in the samedepartment while masking coworkers’ salary with NULL.b) Based on the Virtual Private Database you implemented in step a), would anemployee be able to insert, update or delete a record for another employee whoworks in another department? If yes, what might be the potential security risk andhow would you fix the problem by setting up the VPD appropriately?
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.
Develop a PL/SQL block that would go through all the employees from EMPLOYEES table who work in department id= 90 (there is suppose to be 3 employee who work in Department 90)- IN BEGIN END END SECTION; Execute 3 separate of SELECT statement one after the other. Each SELECT will pull first name. salary of each employee and store them in local variables.+ SELECT LOAD INTO v_firstname1, v_salarxlWHERE emrlexee.id=100 Then second WHERE emploxee.id3101+ SELECT first_name, salary INTO v_firstname2, v_salary2 Then third + SELECTSELECTI tirst.name, salary INTO v_firstname3, v_salary3 iin WHERE emploxee id=102 ..... You must now have 6 local variables. DECLARE all of them accordingly in DECLARE section and use them in BEGIN and END.+ Then add another SELECT statement in BEGIN-END to find company average salary SELECT xa(salary) INTO aKasal FROM EMPLOYEES; !!! Do not forget to create y avgsal local variable in DECLARE section.
What are the potential issues with over-relying on the COALESCE function in SQL?
INFO 2303 Database Programming Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows.
Several new built-in operations are available in SQL;2008. To simplify, just enumerate the four (4) applications.
In Oracle database, if we have any field such as Job_Title field in any of our table then we can use DECODE function to sort the result based on our own numerical value. This can be done by including the following in ORDER BY clause: 1: ORDER BY DECODE(job-title,'Manager',3,Assistant',2,'Sales Rep', 1) DESC II: ORDER BY DECODE(job-title,'Manager', 3,'Assistant',2, Sales Rep', 1) ASC III I and II are correct IV I and II are incorrect ||| IV || 1
Create an anonymous block in PL/SQL to list all the countries from Country table where Currency contain keyword ‘Dollar’. Perform following steps: Create user define type with name of the country, currency.Type should contain datatype for name and currency using %type only.Display should include Name of the country, currency code using user defined type. Filter for the data : All the countries from Country table where Currency contain keyword ‘Dollar’.
My other instruction for SQL says: Create and test a stored procedure called checkin_transaction that processes a “checkin” transaction. When a book is checked in, the corresponding CHECKOUT record is updated to show the date it was checked in, and the Pat_ID attribute in the BOOK table is updated to NULL. The entire transaction (starting with START TRANSACTION; and ending with COMMIT;) should be in between the BEGIN and END statements of the stored procedure. You should be able to call the procedure with a statement like this, passing the values of the Book_Num, Pat_ID, and Check_In_Date to the procedure: CALL checkin_transaction(5243, 1170, ‘2017-04-30’) Do you guys know how?