Database Structure and Sample Data

Here are brief descriptions of a few tables you'll be working with in the Californiana database:

ca$\_$counties $-$ This table contains basic information about the $58$ counties in California: name, location of the county seat, year in which the county was established, population, and area in square miles.

ca$\_$mountain$\_$ranges $-$ A list of mountain ranges in California.

ca$\_$mountain$\_$peaks $-$ A list of the fifty highest mountain peaks in California, including name, elevation and a pointer to the range to which the peak belongs.

ca$\_$books $-$ A list of books that feature California in some significant way.

ca$\_$authors $-$ A list of authors who have written books about California.

ca$\_$book$\_$authors $-$ A table that facilitates the many$-$to$-$many relationship between books and authors $($since an author may have written more than one book, and books can have more than one author$).$

Examples of rows you'll find in each of these tables are as follows:

ca$\_$counties

county$\_$id name county$\_$seat established population area

$11$ Placer Auburn $18514177721407$

$38$ Sacramento Sacramento $18501584169966$

$51$ Kern Bakersfield $18669161088142$

ca$\_$mountain$\_$ranges

range$\_$id name

$5$ Sierra Nevada

$14$ Cascades

$16$ Panamint Range

ca$\_$mountain$\_$peaks

peak$\_$id name elevation range$\_$id

$9$ Double Mountain $79815$

$12$ Breckenridge Mountain $75485$

$32$ Crater Peak $868314$

ca$\_$books

book$\_$id title year

$30$ Roughing It $1872$

$48$ Celebrated Jumping Frog $1867$

$78$ Contested Eden: California Before the Gold Rush $1997$

$136$ Adventures of a Novelist $1932$

ca$\_$authors

author$\_$id name

$184$ Twain, Mark

$379$ Atherton, Gertrude

$390$ Orsi, Richard

$419$ Guti$$rrez$,$ Ram$$n

ca$\_$book$\_$authors

book$\_$id author$\_$id

$30184$

$48184$

$78390$

$78419$

$136379$

Questions

Answer the following questions using the information provided above. Do not write SQL$.$

$(1$ pt$)$ What is the area of Sacramento county?

$(1$ pt$)$ What city is the seat of Kern county?

$(1$ pt$)$ In what mountain range is Breckenridge Mountain?

$(1$ pt$)$ What are the titles of two books by Mark Twain in the database?

$(1$ pt$)$ Who are the two authors of the book Contested Eden: California Before the Gold Rush?

$(2$ pts$)$ Besides county$\_$id$,$ what other field could serve as a primary key for the ca$\_$counties table? Explain your thinking.

$(1$ pt$)$ Explain why population would not be a good field to select as the primary key for the ca$\_$counties table. $($Make sure that your explanation is supported by the definition of a primary key.$)$

$(2$ pts$)$ Identify two foreign key fields in the database. Provide the name of each field, and the name of the table in which each field resides.

$(2$ pts$)$ What is the relationship between the ca$\_$mountain$\_$ranges and ca$\_$mountain$\_$peaks tables? Explain your thinking.

$(2$ pts$)$ What would be a reasonable primary key for the ca$\_$book$\_$authors table? Explain your thinking.

$(2$ pts$)$ Imagine that you have a mountain peak named after you in the Panamint Range, and this is recorded in the Californiana database. Provide an example of what such a row might look like in the ca$\_$mountain$\_$peaks table.

$(1$ pt$)$ Suppose you've written a book about California, and this is recorded in the Californiana database. Provide an example of what such a row might look like in the ca$\_$authors table.

$(1$ pt$)$ Suppose the book you've written is titled Weird Sacramento, and this is recorded in the Californiana database. Provide an example of what such a row might look like in the ca$\_$books table.

$(2$ pts$)$ What additional row would need to be added to the database to clearly indicate that you are the author of the book Weird Sacramento? Show what the row would look like, and identify the table in which it belongs.