Post reply

Table Normalization

  • November 20, 2010 at 10:08 pm

    #97532

    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.

  • November 21, 2010 at 1:07 am

    #1253464

    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

  • November 21, 2010 at 1:45 am

    #1253466

    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

  • November 21, 2010 at 9:57 pm

    #1253576

    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.

  • November 22, 2010 at 4:04 am

    #1253649

    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!

  • November 22, 2010 at 7:37 am

    #1253735

    Here you could find a poster about normalization:

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

    brgds

    Philipp Post

  • November 22, 2010 at 1:12 pm

    #1253994

    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

  • November 22, 2010 at 10:37 pm

    #1254178

    poster is really nice.

  • November 23, 2010 at 1:35 am

    #1254228

    Thanks Philipp for the nice poster! Really handy

    Cheers

  • November 23, 2010 at 2:45 am

    #1254254

    Brigadur (11/23/2010)

    Thanks Philipp for the nice poster! Really handy

    Cheers

    You are welcome!

    brgds

    Philipp Post

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply