Library Database

In this example database for a library, we have Authors, Books and BooksAuthors tables.

Authors and Books are known as base tables, since they contain column definition and data for the actual entities in the relational model. BooksAuthors is known as the relationship table, since this table defines the relationship between the Books and Authors table.

Relationships between tables

Each author can have 1 or more books
Each book can have 1 or more authors

Authors

(view table)

| Id | Name | Country | |:—|:———————|:––––| | 1 | J.D. Salinger | USA | | 2 | F. Scott. Fitzgerald | USA | | 3 | Jane Austen | UK | | 4 | Scott Hanselman | USA | | 5 | Jason N. Gaylord | USA | | 6 | Pranav Rastogi | India | | 7 | Todd Miranda | USA | | 8 | Christian Wenz | USA |

SQL to create the table:

CREATE TABLE Authors (
    Id INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(70) NOT NULL,
    Country VARCHAR(100) NOT NULL,
    PRIMARY KEY(Id)
);

INSERT INTO Authors
    (Name, Country)
VALUES
    ('J.D. Salinger', 'USA'),
    ('F. Scott. Fitzgerald', 'USA'),
    ('Jane Austen', 'UK'),
    ('Scott Hanselman', 'USA'),
    ('Jason N. Gaylord', 'USA'),
    ('Pranav Rastogi', 'India'),
    ('Todd Miranda', 'USA'),
    ('Christian Wenz', 'USA')
;

Books

(view table)

| Id | Title | |:—|:–––––––––––––––––––| | 1 | The Catcher in the Rye | | 2 | Nine Stories | | 3 | Franny and Zooey | | 4 | The Great Gatsby | | 5 | Tender id the Night | | 6 | Pride and Prejudice | | 7 | Professional ASP.NET 4.5 in C# and VB |

SQL to create the table:

CREATE TABLE Books (
    Id INT NOT NULL AUTO_INCREMENT,
    Title VARCHAR(50) NOT NULL,
    PRIMARY KEY(Id)
);

INSERT INTO Books
    (Id, Title)
VALUES
    (1, 'The Catcher in the Rye'),
    (2, 'Nine Stories'),
    (3, 'Franny and Zooey'),
    (4, 'The Great Gatsby'),
    (5, 'Tender id the Night'),
    (6, 'Pride and Prejudice'),
    (7, 'Professional ASP.NET 4.5 in C# and VB')
;

BooksAuthors

(view table)

| BookId | AuthorId | |:—––|:———| | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 3 | | 7 | 4 | | 7 | 5 | | 7 | 6 | | 7 | 7 | | 7 | 8 |

SQL to create the table:

CREATE TABLE BooksAuthors (
    AuthorId INT NOT NULL,
    BookId  INT NOT NULL,
    FOREIGN KEY (AuthorId) REFERENCES Authors(Id),
    FOREIGN KEY (BookId) REFERENCES Books(Id)
);

INSERT INTO BooksAuthors
    (BookId, AuthorId)
VALUES
    (1, 1),
    (2, 1),
    (3, 1),
    (4, 2),
    (5, 2),
    (6, 3),
    (7, 4),
    (7, 5),
    (7, 6),
    (7, 7),
    (7, 8)
;

Examples

View all authors (view live example):

SELECT * FROM Authors;

View all book titles (view live example):

SELECT * FROM Books;

View all books and their authors (view live example):

SELECT
  ba.AuthorId,
  a.Name AuthorName,
  ba.BookId,
  b.Title BookTitle
FROM BooksAuthors ba
  INNER JOIN Authors a ON a.id = ba.authorid
  INNER JOIN Books b ON b.id = ba.bookid
;