Given the following table: library(book_id,book_title,person_id,due_date,first_n

Question

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

Create the table from question above in postgres sql.

Follow these guidelines:

1. Choose a suitable primary key for the table (You are given the FD’s, so this should be easy).

2. Add some constraints to the table:

a. Book_title, first, and last should not be null (you may have to look this up online).

b. If returned_date is after due_date then late_fee must be greater than 0.

c. Late_fee cannot be less than 0.

I am getting strange errors when i am putting my statements in postgres .......any detailed explanation would be helpful....

PLEASE NOTE THIS QUESTION NEEDS TO BE DONE IN POSTGRES......SQL COMMAND

Explanation / Answer

Hi,
primary key is the one with which all columns in a row can be identified,hence the primary key will be
Book_id,person_id,due_date.
to give not null constraint to a column, we have to use NOT NULL.
to have a constraint on values we can use CHECK like

Thumbs up if this was helpful, otherwise let me know in comments.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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