1 / 21

LSP 121

Learn the basics of databases and why they are essential for managing large amounts of data, sharing information, and maintaining data integrity. Understand the key terms and concepts related to tables, fields, records, queries, and field attributes.

moten
Download Presentation

LSP 121

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related