Further Links Contact Us Search Help

Syllabus | Exams | Assessment | Resources | Site reviews | Careers

Information Processes and Technology

Home > Information Processes and Technology > Information Systems and Databases > Data Dictionary and Schematic Diagrams

Data modelling: Data dictionary and schematic diagrams

Questions and answers:

1. 1. Define the term data dictionary and identify the main components.
      A data dictionary is a table providing a comprehensive description of each field in the database. This commonly includes: field name, data type, data format, field size, description and example. |
      
      

      Field Name	Data Type	Data Format	Field Size	Description	Example

      
      
   2. From the Access tables provided develop a data dictionary for both the customer and accounts tables.
      Please note that these answers are based specifically on the data provided by this case study.
      
      Customer Table
      

      Field Name	Data Type	Data Format	Field Size	Description	Example
      Customer ID	Number	####	4	Unique four-digit number that identifies each customer	1001
      Name	Text		20	The name of the customer	Mr. Smith
      Address	Text		20	The contact address of the customer	123 Lexington
      City	Text		10	The city of the customer	Smithville
      State	Text		2	The state of the customer	KY
      Zip (Post Code)	Text		5	The post code of the customer	91232

      
      Accounts Table

      Field Name	Data Type	Data Format	Field Size	Description	Example
      Customer ID	Number	####	4	Unique four-digit number that identifies each customer	1001
      AccountNumber	Number	####	4	The unique identifier for a bank account	9987
      AccountType	Text		8	The type of account	Savings
      DateOpened	Date	DD/MM/YYYY	10	The date the account was opened	08/22/1994
      Balance	Currency	#######.##	10	The current balance in this account	4000.00

      Return to activity

2. Create a schematic diagram for the banking case study. It should include the customer and accounts tables.
   A schema shows the organisational structure of a database. It should show the entities (the tables in the database), and their characteristics (i.e. their properties/attributes/fields). It should clearly identify the primary key in each table and the links and relationships between tables. The example below is just one way a schema can be represented.
   
   

   Entities
   			Primary Key
   Primary Key	Customer Table		Accounts Table
   	Customer ID		Account No
   	Name		Customer ID
   	Address	1 to many relationship	Account Type
   	City		Data Opened
   	State		Balance
   	Zip - Postcode

   Return to activity

   
   
3. Identify the foreign key in the accounts table. Justify your choice.
   The Customer ID is the foreign key. A foreign key is an attribute (field) that is a primary key of another table.

   Return to activity