http://www.cse.msu.edu/~cse103

1
CSE 103

• 103 studentsDo not log in yet. Review Day 6 in
  your textbook. Think about why you might want to
  import data into a database.
• OthersPlease save your work and log out by
  1015.

2
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

3
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 youll have to think about it.)

4
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?

5
Colleges Universities db

• Copy the cu 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?

6
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!

7
Second Import Exercise

• Import the data from Schools-2-06.txt into a new
  table in the CU 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.

8
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?

9
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?

10
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?

11
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!

12
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!

13
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?

14
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

15
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

16
Removing duplicate recordsSQL 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

17
Append queries (3)SQL INSERT INTOSELECT

• 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.

18
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!

19
Homework

• Do NOT finish importing data from mp3s.txt (we
  have not covered all skills you would need)
• Check the link on todays 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