Relational Database Overview: A books Database
We now overview relational databases in the context of a sample books database we created for this chapter. Before we discuss SQL, we overview the tables of the books database. We use this database to introduce various database concepts, including how to use SQL to ob-tain information from the database and to manipulate the data. We provide a script to cre-ate the database. You can find the script in the examples directory for this chapter. Section 22.8 explains how to use this script.
The database consists of three tables—authors, authorISBN and titles. The authors table (described in Fig. 22.3) consists of three columns that maintain each author’s unique ID number, first name and last name. Figure 22.4 contains sample data from the authors table of the books database.
The authorISBN table (described in Fig. 22.5) has two columns that maintain each ISBN and the corresponding author’s ID number. This table associates authors with their books. The columns represent the relationship between the authors and titles tables
one row in authors may be associated with many rows in titles, and vice versa. Figure 22.6 contains sample data from the authorISBN table. [Note: To save space, we split the table’s contents into two columns, each containing the authorID and isbn columns.] The authorID column (and similarly, the isbn column) is a foreign key—a column in this table that matches the primary-key column in another table (i.e., authorID in the authors table). Foreign keys are specified when creating a table. The foreign key helps maintain the Rule of Referential Integrity—every foreign-key value must appear as another table’s pri-mary-key value. This enables the DBMS to determine whether the authorID value for a particular book is valid. Foreign keys also allow related data in multiple tables to be selected from those tables for analytic purposes—this is known as joining the data. If you were to remove an author from the authors table, you’d also want to remove the corre-sponding rows in the authorISBN table.
The titles table described in Fig. 22.7 consists of four columns that stand for each book’s ISBN, the title, the edition number and the copyright year. The table is in Fig. 22.8.
There is a one-to-many relationship between a primary key and a corresponding for-eign key (e.g., one author can write many books). A foreign key creates a relationship between two tables in which the record with a given primary key can be referenced many times in the foreign key’s table. Figure 22.9 is an entity-relationship (ER) diagram for the books database. This diagram shows the database tables and the relationships among them. The first compartment in each box contains the table’s name. The names in italic are primary keys. A table’s primary key uniquely identifies each row in the table. Every row must have a primary-key value, and that value must be unique in the table. This is known as the Rule of Entity Integrity.
The lines connecting the tables in Fig. 22.9 represent the relationships between the tables. Consider the line between the authorISBN and authors tables. On the authors end of the line, there is a 1, and on the authorISBN end, there is an infinity symbol (∞ ), indi-cating a one-to-many relationship in which every author in the authors table can have an arbitrary number of ISBNs in the authorISBN table. Note that the relationship line links the authorID column in the table authors (i.e., its primary key) to the authorID column in table authorISBN (i.e., its foreign key). The authorID column in the authorISBN table is a foreign key.
The line between the titles and authorISBN tables illustrates another one-to-many relationship; a title can be written by any number of authors. In fact, the sole purpose of the authorISBN table is to provide a many-to-many relationship between the authors and titles tables—an author can write any number of books and a book can have any number of authors. The primary key for authorISBN is the combination of authorID and ISBN.
Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.