Week 3: July 14-18

TUES   THURS   TO DO

WEEK'S OBJECTIVE

MS Access 2013 Tables/Design, Relationships, table creation and importing data. GMETRIX Intro. Book: Lesson 2, Lesson 3 pgs 111-123. On-Your-Own Tasks: Tables, Sorts, Modifying Tables.

MIC ASSIGN 2, due Sunday July 20.

MIC ASSIGN 3, due Sunday July 20.

Tuesday

GAMEPLAN

  • Create new tables
  • Keys
  • Import data into new table
  • Import from elsewhere to create a new table
  • Normalizing

DISCUSSION

REMINDER: On-Your-Own Tasks and Resources

You need to be sure to do the requested On-Your-Own Tasks and use the Resource tutorials I list each week in our TO DO section. Folks should not be just jumping into "getting through the homework" and asking me basic questions that are covered in the book, our notes, On-Your-Own Tasks, and the tutorials I provide links to. To really grasp and use MS Access, you need to do the prepwork, and even use tutoring if needed.

Tables

Access allows you to create multiple tables so you can store specific information separately instead of inside one long large table.

  • Tables are for information storage only.
  • Tables are the Objects in a database that are queried for information to report.
  • You can create new tables
  • You can import tables from other programs
  • You can import data from other tables into existing tables
Normalization

Normalization is the process you go through to help eliminate the redundancy of data in a database by ensuring that all fields in a table are atomic. There are several forms of normalization, but the Third Normal Form (3NF) is generally regarded as providing the best compromise between performance, extensibility, and data integrity. Briefly, 3NF states that:

  • Each value in a table is to be represented once and only once.
  • Each row in a table should be uniquely identifiable. (It should have a unique key.)
  • No non-key information that relies upon another key should be stored in the table.
  • Databases in 3NF are characterized by a group of tables storing related data that is joined together through keys.
  • Let's see what normalizing would mean in HomeGrownSample database.
Nulls in Databases

The correct use of null is for a value that is unknown not for a blank or empty value. For example, consider a typical address record that allows two address lines, named Address1 and Address2. Most addresses have only one address, so the second address line is blank. The value of this second address line is, in fact, known — it's blank. That's not the same thing as null. Null would imply that the address may have a second address line; we just don't know what it is. You could set the default value of an Address2 line to be NOT NULL but change that only if there is a second line.

Naming Conventions

A naming convention is a set of rules that promote consistency in the naming of database objects (tables, queries, etc.) and columns/fields.

  • Entity names are field names, like LastName.
  • Ideally, there should not be duplicate entity names in a database, like two tables that both use LastName.
  • For objects in Access, consider using prefixes, like tbl_Customers, or a defining name, like Customers Table.
  • Keep entity/column names simple and table-defining, like Cust_LastName, VendID, Sup_Quantity.
Creating tables
  • Build from scratch - the CREATE Tab.
  • Import from another database or a spreadsheet, a tabular list, a comma-delineated text file, etc. - EXTERNAL DATA Tab.
  • Queries (info from other tables) - the CREATE Tab (discuss in a couple of weeks)
Individual table information
  • Plan new tables carefully
  • Plan table relationships in advance
  • Determine necessary columns
  • Smallest possible amount of required info, like first name, middle name, last name.
  • Avoid Redundancy - don't list the same customer twice, for instance.
Keys: Home tab, Design View

Keys in a database are, no pun intended, key to establishing relationships between tables so that you can query them for data.

  • Primary Key: Every table in your database should have a primary key — a field with a unique value for each record stored in the table. Often is some kind of identifier number. You use the primary key to identify and refer to each record. One of the reasons to create a primary key is to use it to create table relationships. A primary key is a field or set of fields that:
    • Has a unique value for each record - no two records in the table have the same value for the key.
    • Is indexed - a data structure that improves the speed of data retrieval operations on a database table.
    • Identifies the unique record.
    • When you create a NEW table in Datasheet view, Access automatically creates a primary key field for you, names the field ID, and gives it the AutoNumber data type. The field is hidden by default in Datasheet View, but you can see the field if you switch to Design View.
    • Candidate/Alternate Key: If you have/choose another field in the table that would serve as a Primary key instead.
  • Foreign Key: A foreign key is another table's primary key. The values in a foreign key field match values in the primary key, indicating that the two records are related — for example, a customer and an order that she has placed. You create a foreign key when you use the Lookup Wizard to create a field. Unlike primary keys:
    • A table can have more than one foreign key.
    • A foreign key does not necessarily have unique values.
    • A foreign key cannot reliably identify a particular record.

