Skills & Knowledge Development

Step 1.  Data Dictionary

Before we create our Database - we need to document it.  Using Google Doc/Word, please complete the following tasks:

Firstly, here is some helpful info:

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

Source: IPT Course Specifications 

Activity 1

1. Design data dictionaries for a database used by a school. (School Database)
1. Database needs to contains Enrolment Data including
1. Student Name
2. Student Address
3. Student Date of Birth
4. Subject Name - e.g. IPT
5. Subject Description - e.g. Information Processes and Technology
6. Subject Faculty - e.g. TAS, English, Mathematics, Business, Science, etc
   
7. Year of Enrolment - e.g. 2020, 2021
8. Student Grade for Subject - e.g. A, B, C etc
   
   Sample data 

2. Name	Address	Date of Birth	Subject Name	Subject Description	Faculty	Year of Enrolment	Grade
   Joe Bloggs	1 High Street, Chatswood, NSW, 2067	1/12/2003	12IPT	Year 12 Information Processes and Technology	TAS	2021	A
   Michael Chen	56 Charming Ave, St Ives, NSW, 2075	20/5/2003	12IPT	Year 12 Information Processes and Technology	TAS	2021	B
   Reuben Choosey	9 Winterbottom Way, Berowra, NSW, 2081	1/8/2003	12IPT	Year 12 Information Processes and Technology	TAS	2021	B

   
   
2. NOTE:  We need to NORMALISE the database into
1. 1st Normal Form - no repeating groups, (a primary key for all tables, unique field names)
2. 2nd Normal Form - 1NF + non-key attributes dependent of all primary key values
3. 3rd Normal Form - 2NF + all non-key attributes NOT dependent on any other non-key attributes
   
   
3. Create a Data Dictionary for a Student Table.  
1. Think about 5 different types of fields or types of information we might store about a student
2. Consider which field may be the Primary Key (uniquely identifies each student) - create one if it does not exist
3. Include all of the typical data dictionary fields (field name, data type, data format, field size, description and example) in your response.
   
   
4. Create a Data Dictionary for a Subject Table
1. Identify appropriate fields, including the Primary Key for this table
   
   
5. Create a Data Dictionary for a Subject Enrolments table
1. This table will identify all the subjects a student may take and will link the Student and Subjects tables
2. Include some additional fields that are only relevant for a Student and the Subject combination

Step 2. Database Schematic Diagram (aka Schema model)

1. Draw a schema model for the Schools Database
2. Include 
1. the Table and Field names for each table
2. identify the Primary Key fields with a PK
3. draw the lines that represent the relationships between each of the table
4. on each line, identify the Cardinality (1 to many, 1 to 1, many to many, etc) 

3. Create Database in a DBMS

1. Now implement your schema in a Database Management System (DBMS), such as MySQL. 
1. You can watch the various Tutorial videos here or in YouTube, etc., to learn how to do this.
2. Populate the Database with 
1. at least 5 different instances of information as records for Student and Subject Tables, and 
2. sufficient Enrolment Table records to show a Student having many subjects and
3. sufficient Enrolment Table records to show a Subject having many students