Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Normalization


Table Normalization

Author
Message
parivedamohan
parivedamohan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 18
Hi All,

I am new to the Database world.
I would be very happy if any body Normalize the below table, which is in First Normal Form (1NF).

Table:
CREATE TABLE BoatBooking
(Week NUMBER(2),
BoatName VARCHAR2(20),
BoatLength NUMBER,
ExtraRequest VARCHAR2(20),
SkipperPno NUMBER,
SkipperName VARCHAR2(40),
SkipperAddress VARCHAR2(40),
CrewPno NUMBER,
CrewName VARCHAR2(40),
CrewAddress VARCHAR2(40)) ;
GRANT SELECT ON BoatBooking TO PUBLIC ;

Thanks in advance.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47285 Visits: 44392
Homework?

First things first. Do you know what the requirements are for 2nd and 3rd normal forms?

Secondly, that table is not in 1st normal form. To be in first normal form, there must be at least one candidate key that the data depends on. You have no keys listed. What are the candidate keys for that table?

Third, those data types are Oracle data types, not SQL Server. It won't matter for the purposes of normalisation, but that will never run on a SQL Server instance.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sachin Nandanwar
Sachin Nandanwar
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 2633
parivedamohan (11/20/2010)
Hi All,

I am new to the Database world.
I would be very happy if any body Normalize the below table, which is in First Normal Form (1NF).

Table:
CREATE TABLE BoatBooking
(Week NUMBER(2),
BoatName VARCHAR2(20),
BoatLength NUMBER,
ExtraRequest VARCHAR2(20),
SkipperPno NUMBER,
SkipperName VARCHAR2(40),
SkipperAddress VARCHAR2(40),
CrewPno NUMBER,
CrewName VARCHAR2(40),
CrewAddress VARCHAR2(40)) ;
GRANT SELECT ON BoatBooking TO PUBLIC ;

Thanks in advance.


For us to make you happy you need to explain what are the business requirements.From a brief glance I think you are relating skipper & crews to a particular boat.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
christine.lawrie
christine.lawrie
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 126
At the very least, you need to take the fields relating to Skipper and Crew Member out into separate tables, related with SkipperNo and CrewMemberNo.

That assumes you only have one crew member and one skipper per boat. You also need to understand 1:N and M:N relationships, and if you don't understant those, I suggest you read your textbook before getting homework help.
Brigadur
Brigadur
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 27695
Although this is not a SQL Server table, still, this is a SQL Server forum ;-).

So here are some links to information about normalisation, right from the vendor:
Description of the database normalization basics
Normalization

There are wealth of other information on the web in different blogs and vendor(Oracle, Microsoft, IBM) websites.
Normalization and database design are great topics! Good luck!
Philipp Post
Philipp Post
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 634
Here you could find a poster about normalization:

http://www.tf-informatik.dk/FreeStuff/rettigNormalizationPoster.pdf

brgds

Philipp Post
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 3661
Ask yourself some questions before you get to the SQL. You have a table you've called BoatBooking, so presumably we are booking (making reservations) for a boat by someone for some purpose. So.....

1. Can a customer book different boats at different times?

2. Can a single boat have more than one name and length at the same time? If not, where do the independent boat parameters belong?

3. Does customer information (name, address, phone) depend on which boat they've booked?

4. Can a skipper work on different boats at different times?

5. Can someone crew on different boats at different times?

etc.

Every time one of these questions will help you think about which attributes (name, length, date, etc.) are dependent on -- or independent from -- others. Which in turn will help you think about whether those attributes belong in the same table or not.

Good luck,
Rich
fawwad
fawwad
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 162
poster is really nice.
Brigadur
Brigadur
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 27695
Thanks Philipp for the nice poster! Really handy

Cheers
Philipp Post
Philipp Post
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 634
Brigadur (11/23/2010)
Thanks Philipp for the nice poster! Really handy

Cheers


You are welcome!

brgds

Philipp Post
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search