Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Normal Forms done right?

    Hello DBforums.

    First a brief introduction, I am a computer science student from Denmark and have been given an assignment with 3 tasks. It must be said that I'm not being lazy by posting here, and I do not wish for any of you to complete my assignment for me. If you could give me some pointers, suggestions and whatnot, I'd be very appreciative. And answer my questions. Thank you in advance.
    *******************
    2. Decompose the existing database tables into third normal form. Be aware of overlapping information in the different tables. For each normal form, explain the problems with the current solution, what needs to be done, and show the resulting new tables.

    How the database should function:

    In this way a worker is assigned for a specific department, each run by the warehouse keepers and the manager. All administrative workers (manager, warehouse keepers and secretaries) are monthly paid, and the salary is available on the wage accounts the last working day in each month. The other workers are hourly-paid, and the wage is available on the wage-account each 14th day.
    The about 10.000 different products are distributed among the 5 stock houses such that a given product belongs to a certain stock-house.

    For each product a minimum in stock limit is specified. If the amount in stock drops below
    this limit, and the product hasn’t been ordered yet, new product items are being ordered
    from a supplier. For each product a fixed amount to be ordered is specified.
    A series of different suppliers delivers the product articles. More suppliers can be found for
    a given product, so an optimal supplier can be selected based on different criteria such as
    price or delivery time.
    A supplier will typically deliver several different products to the company.
    A number of customers order products from the company. A customer may have more
    than one order at the same time. In contrast an order will always belong to one specific
    customer.
    Each customer will have a unique account number assigned to them in the finance system
    of Easy Money.
    Several years ago the company had a new spreadsheet based application installed, a
    system that should have relieved the burden of registering of employee information,
    customer- and order-information, and the in-stock administration. The IT-system is based
    on the following spreadsheets (Parenthesis indicate composite attributes and curly braces
    indicate multivalued attributes):

    These are the rules for normal forms that I followed:

    1st Normal Form: No repeating elements or groups of elements. A table is not in first normal form if it is keeping multiple values for a piece of information. If a table is not in first normal form, remove the multivalued information from the table. Create a new table with that information and the primary key of the original table.
    2nd Normal Form: No partial dependencies on a concatenated key.
    3rd Normal Form: No dependencies on non-key attributes.

    And here are the tables:
    Code:
    PERSON (Person ID, Last Name, First Name, Address, Zip Code, City, Phone)
          EMPLOYEE (Employee ID, Person ID, Cpr No, Account ID, Dept No)
                ADMINISTRATOR (Administrator ID, Monthly Pay)
                       MANAGER (Manager ID)
                        SECRETARY (Secretary ID)	
                        WAREHOUSEKEEPER (Warehouse keeper ID)
                 WORKER (Worker ID, Hours, Hourly Pay)
    CUSTOMER (Person ID, Customer No, Company, Customer Account ID)
    SUPPLIER (Person ID, Supplier No, Supplier Account ID, Product ID)
    
    DEPARTMENT (Dept No, Dept Name, Dept Address, Dept Zip Code, Dept City, Dept Phone)
          ADMINISTRATION (Administration ID, Manager ID, Secretary ID)
          WAREHOUSE (Warehouse ID, Worker ID,)
    STOCK (Stock No, Product ID, Minimum Stock Quantity, Ordered {Boolean}, Refill Stock Amount Supplier ID)
    
    ACCOUNT (Account No)
            WAGE_ACCOUNT (Wage Account No, Salary)
            CUSTOMER_ACCOUNT (Customer Account No, Balance)
    ORDER (Order No, Customer No, Order Date)
    ORDER_LINE (Order No, Product No, Quantity)
    PRODUCT (Product No, Product Name, Unit Price, Delivery Time)
    Could someone check if I've done this right? I've uploaded a class diagram.
    Attached Thumbnails Attached Thumbnails Class Diagram1.jpg  
    Last edited by Sanker; 02-29-12 at 14:26. Reason: Discrepancies.

  2. #2
    Join Date
    Feb 2012
    Posts
    3
    I've done some more work on the tables, and have implemented some IS-A relations. PERSON is the "superclass".

    Code:
    PERSON (Person ID, Last Name, First Name, Address, Zip Code, City, Phone)
          EMPLOYEE (Person ID, Employee ID, Cpr No, Account ID, Dept No)
                ADMINISTRATOR (Employee ID, Administrator ID, Monthly Pay)
                       MANAGER (Administrator Id, Manager ID)
                        SECRETARY (Administrator Id, Secretary ID)	
                        WAREHOUSEKEEPER (Administrator Id, Warehouse keeper ID, Monthly Pay)
                 WORKER (Employee ID, Worker ID, Hours, Hourly Pay)
    CUSTOMER (Person ID, Customer No, Customer Account ID)
    SUPPLIER (Person ID, Supplier No, Supplier Account ID, Product ID)
    Last edited by Sanker; 02-29-12 at 10:34.

  3. #3
    Join Date
    Feb 2012
    Posts
    3

    Finished

    Uploaded a class diagram.

  4. #4
    Join Date
    Feb 2012
    Posts
    76
    I checked your tables before your last edit, they looked to be in 3NF. You have some redundant attributes (e.g. no need for Employee ID/Administrator ID/Manager ID/Secretary ID/Warehouse keeper ID/Worker ID, Person ID works as well as long as you properly specify your foreign key constraints).

    If you want to check normal forms or properly describe your tables, you really should specify your functional dependencies or primary keys. I can try to assume them based on similarly-named fields, but you're not exactly meticulous in this respect - is Customer Account No the same as Customer Account ID?

    What's with the different diagramming conventions - arrows, diamonds and cardinality labels in one diagram? Are you mixing up your class diagrams and ERD diagrams? Personally, I prefer crow's foot, it just seems more readable, but whatever works for you is fine, just be consistent.

    Ps: don't conflate OOP concepts such as inheritance and subclassing with ADT concepts such as entailment and subtyping. Don't get me started on this topic either. You've been warned!
    Last edited by reaanb; 03-02-12 at 14:31. Reason: Removed a redundant statement

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •