Logical Design

 

3NF

 

l     PK means Primary Key;

l     FK means Foreign Key.

 

COUNTRY

 

ZIP

 

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