1. Draw the Dependency Diagram that is in 3NF for each relation. For each relation show the level of Normalisation achieved, and for any not to Third Normal Form, explain why. Discuss PK and FK for each relation.
2. 
   
3.  Data Dictionary that translates your relational data structures, which includes (as in the Workshop Slide: Session 3, Slide 23):
4. table names,
5. column names and field types
6. primary keys identified

 

 

Transcribed Image Text:

A SAMPLE DATA DICTIONARY FK REFERENCED TABLE NAME ATTRIBUTE NAME CONTENTS TYPE CUSTOMER CUS_CODE CUS_LNAME Customer account code CHAR(5) Customer last name VARCHAR(20) XXXXXXXX CUS_FNAME Customer first name VARCHAR(20) XXXXXXXX FORMAT RANGE 99999 10000-99999 Y Y Y REQUIRED PK OR FK TABLE PK CUS_INITIAL Customer initial CHAR(1) X CUS RENEW_DATE Customer insurance DATE dd-mmm-yyyy renewal date AGENT_CODE Agent code CHAR(3) 999 AGENT AGENT_CODE Agent code CHAR(3) 999 Y FK AGENT PK AGENT_AREACODE Agent area code CHAR(3) 999 Y AGENT_PHONE Agent telephone CHAR(8) 999-9999 Y number AGENT_LNAME Agent last name VARCHAR(20) XXXXXXXX Y AGENT_YTD_SLS Agent year-to-date sales NUMBER(9,2) 9,999,999.99 FK = Foreign key PK =Primary key CHAR = Fixed character length data (1 - 255 characters) VARCHAR NUMBER =Variable character length data (1 - 2,000 characters) = Numeric data. NUMBER (9,2) is used to specify numbers with up to nine digits, including two digits to the right of the decimal place. Some RDBMS permit the use of a MONEY or CURRENCY data type. A SAMPLE DATA DICTIONARY FK REFERENCED TABLE NAME ATTRIBUTE NAME CONTENTS TYPE CUSTOMER CUS_CODE CUS_LNAME Customer account code CHAR(5) Customer last name VARCHAR(20) XXXXXXXX CUS_FNAME Customer first name VARCHAR(20) XXXXXXXX FORMAT RANGE 99999 10000-99999 Y Y Y REQUIRED PK OR FK TABLE PK CUS_INITIAL Customer initial CHAR(1) X CUS RENEW_DATE Customer insurance DATE dd-mmm-yyyy renewal date AGENT_CODE Agent code CHAR(3) 999 AGENT AGENT_CODE Agent code CHAR(3) 999 Y FK AGENT PK AGENT_AREACODE Agent area code CHAR(3) 999 Y AGENT_PHONE Agent telephone CHAR(8) 999-9999 Y number AGENT_LNAME Agent last name VARCHAR(20) XXXXXXXX Y AGENT_YTD_SLS Agent year-to-date sales NUMBER(9,2) 9,999,999.99 FK = Foreign key PK =Primary key CHAR = Fixed character length data (1 - 255 characters) VARCHAR NUMBER =Variable character length data (1 - 2,000 characters) = Numeric data. NUMBER (9,2) is used to specify numbers with up to nine digits, including two digits to the right of the decimal place. Some RDBMS permit the use of a MONEY or CURRENCY data type.