Relational Uniqueness

Keys are columns in database tables that can be related to other tables. For instance, you might have customers, products, and vendors. A fourth table, for Orders, might link to data from the first 3 so that orders can be related to who ordered which item from which vendor.

  • Primary Key This key uniquely identifies every row in a single table, like 0001, 0002, 0003, etc.
  • Foreign Key: This key is essentially another table's primary key when it is used as a link in a table. For instance, the Customer table's primary key 0001 would be one of one or more foreign keys in the Orders table.
  • Uniqueness: Uniqueness in a database table means unique only to that table, NOT the whole database. For instance, you might have 5 tables, and each one has a primary key of an Autonumber. That is okay. However, you do NOT want the primary key in a single table to be duplicated at all.
  • Choosing a unique column/field: You can choose to use Autonumber to have Access set a unique new number for every row, and therefore eavery customer in the customer table, etc. Or, you can create a Text-based special unique code instead.

SubDatasheets

When two tables have one or more fields in common, you can embed the datasheet from one table in another. An embedded datasheet, which is called a subdatasheet, is useful when you want to view and edit related orjoined  data in a table or query. If an expand indicator (+ sign) is present, then the table, query, or form has a subdatasheet.

For instance, if you have a table with a one-to-many relationship with another table, you can view and edit data such as the products included in a specific row by opening the subdatasheet for that related table. However, use them for Viewing only, and use Forms for editing.

Microsoft Office Access automatically creates a subdatasheet when you create a table that matches one of the following criteria:

  • The table is in a one-to-one relationship.
  • The table is the "one" side of a one-to-many relationship, where the table's SubdatasheetName property is set to Auto.
  • You can also create subdatasheets yourself: see Add a Subdatasheet.

DEMO

Start a simple table.

  • Create a new Access database, call it Paws.accdb.
  • Examine the default "starter" table.
  • Name the table.
  • Work on table in Datasheet view.
  • Add a few columns.
  • Set the Format for the column types - text, number, etc.
  • Add a few rows.
  • Work on table in Design view.
  • Create a Primary Key
  • Create a Candidate/Alternate Key
  • Change data types in Design View

DEMO

Import a table from Excel: Customers.xlsx.

  • Name the table.
  • Work on table in Datasheet view.
  • Set the Format for the column types - text, number, etc.
  • Create a Primary Key
  • Create a Candidate/Alternate Key
BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Thursday

GAMEPLAN

  • GMETRIX: Brief overview and resource links
  • Tables: Design
  • Relationships
  • Referential Integrity

DISCUSSION

GMETRIX

GMetrix is a platform for both web-based and concurrent practice tests.

  • In THIS class, students get a free GMetrix Access Code and a Testing Validation code
  • You MUST email me for your own codes.
  • Here is the basic GMETRIX info.
  • You need to do all GMetrix practice testing on your own - no class time or resources will be available for it.
  • Seattle Central GMetric testing wtih a proctor will be held in Fall Quarter, 2014.
Table View: Design
  • Set the Datatype of fields (text, number, etc.)
  • Set other field info - size, default values, formats, required (Y/N)
  • Set Input Masks (to force a default imput format)
  • Set a primary key for EVERY table: Yes (No Duplicates)
  • Force a validation format
    • Use the Field Properties/General/Validation and an expression
    • Example for email: Like "*@*.com" Or Like "*@*.net" Or Like "*@*.org"
  • Force input to only a lookup table
    • Create lookup table
    • In primary table, in Field Properties/Lookup/Limit to List - Yes.
  • Force No Empty fields: Field Properties/Lookup/Required - Yes
  • Default Values: Be sure to check/change as needed the Field Properties/General/Default Value
