VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources]

Chris Fehily

نسخه متنی -صفحه : 169/ 23

The Sample Database

Pick up an SQL or database-design book, and probably you''ll find a students/courses/ teachers, customers/orders/products, or authors/books/publishers database. In a bow to convention, most of the SQL examples in this book use an authors/books/publishers sample database named books. Here are some things that you should know about books:

Recall from "Tables, Columns, and Rows" earlier in this chapter that a database appears to the user as a collection of tables (and nothing but tables). books contains five tables that contain information about authors, titles they''ve published, their publishers, and their royalties.
Figure 2.21 depicts the tables and relationships in books by using the graphical conventions introduced earlier in this chapter.

Figure 2.21. The sample database books.
The SQL statements in Chapters 10 and later modify data in books (rather than just retrieve data). Unless I note otherwise, each new section in a chapter starts with a pristine copy of books. In other words, assume that database changes made in one section don''t carry over to the next section.
Run one of the DBMS-specific SQL scripts listed in Appendix A to create (or re-create) the database books on your own DMBS. You can download these scripts (or a fully populated Microsoft Access database) over the internet; see the "Companion Web Site" sidebar in "About This Book" in the introduction.
Some of the concepts mentioned in this section, such as data types and nulls, are covered in the next chapter.
books is a teaching tool; its structure and size don''t approach the complexity of real production databases.
books is unchanged from the earlier edition of this book. You don''t have to reinstall it if you already have it on your computer.

The table
authors

The table authors describes the books'' authors. Each author has a unique identifier that''s the primary key.

Table 2.3 shows the structure of the table authors, and

Figure 2.22 shows its contents.

Figure 2.22. The contents of the table `authors`.

au_id au_fname  au_lname    phone         address             city           state zip  
----- --------- ----------- ------------ -------------------- -------------- ----- -----
A01   Sarah     Buchman     718-496-7223 75 West 205 St       Bronx          NY    10468
A02   Wendy     Heydemark   303-986-7020 2922 Baseline Rd     Boulder        CO    80303
A03   Hallie    Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco  CA    94123
A04   Klee      Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco  CA    94123
A05   Christian Kells       212-771-4680 114 Horatio St       New York       NY    10014
A06             Kellsey     650-836-7128 390 Serra Mall       Palo Alto      CA    94305
A07   Paddy     O''Furniture 941-925-0752 1442 Main St         Sarasota       FL    34236

Table 2.3.
authors Table Structure
C OLUMN N AME	D ESCRIPTION	D ATA T YPE	N ULLS ?	K EYS
`au_id`	`Unique author identifier`	`CHAR(3)`		PK
`au_fname`	`Author first name`	`VARCHAR(15)`
`au_lname`	`Author last name`	`VARCHAR(15)`
`phone`	`Author telephone number`	`VARCHAR(12)`	`Yes`
`address`	`Author address`	`VARCHAR(20)`	`Yes`
`city`	`Author city`	`VARCHAR(15)`	`Yes`
`state`	`Author state`	`CHAR(2)`	`Yes`
`zip`	`Author zip (postal) code`	`CHAR(5)`	`Yes`

The table
publishers

The table publishers describes the books'' publishers. Every publisher has a unique identifier that''s the primary key.

Table 2.4 shows the structure of the table publishers, and

Figure 2.23 shows its contents.

Figure 2.23. The contents of the table `publishers`.

pub_id pub_name            city          state country
------ ------------------- ------------- ----- -------
P01    Abatis Publishers   New York      NY    USA
P02    Core Dump Books     San Francisco CA    USA
P03    Schadenfreude Press Hamburg       NULL  Germany
P04    Tenterhooks Press   Berkeley      CA    USA

Table 2.4.
publishers Table Structure
C OLUMN N AME	D ESCRIPTION	D ATA T YPE	N ULLS	K EYS
`pub_id`	`Unique publisher identifier`	`CHAR(3)`		PK
`pub_name`	`Publisher name`	`VARCHAR(20)`
`city`	`Publisher city`	`VARCHAR(15)`
`state`	`Publisher state/province`	`CHAR(2)`	`Yes`
`country`	`Publisher country`	`VARCHAR(15)`	`Yes`

The table
titles

The table titles describes the books. Every book has a unique identifier that''s the primary key. titles contains a foreign key, pub_id, that references the table publishers to indicate a book''s publisher.

Table 2.5 shows the structure of the table titles, and

Figure 2.24 shows its contents.

Figure 2.24. The contents of the table `titles`.

[View full width]

title_id title_name                           type       pub_id pages price sales  
 pubdate    contract
-------- ------------------------------------ ---------- ------ ----- ----- -------
 ---------- --------
T01      1977!                                history    P01      107 21.99     566
 2000-08-01        1
T02      200 Years of German Humor            history    P03       14 19.95    9566
 1998-04-01        1
T03      Ask Your System Administrator        computer   P02     1226 39.95   25667
 2000-09-01        1
T04      But I Did It Unconsciously           psychology P04      510 12.99   13001
 1999-05-31        1
T05      Exchange of Platitudes               psychology P04      201  6.95  201440
 2001-01-01        1
T06      How About Never?                     biography  P01      473 19.95   11320
 2000-07-31        1
T07      I Blame My Mother                    biography  P03      333 23.95 1500200
 1999-10-01        1
T08      Just Wait Until After School         children   P04       86 10.00    4095
 2001-06-01        1
T09      Kiss My Boo-Boo                      children   P04       22 13.95    5000
 2002-05-31        1
