Page 64 - SQL

Library Database

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

Live example: SQL fiddle

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

https://riptutorial.com/ 46