Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> SQL HELP!


From: Jeremy Ambler <>
Date: 17 Nov 2004 13:55:16 -0800
Message-ID: <>

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:

  1. Books are identified by number (ISBN).
  2. Categories (fiction, non-fiction, science, math. etc.)
  3. Title, Subtitle, General Description
  4. Arbitrary number of separate chapters. Each chapter has a title. Might have chapter author(s).
  5. Arbitrary number of separate sections (within a chapter).
  6. Arbitrary number of separate subsections (within a section).
  7. Arbitrary number of separate subsubsections (within a subsection).
  8. Furthermore people, objects (dogs, F-14, Toyota Camry, buildings landmarks), and events (WWI, Gulf War), would have to stored in the database.
  9. You may introduce other properties.

Section 2: The Updates (INSERTs, DELETEs, and UPDATEs)

  Give SQL statements for the actions:

  1. Add your DB book into the database.
  2. Add Oracle as a company that is mentioned in chapter 25 section 2 of your DB book.
  3. Add George Bush as a person of the database (make up his address,...etc.)
  4. Add John Gotti as a person of the database associated with the Gambino crime family (make up address,...etc.)
  5. 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.

  1. Which book include "Bill Gates"?
    (give title/ISBN/author/chapter)
  2. Which chapter include the Statue of Liberty?
    (give title/ISBN/author/chapter)
  3. Which pages have topic on NASA Mars Mission?
    (give title/ISBN/author/pages)
  4. Which is the longest fiction book in your collection?
    (give title/ISBN/author/)
  5. Which book has the longest chapter?
    (give title/ISBN/author/chapter number)
  6. Which category of book do you have the most?
    (give category/count)
  7. What are the names, address and phone numbers of all people mentioned in book titled "The History of Computing".
  8. Which was the longest book written by J.K. Rowling?
    (give book title/published year)
  9. Which book published within the last 20 years and contains topics on DBMS?
    (give title/published year)
  10. 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

Original text of this message