Transcript cse103day06

CSE 103
103 students:
Do not log in yet. Review Day 6 in your
textbook. Think about why you might want
to import data into a database.
Others:
Please save your work and log out by
10:15.
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 1
Housekeeping
BT Feedback
– Most of your BTs are graded. Please save comments
or questions about your BTs until after class.
Office hours
– Carl: by appointment only
Suggested times: M, W 11-12; T, R: 1-2
– Someone qualified: MW 3-5; TR 2-4
Helprooms
– 120 CC, Monday and Tuesday nights, 9-11
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 2
Importing Data
Read Day 06 in your textbooks, then
discuss the following question in your
groups:
– Why would you need or want to import
data into a database?
(The answer is not going to be spelled
out in the book; you’ll have to think
about it.)
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 3
Importing into a Database
Two formats of text files can be used:
– Fixed width
Each field has a constant width
– Delimited
Fields are separated by delimiters
Example delimiters: commas, tabs, spaces
Open files Schools-1-06.txt and Schools-2-06.txt
in the Day06 class folder. Assess their formats.
What are the advantages of each format?
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 4
Colleges & Universities db
Copy the c&u database from the Day 06
AFS space to your personal space
Open it in Access and explore the tables.
– What information is stored in each table?
– What information is stored in two places?
In one spot, it will be a primary key of the table
(check Design View), in another, it will be used to
represent the record in the other table (this is
called a foreign key, we'll study it more later)
How will this affect our import?
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 5
Importing into tbl_Schools
From file, choose Get External Data, then
choose Text File, navigate to the Day06 folder,
and select Schools-1-06.txt.
Select Fixed Width and then click Next.
Adjust the breaks if needed, then click Next.
Select an existing table like tbl_Schools and
click Next again.
Click Finish. Check the table contents. Make
sure the data has ended up in the right fields!
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 6
Second Import Exercise
Import the data from Schools-2-06.txt into a
new table in the C&U database. Use HELP if
you need it. Most of the steps are the same as
before.
In a new table, you can name the fields, and you
can select data types. Click Next when done. If
you cannot select a primary key, you can let
Access add one. Click Next to advance and
name the new table.
Check the new table and be sure that the
information ends up in appropriate fields.
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 7
Import Exercise Questions
In your import, pay attention to the
following questions:
– What format style is used in this text file?
– What is the role of the first line in the file?
– What data types and key are appropriate?
– Is the order of the fields the same between
the table tbl_Schools and the text file
Schools-2-06.txt?
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 8
Import Problems
What problems did your group run into
when trying to import?
How did you solve them?
Which format style of text file was easiest
to import from?
What good does it do to have the data
from Schools-2 in a new table? Can we
move the data over somehow?
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 9
Action Queries
What are action queries?
What new SQL commands are needed
to perform action queries?
How do you create action queries in
Access’ Query Design View?
What happens if you make a mistake in
running an action query?
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 10
Favorite music
Open your musicdemo database
We want to label some of our tracks as
"favorites," so using design view on that table,
add a new field called Favorite
– What data type should we use if we just want to
indicate whether or not the track is a favorite?
We really like long songs, but we don't like live
recordings, so construct a select query to list all
tracks over 8 minutes long that don't have LIVE
in their titles
– (qry_day06_long_nonlive_tracks) [15]
Test your query!
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 11
Update Query
Switch to SQL or DesignView
Change the SELECT query to an UPDATE
query to mark these tracks as favorites
– See pages 6-3 and 6-6 of the textbook
Save the query as qry_day06_favorites
Run the query to update the records
Check the results of the update by looking
at the table!
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 12
More on importing, moving data
Import the file mp3s_06.txt from the Day 6
AFS space to a new (temporary) table in
your musicdemo database
What would we have to do if we wanted to
get this data completely into our database
in the right spots?
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 13
Append queries (1)
Different parts of this file need to go to
different places in our database, and some
are unnecessary or redundant
We can construct an append query to
copy data from one table to another
We'll practice with Artists, since it's a
simple table
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 14
Append queries (2)
Start a new query in design view, close the add tables
dialog, and change the query type to an append query
– Asked where to append (copy) the data TO, select tbl_Artists
Now right-click to Show Table…
– We have to select where to get the data FROM, select your
temporary table
Select each field you want to copy
– Under each, use the Append To row to tell Access what field in
the destination table to copy into
Preview your query by switching to datasheet view
– Do not actually run the query yet
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 15
Removing duplicate records:
SQL DISTINCT
Notice any problems here?
– Scroll down for more problems
We have a lot of duplicate records (Artist name
is repeated with each track) and a lot of blank
(no artist known, bad tags on the MP3s, etc.)
– Switch to design and filter out blank Artists
To remove duplicate records, we use SQL
keyword DISTINCT right after SELECT
– Enter in SQL view
– Removes all records where all selected fields are an
exact duplicate of another record
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 16
Append queries (3)
SQL INSERT INTO…SELECT
Go to SQL view to add the word DISTINCT (it
goes right after SELECT)
Notice the new keywords: INSERT INTO
– These are used in all INSERT and APPEND queries,
for adding new records to a table
– Syntax is: INSERT INTO table (field1, field2…)
– The rest is a normal SELECT query
Preview the query in datasheet view
Save & run the query! Then open tbl_Artists to
check your results.
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 17
Next steps: Albums
Look at the table albums in Design view
Then look at it in datasheet view
Compare this to mp3s_06 imported data
How would you construct an append query
to move data from mp3s_06 to tbl_Albums
– What can we do about artists?
Bottom line: You can't… yet. So don't!
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 18
Homework
Do NOT finish importing data from mp3s.txt (we
have not covered all skills you would need)
Check the link on today’s classwork page for
homework to do in advance of Day 07
Read Days 7 and 8 in the textbook, covering
relationships and the JOIN operator
– We will discuss relationships, and use JOIN to do our
first multiple-table SELECT queries
http://www.cse.msu.edu/~cse103
U:/msu/course/cse/103
Day 06, Slide 19