The company provides courier services all over world. The idea is to provide fast transport of
low quantity goods to their destination places as fast as possible. The customers draw up short
time contracts with the company which expire in the moment of fulfilling the stated
agreement points. For good control of the deliveries Misterlink needs a very specific database.
Misterlink has some storage houses and company units scattered on a large area. Each unit
has control over a specific region. Some units are small and contain only one department, but
there are also large units containing several departments responsible for example for logistic
planning, managing, accountancy etc. Most bigger units have control over the neighboring
storages as well.
The firm employs several dozen people working as drivers, managers, logistic experts and so
on. Each administrative worker should be assigned to a specific administrative unit and
department he is working in. The drivers need to be assigned to regions as well. Some
employees can have access to company cars if the situation forces the company to deliver a
package very quickly and there are no transport lorries in the region, whereas drivers must be
assigned to use specific cars for specific orders – to avoid transporting a XVII century table
with a small lorry. The company is in possession of many different personal and transport
vehicles so there should be a possibility to quickly input the car types into the database.
The company possesses a detailed description of customers that have placed any order. For
the invoice preparation several formal details should be fulfilled (for example a Mr., Mrs.
prefix or a NIP if the order was placed by another company).
Orders should have a day specification containing which cars were used to transport the good,
who was driving them etc. There should also be a possibility to view where the package
currently is for example by a internet service. Packages should be classified by the
type/weight of the package and should have a specific price depending on it. The problem of
undelivered packages should be solved as well (for example damaged packages, or packages
that were not delivered). Each returned package should be stored in a company storehouse and
should have a note what the company is supposed to do with it.
It could prove quite difficult to transport a package from Europe to America by car so it
doesn’t have to be delivered by car, it can also be transported with the help of an outer
transport company for example by air, railroad transport etc.
3-RD NORMAL FORM DEVIATIONS
The only deviation from the 3-rd normal form that I used within my project was the fact of
separating the NIP entity from the CUSTOMER column whereas it should be classified as an
entity attribute. The reason for that was as follows: I believe that the invoice containing data
from this entity should have detailed description of the person placing an order and so 2
different people can be connected to 1 NIP number because they work for the same company
but placed orders separately. If NIP would be an UNIQUE attribute in the CUSTOMER entity
then some problems would occur. Of course we could make the whole combination unique
but then if someone wouldn’t have a NIP (for example a student) then the UNIQUE
combination would have to contain a NULL value.