CSCI 305: Introduction to Database Systems

In-Class Exercises

Review for Exam One

You are to complete a collection of exercises to review some of the concepts we have learned from the first part of the semester. See the review guidelines for the exam from the course website.

  1. The given tables below are instances of two relations that might constitute part of a banking database.

    The relation Accounts
    acctNo type balance
    12345 savings 12000
    23456 checking 1000
    34567 savings 25

    The relation Customers
    firstName lastName idNo account
    Robbie Banks 901-222 12345
    Lena Hand 805-333 12345
    Lena Hand 805-333 23456

    Do the following

    1. Indicate the attributes of each relation.
    2. Indicate the proper type of each attribute, i.e., string, float, int etc.
    3. Indicate the relation schema for each relation.
    4. Indicate your choice of key for each relation.
    5. Decompose the Customers table into two tables T1 and T2 so relation conforms with BCNF form.
    6. Perform a Join operation on T1 and T2 to recover the original Customers relation.
    7. Write the SQLite code to create the database that contains the two relations, specify primary key. Note that the first relation would have a one-attribute primary while the second relation would have a two-attribute primary key.
    8. Write the SQLite code to insert the above listed values into the two tables of the database.
    9. Write the SQLite code to set the constraint and related check that the balance of a savings account can't be lower than 1000 dollars.
  2. Given the schema of a database below and a set of sample records (entries) in the hand-out. Write the SQLite queries to answer the following questions.
    1. What PC models have a speed of at least 3.00?
    2. Which manufacturers make laptops with a hard disk of at least 100 GB?
    3. Find the model number and price of all products (of any type) made by manufacturer B.
    4. Find the model numbers of all color laser printers.
    5. Find those manufacturers that sell Laptops, but not PCs
    6. Find those hard-disk sizes that occur in two or more PCs
    7. Find the manufacturers who sell exactly three different models of PC.
    8. Draw E-R diagram for the database that contains these four tables, Product, PC, Laptop, and Printer.
  3. Consider a relation R with attributes A, B, C, D, E, and F. Suppose that this relation has the FDs AB -> C, BC -> AD, D -> E, and CF -> B. Compute and list the closure of {A, B}, that is {A, B}+.
  4. Given the following relation called Movies1, identify some of the anomalies. The decompose it into two relations called Movies2 and Movies3 such that the decomposition is lossless and the anomalies are eliminated.

    5. The relation Movies1
    title year length genre studioName starName
    Star Wars 1977 124 SciFi Fox Carrie Fisher
    Star Wars 1977 124 SciFi Fox Mark Hamill
    Star Wars 1977 124 SciFi Fox Harrison Ford
    Gone With the Wind 1939 231 drama MGM Vivien Leigh
    Wayne's World 1992 95 comedy Paramount Dana Carvey
    Wayne's World 1992 95 comedy Paramount Mike Meyers