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. [[3]]

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.

Review Questions

1. An Access database filename extension is _____.

   An Access database filename extension is a .accdb, separated from the base filename by a dot or space, used to indicate the file's content format or usage.
2. Setting the _________ will define what the column can hold.

   Setting the data type will define what the column can hold.
3. Database software is _____.

   Database software, also known as database management systems (DBMSs), are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data.
4. Each column in an Access table datasheet represents a ____.

   Each column in an Access table datasheet represents a Field.
5. True or False: A table can have more than one primary key.

   True, this is called a composite primary key.
6. Designating a ____ ____ restricts the information entered in a field and ensures a uniform look for that data within the database.

   Input Mask.

See Also

• Database Fundamentals/Tables and Data Types
• Wikibooks: Database Design/Fundamental Concepts
• Microsoft: Data type limitations
• YouTube: Access 2007: Primary And Composite Keys

References

← Introduction

Database Applications

Queries →