Spring 2013: Database Midterm

• The weight for each question is given with the question.
• Use as much paper as you wish, but make your answers are legible.
• Please answer each question thoughtfully and carefully.

1. [5 points] Name and describe the four components of a typical database system. How does SQL fit into this system?

2. [3 points] Databases contain integrated tables. What is an integrated table and what is meta-data?

3. [2 points] What is the result when a select statement is executed?

   Table 1: MINIATURES
   ItemNumber	Line	Description	Price
   77019	Dark Haven: Bones	Orc Swordsman (3)	5.49
   77020	Dark Haven: Bones	Bathalian	2.49
   77013	Dark Haven: Bones	Minotaur	3.49
   77001	Dark Haven: Bones	Skeletal Spearmen (3)	4.99
   03608	Dark Haven: Legends	Aquatic Familiars II (4)	7.79
   03650	Dark Haven: Legends	Fire Elemental(Medium)	6.99
   20021	Legendary Encounters	Bathalian	4.99

   Table 2: ORDERS
   UserID	ItemNumber	Quantity
   10000	77019	2
   10000	77001	5
   10000	03608	1
   10001	77019	1
   10001	77013	1

   ORDERS.ItemNumber is a foreign key which corresponds to MINIATURES.ItemNumber.

4. [3 points] Provide a SQL query which will print out all of the miniatures in the MINIATURES table from the Dark Haven: Legends line.

5. [3 points] Provide a SQL query which will show the total number of items ordered by each user in the ORDERS table.

6. [3 points] Provide a SQL query which will show the Description of all items ordered by UserID 10000, This list should be presented in alphabetic order.

7. [3 points] Provide a SQL query which will compute the total order cost for UserID 10001 (cost = ΣPrice*Quantity)

   Table 3: PHOTOS
   ItemNumber	PhotoName	Painter	Tags
   02568	bathalian1.jpg	Michael Genet	Robe, Cloth, Staff
   02568	bathalian2.jpg	Sean Fulton	Robe, Cloth, Staff
   03135	bathalian3.jpg	Patrick Keith	Robe, Boots
   03135	bathalian4.jpg	Patrick Keith	Robe, Boots
   03135	bathalian5.jpg	Michael	Robe, Boots

8. [4 points] What is a modification Anomalies?

9. [4 points] How are anomalies eliminated in databases?

10. [4 points] Identify potential anomalies in the ORDER and MINIATURES tables.

11. [4 points] Identify the functional dependencies in the PHOTOS table.

12. [2 points] Identify any multivalued dependencies in the PHOTOS table.

13. [10 points] Apply the normalization process to the PHOTOS table.
    1. Provide the schema for the final database.
    2. Include the identification of primary, foreign and surrogate keys.
    3. List any referential integrity constraints.
    4. List any assumptions you made.