Week 2: July 7-11

TUES   THURS   TO DO

WEEK'S OBJECTIVE

Objective: Databases at work, Intro to MS Access 2013, Tables/Datasheets. Book: Lesson 1, Lesson 2, On-Your-Own Tasks: Get Started, Objects, Managing.

MIC AGREE, due Sunday July 13.

MIC ASSIGN 1, due Sunday July 13.

Tuesday

GAMEPLAN

  • Quick Review
  • Access 2013 overview and interface
  • Setting preferences
  • Databases: existing, saving, closing
  • Database objects: Navigation bar, naming conventions

DISCUSSION

Database types
  • Flat database: Typically a single table with all related info, like the Excel spreadsheet we saw last week. Pros: easy to build. Cons: lots of redundancy, data integrity, comparability of data, and size management.
  • Hierarchial database: Typically ordered in a tree-like structure, with parent and child files. One parent table can have many child tables, but child tables can have only one parent table. Pros: Easy to build and organize. Cons: Redundancy in children tables with different parents, data integrity, and comparability of data.
  • Relational database: Stores data in tables that are related to each other by means of repeated columns called keys. Allows for the collection and organization of all data related to the database purpose in multiple tables, and the querying of the data for reports and use.
  • RDMS (relational database management system): is a system to manages relational databases with options like compacting, security, backing up and restoring, monitoring and optimizing, reporting, exporting, etc. Examples are Oracle, Access, SQL Server, MySQL, and DB2.
  • Desktop: A database system that requires installation, and is usually oriented to a specific hardware and operating system. You need to take care of backup and security yourself. Access to the database through the desktop tool is based on licensing. Access and Oracle are examples.
  • Web database: A web database is a database created for use on the Web, such as with a Sharepoint intranet site or a retail website (like Amazon). These do not need installations and are usually not reliant on a specific operating system. The maintenance and backups are handled by the web provider. Examples: SQL, mySQL.
Database concepts*
  • A database is an organized collection of data. 
  • Databases are created to operate large quantities of information by inputting, storing, retrieving, and managing that information.
  • Databases use a table format, which is made up of rows and columns.
  • Each piece of information is entered into a "field", like a Cell in Excel.
  • Each piece of information is entered into a row, which then creates a record.
  • Once the records are created in the database, they can be organized and operated in a variety of ways that are limited mainly by the software being used.
  • Databases are somewhat similar to spreadsheets, but databases are more demanding than spreadsheets because of their ability to manipulate the data that is stored. 

*Per Wikipedia.

Data Storage and Use
  • Data integrity: Accuracy and correctness of data, such as data formatting.
  • Redundancy: Having the same data in ofre than one place in the same database, such as a customer's address in two different tables.
  • Duplication: Having the same data more than once in the same table.
  • Consistancy: Consistant method of entering data in order to prevent duplications / redundancy.
  • Comparability: The ability to compare information in database tables and queries, namely through using different data formats, like text, numbers, dates, currency, etc.
  • Scaleablility: This means how easy it is to adapt your database if it needs to grow very rapidly. Can your database still handle the increased usage without putting you into time consuming troubles? Examples would be web databases like Amazon which might have 100 new cusyomers creating acocunts one day. and two-thousand another day, up to a couple of million in one year.
SCC Computer Basics
  • Look at Taskbar to find Start
  • Look for Microsoft Office: Microsoft Access 2013 (look for little red icon) - then double-click that.
Microsoft Access 2013

