Did you know: Traffic delay information is calculated using Bluetooth device information

1. Did you know:Traffic delay information is calculated using Bluetooth device information

2. Day 12: Database Concepts Cody CutrightCS 101February 17th, 2014

3. Graphicsheat.com

4. Why do databases matter to you? • Among non-CS people, database skills are rare • A healthy understanding of databases makes you marketable • Almost every business has a database of some sort – and someone has to manage it! • You could be selected to be trained by your company to manage a database someday!

5. In the beginning • Pre-1970, databases were flat • We have essentially worked with “flat” databases: • Tab Delimited • CSV • These databases were just text files (.txt)

6. The problem? Lname, FName, Age, Salary|Smith, John, 35, $280|Doe, Jane, 28, $325|Brown, Scott, 41, $265|Howard, Shemp, 48, $359|Taylor, Tom, 22, $250 • A text file database, like above, is difficult to search though and find specific information

7. And then there was light • Relational databases were created in 1970 • IBM researcher, E.F. Codd, wrote a paper describing them • Relational databases store information in tables • For visual purposes, think similar to Excel tables

8. Relational databases are everywhere! Record Keeping Company Inventory Tracking Social Networking Healthcare Internet Schools Online Stores Offline Stores

9. Sample RDB

10. Let’s back up…

11. Field • The smallest data element contained in a table • Field types: • Text • Numeric • Date • Field examples: • Birthdates • Last Name • Age • Social Security #

12. Record • Record – a complete set of all of the data elements (fields) about one person, place, event, or concept • Example record in a course gradebook: • First Name, Last Name, Student ID, Age, E-mail

13. Table • The foundation of every database • Contains a primarykey (unique field) • Is a collection of related records • Example Table: • All Student records in my CS 101 sections would be added to my Section Roster Table during registration

14. Key • A field that contains a unique identifier • Foreign key • A field in one table, that is a primary key in another

15. Database • Consists of: • One or more tables to store data • One or more forms to enter data • One or more reports to output the table data as organized information

16. Let’s define a table • Let’s make a table for student information • What fields? • What data types? • How do we identify records?

17. Relationships “When two tables notice each other…” • Access is a Relational Database Management System • That being said it’s important that, over time, we learn to create relationships effectively.

18. Relationship Basics • For now, let’s ignore how we actually make a relationship (it’s just drag and drop) • Let’s examine some already created relationships and talk through them

19. Sample Relationships

20. What changed?

21. Why is this necessary?

22. Relationship types identified • One-to-Many • Many-to-Many*** • One-to-One

23. Many-to-many?

24. A brief note on many to many “Think about a simple relationship like the one between Authors and Books. An author can write many books. A book could have many authors. Now, without a bridge table to resolve the many-to-many relationship, what would the alternative be? You'd have to add multiple Author_ID columns to the Books table, one for each author. But how many do you add? 2? 3? 10? However many you choose, you'll probably end up with a lot of sparse rows where many of the Author_ID values are NULL and there's a good chance that you'll run across a case where you need "just one more." So then you're either constantly modifying the schema to try to accommodate or you're imposing some artificial restriction ("no book can have more than 3 authors") to force things to fit.” -Joe Stefanelli on StackOverflow.com

25. Referential Integrity • For relationships, you want to be able to be absolutely surethat they are valid. • What if you had a business, and didn’t check that the people you wrote checks to were employees?

26. What could happen?

27. Cascade updates / deletes • Cascading Updates (Good) • Typically, we will always cascade updates. Let’s assume we change the stock number of an item, all orders that referenced that stock number would automatically update (for record keeping information). • Cascades Deletes (Bad.. Well, generally) • Let’s assume an employee is working on some big projects. The HR department accidentally removes the employee… every project in the database, that refers to the deleted employee, will be deleted.

28. Spreadsheet vs databases MS Excel MS Access Require multiple related tables to store data Have a large amount of data Need to connect to and send/receive data to/from external databases Need to group, sort, and total data based on various parameters Have an application that requires multiple users to connect to one data source at once • Only need one worksheet to manage data • Have mostly numeric data (i.e. expense reports) • Require subtotals and totals in your worksheet • Want to run a series of “What-If” Scenarios • Need to create complex charts or graphs