Is this Normalized???

[jwaldron82]

I'm designing a database model for a Gallery. Never done Normalization before, I did some research online and came up with these rules, followed it and this is what I came up with. Trying to get to from UNF to 3NF. Are these stages correct? or am I way off... Much Thanks.
Un-Normal Form (UNF)
Owner ( OwnerID, OwnerName, OwnerContact, ArtID, ArtTitle, ArtistName , Exhibtion, ExhibitionTheme)

In order to get to first Normal form I located a primary Key and identified the part key dependencies.
The second table was comprised of a composite key.
Hence, Ist Normal Form
Owner (PK OwnerID, OwnerName, OwnerContact)
Exhibition (FK OwnerID, PK ArtID, ArtTitle, ArtistName , Exhibtion, ExhibitionTheme)

Not sure how to get to 2NF or 3NF,...

[Ramy Mahrous]

separate Exhibition and Art tables, I think the relation should be many-to-many so you need to use bridge table

[pclfw]

Un-Normal Form (UNF)
Owner ( OwnerID, OwnerName, OwnerContact, ArtID, ArtTitle, ArtistName , Exhibtion, ExhibitionTheme)

1) First normal form removes repeating items. http://en.wikipedia.org/wiki/First_normal_form Here the repeating items can be OwnerContact (an owner may have a number of different contact details), ArtID (one owner may have may items in an exhibition), ArtistName (it is possible that a number of artists colaborated on the piece of art.)

So the new relationships start to take place.

OWNER
rowid
OwnerName

OWNERCONTACT
rowid
OwnerId (FK to OWNER.rowid)
Contact

ARTITEM
rowid
OwnerId (FK to OWNER.rowid)
Title

ARTIST
rowid
ArtId (FK to ARTITEM.rowid)
Name

EXHIBITION
rowid
ArtId (FK to ARTITEM.rowid)
Theme

Here there is a problem that the EXHIBITION table that grew from the base data contains repeating data (ArtId). So we need to change EXHIBITION and add another table.

EXHIBITION
rowid
Theme

EXHIBIT
rowid
ExhibitionId (FK to EXHIBITION.rowid)
ArtId (FK to ARTITEM.rowid)

2) Second Normal Form http://en.wikipedia.org/wiki/Second_normal_form The data items depend upon the whole of the table key and not part of the key.

So the 2FN for this example is

OWNER
rowid
OwnerName

OWNERCONTACT
rowid
OwnerId (FK to OWNER.rowid)
Contact

ARTITEM
rowid
OwnerId (FK to OWNER.rowid)
Title

ARTIST
rowid
ArtId (FK to ARTITEM.rowid)
Name

EXHIBITION
rowid
Theme

EXHIBIT
rowid
ExhibitionId (FK to EXHIBITION.rowid)
ArtId (FK to ARTITEM.rowid)

3) Third Normal Form http://en.wikipedia.org/wiki/Third_normal_form is a stricter application of the 2FN rules. Here though the data seems to fulfill the 3FN rules as well. So the 3FN form for the given example is shown in the 2FN section.

Simple isn't it. :)

PCLFW