I'm getting trouble developing a 3NF stabilized database. Can anybody help please? I can not use whatever dependencies. To a genuine estate site. The table fields are the following:
ID - Type - Loc - Village - Purpose - Cost Range - Picture 1 - Picture 2 - Picture 3 - Status - Qualities - Description
Any assistance is appreciated. Thanks!
If type is really a mention of the another table, it's ok, else you have to place the types in another table. You can also produce a table for that pictures. Village can use another table if you would like more information concerning the towns after.
Typically you'd have tables such as this:
PropertyType Village Purpose Status Property
and there might be many qualities inside a particular village. Purpose and PropertyType may be dependent -- you can not make use of a Cottage like a gathering spot for a sizable audience, for instance, though you could utilize a Cottage like a "Single Family Dwelling" or as "Mattress and Breakfast". To represent this dependency you'd need this intermediary table:
PropertyTypePurposes propertytypeid purposeid
where for every property type you'd have a number of potential reasons. Then, inside your Qualities table, you wouldn't reference Reasons directly but would reference PropertyTypePurposes:
alter table qualities add constraint FK_Qualities_PROPERTYTYPEPURPOSES foreign key (propertytypeid, purposeid) references PropertyTypePurposes(propertypeid, purposeid)
To stay in 3NF, all that you should ensure is you haven't any transitive functional depencencies (which you do not have any non-full dependencies). If ID determines a tuple, and no other characteristics may be used to say for several what the need for another attribute should be, you are with 3NF, but additionally in BCNF.