Database Queries (Database file attached and write queries "Questions posted" )

Assignment 1: Relational Model and Relational Algebra
Keep in mind that this is a homework assignment, not an exam. You can save your answers and come back to revise or continue as many times as you like, before the assignment becomes due.

PART 1 (10 points): SQL CREATE TABLE
In each database, write a SQL CREATE TABLE statement (with proper data types for attributes and proper primary key) for each of the relations. Format 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)
);

Question 1 (4 points)
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.

Question 2 (6 points)
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);

PART 2 (10 points): Relational Algebra
Hint: To nicely formulate your answer, make good use of features of the HTML Editor. You can also paste from external editors, for example,

Πmodel (σspeed ≥ 3.0 PC) (pasted from MS Word), or

Πmodel(σspeed≥3.0PC)
(using LaTeX equation)



Database 3: For the bookstore database, write expressions of relational algebra to answer the following queries:
Question 3 (1 point)
List customers (cno, name) the zip of whose address is 49008.

Question 4 (1 point)
List customers (cno, name) who live in Michigan.

Question 5 (1 point)
List employees (name) who have customers in Michigan.

Question 6 (1 point)
List employees (name) who have both 49008-zipcode customers and 49009-zipcode customers.

Question 7 (1 point)
List customers (name) who've ordered books through an employee named 'Jones'.

Question 8 (1 point)
List customers (name) who've NOT ordered the book "Database".

Question 9 (1 point)
All possible pairs of books (Bname). (A pair should be listed only once).

Question 10 (1 point)
All possible pairs of books (Bname) where the first has a price of 24.99 and the second has a price of 19.99.

Question 11 (1 point)
Customers (name) who ordered at least one book that customer #1111 ordered.

Question 12 (1 point)
Customers (name) who ordered all the books as customer #11111 ordered (although, they may have ordered additional books).