Question about database normalization (1 Viewer)

MRaanan

New member
Local time
Today, 15:10
Joined
May 1, 2015
Messages
3
Howdy.

I have an IT class in which we're studying SQL.

We have a midterm coming up and we've been given a sample exam for studying. I've got everything locked down, just one thing left - Database normalization.

The question is:

1. When is the process of normalization used?

2. Describe BCNF

3. For the given relation, find all functional dependencies and possible anomalies implied from the functional dependencies. Put the relation into the appropriate BCNF.

EmployeeID | Name | Dept. | Title

100 Jones HR Executive director

200 Chau HR PR

205 Parker Marketing Clerk

300 Garret Accounting Accountant

400 Miller Marketing Clerk

I've been googling and reading for a while and still can't seem to really understand BCNF and normalization with all that XY dependency stuff. Can someone perheps help me with this question from the exam? I'm sure an actual answer for an example would explain it much better than any theoretical one would.

Thanks!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:10
Joined
Jan 20, 2009
Messages
12,849
Put as simply as possible I would say that normalization is a matter of having a data structure that makes it impossible to store two pieces of data that contradict each other.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Jan 23, 2006
Messages
15,362
MRaanan,

Don't overlook Google and youtube for finding information on any subject.
In addition to JHB's link and Galaxiom's comment, you might want to watch
https://www.youtube.com/watch?v=hTFyG5o8-EA . It may be a little more than you need, but it does give you some theory and examples to help put definitions into context and practice.

What level is this IT class?

Good luck.
 

MRaanan

New member
Local time
Today, 15:10
Joined
May 1, 2015
Messages
3
OK I think I got the first 2 parts answered, would appreciate if someone can verify my answers, but I still don't know what to do about the third part. Anyone?...

1. When is the process of normalization used?

If a database is designed in a flawed way, it may contain anomalies, which makes managing that database nearly impossible. The normalization of a database is a method to remove all anomalies and bring the database to a consistent state by designing it according to specific rules. In the normalization process we systematically examine relations for anomalies and when detecting some, remove those anomalies by splitting up the relation into two, related, relations. Finding problems with the database structure at an early stage is strongly important, when the other option being those problems detected only at a later stage.


2. Describe BCNF

BCNF stand for "Boyce and Codd Normal Form", named after the two who invented it. It is another version of the 3NF, and deals with certain type of anomaly that is isn't handled by 3NF.


3. For the given relation, find all functional dependencies and possible anomalies implied from the functional dependencies. Put the relation into the appropriate BCNF.



EmployeeID | Name | Dept. | Title

100 Jones HR Executive director

200 Chau HR PR

205 Parker Marketing Clerk

300 Garret Accounting Accountant

400 Miller Marketing Clerk
 

spikepl

Eledittingent Beliped
Local time
Today, 15:10
Joined
Nov 3, 2010
Messages
6,144
I read your answer to question 1.

I have no idea what all those fancy words mean. Therefore am also not convinced that you actually provided any answer to the question asked. Google has been suggested - use it.

Specifically: what is

- flawed way,
- anomalies
- nearly impossible
- consistent state
- specific rules
- anomalies in relations
- splitting up the relation into two, related, relations.
- strongly important,
- later stage.

There are no universal definitions for all these words, so you'd need attach a dictionary to your answer for it to become comprehensible. Google and try again.
 

MRaanan

New member
Local time
Today, 15:10
Joined
May 1, 2015
Messages
3
In the time it took you to write that messeage, you could either provide an insight whether it's correct or not, instead you've just spend perfectly good few minutes for bashing my English. That's really nice of you, when all I was doing is looking for some help.

You are the best, alright?
 

spikepl

Eledittingent Beliped
Local time
Today, 15:10
Joined
Nov 3, 2010
Messages
6,144
I am not bashing your English specifcally, but bashing your senseless regurgitation of incomprehensible or meaningless words.

With google you can in fact easily find specific comprehensible definitions for database normalization. Google is a great tool and critically googling information and extracting the relevant bits is IMHO nowadays a natural part of the entire process.

You asked for an opinion and got it. If you are p---d off that I did not do YOUR work for you is your problem, isn't it?
 

stopher

AWF VIP
Local time
Today, 14:10
Joined
Feb 1, 2006
Messages
2,396
I am not bashing your English specifcally, but bashing your senseless regurgitation of incomprehensible or meaningless words.

With google you can in fact easily find specific comprehensible definitions for database normalization. Google is a great tool and critically googling information and extracting the relevant bits is IMHO nowadays a natural part of the entire process.

You asked for an opinion and got it. If you are p---d off that I did not do YOUR work for you is your problem, isn't it?
With respect, the OP is clearly studying database design in an educational format and has asked for some help to understand some concepts. Sure, pretty much every question asked in these forums could be answered by googling. I'm sure the OP has googled "normalisation" until he/she is blue in the face. But if the penny hasn't dropped, it really doesn't matter. And I'll be honest here, I studied exactly what the OP is raising and I found the concepts tough to digest and I doubt even now I could get a good mark. We're not talking about the instinctive database design we do to create our tables. We're talking about the hard core theory that you need to pass exams. The terminology that the OP is using is very much the kind of terminology used in educational terms. It's not his/her fault that that's the way it is dealt out and that's the way they have to deliver it in an exam/test. Please respect their perspective.
 

