Logical
Design
3NF
l
PK means Primary Key;
l
FK means Foreign Key.
l The city and the
state/province can be decided if one provides his/her country and zip
information. So, COUNTRY and ZIP should be considered as new entities due to
normalization. The reason we use an artificial PK Zip_No in ZIP instead
of the combination of Zip_Code and Country_No
is because thus we only pass one FK to USERS and MEMBER tables.
Note:
Zip_Code alone cannot decide city or state/province, because cities in
different
countries may use same zip codes.
INSTITUTE
POSITION
USERS
MEMBER
SCIENTIST
l © stands for “calculated”.
HOLD
MODURATOR
QESTEDIT
MESSAGE
REPLY
AREA
INTERESTS
KEYWORD
LINK
LINKWORD
l Break down the many-to-many
relationship between LINK and KEYWORD to a new table: LINKWORD. The FKs should be indexed.
GLEVEL
EXPERIMENT
EXPWORD
l Break down the many-to-many
relationship between EXPERIMENT and KEYWORD to a new table: EXPWORD. The FKs should be indexed.
DIRECTORY
STATUS
QUESTION
QUESWORD
l Break down the many-to-many
relationship between QUESTION and KEYWORD to a new table: QUESWORD. The FKs should be indexed.
PASS
DELINQUENCY
DEFER
ANSWER
ANSEDIT
REJECT
RANK
ACCEPT