Introduction to MIC120 class, Excel Review, Intro to Database concepts. Book: About This Courseware. On-Your-Own Tasks: Access Intro.
FIRST - no worries. If you are not yet signed up for this class, and/or are awaiting financial aid, you can sign into the computers as a guest: just hit enter at the open login, then enter again. This should work for the first 2 weeks.
NOTE: It turns out that students can only get into the CANVAS tool once they are actually registered for a class and payment (whatever
arrangement) HAS been received and the IT dept notified to "activate" the student's
current logins and passwords.
- Class Information
- Context of this class
- Get logged in to Seattle Central computer
- Computer Resources
- Web Browser Email Basics
- Class Book: Microsoft Access 2013 Certification Guide, Lambert/Cox, Microsoft Press, ISBN: 13: 978-1-55332-399-0. Print and digital okay. This book is REQUIRED for graded assignments. The SCC bookstore could NOT order it from the vendor, so you need to get it yourself right away.
- Access to the program MS Access 2013 for PC (school, home, library, community
center, etc.). Note that MS Access 2011 for MACs and earlier versions of MS Access are NOT acceptable for completing assignments in this class.
- 24/7 internet access, even dial-up or cell
- Email account.
- 2 gig USB Flash drive,
- Class web site (you are here now!)
- CANVAS tool
- Seattle Central programs access - class, computer lab, library.
- Attendance and performance
- Time management - at least 2 hours out-of-class time:1 hour of class time
- On-Your-Own Tasks
Attendance and performance
What this class does
- Understand database concepts.
- Recognize commonly used database terms and abbreviations.
- Access and edit the main components of a MS Access database: tables, relationships,
forms, and reports.
- Create basic relationships and queries.
- Build a simple MS Access database that works.
- Understand and discuss issues and trends related to database use.
- Communicate effectively orally and in writing.
- Recognize and appreciate the diversity both in the classroom and the IT field and community.
- Practice efficient communications and interactions with each other and supervision (instructor).
- Find everyone's Seattle Central student login
- Log into system as guest
- Log into system with student info
- Internet Explorer: toolbar, url, tabs, options
- Contacting LJ - add firstname.lastname@example.org to
your email contact list
- Get into our CANVAS tool
- Here's How.
- Check the email you GAVE the school when you registered.
- Look for the CANVAS Course Invitation - inbox or Spam.
- Open the email, and click the invitation link.
- THIS Canvas will be NEW - not Seattle Central - so Register for it and choose your password.
- Log On Info: Your login is the your email address, and your password is whatever you choose.
- SUPER HOT: Get your book ASAP!
- HOT: Get a 2 gigabyte USB Flash drive before Tuesday
- Check out your Learning
Style to see what class info/method you might need to boost.
Excel as a common tabular collection method
- Easy-to-create tabular data design.
- Easy to format to look nice.
- Allows thousands of rows and dozens of columns.
- Enables simple charts and graphs.
- You can sort and filter data.
- You can save specific filtered and sorted views.
- 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.
- Excel can only act as a flat database, although its ability to allow for differing data types can help information consistency.
- Sorts and filtering only go down a couple of levels.
- You can't really query a spreadsheet for a clean, simplified copy of a
subset of information.
- You can easily have unnecessary duplication of information.
- You can hide and filter columns for viewing, but the data is still there.
- Large spreadsheets get unwieldy for quick filters on the fly.
- Spreadsheet information not really scalable for months to years of capture and use.
- Spreadsheets commonly used for calculations as well as data layout, and
calculations can be easily corrupted.
Specific Spreadsheet Issues that Databases resolve
One customer, different names, emails, addresses - HOW to keep that information
consistent? Spellings, abbreviations, punctuation, etc.? How to find the same
customer? HOW to keep the checks and balanaces lean and not corrupt a spreadsheet/workbook? How to make scalable?
- Dropdown lists (states).
- Criteria (no punctuation)
- Specific formats (directionals changed to abbreviations, dates/times, phone
numbers turned to dashes, credit card numbers)
- Variants: look for and pull
error for spellings, out-of-order info.
- Unique ID - one per customer, product, vendor, etc. Catch redundancies.
- A database is an organized collection
- Databases are created to operate large quantities of information by inputting,
storing, retrieving, and managing that information.
- Databases use a table format, that is made up of rows and columns.
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
- Databases are somewhat similar to spreadsheets, but databases are more
demanding than spreadsheets because of their ability to manipulate the data
that is stored.
Must recognize several ways a word or name can be input. Different ways it
can be on the web already. what is most likely list of responses to list
first of thousands?
- Expedia: Must recognize customer, login, banking info, travel preferences, purchased
trips itinierary, changes in airline status, insurance, recommend things
to do, make special offers, upsell, sort by price/location, offer maps. Must
have secure transactions, contact hotels, confirm reservations.
must recognize customer, login, purchaing info. Must secure it. Must suggest
alternatives/faves. Must report status, out-of-stock, colors, quantities.
Must report info to vendors, shippers, suppliers. Must offer comments. Must
have secure transactions.
- Everquest II: Must tie several characters to account. Must link EULA issues.
Must store character information - race, skills, status, weapons. Must tell
if gold or free membership and amenities. Must place character locations.
Must direct fighting actions and spawning of NPCs. Must update game, verify
Microsoft Access 2013 is a relational database management system program. It supports*:
- Data definition: defining new data structures for a database, removing
data structures from the database, modifying the structure of existing data.
- Updating: Inserting, modifying, and deleting data.
- Retrieval: obtaining information either for end-user queries and reports
or for processing by applications.
- Administration: registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control, and recovering information if the system fails.
Let's look at a "flat" Excel table spreadsheet.
- Download Gardening.xlsx and SAVE to your pen drive.
- Open in MS Excel 2013
- Focus on the first tab.
- We'll look at the sorts and filtering we can do.
Why create a database?
- Imagine a small jewelry business
- 5 artists, 100 core products, 7 sales outlets to reach customers, shipping, etc.
- To determine and record the price of 1 single product, what do we need to consider?
- Why use a database?
- What questions would a database have to answer?
- Book: About This Courseware
- On-Your-Own Tasks: Access Intro.