2nd Cut Data Model

When an evolving data mode and its entities are said to be "in the 1st Normal Form, No Repeating Groups," it may be examined to see if it can pass the test for "2nd Normal Form, No Partial Dependencies."

If all of the attributes in an entity depend on all of the elements of its identifier it is in the 2nd Normal Form. This test only applies to tables with compound, non-atomic identifiers such as we see in the PurchaseOrderItem, OfficeDepartment, PurchaseOrderEmployee and DepartmentEmployee entities.

           PurchaseOrder.Id; item.Id; Item.Name; Item.Code; Quantity; Item.Price; Discount;

The PurchaseOrderItem has an Identifier for the PurchaseOrder and an Identifier for Item. Now the question is asked "Does Item.Name depend on both PurchaseOrder.Id and Item.Id?" The answer is oviously no. Item.Name is dependant on only Item.Id. It is "partially dependant." It needs to move out and into a new Entity. So, Item.Id, Item.Name, item.Code, Item.Price are moved out to the new Entity "Item". A many-to-many is always resolved with a junction entity.

           PurchaseOrder.Id; Quantity; Discount; Total

    Item -- Bring down the relationship to the P.O. and data now occurs once
           Id; Name; Code; Price;

Lookee here! The OfficeDepartment Entity has no attributes, just identifiers. That's fine. Happens all the time. We have only analyzed the Purchase Order and we're not done yet. You're going to find tons more data as the Requirements Data Analysis phase continues and you look at several dozen more artifacts. What's important is that we have this junction entity. Notice that this entity provides bi-directional views of the relationship.

            Office.Id; Department.Id

There's also something called "trasportation." Without this entity, we can not support the Purchase Order application. We can't access Department from Office or Office from Department. See the 1st Cut Data Model.

    PurchaseOrderEmployee -- the attribute "Role" is dependant on both identifiers.
            PurchaseOrder.Id; Employee.Id; Role

    DepartmentEmployee -- the attribute "Role" is dependant on both identifiers.
            Department.Id; Employee.Id; Title

All of our entities now pass the test for 1st Normal Form (no repeating groups) and 2nd Mormal Formw (nopartial dependancies)

At the top of the next page is our evolving data model.