Data dictionary

Course: BTEC National Diploma
Unit : 18 – Database Design
Topic: Data Dictionary / Schema

You are to decide what data Types and Formats the following data items should be, and what validation checks should carried out:
Data Validation rules/input
Entity name Key Attribute/Field Data Type Format/ Rqd masks/lookups/drop down Description
Length/Size lists
Tbl_customer PK Auto Yes Customer unique ID number
Customer ID number
Title Look up Drop down Yes DDL, Mr, Mrs, Miss Customer title e.g Mr
wizard
First Name Short text 25 Yes Is Null OR Not Like "*[!a- Customer first name
z]*"
Surname Short text 25 Yes Is Null OR Not Like "*[!a- Customer last name
z]*"
Address line 1 Short text 45 Yes Is Null OR Not Like "*[!((a- Customer address line 1
z) or (0-9))]*"
Address line 2 Short text 45 No Is Null OR Not Like "*[!((a- Customer address line 2
z) or (0-9))]*"
Town Short text 35 Yes Is Null OR Not Like "*[!a- Customer town
z]*"
County Short text 40 Yes Is Null OR Not Like "*[!a- Customer county
z]*"
Postcode Short text 35 Yes >LL09\ 0LL;;* Customer postcode

Tel No Short text 11 Yes Is Null OR Not Like "*[!0- Customer contact number
9]*"
Short text 30 Yes Is Null OR ((Like "*?@?*.? Customer Email
*") AND
Email (Not Like "*[ ,;]*"))

Member y/n Look up Drop down Yes DDL, Y/N Is customer member or not
Wizard
Surcharge Currency Decimal Yes £ 2DPL Surcharge for customers from
outside
Payment method Look up Drop down Yes DDL, Cash, Direct debit, Credit Customer payment method
wizard card