Information Processes and Technology

Home > Information Processes and Technology > Information Systems and Databases > Data modelling: Data Dictionaries

Data modelling: Data dictionaries

This activity addresses aspects of the following syllabus outcome:

Outcome

A student:

H7.2 uses methods to thoroughly document the development of individual and team projects.

Source: Information Processes and Technology Syllabus, June 2009
http://www.boardofstudies.nsw.edu.au/syllabus_hsc/pdf_doc/ipt-st6-syl-from2010.pdf

Data Dictionaries: Typical Fields

A data dictionary is a table providing a comprehensive description of each field in the database. Data dictionaries (are used) to describe the characteristics of data including:

field name: the name of the field as it appears in the database.
data type: includes text, number, date, currency, yes/no (boolean).
data format: any special formatting applied to the field; e.g. number of decimal places in number fields, short, medium and long date and time formats.
field size: the number of characters allowed for a given field.
description: a simple description of field contents.
example: a typical field entry.

Sample: an excerpt from a data dictionary

Field name	Data type	Data Format	Field Size	Description	Example
UserID	Text	NNNNNNNN	8	Unique eight digit number represented as text	0001539
FirstName	Text		25	First name of employee	Bill
Surname	Text		25	Surname of employee	Smith
DOB	Date	DD/MM/YYYY	10	Date of birth as a short date format	15/07/1982
HourlyPayRate	Currency	#####.##	8	Rate of pay expressed in dollars per hour	34.50
Height	Real	#.##	3	Height in metres, with two decimal places	1.58
FeesPaid	Boolean		1	Y or N for Yes or No	Y

Source: IPT Course Specifications

Activity 1

Design a data dictionary with at least 5 database fields for a student database used by a school. Include all of the typical data dictionary fields (field name, data type, data format, field size, description and example) in your response.
Design a data dictionary with at least 5 database fields for a patient database used by a medical centre. Include all of the typical dictionary fields (field name, data type, data format, field size, description and example) in your response.

Activity 2

A simple relational database.

A bank has many customers who open and maintain one or more accounts. The bank keeps a record of each customer's name and address. Each customer has a unique customer ID as a unique identifier (or primary key). This primary key is one of the most important fileds used in the bank's data base.

All accounts are assigned a unique account number. The account can either be checking (cheque) or savings. A savings account can earn interest but the only transactions allowed are deposits and withdrawals.

Go to the Zicklin School of Business web site and answer the following questions:

Questions:

As revision, define the term data dictionary and identify the main components.
Answer
From the Access tables provided on the Zicklin School of Business website, in sections 2 or 5, develop a data dictionary for both the Customer and Accounts tables.
Answer
Create a schematic diagram for the banking case study above. It should include the Customer and Accounts tables.
Answer