Exercise 5: Normalisation exercises
The following tables are poorly designed. Normalise them into the 3rd normal form. Add the primary key (PK) and appropriate foreign keys (FK).
1. A software and harware production company
a) WORK: how many hours a person has spent for developing the application
- PersonId (PK)
- ApplicationId (PK)
- PersonName
- ApplicationName
- Hours
b) DEPARTMENT: who are (and has been) working in different departments
- Employee -- i.e. person
- DepartmentId (PK)
- DepartmentLocation
c) STOCK: which products and how many of them are located in a certain stock (i.e. store/warehouse)
- StockId (PK)
- ProductNumber (PK)
- StockLocation
- AmountInStock
d) PROJECT_ASSIGNMENT: who is working in a project (incl. salary) and how much money is allocated for a project
- PersonId (PK)
- PersonName
- Salary
- ProjectCode
- ProjectBudget
Let's assume that a person can work for several projects.
2. We are storing information about persons and skills
What would be the best solution for the following (add primary and foreign keys):
- DepartmentId (PK)
- Number (PK) -- persons are numbered automatically (incrementally) inside a department
- PersonName
- PersonAddress
- JobName
- SkillCode
- Salary
Examples of skills:
| SkillCode | SkillName |
|---|---|
| 01 | English |
| 02 | German |
| 03 | C# |
Document your solution properly (e.g. explaining your decisions). Write the learnings to your learning report.
© Ari Hovi ja Jouni Huotari 2003-2021