BIG QUESTION PLEASE READ IT CAREFULLY & SQL PART.....It will be really helpful i

Question

BIG QUESTION PLEASE READ IT CAREFULLY & SQL PART.....It will be really helpful if someone explains and answers all the question so that it's easy to understand and try next one on my own...PLEASE ANSWER ALL 3 PARTS INCLUDING SQL COMMAND IN PART 2 "DELETE STATEMENT"

Given the following table:

library(book_id,book_title,person_id,due_date,first_name,last_name,returned_date,late_fee)

With the following assumptions: -

One copy of each book is held by the library -

Person_id is unique for each person, book_id is unique for each book -

The library table is meant to store information about loans of books made by the library.

Here are the functional dependencies for the above table:

Book_id book_title

Person_id first_name,last_name

Book_id,person_id,due_date returned_date,late_fee.

Q1) Create three tables based off the table from question .

The first table should be:

1. persons(person_id, first_name, last_name) (person_id is the primary key).

2. books(book_id,book_title) (book_id is the primary key).

3. loans(person_id,book_id,due_date,returned_date,late_fee) (person_id,book_id,due_date is the primary key).

In the loans table, person_id and book_id should be foreign keys, each referring to the appropriate table. You should set things up so that if a book referenced in the loans table is deleted from the books table, a default value is put in it’s place.

Q2) Demonstrate with SQL statements that your delete works

Q3) Why is the on delete… that Q1 asks you to set up a bad idea?

Explanation / Answer

1. To create the tables, SQL statement CREATE TABLE... is used. In this case, we will need to create three tables. They can be created in the following ways-
a) CREATE TABLE persons (person_id INTEGER PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20))

/*--- this creates table named 'persons', having attributes person_id, which is an integer and primary key for the table, first_name and last_name are both of type varchar of length 20 ---*/

SImilarly,
b) CREATE TABLE books (book_id INTEGER PRIMARY KEY, book_title VARCHAR(50))

c) CREATE TABLE loans(
person_id INTEGER, book_id INTEGER, due_date DATETIME, returned_date DATETIME, late_fee INTEGER,
PRIMARY KEY(person_id, book_id, due_date),
FOREIGN KEY(person_id) REFERENCES persons(person_id),
FOREIGN KEY(book_id) REFERENCES books(book_id) ON DELETE SET NULL
)

/*--- NOtice different implementation of primary key constraint. Also, foreign key on book_id has ON DELETE SET NULL, meaning, if an entry from books table is deleted, a default value of null will be set in its place in loans table. ---*/

2. To demonstrate that delete is working, let us add an entry to books table, then delete it -

INSERT INTO books VALUES(1,"New Book");

DELETE FROM books WHERE book_id = 1;

This will delete the entry.

3. Suppose book A with book_id 10134 was loaned to person with person_id 1123, and book B with id 10164 was assigned to another person, say 1442. This data existed in your loans table. Now, if you delete books A and B from your table, in the loans table a default value if null will appear, and the book loaning information will be lost. Hence, it is a bad idea to proceed with this way in a real-life situation.

Our database should have, in a real world, restricted the deletion of book's information if it had been loaned to a person. Thus, a better database design should have had ON DELETE RESTRICT, instead of ON DELETE SET NULL.

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.