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 …