Design a schema in SQL for a Library System. Show one example each for PRIMARY KEY and FOREIGN KEY constraint. Create one assertion for the following Constraint:

“No member can borrow more than three books at a time"

Mumbai University > Information Technology > Sem 5 > Advanced Database Management System

Marks: 10M

ER Diagram for Library System is shown below:

Converting ER to Schema:

Book

| BookId | Author | ISBN | Published_Year | Remarks | |--------|--------|------|----------------|---------|

User

| Username | First_Name | Last_Name | Password |
|----------|------------|-----------|----------|

Lending

| Lending_Id | Username | Book_Id | Date_Time_borrowed |
|------------|----------|---------|--------------------|

SQL for above schema:

CREATE TABLE Book
(
Book_Id int NOT NULL PRIMARY KEY,
Author varchar(255) NOT NULL,
ISBN varchar(255),
Published_Year varchar(255),
Remarks varchar(255)
)

CREATE TABLE User
(
Usernamevarchar(255) NOT NULL PRIMARY KEY,
First_Name varchar(255),
Last_Name varchar(255),
Password varchar(255)
)

CREATE TABLE Lending
(
Lending_Id int NOT NULL PRIMARY KEY,
Username varchar(255) FOREIGN KEY REFERENCES User(Username),
Book_IdintFOREIGN KEY REFERENCES Book(Book_Id),
Date_Time_borrowed varchar(255)
CONSTRAINT chk_borrow CHECK (COUNT(UNIQUE(Lending_Id)) < 4)
)

Constraint clause is used check that unique count of ID is less than 4 which mean not more than 3 transactions have occurred.