1nf, 2nf, 3nf help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

1nf, 2nf, 3nf help

Author

Topic

lilmoore1000
Starting Member

2 Posts

Posted - 2012-05-01 : 00:11:27

Okay, i am creating a database for my CSIS class, i am confused by 1nf, 2nf, and 3nf in my own tables, i was wondering if some of you more experienced with SQL could take a look at my Tables and tell me if they are in these forms. They are very basic tables, about random things. thank you!!!

GA GAME_NAME GAME_DEVELOPER RELEASE_D
-- --------------- --------------- ---------
1 Halo Reach Bungie 14-OCT-11
2 BattleField 3 Dice 25-OCT-11
3 Modern Warfare3 Activision 08-NOV-11
4 HomeFront THQ 15-MAR-11
5 Halo 4 343 Studios 06-NOV-12

Second Table

MLB_TEAM GAMES HR BA SLG
-------------------- ----- ----- ---------- ----------
Chicago White Sox 22 23 .241 .398
Cleveland Indians 20 16 .244 .371
Detroit Tigers 22 23 .243 .391
Kansas City Royals 21 20 .263 .424
Minnesota Twins 21 14 .266 .4

Any feedback or help understanding these would be greatly help me

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-01 : 06:04:50

Your tables seem to be Normalized. Following are two links which helped me with Normalization. Hop they help you too:

[url]http://www.phlonx.com/resources/nf3/[/url]

[url]http://www.bkent.net/Doc/simple5.htm[/url]

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-01 : 08:01:40




Table1:  Create below table with foreign key reference to Tabl2  
DeveloperId is a Foreign key 

GA   GAME_NAME    RELEASE_D  DeveloperId 
--    --------------- --------------- ---------
1    Bungie       14-OCT-11   01
2    3 Dice       25-OCT-11   02
3    Activision   08-NOV-11   03
4    THQ          15-MAR-11   04
5    343 Studios  06-NOV-12   05

Table2: DeveloperId  is  a primary key key 

DeveloperId GAME_DEVELOPER 
01          Halo Reach 
02          BattleField
03          Modern Warfare3
04          HomeFront
05          Halo 4 

Your first table is in Third normal form

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 10:14:55

quote:

Originally posted by vijays3




Table1:  Create below table with foreign key reference to Tabl2  
DeveloperId is a Foreign key 

GA   GAME_NAME    RELEASE_D  DeveloperId 
--    --------------- --------------- ---------
1    Bungie       14-OCT-11   01
2    3 Dice       25-OCT-11   02
3    Activision   08-NOV-11   03
4    THQ          15-MAR-11   04
5    343 Studios  06-NOV-12   05

Table2: DeveloperId  is  a primary key key 

DeveloperId GAME_DEVELOPER 
01          Halo Reach 
02          BattleField
03          Modern Warfare3
04          HomeFront
05          Halo 4 

Your first table is in Third normal form

what if multiple developers were part of developing a game?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 10:17:51

if relationship is many to many i would do design like
Game (GA, GAME_NAME, RELEASE_D)
Developer (DeveloperId, GAME_DEVELOPER )
GameDeveloper (DeveloperId (fk to Developer.DeveloperId),GameID (fk to Game.GA))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-01 : 10:29:41

quote:
Originally posted by visakh16

if relationship is many to many i would do design like
Game (GA, GAME_NAME, RELEASE_D)
Developer (DeveloperId, GAME_DEVELOPER )
GameDeveloper (DeveloperId (fk to Developer.DeveloperId),GameID (fk to Game.GA))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Your design is correct ..I only though about single developer But in my case I can add extra column Like ID coulum  Which can uniquely the records .See records 15 and 16 
ID  GA   GAME_NAME    RELEASE_D  DeveloperId 
     --    --------------- --------------- ---------
11     1    Bungie       14-OCT-11   01
12     2    3 Dice       25-OCT-11   02
13     3    Activision   08-NOV-11   03
14     4    THQ          15-MAR-11   04
15     5    343 Studios  06-NOV-12   05
16     5    343 Studios  06-NOV-12   06

Please comment on this .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 10:40:00

this is also not fully normalised as you've all details for the game repeated for every developer who was part of development team. This can still cause update anomalies

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-01 : 11:26:40

quote:
Originally posted by visakh16

this is also not fully normalised as you've all details for the game repeated for every developer who was part of development team. This can still cause update anomalies

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes you are correct ..I will go with your design .Thanks for the comment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 11:58:31

quote:
Originally posted by vijays3

quote:
Originally posted by visakh16

this is also not fully normalised as you've all details for the game repeated for every developer who was part of development team. This can still cause update anomalies

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes you are correct ..I will go with your design .Thanks for the comment

No problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources