Homework 2 (due 9/27)
Homework is due at class-time on Thursday (9/27). Submit either a hardcopy or email it to me.
We finished talking about the basics of the relational database model (chapter 5, up to page 213, we will see the rest of chapter 5 later), and began SQL (chapter 7, skipping, for the time being, the DDL part; we will get back to it).
For the following homework you will need Microsoft Access, you can find it in the labs, or get a free license and software at http://www.cti.depaul.edu/resources/msdnaa.asp. Microsoft offers introductory tutorials on Access 2003 and Access 2007.
1. [Pine Valley Furniture, relational schema, 15pt] Draw a diagram with the relational schemas and foreign keys for the Pine Valley Furniture database, involving the following relations: employee, works_in, work_center, employee_skills, skill, produced_in, product, product_line.
The final diagram should be similar to Figure 5.5 (p. 194) of the book. In particular:
- list all attributes of each relation (using either the short display names, or the real field names)
- underline the primary key of each relation (every relation must have a primary key)
- draw an arrow from each foreign key to the primary key it refers to (there should be 8 foreign keys in your diagram)
2. [Pine Valley Furniture] Write SQL queries to answer the following questions. Run the queries, and submit the queries as well as their output. (You can copy paste graphical output from Access; you can also use screenshots through CTRL/ALT/PRINT SCREEN).
- [5pt] List all customers (name and city) located in Florida.
- [5pt] List all customers (name and city) that placed orders after 1/1/2003.
- [5pt] List the descriptions and finishes of all products that are available for less than $200.
- [5pt] List the descriptions of products belonging to the 'Scandinavia' product line.
3. [Pine Valley Furniture] Write SQL queries to answer the following questions. Run the queries, and submit the queries as well as their output.
- [10pt] List all employees that can use a 12in band saw
- [10pt] List all customers doing business in the North East territory.
- [10pt] List the names of all customers who placed orders within the last two years. Hint: use Now() to get current date.
Last updated: September 18h, 2007.