Microsoft Access 2013 is a relational database management system program.  It is primarily used for building databases and collecting/integrating data from them into tables and reports for output. It has support for use in a MS Office workflow with Word, Excel, and Outlook, and is usually used in administrative functions to add, sort, and report data. Most admin pros never create or manage full databases. This class will focus on Access as a basic database, report, and table tool.

  • Versions earlier than 2007 relied on the Jet database engine (code and organizing basis) which is 32 bit, enforces entity integrity and referential integrity, and allows locking of user input. Jet will by default prevent any change to a record that breaks referential integrity. The extension name of files is .mdb.
  • The 2007 and later versions includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine. 64 bit was introduced in the 2010 version, and the extension name of files is .accdb.
  • The engine in Microsoft Access 2013 discontinued support for Access 95, Access 97 and xBase files.
  • The MSDE Microsoft Desktop Engine is based on SQL Server 7.0 and was an alternative to Jet, its ability to operate as a client–server application instead of requiring direct access to the file system which the Jet database resided on.
  • The SQL Server Express engine is a software product whose primary function is to store and retrieve data as requested by other software applications, either on the same computer or on another computer across a network (including the Internet).
MS Access 2013 Overview

Get to know the basic functions of MS Access 2013. Note that MS Access 2011 for MACs and earlier versions of MS Access are NOT acceptable for completing assignments in this class. There are too many differences.

  • Open, Review screen:
    • Template categories, Getting Started, New and Open
  • New blank database
  • Save, save as, file naming
  • Explore Menu/Ribbon tabs:
    • Home: Basic text functions -  formatting, positioning, styles
    • Create: tables, reports, forms, queries
    • External Data: Importing and exporting data
    • Database Tools: For developers and database mechanics – not admin/clerical use.
    • Acrobat: For PDF workflow.
    • Format: A context-tab for formatting functions of reports, forms only. *
    • Arrange: A context-tab for arrangement of data of reports, forms only. *
    • Datasheet: A context-tab for table data only. *
Preferences - File tab

Set your preferences for workflow and productivity.

  • Office 2007 had an Office button, 2010 has a customizable tab
  • SCCC customized the tab to be called File.
  • MS Office Tab
    • Same layout as Word, Powerpoint, and Access.
    • Allows personalizing, print, save, and publish.
  • Preferences: Access Options:
    • Customize editing, saving, and other program options
    • Personalize a mini-toolbar for minimizing ribbon
Database Objects

Databases are made up of several objects: tables of data, queries on that data, forms for inputting data, and reports for exporting the data.

  • Database: A relationship between multiple tables of related data that allows you to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents.
  • Table Object: Tables are like Excel tables – rows and columns of data you are collecting about something.
  • Query Object: Combine information from multiple tables in your database. Queries provide the capability to combine data from multiple tables and place specific conditions on the data retrieved. 
  • Form Object: User-friendly forms interface allows you to enter information in a form that passes that info to the database tables.
  • Report Object: Provides the ability to quickly produce formatted summaries of the data contained in one or more tables and/or queries of the database.
Navigation Panel
  • Database Objects
  • Naming conventions
  • Sorting/views in Navigation
Database table structure
  • Tables are the CORE component of a database. They are where all the information is stored.
  • A relational database uses as many tables as needed to break up information into its smallest needed components
  • A table should focus on only one subject and its immediate and necessary related information, like a customer, customer's contact information, and customer-specific comments/notes.
  • A table should be created with ONLY the required info for the subject, such as employee-related info, customer-related info, Product-related, Order-related, etc.
  • A table should NOT ever have information replicated from another table. Note: Foreign keys are the "exception".
  • No table should ever duplicate the information/content that exists in a another table already.
  • If more than one table needs to use the same data, that data should be stored in a third Look-up table, like a table of States, products, etc.
  • A table should have at least ONE totally unique column of information so it can be used in relationships with other tables. This is the Primary Key.

DEMO

  • HomeGrownSample database
  • Look at Ribbons/Menus
  • Look at Navigation
  • Look at Objects
  • See tables, forms, queries, etc.

DEMO

  • GS-Generic database
  • Look at Ribbons/Menus
  • Look at Navigation
  • Look at Tables
BACK TO TOP

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

Thursday

