Results 1 to 2 of 2

Thread: Database design and normalization homework issue

  1. #1
    Join Date
    Aug 2018
    Posts
    1

    Database design and normalization homework issue

    I am supposed to be designing a database from an order form that is in 1NF, another in 2NF, and 3NF however I have been given feedback that my project has not met the requirements of 1NF, 2NF or 3NF forms and my professor will not get back to me in any kind of timely manner. The 1NF structure must contain 1 table, 2NF 3 tables, and 3NF 4 tables. Also calculated fields are to be left out of the tables for some reason according to the rubric.

    Here is my sales order form
    sales_order.png


    From this I have extracted the following structures for 1NF, 2NF and 3NF respectively:

    SalesOrder ( orderId (PK), customerId, firstName, lastName, address, apartment, city, state, zip, donutId, donutName, donutDescription, unitPrice, homePhone, mobilePhone, otherPhone )

    ///////////////

    SalesOrder ( orderId (PK), donutId (FK), customerId(FK), donutTotal, orderDate )
    Customer ( customerId (PK), firstName, lastName, address, apartment, city, state, zip, homePhone, mobilePhone, otherPhone )
    Donut ( donutId (PK), donutName, donutDescription, donutPrice )



    //////////////

    SalesOrder ( orderId (PK), customerId (FK), donutId (FK), orderDate )
    SalesItem ( orderItemId (PK), orderId (PK), donutId (FK), donutTotal )
    Customer ( customerId (PK), firstName, lastName, address, apartment, city, state, zip, homePhone, mobilePhone, otherPhone )
    Donut ( donutId (PK), donutName, donutDescription, donutPrice )



    The results from the rubric state for 1NF: "The submission depicts the sales order form table to fulfill the first normal form requirement. The selected primary key does not depict the provided data in first normal form and the table is missing required fields"

    For 2NF: "The submission depicts the order, customer and line item tables. The tables as depicted are not in 2nd normal form and do not include all required fields."

    And 3NF: "The submission depicts four tables. The tables as depicted are not in 3rd normal form. The selected keys of some of the tables are incorrect and the tables are missing required fields."



    From my understanding of normalization I have done exactly what is asked but for some reason my solution is being labeled incorrect. If anyone could point out a flaw in my normalization I would appreciate it immensely.

  2. #2
    Join Date
    Dec 2020
    Posts
    3
    "The selected primary key does not depict the provided data in first normal form"

    This presumably refers to the fact that you will have multiple rows in your 1NF schema that all have the same PK value, which contradicts the very thing of what it means to be a candidate key. What you mention as the key in this schema must be the "smallest" combination of attributes that satisfies the property of "being a candidate key in the first place". Look at the form and try to guess which table those multiple rows will derive from that all have the same OrderId.

    "the table is missing required fields"

    This presumably refers to the fact that you have not provided any facility to record the "special handling notes". Obviously, this phenomenon is then inherited by your 2NF and 3NF solutions too. Same remark might also apply to SubTotal, SalesTax and Total. Well, SubTotal and Total might have to be considered as "calculated fields", but that depends rather heavily on what the ***precise*** definition is of the term "calculated field". If that ***precise*** definition says that a calculated field is a field whose value can be calculated from other values ***that all appear in the same row***, then these two fields by definition don't fit the bill and must be included in the design. Ditto for Salestax. The filled-in form does not by and of itself specify whether the applicable sales tax is the same percentage for all orders or can differ from one order to another (let alone the predicates that determine how so, in the latter case). And database designers are not required to be tax lawyers (though it does always help to have one's own basic knowledge of the business stuff - even if it only helps you to ask all the right questions early enough in the design process).

    "The tables as depicted are not in 2nd normal form"

    Questionable remark. Only thing I can see is you have the same PK problem in your SalesOrder table as in your 1NF schema. Well, you also have ***introduced*** the DonutTotal field in this design when such a field was not mentioned in the 1NF design. If you arrived at your 2NF design by applying a decomposition rule to your 1NF design, where did that field come from ?

    "The tables as depicted are not in 3rd normal form."

    Probably inherited from the PK error in SalesOrder in your 2NF design. ***WHY*** do you have a DonutID on the level of the overall order which is supposed to be able to cover ***all the different*** DonutID's involved ???



    (All that said, pls take note of the fact that I believe that people teaching this sort of stuff as "the proper way to do database design" should be banned from database teaching altogether and this rather yesterday than today.)

Posting Permissions

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