Wikiversity

Database Applications/Tables

< Database Applications

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

Objectives and Skills

edit

Objectives and skills for this lesson include:

Readings

edit
  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

edit
  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

edit
  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

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

Key Terms

edit
AutoNumber
a type of data used in Microsoft Access that will automatically generate the next incremented numeric counter [6]
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. [7]
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. [8]
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).[9]
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.[10]
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.[11]
field name
In computer science, a field name identifies a field in a database record. [12]
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.[13]
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.[14]
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.[15]
large number
Provides additional analytical capability.[16] Added in Access 2016. Not recommended if one needs to ensure compatibility with versions before Access 2016.[17]
long text
Long text can hold up to 2^30-1 bytes or around 1.07GB of text.[18] Is limited to 65,536 if entered via the user interface.[19] 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.[10]
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. [20]
memo
A text field that can store large amounts of data (65,536 characters in length). Now called Long Text.[21]
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.[22]
primary key
A specific choice of columns which uniquely identify rows is called the primary key.[2]
short text
Short text can store up to 4000 characters.[18] The default is set to 255.
short integer
Short integer For example: 16 bits, whole numbers between -32767 and +32767.[10]
table
A table is a collection of related data held in a structured format within a database which consists of columns and rows. [2]
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

edit
Enable JavaScript to hide answers.
Click on a question to see the answer.
  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.[23]
  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

edit

References

edit
  1. ↑ Wikipedia: Database
  2. ↑ 2.0 2.1 2.2 Wikipedia:Table (database)
  3. ↑ Wikipedia:Data type
  4. ↑ 4.0 4.1 Wikibooks: Microsoft Office/Create a basic two table database with reports
  5. ↑ Wikipedia: Database
  6. ↑ Wikipedia: AutoNumber
  7. ↑ [1]
  8. ↑ [2]
  9. ↑ Wikipedia: Compound key
  10. ↑ 10.0 10.1 10.2 Wikiversity: C/Data Types and Keywords
  11. ↑ Wikipedia: Function key
  12. ↑ Wikipedia: Field name
  13. ↑ "Set the field size". support.office.com. Retrieved 2019-01-31.
  14. ↑ Wikipedia: Foreign key
  15. ↑ Wikipedia: Input_mask
  16. ↑ "Using the Large Number data type". support.office.com. Retrieved 2019-01-31.
  17. ↑ team, the Access (2017-03-06). "New in Access 2016—Large Number (BigInt) support". Microsoft 365 Blog. Retrieved 2019-01-31.
  18. ↑ 18.0 18.1 "Data types for Access web apps". support.office.com. Retrieved 2019-01-25.
  19. ↑ "Access specifications". support.office.com. Retrieved 2019-01-25.
  20. ↑ McFadyen, Ron. “Relational Databases and Microsoft Access”, 2016.
  21. ↑ "The Memo data type is now called "Long Text"". support.office.com. Retrieved 2019-01-25.
  22. ↑ "Introduction to data types and field properties". support.office.com. Retrieved 2019-01-31.
  23. ↑ Wikiversity: Editing IC3/Database Software (section)
Retrieved from "https://en.wikiversity.org/w/index.php?title=Database_Applications/Tables&oldid=2309387"