LSP 121 - Practice Final

Part A: Short Answers

  1. Explain the difference between categorical and continuous variables.

    Ans. Categorical variables are nonnumeric and describe distinct categories of data. Continuous variables can take on any value in a range.

  2. What information does a row of a database contain?

    Ans: It contains all of the data for a specific item described in the table.

  3. What is the difference between a field and a column?

    Ans: A field is a specific data item in a row. A column contains all of the fields in all the rows refering to the same piece of data.

  4. Why are one-to-many relationships important when defining queries involving more than one table.

    Ans: A one-to-many relationship connects a row containing a foreign key in the "many" table to the row in the "one" table whose primary key matches the foreign key.

  5. What is meant by a join of two tables?

    Ans: The composite table formed by putting together two tables according to a one-to-many relationship.

  6. Explain the difference between a detail query and a aggregate query.

    Ans: An aggregate query contains a Total row in the Query Design window, which contains one of the keywords Ave, Count, Group By, Max, Min, Sum.

  7. What is the most annoying thing about Access, in your opinion?

    Ans: No answer is wrong here if it is backed up with good reasons.

  8. What is a data anomaly?

    Ans: It is either a table that contains a column that depends on another column that is not the primary key. Example: If the columns in a table are FootballPlayerName (PK), JerseyNumber, TeamName, TeamCity, TeamCity is completely determined by TeamName, which is not the primary key.

  9. An 8.2 earthquake is how many times more powerful than a 4.2 earthquake?

    Ans: ANTILOG(8.2 - 4.2) = 10^4 = 10,000.

  10. A 64 decibel sound is how many times more intense than a 34 decibel sound?

    Ans: 1,000 times.

  11. Explain what the following Alice terms mean.

    Ans: An object is any person, animal, plant, etc., that can be placed in an Alice world. The camera and world itself are also both objects. A method is a set of Alice statements is usually part of an Alice animation. A function is like a method, but it returns a value that can be used elsewhere in the animation.

  12. Give examples of how you would use these constructions in Alice.

    Ans: Sequence just lists statements in order. Repetition is used to repeat a set of statements, either a fixed number of times, or while a condition is true. Decision is used to choose one of two choices, for example, turn left or right.

Part B: Short Essay

  1. Explain how relational databases are useful.

  2. Explain the advantages and disadvantages of databases vs. spreadsheets.

    Ans: See the 5/10 Lecture Notes.

Part C: Database Normalization

  1. Normalize the following table. Assume that names are unique for players and dependents.
     
    NFL Football Teams
    Team Name
    City
    State
    Mascot
    Owner
    Players (Repeats 1 to n times)
    Stadium Name
    Stadium Capacity
     
Ans:
NFL Football Teams
Team Name (PK)
City
State
Mascot
Owner
Phone
Salary 		Players
Name (PK)
Jersey Number
Team Name (FK)
Position
Phone
Salary 		Dependents
Name (PK)
Players.Name (FK)
Relationship
Phone 		Stadiums
Stadium Name (PK)
Stadium Capacity
Team Name (FK)

Part D: Query Specifications

  1. A database (inventory.accdb) has the following tables:
     
    Stores
    PK Field Datatype
    Yes ID Number
    No Phone Text
    No City Text
    No State Text
    		Parts
    PK Field Datatype
    Yes Number Number
    No Name Text
    No Price Number
    Inventory
    PK Field Datatype
    Yes PartNumber Number
    No StoreID Number
    No Quantity Text
    		 

     
    These one-to-many relationships between the tables are also given:
    Table: Stores Inventory
    Field: ID StoreID
    Table: Parts Inventory
    Field: Number PartNumber

    Specify queries that solve the following problems:
     

    1. How many bolts does the store with ID 4444 have in stock? (Detail Query)

      Field: Name ID Quantity  
      Table: Parts Stores Inventory  
      Sort:        
      Show: No No Yes  
      Criterion: "Bolt" 4444    
      or:        

       

    2. What are the phone numbers of all stores that have more than 800 washers in stock? (Detail Query)

      Ans:
      Field: Phone Name Quantity  
      Table: Stores Parts Inventory  
      Sort:        
      Show: Yes No No  
      Criterion:   "Washer"  >800  
      or:        

       

    3. What is the total inventory in dollars of the parts in all stores? (Summary Query)

      Field: Quantity      
      Table: Inventory      
      Total: Sum      
      Sort: Yes      
      Show:        
      Criterion:        
      or:        

       

    4. Which store has the maximum number of nuts? (Detail Query)

      Ans: Take ID of the top row of sorted table:
      Field: ID Quantity Parts  
      Table: Store Inventory Name  
      Show: Yes Yes No  
      Sort:     Descending  
      Criterion:     "Nuts"  
      or:        

       

 

Part F: Alice Methods