Normalisation from 1NF to 3NF

database-designnormalization

I need some guidance on normalisation from 1st Normal Form to 3rd Normal form. I have already set up my database which is working fine but needed to show some normalisation to show that I did do some design. MY only issue is that while going through the normal forms, have I actually normalised the database correctly or not?

Can somebody please have a look at my normalisation and show any changes I should make in their replies?

Screenshort of ER schema – For full size image click here:

And where I started. Screenshot of 0NF:

Screenshot of 1NF:

Screenshot of 2NF:

Screenshot of 3NF:

Best Answer

question.noofanswers. Why is it there? Cant you just pull this from answer table at run-time? Or are you enforcing rules on this?
Your student table looks like it has transitive functional dependencies in it but I can't be sure. Is year dependent on courseid? At any rate you are likely to want to break off student records and what courses they take since a student could take multiple courses over time.

Those are the only clear normalization/Codd's rules issues I see with this. I could be missing something though.

Expanding on the second. Take your student table. Presumably there is a natural key somewhere but for now let's use StudentEmail as a candidate key because this can uniquely identify a given student (if there is a practical problem in your case, you can use StudentID as a substitute for argument's sake).

In this case StudentForename, StudentSurname depend functionally on StudentEmail. For every email address there is one and only one forename and surname associated with it. You might want to break away StudentAlias as well because that would give you an ability to have more than one alias per student, but if you will not have that, it breaks no normalization rules.

The problem is that Year depends on (StudentEmail, CourseID) meaning you have a 2NF violation here, where some attributes are dependent on part of the candidate key and others are dependent on the whole candidate key. This really needs to be fixed before you can call it 3NF.

Related Solutions

Functional Dependencies and Normal Forms

1NF satisfied, as Y and Z depend on the key WX;
2NF satisfied as Y and Z depend on the whole key WX, and not on any subset of the key;
3NF not satisfied as Z depends on Y, which is not part of the key.

To put this into 3NF one would restructure as (W X Y) and (Y Z).

Can somebody help me normalise this database/check the workings

You need a one-to-many table to list the types of pets that a person has, like

create table people_pet_types (
  person_name varchar(...) references people(name),
  pet_type varchar(...),

  primary key (person_name, pet_type)
);

Assuming name is the primary key of People (which it shouldn't be, else you can't have two people with the same name).

This allows a person to have many pets types.

Also, "Age" and "Alive" should probably be derived from date_of_birth and date_of_death (nullable). Otherwise, you need to update the table to re-calculate the age at least every year. You can use a VIEW to calculate the age, and alive values for you.

Best Answer

Related Solutions

Functional Dependencies and Normal Forms

Can somebody help me normalise this database/check the workings

Related Question