Database Management for Speed Delivery Company

Project at Management Information Systems

1. Problem description

A speed delivery company provides services for their clients.

The client has the following attributes: ID client, client name, client address, client age, client birth-date.

The services of the company are accomplished by delivery agents who have the following attributions: ID agent, agent name, hiring date.

For the packages delivered, an invoice is issued with the following attributes: ID invoice, invoice date, due date, ID client, client name, ID agent, agent name, invoice value.

An invoice is issued by an agent for only one client.

A package has the following attributes: ID package, package color, package weight.

For each package delivered the number of packages is mentioned.

An invoice can contain many packages. A certain package can be on many invoices.

After the client receives the invoice he goes to the bank to pay. The attributes of the bank are: ID bank, bank name, bank address.

The invoice can be paid only at one bank.

2. Data dictionary

IDClient, ClientName, ClientAddress, ClientAge, ClientBirthdate, IDAgent, AgentName, HiringDate, IDInvoice, InvoiceDate, DueDate, InvoiceValue, IDPackage, PackageColor, PackageWeight, IDBank, BankName, BankAddress, Quantity.

3. Business rules

1 Agent → n Invoices 1 Invoice → 1 Agent

1 Client → n Invoices 1 Invoice → 1 Client

1 Bank → n Invoices 1 Invoice → 1 Bank

1 Package → 1 Invoices 1 Invoice → n Packages

4. First normal form

ClientAge has to be eliminated because it is a non-atomic attribute.

IDClient, ClientName, ClientAddress, ClientBirthdate, IDAgent, AgentName, HiringDate, IDInvoice, InvoiceDate, DueDate, InvoiceValue, IDPackage, PackageColor, PackageWeight, IDBank, BankName, BankAddress, Quantity.

5. Second normal form

IDClient

→ClientName

→ClientAddress

→ClientBirthdate

IDAgent

→AgentName

→HiringDate

IDPackage

→PackageColor

→PackageWeight

IDBank

→BankName

→BankAddress

IDInvoice

→InvoiceDate

→DueDate

→InvoiceValue

→IDClient

→IDAgent

→IDBank

IDInvoice

IDPackage

→Quantity