Frequently-asked questions »

Question »

Questions

OO class modelling 1 »

OO class modelling 2 »

Data/ Attribute Normalisation »

Fact Gathering »

DFDs and STDs 1 »

DFDs and STDs 2 »

First, it is important to remember to answer each part of the question.  Make it clear which part of the question you are answering by using the question parts indicated (a, b, c and d). It is useful to start each part of your answer on a new page, allowing you to add extra detail after your initial answer. 

• Answer to part a
• Answer to part b

• Answer to part c

• Answer to part d

Part a

A data design should always be checked after creation to ensure that it is at least adequate for the system being designed and supports the system in meeting the user requirements.

As far as data is concerned, it is also necessary to check the quality of the design- it is not sufficient simply for data to meet the current user requirements- it is also necessary that it does this both efficiently and is structured in such a way as to avoid problems using the data in the future.

When considering data, there are three tests which can be applied:

• can you insert data without problems
• can you amend data without problems
• can you delete data without having undesirable side effects

Where these tests fail, you have what is known as an insertion, update or deletion anomaly.

An insertion anomaly arises when you have variable length or repeating data; for example where a data structure contains details of an invoice and the goods sold.

INVOICE (invoice-number, (product-id))

If the data structure to support this entity allowed 3 product-ids, you have a problem when coming to add a 4th product to invoice with invoice-number INV002.

An update anomaly occurs when you have data which is repeated.  The problem is that a programmer might only update the first occurrence of the data and not the subsequent data.  For example, in the following table:

INVOICE (invoice-number, customer-reference, customer-name...)

if customer C175 changed his/her name to WILSON, there is the danger that the second occurrence of customer C175 would not be updated.

A deletion anomaly occurs when you have a structure such as that shown immediately above.  If you delete invoice INV004, you loose the information that customer C183 is the customer FIKES (this is assuming it is not stored elsewhere).

Part b

For two attributes A and B in a given table, A is functionally dependent upon B if for every valid instance, the value of B determines the value of A.

Part c

Part d

1NF: First normal form states that an entity should have now repeating fields.  If there are repeating fields, there may be insertion anomalies when adding repeated data to the entity.

To obtain a structure in 1NF, you must remove the repeating attributes from the entity and place it in a new entity.  You must create a composite primary key in the new entity comprising the primary key in the original entity, plus one other attribute which will indicate the occurrence of the repeated data (1st occurrence, 2nd, 3rd etc.)

SHOP (shop-id, shop-town)
ITEM (shop-id, product-id, product-name, manufacturer, warranty, quantity, sale-price)

The structures SHOP and ITEM are now in 1NF.  ITEM is the new structure.  The attribute shop-id in ITEM is brought from the original SHOP entity, whilst product-id is sufficient to make the composite key unique provided that each product has a unique identifier.

2NF: Second normal form states that an entity must have all non-key fields dependent upon the whole of its primary key.  This means that by definition any structure with a simple primary key (i.e. made up from one attribute) is automatically in 2NF, such as SHOP.  

However ITEM has a composite primary key and a check must be made to ensure its non-key attributes are dependent upon the key and the whole of the key.

From the functional dependency diagram in part c, it is clear that sales-price and quantity are both dependent on shop-id and product-id.  But manufacturer and warranty are only dependent upon product-id.

Therefore we need to remove the attributes which are only dependent upon part of the primary key into a structure of their own.  This is shown as PRODUCT below.  product-id becomes the primary key of this new table.

SHOP (shop-id, shop-town)
ITEM (shop-id, product-id, quantity, sale-price)
PRODUCT (product-id, product-name, manufacturer, warranty)

3NF: Third normal form states that there must be no functional dependency between non-key fields.

Taking each structure in turn, we can check for dependencies.  SHOP has only one non-key field so must be in 3NF.  In ITEM, there is no dependency between quantity and sale-price, so it is in 3NF. 

However in PRODUCT, there is a dependency between manufacturer and warranty (as shown in the functional dependency chart in part c).

This is because we assume that a warranty is associated with a manufacturer, rather than a product or shop.  Checking the sample data for example, you can see that Suzuki has a warranty of 24 months.

Therefore, to create a 3NF structure, we must remove the dependent attribute from PRODUCT and create a new structure.  In the new structure, the fields are the dependent attribute (warranty) and the primary key is the attribute on which it is dependent (in this case manufacturer).

SHOP (shop-id, shop-town)
ITEM (shop-id, product-id, quantity, sale-price)
PRODUCT (product-id, product-name, manufacturer)
WARRANTY (manufacturer, warranty)

So to create a structure in 3NF, we must ensure that each structure:

• has no repeating fields
• has non-key attributes which are dependent upon the whole of the primary key
• and only the primary key.

It should be noted that 3NF is the commonest form of normal forms, but other forms such as 4NF and 5NF do also exist.

Show comments »