Relationships - Database Tools tab, Relationships

Relationships between your database tables are what allow you to query multiple tables for information in them and end up with a query table that consolidates what you want to know. This is how you can take information from a table of thousands of customers, another table of their orders, and a third table of shipping preferences, and query a list of orders, last names, addresses, and shipping preferences set into a new query table.

nonessential: image of relationships01

One of the goals of database design is to remove data redundancy (duplicate data). To do that, you divide your data into many subject-based tables so that each fact is represented only once. You then provide your database with the means by which to bring the divided information back together as needed — by placing common fields in tables that are related to each other, and by defining relationships between these tables. NOT all tables will be directly related to each other.

  • A table relationship works by matching data in key fields — often a field with the same name in both tables.
  • In most cases, these matching fields are the primary key from one table, and a foreign key in the other table.
  • You can view your table relationships by choosing Database Tools Tab/Relationships.

Joins

When you include multiple data sources in a query, you use joins to limit the records that you want to see, based on how the data sources are related to each other. You also use joins to combine records from both data sources, so that each pair of records from the sources becomes one record in the query results. By default, a join is automatically created if there is already a relationship between two data sources that you use in a query. A join is also created if there are fields that clearly correspond to each other.

Join Types

  • Inner Joins: If you want to show only those rows that have matching values in the joined field, you use an inner join. Access creates inner joins automatically. When a query with an inner join is run, only those rows where a common value exists in both of the joined tables will be included in the query results.
  • Outer joins: These show all rows from one table, and only the corresponding rows that share a common value on both sides of the join from the other table.

There are several Join/Relationship types for database connections.

  • One-to-one relationship: Each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table.
  • One-to-many relationship: For instance, in the Gardening database, for any customer represented in a Customers table, there can be many groomers represented in the Employees table.
  • Many-to-many relationship: Consider a Products table and an Orders table. A single order can include more than one product. A single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table.

Join Creation/Editing

Joins can be created / edited in the Relationships window, when you create a new relationship or edit an existing one. Steps:

  • Make sure the related tables, queries, forms, and reports are closed.
  • Go to Relationships window, and view the needed tables and their relationship.
  • Right-click on the relationship line to edit.
  • Click on the Join Type button in the pop-up wizard.

 

nonessential: image of relationships01

nonessential: image of relationships01

nonessential: image of relationships01

Creating Relationships - Database Tools tab, Relationships

You can create a table relationship by using the Relationships window. When you create a relationship between tables, the common fields are not required to have the same names, although it is easier to understand. Rather, those fields MUST have the SAME data type.

  • Relationships Window: Choose Database Tools Tab/Relationships, then choose the tables you want and click Add, then drag the Primary Key from one table to the Foreign Key of the other.
Referential Integrity

When you design a database, you divide your information into tables, each of which has a primary key. You then add foreign keys to related tables that reference those primary keys. These foreign key-primary key pairings form the basis for table relationships and multi-table queries. It is important, therefore, that these foreign key-primary key references stay synchronized. Referential integrity helps ensure that references stay synchronized and is dependent upon table relationships.

DEMO

Let's play with a super-simple basic database.
  • Open BasicRelations.accdb database.
  • This database has a tables for Customers, Services, and Groomers.
  • Look at the Primary key for the Customers and Groomers tables.
  • Set a Primary key for the Services table.
  • Right now, none of the information is related to each other. What is the table that would be related to both the services and groomers?
  • One table must have info that is in the other tables to be related, so they are linked.
  • Check the Format of the Customers and Groomer ID columns.
  • How would we relate that info? DATABASE TOOLS Tab - Relationships.
  • Drag and RefInteg GroomerID Number to Groomer - Good.
  • Drag and RefInteg Service to Service - Oops. This is because Service type linked to Service ID doesn't make much sense.
  • Drag and RefInteg ServiceID Number to Service - Good.

DEMO

Let's look at an existing database
  • Open HomeGrownSample database.
  • Look at the Primary keys for the tables.
  • Look at the Relationships between the tables.
  • What happens if we break a relationship?

 

BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

TO DO

Resources
BACK TO TOP