Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-01-2015, 01:05 AM   #1
MRaanan
Newly Registered User
 
Join Date: May 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
MRaanan is on a distinguished road
Question about database normalization

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!

MRaanan is offline   Reply With Quote
Old 05-01-2015, 02:29 AM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Question about database normalization

Have you been watching that video?
https://www.youtube.com/watch?v=Xo6IRbNgeXo
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 05-01-2015, 03:16 AM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,648
Thanks: 97
Thanked 1,499 Times in 1,414 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Question about database normalization

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.

Galaxiom is offline   Reply With Quote
Old 05-01-2015, 04:36 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,220
Thanks: 91
Thanked 2,022 Times in 1,970 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Question about database normalization

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 05-02-2015, 08:37 AM   #5
MRaanan
Newly Registered User
 
Join Date: May 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
MRaanan is on a distinguished road
Re: Question about database normalization

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
MRaanan is offline   Reply With Quote
Old 05-02-2015, 09:32 AM   #6
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Question about database normalization

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.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 05-02-2015, 09:41 AM   #7
MRaanan
Newly Registered User
 
Join Date: May 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
MRaanan is on a distinguished road
Re: Question about database normalization

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?

MRaanan is offline   Reply With Quote
Old 05-02-2015, 09:51 AM   #8
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Question about database normalization

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?
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 05-02-2015, 12:20 PM   #9
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Question about database normalization

Quote:
Originally Posted by spikepl View Post
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 is offline   Reply With Quote
Old 05-02-2015, 12:41 PM   #10
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Question about database normalization

Quote:
Originally Posted by MRaanan View Post
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.

Quote:
Originally Posted by MRaanan View Post
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!

Quote:
Originally Posted by MRaanan View Post
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
stopher is offline   Reply With Quote
Old 05-02-2015, 03:34 PM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,220
Thanks: 91
Thanked 2,022 Times in 1,970 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Question about database normalization

What level is this IT class?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 05-02-2015, 11:27 PM   #12
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Question about database normalization

Quote:
Originally Posted by jdraw View Post
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.
stopher is offline   Reply With Quote
Old 05-03-2015, 04:42 AM   #13
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,220
Thanks: 91
Thanked 2,022 Times in 1,970 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Question about database normalization

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 05-11-2015, 08:53 AM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,540
Thanks: 92
Thanked 1,678 Times in 1,558 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Question about database normalization

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-11-2015, 11:36 AM   #15
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: Question about database normalization

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/arti...ization/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/arti...alization/584/

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

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
database normalization - question on practices of this Maximus Primal Theory and practice of database design 17 08-18-2010 05:32 PM
Normalization question Acenmbr1 General 8 03-17-2008 06:22 PM
Normalization question CEH General 8 12-13-2007 05:36 PM
Access Tamplate "Contact Management" Database Normalization Question Crapaw General 2 07-07-2004 05:32 AM
normalization question peterbowles General 7 04-02-2003 07:22 AM




All times are GMT -8. The time now is 12:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World