LSP 121

1. LSP 121 Week 6 Intro to Databases

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

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

4. Example of a table • This “table” of customers has: • Four “fields” (last name, first name, address, telephone) • Three “records”

5. 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”

6. 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…

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

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

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

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

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

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

13. 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…

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

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

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

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

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

19. 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???

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

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