T10      Not Without My Faberge Egg           biography  P01     NULL  NULL    NULL NULL  
            0
T11      Perhaps It''s a Glandular Problem     psychology P04      826  7.99   94123
 2000-11-30        1
T12      Spontaneous, Not Annoying            biography  P01      507 12.99  100001
 2000-08-31        1
T13      What Are The Civilian Applications?  history    P03      802 29.99   10467
 1999-05-31        1

Table 2.5.
titles Table Structure
C OLUMN N AME	D ESCRIPTION	D ATA T YPE	N ULLS ?	K EYS
`title_id`	`Unique title identifier`	`CHAR(3)`		PK
`title_name`	`Book title`	`VARCHAR(40)`
`type`	`Subject of the book`	`VARCHAR(10)`	`Yes`
`pub_id`	`Publisher identifier`	`CHAR(3)`	`Yes`	FK publishers(pub_id)
`pages`	`Page count`	`INTEGER`	`Yes`
`price`	`Cover price`	`DECIMAL(5,2)`	`Yes`
`sales`	`Lifetime number of copies sold`	`INTEGER`	`Yes`
`pubdate`	`Date of publication`	`DATE`	`Yes`
`contract`	`Nonzero if author(s) signed contract`	`SMALLINT`	`Yes`

The table
title_authors

Authors and books have a many-to-many relationship, because an author can write multiple books and a book can have multiple authors. title_authors is the junction table that associates the tables authors and titles; see "Relationships" earlier in this chapter. title_id and au_id together form a composite primary key, and each column separately is a foreign key that references titles and authors, respectively. The nonkey columns indicate the order of the author''s name on the book''s cover (always 1 for a book with a sole author) and the fraction of total royalties that each author receives (always 1.0 for a book with a sole author).

Table 2.6 shows the structure of the table title_authors, and

Figure 2.25 shows its contents.

Figure 2.25. The contents of the table `title_authors`.

title_id au_id au_order royalty_share
-------- ----- -------- -------------
T01      A01          1          1.00
T02      A01          1          1.00
T03      A05          1          1.00
T04      A03          1          0.60
T04      A04          2          0.40
T05      A04          1          1.00
T06      A02          1          1.00
T07      A02          1          0.50
T07      A04          2          0.50
T08      A06          1          1.00
T09      A06          1          1.00
T10      A02          1          1.00
T11      A03          2          0.30
T11      A04          3          0.30
T11      A06          1          0.40
T12      A02          1          1.00
T13      A01          1          1.00

Table 2.6.
title_authors Table Structure
C OLUMN N AME	D ESCRIPTION	D ATA T YPE	K EYS
`title_id`	`Title identifier`	`CHAR(3)`	PK, FK titles(title_id)
`au_id`	`Author identifier`	`CHAR(3)`	PK, FK authors(au_id)
`au_order`	`Author name order on book cover`	`SMALLINT`
`royalty_share`	`Author fractional royalty share`	`DECIMAL(5,2)`

The table
royalties

The table royalties specifies the royalty rate paid to

all the authors (not each author) of each book, including the total up-front advance against royalties paid to all authors (again, not each author) of a book. The royalties primary key is title_id. royalties has a one-to-one relationship with titles, so the royalties primary key also is a foreign key that references the titles primary key.

Table 2.7 shows the structure of the table royalties, and

Figure 2.26 shows its contents.

Figure 2.26. The contents of the table `royalties`.

title_id advance     royalty_rate
-------- ----------- ------------
T01         10000.00         0.05
T02          1000.00         0.06
T03         15000.00         0.07
T04         20000.00         0.08
T05        100000.00         0.09
T06         20000.00         0.08
T07       1000000.00         0.11
T08             0.00         0.04
T09             0.00         0.05
T10             NULL         NULL
T11        100000.00         0.07
T12         50000.00         0.09
T13         20000.00         0.06

Table 2.7.
royalties Table Structure
C OLUMN N AME	D ESCRIPTION	D ATA T YPE	N ULLS ?	K EYS
`title_id`	`Unique title identifier`	`CHAR(3)`	N ULLS ?	PK, FK titles(title_id)
`advance`	`Upfront payment to author(s)`	`DECIMAL(9,2)`	`Yes`
`royalty_rate`	`Fraction of revenue paid author(s)`	`DECIMAL(5,2)`	`Yes`

VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources]

Chris Fehily

The Sample Database

Figure 2.21. The sample database books.

The table authors

Figure 2.22. The contents of the table authors.

Table 2.3. authors Table Structure

The table publishers

Figure 2.23. The contents of the table publishers.

Table 2.4. publishers Table Structure

The table titles

Figure 2.24. The contents of the table titles.

Table 2.5. titles Table Structure

The table title_authors

Figure 2.25. The contents of the table title_authors.

Table 2.6. title_authors Table Structure

The table royalties

Figure 2.26. The contents of the table royalties.

Table 2.7. royalties Table Structure

Figure 2.21. The sample database `books`.

The table
authors

Figure 2.22. The contents of the table `authors`.

Table 2.3.
authors Table Structure

The table
publishers

Figure 2.23. The contents of the table `publishers`.

Table 2.4.
publishers Table Structure

The table
titles

Figure 2.24. The contents of the table `titles`.

Table 2.5.
titles Table Structure

The table
title_authors

Figure 2.25. The contents of the table `title_authors`.

Table 2.6.
title_authors Table Structure

The table
royalties

Figure 2.26. The contents of the table `royalties`.

Table 2.7.
royalties Table Structure