Spring 2015: 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. [8 points] Name and describe each of the components of a database system.

2. [2 points] Describe the roll of SQL in a database system.

3. Consider the following database schema:

           RECIPE(RecipeID,RecipeTitle, IngredientID, Quantity)
   	INGREDIENT(IngredientID, IngredientName, MeasurementSize)
   	

   [4 points] Describe the table(s) specified by this schema and any relationships.

4. [6 points] Compare and contrast subqueries and joins.

5. [2 points] Write an SQL query which will display all information contained in a table TEST_SCORES.

6. [2 points] What is the result of the following query

    SELECT * FROM TABLE1, TABLE2;
       

7. [4 points] State the characteristics of a relation.

8. Consider the following table called CHECKING_ACCOUNT:

   CheckNo	Name	Local	Amount	Account No	Account Name	Balance
   123	Bob Smith	N	23.00	100	Expenses	500.00
   124	Boots & Saddle	Y	300.00	120	Farm	1000.00
   125	Home Mortgage & loan	N	678.92	105	House	0.00
   126	Equity Feed Co	Y	200.00	120	Farm	800.00
   127	Boots & Saddle	Y	100.00	120	Farm	700.00

   1. [2 points] If check 123 is deleted information about Expenses is lost. What is this called? How can such a loss be prevented?
   2. [2 points] What does it mean for a table to be in BCNF?
   3. [3 points] List functional dependencies for this table.
   4. [3 points] List multivalued dependencies for this table.
   5. [2 points] Is this table in 1NF? Why or why not?
   6. [6 points] How would you decompose this table to place it in BCNF and 4NF? Give a new database schema.

   OVER PLEASE

    

    

    

9. What is the result of running the following queries?
   1. [2 points]

      SELECT Buyer
         FROM SKU_DATA 
         WHERE Buyer NOT IN (
            SELECT BUYER 
               FROM SKU_DATA,BUYER
      	 WHERE SKU_DATA.BUYER = BUYER.Buyer
         );
      	 

   2. [2 points]

      INSERT INTO EQUIPMENT_ITEM
      	SELECT DISTINCT ItemNumber, EquipmentType, AcquisitionCost
      	FROM EQUIPMENT_REPAIR;