120 2NF

Informally, Second Normal Form, or 2NF, says that every non-key attribute should depend on the whole of each candidate key, not merely on part of a composite key.

Example which violates 2NF

The following relation has the composite key {shop, book}:

The functional dependency {shop} → {shop_city} is visible in the repeated city values. The city depends on the shop, not on the whole {shop, book} key. The same modelling mistake can be written in Predica:

entity BookStock
{
    String shop;
    String book;
    String shop_city;

    key {shop, book};
}

Here shop_city is a fact about the shop, but it is stored on BookStock.

Writing the dependency as a constraint

If the model is left in this shape, the functional dependency has to be imposed as a constraint over the containing collection of BookStock values. One way to write it is to say that there is no pair of stock records with the same shop and different shop cities:

entity BookStore
{
    BookStock stock*;

    constraints
    {
        let store = this in
            stock.(let x = this in
                store.stock.[
                    shop = x.shop
                    ∧ shop_city ≠ x.shop_city
                ].Σempty);

        // Alternatively
        stock.group(g)by(shop).(g.shop_city : ?);
    }
}

This constraint is the Predica form of {shop} → {shop_city}. It is non-local because BookStock does not contain a single independently updatable city fact for each shop; instead the city fact is repeated across the stock records for that shop.

Fixing the violation

The fix is to separate the fact about the shop from the fact that a shop stocks a book. In relational form, this decomposes the original relation into two relations:

In Predica, the same repair is to model Shop explicitly:

entity Shop
{
    String name;
    String city;

    key {name};
}

entity BookStock
{
    Shop shop;
    String book;

    key {shop, book};
}

entity BookStore
{
    Shop shop*;
    BookStock stock*;
}

After this repair, BookStore contains shops and stock records directly, and the cross-record FD constraint on BookStock is no longer needed. The city is a local attribute of Shop, so changing a shop's city has a natural update target and does not require coordinated updates to every book stocked by that shop.