Real-World Example: Designing a Library Database

Intermediate

🏗️ Real-World Example: Designing a Library Database

Let’s build a simplified Library Management System schema:

+-----------+         +-------------+        +---------------+
|  Books    |         |  Members    |        |   Loans       |
+-----------+         +-------------+        +---------------+
| BookID PK |<-----+  | MemberID PK |        | LoanID PK     |
| Title     |      |  | Name        |   +--> | BookID FK     |
| Author    |      +--| JoinDate    |   |    | MemberID FK   |
+-----------+         +-------------+   |    | LoanDate      |
                                        +----| ReturnDate    |
                                             +---------------+

SQL Schema:

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name NVARCHAR(100),
    JoinDate DATE
);

CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    BookID INT FOREIGN KEY REFERENCES Books(BookID),
    MemberID INT FOREIGN KEY REFERENCES Members(MemberID),
    LoanDate DATE,
    ReturnDate DATE
);