LSP 121

1. LSP 121 Week 1 Intro to Databases

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

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

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

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

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

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

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

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

10. Table Example • Let’s create a table for the following: • Home address / phone book * What is the primary key? * What are the indexes?

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

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

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

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

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

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

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

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

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

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