Description
PART 1 (20 points): SQL CREATE TABLE
Database 1: A computer database schema consists of four relations, whose schemas are:
Product (maker, model, type)
PC (model, speed, ram, hdisk, price)
Laptop (model, speed, ram, hdisk, screen, price)
Printer (model, color, type, price)
Primary key attributes are underlined. The Product relation gives the manufacturers, model numbers and types (PC, laptop, or printer) of various computer products. The PC (Personal Computer) relation gives, for each model number, the CPU speed (in GHz), memory size (in MBytes), hard disk size (in GBytes), 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 it is a color printer, the printer type (laser or inkjet, etc.), and the price.
Database 2: Here is another database schema concerning World War II warships. It involves the following relations:
Classes (class, type, country, guns, bore, displacement)
Ships (name, class, launched)
Battles (name, bdate)
Outcomes (ship, battle, result)
Ships are built in “classes” from the same design, and the class is usually named for the first ship of that class. The relation Classes records the name of the class, the type (’bb ’ for battleship or ’bc’ for battlecruiser), the country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships, and relation Outcomes gives the result (sunk, damaged, or ok) for each ship in each battle.
Database 3: Assume a book store has a mail-order database with the following schema of relations:
(Primary keys are underlined. Meaning of each attribute is self-explanatory by its name.)
Employees (ENO, Ename, Hire_Date); Books (ISBN, Bname, Quantity, Price); Customers (CNO, Cname, Street, Zip, Phone); Orders (ONO, CNO, ENO, Received, Shipped); Orderline (ONO, ISBN, Qty); Zipcodes (Zip, City, State);
Write a SQL CREATE TABLE statement (with proper data types for attributes and proper primary key) for each of the above relations.
PART 2 (30 points): Relational Algebra
For the computer database, write expressions of relational algebra to answer the following queries:
1
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.
6. Find those hard-disk sizes that occur in two or more PC’s.
7. Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once; e.g., list (i, j) but not (j, i).
8. Find those manufacturers of at least two different computers (PC’s or laptops) with speeds of at least 2.80.
9. Find the manufacturers of PC ’s with at least three different speeds.
10. Find the manufacturers who sell at least three different models of PC.
For the battleship database, write expressions of relational algebra to answer the following queries:
11. Give the class names and countries of the classes that carried guns of at least 16-inch bore.
12. List all ships that belong to USA.
13. Find the ships launched prior to 1921.
14. Find the ships sunk in the battle of the Denmark Strait.
15. The treaty of Washington in 1921 prohibited capital ships heavier than 35,000 tons. List the ships that violated the treaty of Washington.
16. List the name, displacement, and number of guns of the ships engaged in the battle of Guadalcanal.
17. List all ships mentioned in the database. (Remember that all these ships may not appear in the Ships relation.)
18. Find the classes that had only one ship as a member of that class.
19. Find those countries that had both battleships and battlecruisers.
20. Find those ships that were damaged in one battle, but later fought in another.
For the bookstore database, write expressions of relational algebra to answer the following queries:
21. list customers (cnd, name) the zip of whose address is 49008.
22. list customers (cno’s and names) who live in Michigan.
23. list employees (names) who have customers in Michigan.
24. list employees (names) who have both 49008-zipcode customers and 49009-zipcode customers.
25. list customers (names) who’ve ordered books through an employee named ‘Jones’.
26. list customers (names) who’ve NOT ordered the book “Database”.
27. all possible pairs of books (Bname’s). (A pair should be listed only once).
28. all possible pairs of books (Bname’s) where the first has a price of 24.99 and the second has a price of 19.99.
29. customers (names) who ordered at least one book that customer #1111 ordered.
30. customers (names) who ordered all the books as customer #11111 ordered (although, they may have ordered additional books).
2
Prepare your answers in a single file using a word processor and submit the file to the WMU e-Learning Dropbox.
To answer questions in Part 1, write your SQL statements in a nice way. For example, to create the
Product table, use the following format:
CREATE TABLE Product (
maker CHAR(20),
model CHAR(20),
type CHAR(10),
PRIMARY KEY (maker, model)
);
To answer questions in Part 2, include the query number and the query itself in full text. For example, to answer the first question:
1. What PC models have a speed of at least 3.00?
Πmodel (σspeed ≥ 3.0 PC)
3
