CSIS 115 Database Design and Applications for Business

CSIS 115 Database Design and Applications for Business Dr. Meg Fryling “Dr. Meg” Fall 2012 @Siena. Dr. Meg #csis 115

Agenda • Course Resources – Welcome – Syllabus & Course Schedule – Clickers • The Future of Work • Why not just use a spreadsheet? – CSIS 114 Lab Revisited • Homework

Course Resources • • http: //blackboard. siena. edu Z: \afssosad. sos. siena. eduusers. . . http: //piazza. com/class http: //megfryling. com – Office Hours – Links to Course Resources 1 -3

The Future of Work • Rand Corp reports that most secure jobs in the US involve the adaption of new technology to solve business problems in innovative ways* • Databases have changed the world!!!! *Karoly, Lynn A. , and Constantijn W. A. Panis. The 21 st Century at Work. (Santa Monica: CA: The Rand Corporation, 2004). 1 -4

The Characteristics of Databases • The purpose of a database is to help people track things of interest to them. • Data is stored in tables (relations), which have rows (records or tuples) and columns (fields or attributes) like a spreadsheet. • A database may have multiple tables, where each table stores data about a different thing. • Each row in a table stores data about an occurrence or instance of the thing of interest. • A database stores data and relationships. KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 1 -5

Why not just use a spreadsheet? CSIS 114 Lab Revisited Assignments > In-Class Activities > DB vs SS > Vet Clinic SS 1 -6

Vet Clinic Spreadsheet • Add a record for Sara Craver’s (client #17) new dog named Peaches who had an appointment on 9/16/05 at a charge of $50. • Note: do it the best way you see fit! 1 -7

Adding a record for Sara Carver's new dog – which is the best (least limiting) strategy? A. Replace the name of the dog, visit, and amount values in record 17 with the new values. B. Add a blank record right under record 17, and put in just the new dog, date and charge values. C. Add a blank record right under record 17, put in the new dog name, date, and change values, then copy all other fields of record 17 data into the new record. D. Start another sheet in the workbook and put the new record there. 1 -8

SS - Data Validation • In cell G 14, type in the zip code 01237 • In cell H 13, type in the following for phone number: 518 372 -67895 • In cell K 13, type in the following date 12/35/2004 • In cell L 12, type in for the amount 3050 (this is a typo, the value should be 30. 50) 1 -9

Database Benefits • Databases allow us to organize data in a structured way • Eliminate (or at least significantly reduce) data duplication • Database user interfaces help Prevent Data Errors 1 -10

Account Balances • Becker's keeps client accounts on a second sheet named accounts. • Notice that the Client Id Field is used to link the records on the two sheets. – But is there really a “link”? • How would you accomplish the tasks described in the next questions? 1 -11

What is the best procedure to determine the current balance for all the clients? A. For each client use calculator to subtract the payment amounts from the visit amount B. Sort accounts by client number, subtotal with sum on the Credit/Debit field. C. Filter for all the Payments and form their sum, then filter for all the visits, form their sum, subtract the sum of payments from the sum of visits. D. Compute the sum of the Credit/Debit field. E. This task could be accomplished more 1 -12 efficiently in a database.

Vet Clinic Spreadsheet Why is this not a good solution? 1 -13

Database Benefits • Databases allow us to organize data in a structured way • Eliminate (or at least significantly reduce) data duplication • Database user interfaces help Prevent Data Errors 1 -14

Components of a Database System with SQL Homework 1 KROENKE AND AUER - DATABASE PROCESSING, 12 th Edition © 2012 Pearson Prentice Hall 1 -15

Applications, the DBMS, and SQL • Applications are the computer programs that users work with. • The Database Management System (DBMS) creates, processes, and administers databases. • Structured Query Language (SQL) is an internationally recognized standard database language that is used by all commercial DBMSs. KROENKE AND AUER - DATABASE PROCESSING, 12 th Edition © 2012 Pearson Prentice Hall 1 -16

Homework • Review Syllabus Carefully – Let me know if you have any questions • Sign-in to Piazza and complete Homework P 1 – Due Monday (9/10) by start of class • Start Homework 1 – Due Wednesday (9/12) by start of class • Start Reading Chapter 1, 2 (Skip 48 -56 and 63 -82), & Appendix A (Online) – http: //wps. prenhall. com/bp_kroenke_database_12/ 1 -17

Homework P 1 • Post a new Piazza note with: – An introduction of yourself including your name, major(s), minor(s), and class year. – A picture of yourself (please keep it clean!) – Two Lies and A Truth. List three interesting things about yourself. (I own two iguanas; I once shook hands with Tom Cruise; and I love to waterski. ) Two must be lies and one must be true (again, please keep it clean). Make it good because we will vote later and the student with the most incorrect votes wins! – Tag the note with #Homework. P 1 1 -18

Homework 1 • Now available on Blackboard – Due Weds 9/12 by start of class • Continuation of our lecture from last week – DB vs. SS • Develop main menu (user interface) for Becker’s Vet Clinic Database • Should be a breeze with skills learned in MIS 114! 1 -19