Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table Normalization Expand / Collapse
Author
Message
Posted Saturday, November 20, 2010 10:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 29, 2013 9:50 PM
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.
Post #1023995
Posted Sunday, November 21, 2010 1:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1024002
Posted Sunday, November 21, 2010 1:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
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.


Post #1024004
Posted Sunday, November 21, 2010 9:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 27, 2012 6:19 PM
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.
Post #1024138
Posted Monday, November 22, 2010 4:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
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!
Post #1024226
Posted Monday, November 22, 2010 7:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:06 AM
Points: 593, Visits: 634
Here you could find a poster about normalization:

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


brgds

Philipp Post
Post #1024329
Posted Monday, November 22, 2010 1:12 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:31 AM
Points: 717, Visits: 3,037
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
Post #1024631
Posted Monday, November 22, 2010 10:37 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 20, 2014 8:47 AM
Points: 81, Visits: 162
poster is really nice.
Post #1024846
Posted Tuesday, November 23, 2010 1:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
Thanks Philipp for the nice poster! Really handy

Cheers
Post #1024907
Posted Tuesday, November 23, 2010 2:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:06 AM
Points: 593, Visits: 634
Brigadur (11/23/2010)
Thanks Philipp for the nice poster! Really handy

Cheers


You are welcome!


brgds

Philipp Post
Post #1024938
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse