Transcript Intro to course and simple databases

LSP 121
Week 1
Intro to Databases
Welcome to LSP 121
• Quantitative Reasoning and Technological Literacy II
• Continuation of quantitative data concepts from LSP 120
• We will spend three weeks storing and retrieving data
using a database
• Given a data set, what is its mean, median, standard
deviation and more (descriptive statistics)?
• Given two sets of data, is there a correlation?
Welcome to LSP 121
• Continuation of quantitative reasoning concepts from LSP
120
• Given a set of data, can we calculate probability and
risk?
• How do we manipulate data, compress it, or check for
errors - algorithms
• If you feel you know this material, take the test
• Let’s get started!
Are Databases Important?
• Virtually every major area of study in college and in the
real world needs to store and retrieve data
• Commerce (sales, marketing, production, management, …)
• Education (daily operations, research, …)
• Healthcare
• Government
• Do you really want to enter the workforce and not know
what a database is and how they are created?
Why a Database (and not a
spreadsheet)?
• You have too many separate files or too much data in a
single file
• You need to look at the data in many different ways
• You need to share the data
• You want to secure access to the data and control data
values
Basic Terms
• Table – an object you define and use to store data. Each
table contains information about a particular subject, such
as customers, patients, or orders
• Fields – tables contain fields (columns) such as first
name, street address, score
• Record – a logical collection of fields
• Query – an object that provides a custom view of data
from one or more tables
Field Attributes (Data Types)
 You want to create fields for Name, Address,
Telephone, Amount Paid,…
 Text – the most common!
 Number – do NOT use unless you plan to do
arithmetic with this field
 Date/Time
 Currency – use with money fields
 AutoNumber – similar to Number but Access
automatically inserts the numbers for you
 Yes/No (and others)
What is a Primary Key?
 Most tables should have a primary key, but it is
optional
 A primary key is a field that can uniquely identify
every record
 For example, your student ID is a primary key in
a lot of databases here at DePaul (every student
ID is unique – no duplicates)
 Social Security number primary key for a lot of
government databases
 If you are searching for a particular record, you
can use the primary key
What is an Index?
• You can make any field an index
• An index also let’s you search for one or more
records
• An index can require unique values (like the
primary key) but an index can also allow
duplicate values
• You can search a database on any field, but the
search will be faster if you make the field an index
Table Example
• Let’s create a table for the following:
• Home address / phone book
* What is the primary key?
* What are the indexes?
Let’s Practice
• Let’s run the program Access and create a simple table
and then enter some data for the example Home Address
Phone Book (Smith, Jones, Anderson, Abbott) (in Oak
Park, Chicago)
• Did you correctly assign the type for each field? Did you
select the field as an index? Did you create a primary
key?
Queries
• Let try a couple queries
• Don’t use the Query Wizard – we’ll design our own
queries
• What is Smith’s phone number?
• Show all people living in Oak Park
• ???
• STOP. Let’s do Activity 1.
Importing Data into
a “Flat” Access Database
• Many times you want to take an existing data set and
“import” it into an Access database
• The existing data set can be plain text (.txt), a
spreadsheet (.xls), or other forms of existing databases
• Let’s consider the example of importing a spreadsheet
into an Access database
Importing Spreadsheet Data
• Create a new (blank) database (unless you are importing
into an existing database, then simply open that one)
• Select External Data from the top tabs
• Select the appropriate type of file that you want to import
Importing Spreadsheet Data
• You probably want to import the data into a new table
• If the spreadsheet you are importing has more than one
worksheet, select the appropriate worksheet
• Does the first row contain column headings?
Importing Spreadsheet Data
• Now you can tell the wizard which fields should be
indexed in the new table
• Choose your own primary key or let Access add a primary
key?
• Give the table a name and click on Finish
• You may have import errors – check the file
xx$_ImportErrors
Importing Spreadsheet Data
• Let’s try an example – we will import the data in
•
•
•
•
spreadsheet Presidents.xls
Open the QRC website and copy the file Presidents.xls to
either My Documents or the Desktop (may not be able to
copy to Desktop)
Run Access and perform the import operation on the
Master worksheet
Try a FIND or two
Try a FILTER or two
Finding a Record
• A very simple way to look for a record
• But you can only search in one table, and you can only
•
•
•
•
•
look for one field
Open the table, then on the Home tab click on Find (or
CTRL-F)
Enter values as necessary
Wildcard character: *
Find is nowhere near as powerful as performing a query
(later)
We’ll try FINDing a record in a few moments
Filtering Records
• If you want to see all the rows in a table that match any
•
•
•
•
part of a value, you can use the Filter command
A couple different forms of Filter. Let’s look at Filter by
Selection
Open the table you want to filter. Highlight the field you
want to filter on. Then on the Home tab click on Selection
To undo filtering, click on Toggle Filter
We’ll try filtering in a few moments
Further Questions
• How does Google work?
• Should Google restrict its searching capabilities to comply
with a government?
• What is data mining?
• STOP. Now do Activity 2.