Database Design: Creating a Data Dictionary

Introduction

• Focus on how to create a data dictionary.
• Example used: Andy's E-Brake System (Learner, Instructor, Vehicle, Lesson).
• Continue from previous lecture where field names were identified for entities.

What is a Data Dictionary?

• Defines the structure of data (types of data in fields, how data is stored).
• Helps build the database structure.
• Example form includes:
  • Title: Data Dictionary.
  • Entity/Table name.
  • Rows/Columns for the table definition.

Developing the Learner Entity

Primary Key

• Learner ID:
  • Field name: Learner ID.
  • Type: AutoNumber (system auto-generates, unique identifier).
  • No caption needed.
  • No need to define field size or add notes.

Foreign Key

• Instructor ID:
  • Field name: Instructor ID.
  • Caption: Usual Instructor (user-friendly).
  • Data type: Long Integer (matches primary key in Instructor table).

Non-Key Fields

• First Name:
  • Field name: strFirstName.
  • Caption: First Name.
  • Data type: Text (letters, numbers).
  • Field size: 20 characters.

Additional Text Fields

• Last Name (field size: varies for last names).
• Address fields (House Number, Street, Locality, etc. with varying sizes).

Special Fields

• Postcode:

  • Data type: Text.
  • Field size: 10 characters.
  • Input Mask to define structure (e.g. placeholder and format for postcode).

• Telephone Number:

  • Data type: Text.
  • Input Mask includes brackets and spaces (e.g. (00000) 000 000).

Other Fields

• Mobile Number (similar to Telephone Number).
• Date of Birth:
  • Data type: DateTime.
  • Input Mask for DOB format (e.g., DD MMM YYYY).
• License (data type: Text, includes Input Mask).
• Passed Test (data type: DateTime).
• Notes (data type: Memo for large text entries).
• Level (data type: Text, size: 19 for longest word).
• Passed Theory (data type: Yes/No).
• Transmission (field name: strTransmission, Text type, sizes for Manual/Automatic).

Data Types Summary

• Text: Any group of characters (up to 255 characters).
• Memo: Large text entries (up to 65,000 characters).
• Number: Numeric values (integers, fractional numbers, etc.).
• Date/Time: Specific format for dates and times.
• Yes/No: Binary choice fields.

How to Identify Numbers and Text

• Leading zeros indicate text (e.g., 01442).
• Non-numeric characters (e.g., brackets, spaces) indicate text.
• No logical calculations on the field (for telephone numbers, addresses).

Input Masks Explained

• 0: User must enter a digit.
• 9: User can enter a digit.
• Letters for specific character requirements.
• Symbols to define format constraints (e.g., brackets).

Conclusion

• Completed the learner table design in the data dictionary.
• Covered Instructor, Vehicle, Lesson in similar approaches.
• Introduced basic data types and discussed their usage.
• Explained input masks and when data should be text instead of numbers.