Database Management Systems

© 2004 by Jerry Post

Chapter 3: Data Normalization

Exercises

  1. A local retail firm is building a website and wants you to create a database to track information requests. The company wants to collect basic customer data and then record when potential customers return to track their comments by topic. The topic is a predefined list of items (which managers can change) that potential customers will choose via a selection box. Define the normalized tables needed for this project. Create the class diagram and list of normalized tables for this case.
  2. Name
    Phone
    E-Mail
    Address
    City, State  ZIP Code
    Country
    Date/Time IP Address Commend Topic
           
           
           
  3. You have been hired to develop a small database for a company that wants to offer products for sale on the Internet. Create the class diagram and list of normalized tables for this case.
  4. Date/Time           Order Form
    Customer Credit Card Internet
    Name Shipping Address Card # E-Mail
    Phone City, State  ZIP Expiration Date IP Address
        Bank Referred From
    Items
    Item # Name Description Quantity List Price Sale Price Quantity Shipped Back Order Extended
                     
                     
                     
    Item Total
    Shipping
    Tax
    TotalDue
     
  5. A local company needs help with its human resources department. The managers want to offer cafeteria-style benefits, where employees can choose which benefit packages they want. The firm kicks in a fixed amount of total money, and employees pay the difference if the cost of the items selected goes over the limit. The Human Resources department also wants to keep a listing of the jobs each employee has held. Currently, this information is collected on a form similar to the one shown here. Create the class diagram and list of normalized tables for this case.
  6. Employee
    Last Name, First Name
    Office
    Phone
    Date Hired
    Benefit Date Monthly Cost
         
         
         
    Job title Location Start Date End Date Salary Supervisor
               
               
               
  7. A friend of yours has started a garage band. The band has already written several songs and has jobs (gigs) at several local nightclubs. To improve the performances and the band, she wants a database to track which songs were played and how well they were received. The band members also want to track who played on each song and indicate if there were problems (or highlights) so they know what to work on. The database has to be easy to use, and she has sketched out a sample form. Create the class diagram and list of normalized tables for this case.
  8. Gig
    Date
    Start Time
    #People
    Comments
    Location
    End Time
    Money
    Song
    Length       Actual     
    Author
    Style          Key
    Comments
    Person   Instrument   Comment
    --------------------------------
    --------------------------------
    --------------------------------
    Song
    Length       Actual     
    Author
    Style          Key
    Comments
    Person   Instrument   Comment
    --------------------------------
    --------------------------------
    --------------------------------
  9. A small firm that performs lawn care needs to track customer jobs and employees. Currently, the owner keeps daily records on a pad of paper. Similar to the form shown here, it lists the jobs by time. Employees can do several tasks for each customer, including lawn mowing, edging, and tree trimming. Create the class diagram and list of normalized tables for this case.
  10. Lawn Care
    Date
    Time
    Customer Address Total
    Task Employee Time Charge
           
           
           
     
     
     
     
  11. A local country club has a beautiful new clubhouse that the club rents out to individuals and companies for small parties and receptions. The clubhouse has four rooms of different sizes that can be rented separately, but the managers generally do not like to host more than two large events at one time. Clients must make reservations in advance, and can choose the room based on the number of expected guests. Clients can elect to have meals served or use a buffet. The meal costs depend on the items served. As shown in the sample form, for additional fees, clients can elect to have valet parking service and flowers provided by the club. Bar service is treated similar to a meal option. Create the class diagram and list of normalized tables for this case.
  12. Reservation Form
    Date Employee
    Room/Hall
    Maxiumum Capacity
    Cancel Date
    Total Due
    Deposit
    Event Date, Time, Length
    Event Title
    O Valet Parking (Cost:___)
    O Flowers (Cost: ___)
    Lunch (sit down)    # Guests     # Servers
    Menu
    Item Description Cost Quantity Subtotal
             
             
             
    Dinner (sit down)    # Guests     # Servers
    Menu
    Item Description Cost Quantity Subtotal
             
             
             
    Buffet...
    Bar...
  13. A small woodworking shop specializes in building grandfather clocks. The shop orders the wood, clockworks, and miscellaneous components from various suppliers. The wood panels are planed from rough wood, glued, shaped, and assembled. The ornate carvings for the top are purchased from a single supplier where they are hand carved. Some clocks are sold as custom orders where the client chooses options such as the height and the clockwork. For regular production, the owner usually fills out a sheet similar to the one shown just to keep track. The clocks are in high demand, and the owner makes a deliberate effort to hold down production so prices stay high. Based on the purchase order and the sales forms, create the class diagram and list of normalized tables needed for this case. Note that the item total on the Sale form does not equal the total price, because the total price includes some overhead charges, but not the delivery charge. Although not displayed on the form, the owner also wants to track the date each clock was started and finished.
  14. Sale
      Employee        
     
    Order Date
    Estimated Delivery Date
    Actual Delivery Date
    Customer
    Phone
    Address
    City, State  ZIP          
    Total Price
    Delivery Method
    Payment Method
    Delivery Charge
    Item/Feature        Color      Quantity     Price       Subtotal    
             
             
             
    Item Total  

     

    Purchase Order
    Supplier
    Contact        Phone
    Estimated Ship Date
    Date Received
          Order Date
          Employee
    Item Quantity Cost Quantity Received Clock (customer orders)
             
             
             
    Total Charges         Date Due
    Date Paid Amount Paid
       
       
       
  15. A local pizza shop wants a database to track customer orders and deliveries. The basic order form is shown here, along with a simple form the driver fills out when the pizza is delivered. Drivers are encouraged to write down comments about the delivery or the order that might be useful to drivers who deliver the next time. Tips have to be recorded because they are reported to the IRS. Create the class diagram and list of normalized tables for this case.
  16. Pizza Order
    Customer          
    Phone
    Address
           Order Date/Time

           Employee

    For each pizza:
    Crust type
    Size
    Base price
    Specialty pizza

    Custom Toppings

    Item     Cost     Comments (e.g., half/half)              
         
         
         
      Topping Cost
    Tax
    Discount/Coupon
    Total

     

    Delivery Sheet
    Delivery Time
    Employee
    Directions

    Payment Method
    Credit Card #
    Expiration
    Driver Tip
    Comments (e.g., Dog)
  17. A company that creates animated videos wants you to build a database to track the progress in producing the video. Based on the accompanying forms, the main focus is on tracking the individual characters and on the scenes. A principal artist draws a character and the basic data is stored so other artists can retrieve it and place samples in their scenes. The company also asks workers to track the amount of time spent on producing the various scenes. Create the class diagram and list of normalized tables for this case.
  18. Character
    Name
    Description
    Relative Size
       Artist in Charge   
    Character Views: digitized and stored
    Front     

    Left     

    Right     

    Rear     

    Top     

     

    Scene
    Sequence/Position
    Lead Writer
    Synopsis
    Character
    View
    Role
    Action
    Description         
                                             

     

    Work Status
    Date Employee
    Time      Scene      Character      Changes/Work      Amount of Time     
             
             
             
  19. A company that makes designer jeans is in trouble. The managers need help matching production to orders and ensuring that products are delivered to customers on time. The problem is made more difficult because each of the several styles of jeans can have many sizes, and customers have very specific orders. The orders can be quite large and are often filled by production from several factories, so several deliveries are needed to fill an order. The order, delivery, and production forms show the main items needed. Each factory has several shifts. A production line consists of about five employees who work on different aspects of the jeans. Create the class diagram and list of normalized tables for this case.
  20. Order
    OrderID
    Customer P.O.
    Customer Contact        Phone
         Order Date
         Exp. Delivery Date
    Style    Name    Size    Description    List Price    Sale Price    Value   
                 
                 
                 
    Order Total  

     

    Delivery
    Order ID

    Shipping Costs
    Delivery Date

    Factory: Location
    Manager
    Phone
    Style    Color    Size    Quantity   
           
           
           

     

    Factory Production
    Address
    City, State  ZIP
    Country
    Maximum Capacity

    Date
    Shift Line Style Gender Quantity Defective Hours # People
                   
                   
                   
                   

    Sally’s Pet Store

  21. Define the tables needed to extend the Pet Store database to handle genealogy records for the animals.
  22. Define the tables needed to extend the Pet Store database to handle health and veterinary records for the animals.
  23. Sally wants to add payroll and monthly employee evaluation information to the database. Define the tables needed.
  24. Sally wants to add pet grooming services. Define the tables necessary to schedule appointments, assuming two workers will be dedicated to this area.
  25. Rolling Thunder Bicycles

  26. Using the class diagram, identify five business rules that are described by the table definitions and table relationships (similar to the RentPrice rules described by the Video store).
  27. The company wishes to add more data for human resources, such as tax withholding, benefits selected, and benefit payments by the employees and by the company. Research common methods of handling this type of data and define the required tables.