I’m a beginner, studying Data modelling as part of the course. I’m having trouble understanding Normalisation. How to i improve

database-designnormalization

It was difficult to frame the title given the word limit, so apologies for that.

Currently I'm studying DBA and Data modelling as a part of my ICT Apprenticeship course in a Web Development team. I'm kind of grappling with understanding Normalisation. I understand the concept, of eliminating redundant data, stopping anomalies, using keys and relationships, but in practice I'm struggling. In an effort to help me understand, I've tried to make and normalise a small table of information:

My UNF table:

My attempt at normalising the table to 3NF:

So, questions. I suppose straight away, from an expert, am I doing this right? Or am i completely goofing up? Or am i settling in the middle?

Right now I'm worried that, in this example, the 'Assignments' table has Employee ID as a Foreign Key, meaning Porject ID would have to be the Primary. But that seems counter intuitive seeing as the 'Projects' table should be using Project ID as the primary key. And I'm not quite sure how i should proceed in a situation like this.

Apologies if this question is too rookie/generally not appropriate for the forum. I've had some trouble finding basic resources that cover this material.

Many thanks.

Best Answer

Just looking at an example of a table is a wrong way of normalizing. You should understand the meaning of the fields to properly normalize. For instance you should know if the salary is the same for all the persons that have a certain role, or if it differs from person to person (in the above example we cannot know, since each role has only a person, so that both interpretations are possible).

To know the meaning of the properties you have two routes: either know the meaning of the elements of the domain, through a high level model of it, (for instance through an Entity-Relationship model or a UML model), or to have formalized this knowledge through the definition of the functional dependencies holding between the properties.

In your example there are two points that are not clear (that this that are not clear from the example table): is the salary related to a role or to a person? Is the Est. Time related to a project or to the fact that an employee works for a certain project for that estimated time? If the correct anwer to those question is the first one in both cases, your schema is correct (or normalized). Otherwise you must revise it.

Related Solutions

Confused about Primary Keys, and Foreign Keys

Teaching yourself database design is indeed very tricky. You might benefit from finding a good tutorial that would help you master the basics. If you want suggestions concerning a good learning vehicle, ask.

Here is a very oversimplified summary of some of the basics.

Before you can design a database, you have to analyze the subject matter in some detail. To quote GregH, "completely understanding how the business works". One way to do this is to get some reliable source on Museums and how they work in the real world, or whatever approximation to the real world you are willing to settle for. You may rely on your own memory for this, but it's better to get some input from experts on Museums, your subject matter. ER modeling can help summarize what you learn.

Another thing you need to spend a little time on is how you intend to use the data. Your design can be influenced by your intended use. This can turn out to be a moving target. You may decide to use the data in more than one way, somewhere in the future. Think ahead as best you can.

Then, you are ready to design your tables. Normalisation is one fairly mechanical approach to avoiding certain pitfalls that can creep up on you if you just design at random. You table design should reflect the relational model of data. The relational model is slightly different from the ER model, in that foreign keys are used to link tables together. But the question of which table non key data should go in is nearly impossible to get right unless you have a clear understanding of the subject matter.

good luck.

Normalisation to 3NF

The card is UNF. Everything is one row. 3NF is when you make a list of the things, and each list of things has a unique item list, and you link them using thing-item-id.

[list] Form [items] FormID, FormName, FormTemplate (definition of    report) 
[list] Address [items] AddressID, StreetNumber, StreetName...    (address data) 
[list] Vet [items] VetID, VetName 
[list] Species    [items] SpeciesID, SpeciesName 
[list] ExaminationType [items]    ExaminationTypeID, ExaminationTypeName, Cost   
[list] Patient [items]    PatientID, PatientName, PatientSpeciesID REFERENCES Species (SpeciesID)
[list] Appointment [items] PatientID REFERENCES Patient(PatientID), ExaminationTypeID REFERENCES ExaminationType(ExaminationTypeID), Date, VetID REFERENCES Vet(VetID)

Data is uniquely stored, referenced in table containing transactional data. Form template would have RDL for creating the card, from the relevant list data. The idea behind 3NF is store related information in the same list, only once, and reference that data wherever applicable. Gets complicated when Vet changes name, but you want to reprint previous statement with Vet's previous name ... :)

Best Answer

Related Solutions

Confused about Primary Keys, and Foreign Keys

Normalisation to 3NF

Related Question