Transcript Intro to Databases

LSP 121
Week 6
Intro to Databases
Read the handout on databases
• Found under ‘week #6’
• Go home and read this document closely.
• You can skip the sections on “Relationships”
and “Forms” for now.
• But get comfortable with the other sections.
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
– E.g. Airline ticketing agents need access to the flights/seats/prices
database for an airline from all over the world
• You want to secure access to the data and control data values
• Spreadsheets are much better for crunching numbers
– E.g. Statistical analysis, graphs, etc
• Databases are better at dealing with very large amounts of
data – especially if it goes much beyond numeric data
• As you become familiar with them, you’ll learn how to decide
which (DB vs Spreadsheet) is the best tool for your needs.
Example of a table
• This “table” of customers has:
– Four “fields” (last name, first name, address,
telephone)
– Three “records”
Basic Terms – Know these!
• Table – an object you create and then use to store data.
– Each table contains information about a particular subject, such as
customers, patients, or orders
– e.g. Students, Customers, Cubs_Roster, Sox_Roster, Nobel_Prize_Winners
• Fields – tables are made up of a series of fields (columns).
– e.g. Students table: first_name, last_name, gpa, SSN, etc
– e.g. Sox_Roster table: f_name, l_name, position, year_hired, salary, etc
– e.g. Nobel_Prize_Winners table: f_name, l_name, year_won, country, etc
• Record – a collection of fields
– e.g. a collection of students from a table of student info
• Query – an object that provides a custom view of data from one
or more tables
– A “question” you ask of the database
– e.g. Show records in the Students where gpa > 3.5
– e.g. Show records in the Nobel_Prize_Winners table where country = “USA”
Field Attributes (Data Types)
 Recall how in SPSS you can have different field types such as String, Numeric,
Dollar, etc. The same thing applies to creating a field.
 You need to specify which type of data you will enter for each field.
 In access, you can change these settings under the ‘View’ icon (upper left of screen)  Design View
 You want to create fields for Name, Address, Telephone, Amount Paid,…
 Text – the most common!
 Dollar: for salaries, sales, tuition, etc, etc
 Number: (same as Excel) gpa, ssn, zip_code, etc, etc  or should we ???? (see next)
 Number – do NOT use unless you plan to do arithmetic with this field
 Should we rethink ssn in the previous example? What about zip code?
 gpa is probably good as numeric. SSN and Zip probably should be text.
 Date/Time
 Currency – use with money fields
 AutoNumber – similar to Number but Access automatically inserts the numbers
for you
 Yes/No
 And a few others…
** Primary Key
 A way to identify every record uniquely
 Imagine if you had multiple students in the same table with the same first and last
name. How could you uniquely identify each one? (John Smith, Rashmi Patel, etc)
 Almost every table should have a primary key
 For example, your student ID is a primary key in a lot of databases here at
DePaul (every student ID is unique – no duplicates)
 We could also use SSN (many government DBs use SSNs as their primary key)
 E-mail address is becoming a popular choice for primary key. Can you think of why
this might be a good idea? Why it might be a flawed idea?
 What about phone number?
 If you are searching for a particular record, you can quickly find it using
the primary key
 Whenever you are asked for your DePaul ID, or SSN, or e-mail address, etc it is
often so that a database can be queried toquickly bring up your information.
 In Access, a Primary Key is shown by a little golden key icon.
What is an Index?
• Similar – but not identical – to a primary key
– Index can have NO value inside. Primary key must have a value.
– Multiple records can share the same index. Primary key has to be
unique to every record.
•
•
•
•
Sometimes used for sorting a table
You can make any field an index
An index also let’s you search for one or more records
You can search a database on any field, but the search will be
faster if you make the field an index
– This becomes very important with very large databases
• We won’t worry much about index for now. Focus on
understanding primary key.
Table Example
• Suppose we were creating a table for a
personal phone book.
• What should we use as the primary key?
– Last name? No!
– Phone number? Maybe… probably not.
– Let’s assign every person
Let’s Practice
• Let’s run the Microsoft’s databased program Access
– Create a database called: My Phone Book
– We will create a single table called ‘People’ and then enter
some data
– For now, just create fields for last name, first name, city,
state, phone number.
• Don’t forget!
– Create a primary key field for your table
• Allow Access to ‘autonumber’ this primary key
– Assign a correct type for each field
Enter some data into the table
• Click on View  Datasheet View
• Enter the following records
– Note how the autonumber is generated as you
enter a new record
•
•
•
•
•
Bobby Barnacle, Moline, IA, 222-555-6666
Susan Smith, Chicago, IL, 333-444-5555
Carlos Headcase, Chicago, IL, 773-444-6666
Rita Skeeter, Hogsmeade, FL, 666-888-7777
Neuro Surgeon, Richville, IL, 222-333-4444
Queries
• Let’s “query “ our database to list the first name and phone number of all
users from “Chicago”
• From the tabs, choose: Create  Query Design
– Don’t use the Query Wizard – we’ll design our own queries
• Choose the table you are interested in.
– You could pull in info from multiple tables, but we’ll leave that for later.
• Double-click the fields you want to retrieve, in this case, first
name and phone number
• Double-click ‘City’
– Where it shows “Criteria” down below, type “Chicago” (include the
quotes)
• Click the ‘Run’ icon at the upper left
Queries contd
• To modify a query, click on View  Design View
– Modify your previous query to find all users from Illinois
• Also try:
– List first name and last name and city for all users
from Florida
– Do the same for users from CA (California)
• Obviously you should not find any
– Experiment with others…
Learn Queries!
• Learning to properly query a database is probably
the most important skill for the database part of the
course.
• Queries can be very elaborate and complex. We will
be spending some more time with them.
– The handout has some good examples.
• STOP. Let’s do the first activity.
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
– You can also import additional new data into an
existing database, but this can be tricky
• 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
• Be sure to note if the first row contains 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?
– Remember that it is important to make a good
choice for your primary key. Put some thought into
it.
• 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 worksheet named
“Master”
•
When you click on ‘Next’ note that you are asked whether the first row contains
headings. For the “Master” worksheet, it does.
• Don’t just keep clicking ‘Next’. That is, note the kinds of options that
Access is prompting you with.
•
•
For example, allow Access to create a primary key
Allow Access to create the errors table
• Question: Why is the datatype for “# Electoral Votes” Text instead of
Number???
• Now experiment with your table
• First take a quick look at the errors table just to see what is
in there. (We won’t worry about it for now). Then go back
to the regular “Master” table and practice…
• Try some of the following queries:
• Recall: Create  Query Design  choose the table(s) you want
to query. (“Master” in this case)
1.
2.
3.
4.
5.
List the presidents from Illinois
List the presidents from Illinois OR Ohio
List the presidents from Illinois AND Ohio
List the presidents younger than 50 at inauguration
Experiment
Filtering Records
• Sort of a simple way to quickly query
• Best idea though, is to learn to create quality
queries.
• 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