Designing a Simple Database
Design a simple database for storing information
about your private Book collection. The ideal design for the database
will
represent all the information specified in an easily understood schema
without
unnecessary redundancy. You must follow the relational database
design
methodology (normalization), and indexing (to speed up your 10
queries).
The database should include at least 10 tables.
Section 1: The Database Schema
The database must allow the representation of the following kinds of
information in an SQL Schema:
- Books are identified by number (ISBN).
- Categories (fiction, non-fiction, science, math. etc.)
- Title, Subtitle, General Description
- Arbitrary number of separate chapters. Each chapter has a
title.
Might have chapter author(s).
- Arbitrary number of separate sections (within a chapter).
- Arbitrary number of separate subsections (within a section).
- Arbitrary number of separate subsubsections (within a
subsection).
- Furthermore people, objects (dogs, F-14, Toyota Camry,
buildings
landmarks), and events (WWI, Gulf War), would have to stored in
the database.
- You may introduce other properties.
Section 2: The Updates (INSERTs, DELETEs, and UPDATEs)
Give SQL statements for the actions:
- Add your DB book into the database.
- Add Oracle as a company that is mentioned in chapter 25 section 2
of your DB book.
- Add George Bush as a person of the database (make up his
address,...etc.)
- Add John Gotti as a person of the database associated with the
Gambino
crime family (make up address,...etc.)
- Record that chapter 10 in LORD OF THE RING from page 189-210
contains:
Elijah Wood (Frodo), Sean Astin (Sam), and Andy Serkis (Gollum),
each
associated with Mount Doom and the Ring.
Section 3: Queries
Give Select statements that answer the following questions for any
instance
of your database.
- Which book include "Bill Gates"?
(give title/ISBN/author/chapter)
- Which chapter include the Statue of Liberty?
(give title/ISBN/author/chapter)
- Which pages have topic on NASA Mars Mission?
(give title/ISBN/author/pages)
- Which is the longest fiction book in your collection?
(give title/ISBN/author/)
- Which book has the longest chapter?
(give title/ISBN/author/chapter number)
- Which category of book do you have the most?
(give category/count)
- What are the names, address and phone numbers of all people
mentioned
in book titled "The History of Computing".
- Which was the longest book written by J.K. Rowling?
(give book title/published year)
- Which book published within the last 20 years and contains
topics
on DBMS?
(give title/published year)
- Which was the most expensive book in your collection?
(give title/author/price)
Section 4: Indexing
Which attribute(s) in your schemas need to be indexed!
Received on Wed Nov 17 2004 - 15:55:16 CST
