Homework 2

Reading

Read through the rest of Chapter 2

To submit

The purpose of this assignment is to get you to do some basic SQL

Written

Required

• Exercise 2.2.1
• Exercise 2.2.2
• Exercise 2.3.1(a-f)
• Exercise 2.4.1(a-e)

NOTE: Hardcopy is due on the podium in the classroom at or before the beginning of class. Any hardcopy turned in after that time — even by a few minutes — will be counted as late. This is a firm deadline.

SQL

Do Exercise 2.4.1(a-e) in SQL.

Place all your files in a single directory hw2. Create a zip of this directory: in the parent directory, type

    zip -r hw2 hw2

Upload the created file hw2.zip to Dropbox. Note that you are not required to include output in your submission. You should check for yourself that everything runs correctly before you submit.

Reference

Exercise 2.2.1

The relation Accounts:

The relation Customers:

In Fig 2.6 are instances of two relations that might constitute part of a banking database. Indicate the following:

1. The attributes of each relation.
2. The tuples of each relation.
3. The components of one tuple from each relation
4. The relation schema for each relation
5. The database schema
6. A suitable domain for each attribute.
7. Another equivalent way to present each relation.

Exercise 2.2.2

In Section 2.2.7 we suggested that there are many examples of attributes that are created for the purpose of serving as keys of relations. Give some additional examples.

Exercise 2.3.1

In this exercise we introduce one of our running examples of a relational database schema. The database schema consists of four relations whose schemas are:

    Product(maker, model, type)
    PC(model, speed, ram, hd, price)
    Laptop(model, speed, ram, hd, screen, price)
    Printer(model, color, type, price)

The Product relation gives the manufacturer, model number, and type (PC, laptop, or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types; that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number. The PC relation gives for each model number that is a PC the speed (of the processor, in gigahertz), the amount of RAM (in megabytes), the size of the hard disk (in gigabytes), and the price. The Laptop relation is similar, except that the screen size (in inches) is also included. The Printer relation records for each printer model whether the printer produces color output (true, if so), the process type (laser or ink-jet, typically), and the price.

Write the following declarations:

1. A suitable schema for relation Product.
2. A suitable schema for relation PC.
3. A suitable schema for relation Laptop.
4. A suitable schema for relation Printer.
5. An alteration to your Printer schema from above to delete the attribute color.
6. An alteration to your Laptop schema from above to add the attribute od (optical-disk type, e.g., cd or dvd). Let the default value for this attribute be 'none' if the laptop does not have an optical disk.

Exercise 2.4.1

To populate the tables, put INSERT INTO commands in a file ex2-4-1.sql, using the data given in Figures 2.20 and 2.21 to populate the tables.

Render the following queries as SELECT statements. Place these in text files ex-2-4-1a.sql, ex2-4-1b.sql, etc. Check that they return the correct results.

This exercise builds upon the products schema of Exercise 2.3.1. Recall that the database schema consists of four relations, whose schemas are:

    Product(maker, model, type)
    PC(model, speed, ram, hd, price)
    Laptop(model, speed, ram, hd, screen, price)
    Printer(model, color, type, price)

Some sample data for the relations is shown in the figures below. Manufacturers and model numbers have been “sanitized,” but the data is typical of products on sale at the beginning of 2007.

Write expressions of relational algebra to answer the following queries. You may use the linear notation of Section 2.4.13 if you wish. Show the results of your query given the data below. However, your answer should work for arbitrary data (not just the data of these figures).

1. What PC models have a speed of at least 3.00?
2. Which manufacturers make laptops with a hard disk of at least 100GB?
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 PC’s.

Figures

Figure 2.20

Figure 2.21