Project Assignment 1

CSC319 - Database Technology

Due: 7/5/2001

You are a new hire at a local consulting firm and have been assigned to a development team that is about to start creating application programs for a database system. The database system is being developed for a local bookstore chain. Microsoft SQL Server will be the database management system.

You attend the first project meeting and discover that the database administrators have already created the database and have also populated the tables with sample data provided by the client. The development manager has asked you to work with a programmer who will be developing the application interface and has asked you to develop SQL code for the queries that will be needed.

The database is named pubs and contains eight tables (publisher, authors, titles, titleauthor, sales, stores, discounts, roysched). Details of the tables (i.e. column name, domain, primary key etc.) are available.

To complete the assignment, do the following:

  1. Install Microsoft SQL Server as explained in Appendix B of your text.

    Note: Your database will be created and the tables populated during installation. Do not attempt to create the pubs database. Pages 527 - 536 contain sections on creating a database, creating tables, and entering data. You should read these sections for important background information but you will not need to create your database.

  2. Use the SQL Server Enterprise Manager to get to the pubs database.

    Note: Pages 533 - 536 provide details of how to launch Enterprise Manager. Figure B-12 illustrates Enterprise Manager.

  3. Review the table details provided in the attachment. After getting familiar with the database, use SQL Server Query Manager to code and test SQL queries for the following:

    Note: Pages 535 and 536 mention SQL Server Query Manager and Figure B-13 illustrates its use.

    1. List the name (first name then last name) and city of residence of all authors that do not live in a given city (use Oakland as an example to illustrate your answer).
    2. List the type, title, and price of books in a given price range (use $10.00 and $19.99 inclusive as an example to illustrate your answer). The listing should be in ascending order of type and descending order of price.
    3. List the type and average price for each type of book.
    4. List all authors and publishers that are based in the same city.
    5. List the publishers of books of a given type (use business as an example to illustrate your answer).
    6. List the name, address, city, state, and zip of all stores that have sold more than a given quantity of a given type of book (use 30, and business as an example to illustrate your answer).

    Submit a typed report with the SQL query and the corresponding output for the pubs database.