database concepts

Advanced SQL : advanced querying

A hardware database

The database schema1 consists of four relations, whose schema are:
     Product(maker, model, type)
     PC(model, speed, ram, hd, rd, price)
     Laptop(model, speed, ram, hd, screen, price)
     Printer(model, colour, type, price)

The Product relation gives the manufacturer, model number and type (PC, laptop, 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 megahertz), the amount of RAM of the removable disk (CD or DVD), and the price. The Laptop relation is similar, except that the screen size (in inches) is recorded in place of information about the removable disk. The Printer relation records for each printer model whether the printer produces colour output (1 if true, 0 if false), the process type (laser, ink-jet, or bubble), and the price. Some sample data is below.

Product Table
Product table

PC table

Laptop table

Printer table
Printer table

1Source: Garcia_Molina, H., Ullman, J. and Widom, J. (2002) Database Systems: the complete book, International ed., Prentice Hall. The data is typical of products on sale in the US at the beginning of 2001.