Database Applications/Tables

This lesson assists users in creating tables in their new Access database.

Objectives and Skills
Objectives and skills for this lesson include:

Readings

 * 1) McFadyen: Relational Databases and Microsoft Access- Chapter 1
 * 2) Wikibooks: Create a basic two table database with reports
 * 3) Microsoft: Create a new database
 * 4) Microsoft: Create a table
 * 5) Microsoft: Database design basics
 * 6) Microsoft_Access: Create Table in Design View / Input Mask / Add data to Table

Multimedia

 * 1) GCF Global: Access 2016 Working with Tables
 * 2) Microsoft Access - Build tables with Table Designer
 * 3) Microsoft: Design and build tables for a database (Part 1)
 * 4) YouTube: MS Access 2016 - Create Simple Database
 * 5) YouTube: Best Demonstration of | Primary Key | Foreign Key | Composite Key | Candidate Key | Unique Key
 * 6) YouTube: Microsoft Access 2013 Tutorial - Creating Tables - Part 1 - Access 2013 Training for Beginners
 * 7) YouTube: How to Create Table Relationships
 * 8) YouTube: How To Create a New Table in Microsoft MS Design & Datasheet View
 * 9) Youtube: Access 2016 - Database Lessons For Students 01 - Tables
 * 10) YouTube: Access Database Basics-Referential Integrity
 * 11) YouTube: Microsoft Access 2016 for Beginners: Creating a Database from Scratch

Activities

 * 1) Learn how to create a new database. Youtube: Access 2016 - Creating a Database from Scratch
 * 2) Complete the tutorial GCF Global: Access 2016 Working with Tables.
 * 3) Review McFadyen: Relational Databases and Microsoft Access - Chapter 2 (Microsoft Access Creating Tables). Complete all of the exercises in Chapter 2.

Lesson Summary

 * A database is an organized collection of data.
 * A table stores data as a collection of related data consisting of fields (columns), and rows.
 * A data type defines the data values for that specific data type stored in a field.
 * Each table should have a primary key which is the one value in each table that can not be repeated.
 * Two fields can be selected as the primary key for a table
 * Common Access data types include integers, short text, long text, date/time, autonumber, etc.

Key Terms

 * AutoNumber
 * a type of data used in Microsoft Access that will automatically generate the next incremented numeric counter


 * attribute
 * In database management systems like Microsoft Access, attributes are referred to as a database component. An example is in the Department's datasheet table, the "deptCode, deptName, and deptPhone" field is an attribute since all three fields are data for the table.


 * calculated field
 * calculation are developed with in a field by applying several field values in a formula.


 * caption
 * The caption attribute of a field allows a more descriptive header than the field name. If nothing is entered into the caption the name of the field is used for the header.


 * composite key
 * A composite key is a candidate key that consists of two or more attributes (table columns) that uniquely identify an entity occurrence (table row).


 * currency
 * Currency data values stored in 8 bytes.


 * date/time
 * Date and time data values stored in 8 bytes.


 * data type
 * Data types indicate the type of data a variable can hold. When a variable is defined, a memory location will be assigned to the newly defined variable and it will also define the type of data that memory location will hold.


 * datasheet view
 * An Access view used to view or modify data displayed in rows and columns.


 * design view
 * An Access view used to create tables, and edit the different attributes associated with each table.


 * F1
 * F1 key is universally associated with Help in most early Windows programs.


 * field name
 * In computer science, a field name identifies a field in a database record.


 * field size
 * The field size is the size of a field that can store data. Note that when changing a field size to a smaller value any existing records that have a field with more data than the new size will be truncated.  You may need to consider making a backup if the field already contains data.


 * foreign key
 * A foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.


 * Format Property
 * Format Property is used to customize how texts, numbers, dates and times are displayed and viewed to the end user.


 * input mask
 * a set of simple rules that specifies the format in which data can be entered into a field.


 * large number
 * Provides additional analytical capability. Added in Access 2016.  Not recommended if one needs to ensure compatibility with versions before Access 2016.


 * long text
 * Long text can hold up to 2^30-1 bytes or around 1.07GB of text. Is limited to 65,536 if entered via the user interface. Additionally, long Text field works the same as the Memo field of old. That is, it can store up to about a gigabyte of text, even though controls on forms and reports can only display the first 64,000 characters. []


 * long integer
 * Long integer For example: 32 bits, whole numbers between -2,147,483,647 and +2,147,483,647.


 * Lookup Wizard
 * Lookup Wizard is a type of data used in Microsoft Access that allows the user to choose input from the list of available values.


 * memo
 * A text field that can store large amounts of data (65,536 characters in length). Now called Long Text.


 * number
 * A field that stores only numbers. This field can store the following sizes 1, 2, 4, 8, 12, and 16 bytes depending on field size selected.


 * primary key
 * A specific choice of columns which uniquely identify rows is called the primary key.


 * short text
 * Short text can store up to 4000 characters. The default is set to 255.


 * short integer
 * Short integer For example: 16 bits, whole numbers between -32767 and +32767.


 * table
 * A table is a collection of related data held in a structured format within a database which consists of columns and rows.


 * validation rule
 * conditions that restrict user input in a particular field.


 * yes/no
 * This data type restricts the values to yes or no using a checkbox.