GAMEPLAN

  • Reminder: On-Your-Own Tasks and Resources
  • Databases: existing tables
  • Tables: Datasheet
  • Tables: Design view
  • Redundancy/Repetition
  • Sorting
  • Filtering

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.
  • Queries are output as tables, and can create new tables of information.
Tables: Information

Tables are your core item for all database data. The information in them must be relevant, consistent, non-repeating, and easy to filter, sort and query.

  • Create consistent column names for use on all your tables.
  • Be sure to create one totally unique column in each table that serves as a potential Primary Key (even if you do not set one).
  • Break information into the smallest parts for the subject matter. For instance, a Customers table would have FirstName, MI, LastName, Address1, Address2, City, County, State, Zip, Country, etc. You would NOT use one field for the whole address, or one field for the full name.
  • Take the user’s keys away. It’s usually a bad idea to choose a user-editable field as a key, so an autonumber can be set instead. 
  • Carefully check and set the formats and values for each field in a record, so that these are clearly defined rather than just left as text.
Table parts: Home tab
  • Datasheet: The default table info insertion view. Looks like a spreadsheet view.
  • Design view (we'll discuss Thursday): The table design and criteria-setting view.
  • Headers: The tables need header rows to state what data is being collected in the columns.
  • Records/rows: Each record in the database is based on a row of data you input, like name, address, phone, etc.
  • Columns/fields: Each column stands for a specific type of data being collected for each record.
  • Fields/cells: The equivalent of a "cell" in Excel or item in a list's column.
Table Contextual Ribbon
  • Fields: This contextual ribbon allows you to make Field design changes on the fly.
  • Table: This contextual ribbon offers shortcuts for logic functions related to table changes.
Database table structure
  • Tables are the CORE component of a database. The are where all the information is stored.
  • A relational database uses as many tables as needed to break up information into its smallest needed components
  • A table should focus on only one subject and its related information, like a customer, customer's contact information, and customer-specific comments/notes.
  • A table should be created with ONLY the required info for the subject, such as employee-related info, customer-related info, etc.
  • A table should NOT ever have information replicated from another table.
  • No table should ever duplicate the information/content that exists in a table already.
  • If more than one table needs to use the same data, that data should be stored in a third table, like a table of States, products, etc.
  • A table should have at least ONE totally unique column of information so it can be used in relationships with other tables.
Individual table information
  • Determining columns
  • Smallest possible, like first name, middle name, last name.
  • Avoid Redundancy - don't list the same customer twice, for instance.
Sorting information
  • Allows for reorganization of rows of information.
  • Use the Header row for basic sorts.
  • The HOME tab also allows for basic sorts, and removing of sorts.
Filtering information
  • Allows for showing/hiding specific rows of information.
  • Use the Header row for basic filters.
  • The HOME tab also allows for basic filters, and removing of filters.
  • More detailed/complex filters occur through Queries (next week).

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.

DEMO

Datasheet view: work on an existing table: GardenTables-data.accdb.

  • Work on table in Datasheet view.
  • Add a column or two.
  • Set the Format for the column types - text, number, etc.
  • Add a few rows.
  • Sort the rows
  • Filter the rows
  • Work on table in Design view.
  • Add a few columns.
  • Set the Format for the column types - text, number, etc.
  • Change header names.
  • Look at data types.
BACK TO TOP

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

TO DO

  • MIC AGREE: Due Sunday, July 13, before 11:59pm, on CANVAS tool.
  • MIC ASSIGN 1: Table - Edits/Sorts, due Sunday, July 13, before 11:59pm. Lesson 2, approximate pages 78-89
    • (Learn the Skill Modules: Updating and Deleting Records, AND Manipulating a Datasheet).
    • USE GardenShop2.accdb, and save YOUR version of the file as assign#firstinitiallastname.accdb so I know whose file it is I am grading.
    • You do NOT need to try to upload the linked Excel table - I just want your database .accdb file.
    • IF you need a picture-free PDF of the instructions because you don't have the book, click HERE.
Resources
BACK TO TOP