stopher

AWF VIP
Local time
Today, 14:10
Joined
Feb 1, 2006
Messages
2,396
OK I think I got the first 2 parts answered, would appreciate if someone can verify my answers, but I still don't know what to do about the third part. Anyone?...

1. When is the process of normalization used?

If a database is designed in a flawed way, it may contain anomalies, which makes managing that database nearly impossible. The normalization of a database is a method to remove all anomalies and bring the database to a consistent state by designing it according to specific rules. In the normalization process we systematically examine relations for anomalies and when detecting some, remove those anomalies by splitting up the relation into two, related, relations. Finding problems with the database structure at an early stage is strongly important, when the other option being those problems detected only at a later stage.
I kind of get where spikepl is coming from. These reads like you've just mashed together stuff from various sites. It's a bit wordy and I think you've still got to get nailed what normalisation is trying to address. Anomolies is a key word but I think you need to make clear what this means. 1NF,2NF 3NF etc each deal with specific things. Get clear in your head what each does and you should be able to summarise.

2. Describe BCNF

BCNF stand for "Boyce and Codd Normal Form", named after the two who invented it. It is another version of the 3NF, and deals with certain type of anomaly that is isn't handled by 3NF.
I kind of think of bcnf as 3NF with belts and braces. Specifically it deals with multiple overlapping keys which 3NF doesn't deal with afai remember. An example might be hotel bookings where we might have a bookingID as a key but also have Date/room Number as a key. In BCNF we don't have two overlapping keys in a relation. So we split our relation into two new relations to arrive at bcnf. Not 100% sure on this!

3. For the given relation, find all functional dependencies and possible anomalies implied from the functional dependencies. Put the relation into the appropriate BCNF.



EmployeeID | Name | Dept. | Title

100 Jones HR Executive director

200 Chau HR PR

205 Parker Marketing Clerk

300 Garret Accounting Accountant

400 Miller Marketing Clerk
When I look a Q3 don't over-complicate it - it's really quite an easy example unless I've got it wrong. Don't think real world though as you will make wrong assumtions about the information you've been given, just work on exactly the data that is provided. From the data you can derive your dependencies and hence answer the question. Take a look at this link particularly the last bit.
As a clue, Dept and Title is not a key because marketing/clerk is repeated and therefore cannot be used to uniquely identify a record <tuple?>

note: it's been a while since i studied this stuff so I may be off the mark :D
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Jan 23, 2006
Messages
15,362
What level is this IT class?
 

stopher

AWF VIP
Local time
Today, 14:10
Joined
Feb 1, 2006
Messages
2,396
What level is this IT class?
I've no idea what level or course the OP was studying but I studied all this stuff in a an Open University course in the UK. The course was a level 3 course meaning it's a 3rd year module towards a degree. The course was M359 Relational Databases: Theory and Practice and the course overview can be found here. See section 7.2 of the course overview for the relevent section.

One of the OU lecturers has done a website here for his students on the course. Also see here for his take on functional dependencies. Also see page 12 onwards here.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Jan 23, 2006
Messages
15,362
Thanks stopher for the links, they're very good.
In my earlier post I asked to OP what the class level was to get some idea of the proposed answer.

I think the materials you pointed us to are relevant to a range of audiences and contexts.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:10
Joined
Feb 28, 2001
Messages
26,999
MRaanan - for "Why normalize?" I would respond in this way:

Databases have the potential to be laid out in ways that could inadvertently lose track of implied data (because the implied data elements were not stored correctly) or could cause wasteful storage of data by duplicating data that need not be duplicated or by providing space for storage of data that is not always used. Normalization is the process of removing the potential for these situations by following certain simple rules that lead to the separation of data into related but distinct tables that store data in ways to avoid unintended data loss, wasted space, or excessive data duplication.

This is perhaps a bit oversimplified but doesn't involve quite so many "buzz-words" - which I believe is the basis of Spike's and Stopher's comments.

BCNF is something I rarely worry about because it requires specific applications for this to become an issue. I therefore must defer to my fellow forum members on this one.

As to the 3rd question, you should take a look at the rules of how you normalize data and tackle that one yourself.
 

Rx_

Nothing In Moderation
Local time
Today, 08:10
Joined
Oct 22, 2009
Messages
2,803
There are some good articles, tutorial and blogs at SQL Server Central.
When I go to MS SQL Server meetings, they can spend... hours ... (it seems) on the subject. Of course, with large data bases, it can have huge impacts.
For my graduate class, the use of Discrete Math was important for descriptions.
Example:
http://www.sqlservercentral.com/articles/Normalization/74437/
The easiest definition to understand is
A relation R is in 3NF if and only if (i) it is in 2NF and (ii) whenever X is a set of attributes of R and A is a non-key attribute of R that is not in X, the value of A can be determined from the values of X only if X includes a candidate key.
Actually, the first clause of this could refer to 1NF instead of 2NF, because given 1NF it's clear that (ii) implies (i); but it's traditional to say for each normal form that it requires the next lower normal form, to avoid messy proofs.

Also - Article on Normalization Primer -
http://www.sqlservercentral.com/articles/T-SQL/normalization/584/

This was written in 2003. Would suggest not much has changed.
 

Users who are viewing this thread

Top Bottom