Database Systems - Exam 1

When taking this exam you may use a single $3\times5$ card containing SQL syntax. The card must be written by you. Write your name on this paper and write your answers on the answer sheets provided.

  1. (4 pts) What does SQL stand for and why is it important to know?

  2. (2 pts each) Expand each acronym:
    1. DBMS
    2. DBA
    3. ER (as in ER diagram)

  3. (4 pts) What is the difference between a database and a DBMS?

  4. (4 pts) The term ``schema'' has (at least) two distinct definitions depending on the context in which it is used. Explain the meaning in the context of PostgreSQL and in the context of the relational model.

  5. (3 pts) There are three levels of data abstraction relavent to the study of database systems: the view level, the logical level, and the physical level. Explain any one of the three.

  6. (3 pts each) Briefly define each of the following terms:
    1. relation
    2. foreign key
    3. UML

  7. (4 pts) Name one advantage of MS Access over PostgreSQL. Name one advantage of PostgreSQL over MS Access.

  8. (6 pts) Name three reasons why a DBMS might be preferred over using programmer-defined data files for storing information.

  9. Consider the following three relations/tables that represent the efforts of a restaurant to keep track of various dinner parties and their orders.
    • dinner_party=(party_id,name,number)
    • menu=(menu_id,description)
    • orders=(menu_id,party_id,quantity)
    Assume that matching field names represent related fields among the tables.

    Table: dinner_party
    party_id name number
    1 Johnson 4
    2 Linebarger 2
    3 Garcia 3
    4 O'Toole 8
    5 Hill 4
    6 Anderson 5
    Table: menu
    menu_id description
    1 Fried Rice
    2 Chicken Pot Pie
    3 Beef KaBobs
    4 Lamb Chops
    5 ToFu

    Table: orders
    menu_id party_id quantity
    1 1 1
    2 1 1
    4 1 2
    1 2 1
    3 2 1
    1 3 2
    2 3 2
    Table: orders (continued)
    menu_id party_id quantity
    3 4 4
    1 4 2
    2 4 1
    3 6 2
    1 6 2
    4 6 1

    1. (4 pts) Draw an ER-diagram representing these tables and their relationships. Be sure to specify the cardinality of the relationships based on the structure of the tables and what you know of the problem domain.
    2. (4 pts) Represent the tables graphically again, this time using UML notation.
    3. (3 pts each) Given the structure and contents of the tables above, show the output that would be produced by each of these queries. For queries that produce more than 20 rows I will accept intelligent use of dots.
      1. select name, description from dinner_party natural inner join orders

        natural inner join menu

      2. select name, description from dinner_party natural left outer join

        orders natural right outer join menu

    4. (3 pts each) Construct SQL statements to achieve the following results (do not assume that the contents of the tables exactly match the samples given above):
      1. Create the dinner_party table having field names and types as illustrated above. Also specify the primary key.

      2. Remove the entire menu table from the database (don't just empty it ... delete it entirely).

      3. List all parties with 4 or more members.

      4. List all parties who have not placed an order.

      5. List all menu items along with the number of times they've been ordered. Sort the results showing the most often ordered items first. If an item has not been ordered then it should not show up in the list.

      6. Repeat the query of problem 9(d)v except include in the list items that have not been ordered.

      7. List all orders (including name of ordering party, quantity ordered, and description of menu item ordered) of all parties having ``son'' in the name.

      8. Create a view named all_orders that lists all the items ordered together with the dinner party that ordered them. The view should display all fields from all three tables. The results should be ordered first by party name and then by the quantity ordered with most frequently ordered listed first. menu_id number.

      9. Use the view you create in problem 9(d)viii to produce a list of parties who have ordered, but have ordered fewer items than members in their dinner party. (NOTE: In the instance above, O'Toole is an example of this).

      10. Remove all parties that do not have any items ordered.

      11. Change all parties that have ``Smith'' anywhere in the name to 10 people in their party.

Quick Links