Introduction to MIS

1. Introduction to MIS Chapter 6 Database Management Systems

2. Database DBMS Database Management Systems Reports and ad hoc queries Programs Sales and transaction data

3. Outline • Relational Databases • Queries • Designing a Database • Database Applications • Database Administration • Database and e-Business • Cases: Airlines • Appendix: Building Forms in Access

4. Programmer Analyst Programs & Revisions Ad Hoc Queries and Reports Data Collection and Transaction Processing DBMS & People Database Administrator (Standards, Design, and Control) Data Database Management System Managers Program Program Business Needs Business Operations

5. Relational Databases • Tables • Rows • Columns • Primary keys • Data types • Text • Dates & times • Numbers • Objects Customer Table Phone Name Address City 312-555-1234 Jones 123 Main Chicago 502-555-8876 Smith 456 Oak Glasgow 602-555-9987 Juarez 887 Ribera Phoenix 612-555-4325 Olsen 465 Thor Minneapolis Orders Table Customer Date Salesperson Total_sale 502-555-8876 3/3/04 2223 157.92 602-555-9987 4/4/04 8876 295.53 612-555-4325 4/9/04 8876 132.94 502-555-8876 5/7/04 3345 183.67

6. Focus on data Stable data Programs change. Data independence Change programs without altering data. Data integrity Accuracy. Time. Concurrency. Security. Ad hoc queries Speed of development Report writers. Input forms. Data manipulation. Flexibility & Queries Database Advantages All Data Files Database Management System Invoice Program Billing Program

7. Database Queries • Single Table • Computations • Joining Tables Four questions to create a query 1) What output do you want to see? 2) What tables are involved? 3) What do you already know? (constraints) 4) How are the tables joined?

8. File: C05E15a.mdb Single Table Query Introduction Sample Data CID Name Phone City AccountBalance 28764 Adamz 602-999-2539 Phoenix 197.54 87535 James 305-777-2235 Miami 255.93 44453 Kolke 303-888-8876 Denver 863.39 29587 Smitz 206-676-7763 Seattle 353.76 Query: Which customers have balances greater than $200? Access Query Screen (QBE)

9. “AND” Conditions and Sorting Sample Data CID Name Phone City AccountBalance 28764 Adamz 602-999-2539 Phoenix 197.54 87535 James 305-777-2235 Miami 255.93 44453 Kolke 303-888-8876 Denver 863.39 29587 Smitz 206-676-7763 Seattle 353.76 Query: Which Denver customers have balances greater than $200? Access Query Screen (QBE)

10. SQL Introduction CID Name Phone City AccountBalance 28764 Adamz 602-999-2539 Phoenix 197.54 87535 James 305-777-2235 Miami 255.93 44453 Kolke 303-888-8876 Denver 863.39 29587 Smitz 206-676-7763 Seattle 353.76 Query: Which customers have balances greater than $200? SQL: SELECT CID, Name, Phone, City, AccountBalance FROM Customers WHERE AccountBalance > 200 ; Query: Which Denver customers have balances greater than $200? SQL: SELECT CID, City, AccountBalancel FROM Customers WHERE AccountBalance > 200 and City = “Denver” ORDER BY Name ASC ;

11. Useful WHERE Conditions • Comparisons <, =, >, <>, BETWEEN • Numbers AccountBalance > 200 • Text • CommonName > “Jones” • LIKE • Match allName LIKE “J*” • Match one Name LIKE “?m*” • Dates Odate between #8/15/95# and #8/31/95# • Missing data City is NULL • NOT Name is NOT NULL Use with QBE or SQL

12. SQL General Form • SELECT columns • FROM tables • JOIN link columns • WHERE conditions • GROUP BY column • ORDER BY column (ASC | DESC)

13. Computations SQL • Sum • Avg • Min • Max • Count • StDev • Var SELECT Count(C#), AVG(AccountBalance) FROM Customers ; QBE

14. Groups or Subtotals QBE Sample Output City AVG(AccountBalance) Chicago 197.54 Denver 863.39 Miami 255.93 Phoenix 526.76 Seattle 353.76 SQL SELECT City, AVG(AccountBalance) FROM Customers GROUP BY City ;

15. Groups with Conditions Query: What is the average account balance for customers from Denver?

16. Multiple Tables Customers Orders CID Name Phone City AccountBalance 12345 Jones 312-555-1234 Chicago $197.54 28764 Adams 602-999-2539 Phoenix $526.76 29587 Smitz 206-656-7763 Seattle $353.76 44453 Kolke 303-888-8876 Denver $863.39 87535 James 305-777-2235 Miami $255.98 OrderID CID SID Odate Amount 117 12345 887 3/3/2004 $57.92 125 87535 663 4/4/2004 $123.54 157 12345 554 4/9/2004 $297.89 169 29587 255 5/5/2004 $89.93 178 44453 663 5/1/2004 $154.89 188 29587 554 5/8/2004 $325.46 201 12345 887 5/28/2004 $193.58 211 44453 255 6/9/2004 $201.39 213 44453 255 6/9/2004 $154.15 215 87535 887 6/9/2004 $563.27 280 28764 663 5/27/2004 $255.32 Salespeople SID Name DateHired Phone Commission 225 West 5/23/75 213-333-2345 5 452 Zeke 8/15/94 213-343-5553 3 554 Jabbar 7/15/91 213-534-8876 4 663 Bird 9/12/93 213-225-3335 4 887 Johnson 2/2/92 213-887-6635 4 ItemsSold Items OID ItemID Quantity 117 1154 2 117 3342 1 117 7653 4 125 1154 4 125 8763 3 157 7653 2 169 3342 1 169 9987 5 178 2254 1 ItemID Description Price 1154 Corn Broom $1.00 2254 Blue Jeans $12.00 3342 Paper Towels--3 rolls $1.00 7653 Laundry Detergent $2.00 8763 Men's Boots $15.00 9987 Candy Popcorn $0.50

17. Linking Tables The Orders to ItemsSold relationship enforces referential integrity. One Order can list many ItemsSold.

18. Query Example • Which customers (CID) have placed orders since June 1, 2004? QBE SQL SELECT CID, ODate FROM Orders WHERE Odate >= #6/1/2004# ; CID ODate 44453 6/9/2004 44453 6/9/2004 87535 6/9/2004 28764 6/27/2004 Results

19. What are the names of the customers who placed orders since June 1, 2004? Query Example QBE SQL SELECT DISTINCT Name, Odate FROM Orders INNER JOIN Customers ON Orders.CID = Customers.CID WHERE Odate >= #6/1/2004# ; Name Odate Adamz 6/27/2004 James 6/9/2004 Kolke 6/9/2004 Results

20. Query Example • List the salespeople (sorted alphabetically) along with the names of customers who placed orders with that salesperson. SQL SELECT DISTINCT Salespeople.Name, Customers.Name FROM Salespeople INNER JOIN (Customers INNER JOIN Orders ON Customers.CID=Orders.CID) ON Salespeople.SID = Orders.SID ORDER BY Salespeople.Name ; Results QBE SalesName Cust.Name Bird Adamz Bird James Bird Kolke Jabbar Jones Jabbar Smitz Johnson James Johnson Jones West Kolke West Smitz

21. What is the total amount of orders placed from customers who live in Miami? Aggregation Query QBE Results $2,418.84 SELECT SUM(Amount) FROM Orders INNER JOIN Customers ON Orders.CID = Customers.CID WHERE City = “Miami” ; SQL

22. Primary keys One value per cell Column depends on whole key and nothing but the key. Database Design Customers CID name city home business fax service 11 Jones Chicago 111-1111 222-2222 222-35534 876-3456 22 Smith Chicago 111-4567 444-5353 33 James Chicago 111-2567 222-8976 44 Ricci Chicago 333-8765 Phones(CID, phone_type, number) CID phone_type number 11 home 111-1111 11 business 222-2222 11 fax 222-3534 11 service 876-3456 22 home 111-4587 22 service 444-5353 33 home 111-2567 44 fax 333-8765 Customers(CID, name, city) CID name city 11 Jones Chicago 22 Smith Chicago 33 James Chicago 44 Ricci Chicago

23. File: C05Vid.mdb Database Design: Normalization

24. Notation Table columns Table name Customer (CustomerID, Phone, Name, Address, City, State, ZipCode) Primary key is underlined CustomerID Phone LastName FirstName Address City State ZipCode 1 502-666-7777 Johnson Martha 125 Main Street Alvaton KY 42122 2 502-888-6464 Smith Jack 873 Elm Street Bowling Green KY 42101 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171 4 502-333-9494 Adams Samuel 746 Brown Drive Alvaton KY 42122 5 502-474-4746 Rabitz Victor 645 White Avenue Bowling Green KY 42102 6 615-373-4746 Steinmetz Susan 15 Speedway Drive Portland TN 37148 7 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 37148 8 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 37031 9 502-222-4351 Chavez Juan 673 Industry Blvd. Caneyville KY 42721 10 502-444-2512 Rojo Maria 88 Main Street Cave City KY 42127

25. 1st: Repeating RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) ) Repeating Section Causes duplication TransID RentDate CustomerID LastName Phone Address VideoID Copy# Title Rent 1 4/18/04 3 Washington 502-777-7575 95 Easy Street 1 2 2001: A Space Odyssey $1.50 1 4/18/04 3 Washington 502-777-7575 95 Easy Street 6 3 Clockwork Orange $1.50 2 4/30/04 7 Lasater 615-888-4474 67 S. Ray Drive 8 1 Hopscotch $1.50 2 4/30/04 7 Lasater 615-888-4474 67 S. Ray Drive 2 1 Apocalypse Now $2.00 2 4/30/04 7 Lasater 615-888-4474 67 S. Ray Drive 6 1 Clockwork Orange $1.50 3 4/18/04 8 Jones 615-452-1162 867 Lakeside Drive 9 1 Luggage Of The Gods $2.50 3 4/18/04 8 Jones 615-452-1162 867 Lakeside Drive 15 1 Fabulous Baker Boys $2.00 3 4/18/04 8 Jones 615-452-1162 867 Lakeside Drive 4 1 Boy And His Dog $2.50 4 4/18/04 3 Washington 502-777-7575 95 Easy Street 3 1 Blues Brothers $2.00 4 4/18/04 3 Washington 502-777-7575 95 Easy Street 8 1 Hopscotch $1.50 4 4/18/04 3 Washington 502-777-7575 95 Easy Street 13 1 Surf Nazis Must Die $2.50 4 4/18/04 3 Washington 502-777-7575 95 Easy Street 17 1 Witches of Eastwick $2.00

26. First Normal Customer Rentals Name Phone Address City State ZipCode VideoID Copy# Title Rent 1. 6 1 Clockwork Orange 1.50 2. 8 2 Hopscotch 1.50 3. 4. 5. {Unused Space} Not in First Normal Form

27. 1st: Split RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) ) RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode) RentalLine(TransID, VideoID, Copy#, Title, Rent ) RentalForm2 TransID RentDate CustomerID Phone LastName FirstName Address City State ZipCode 1 4/18/04 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171 2 4/30/04 7 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 37148 3 4/18/04 8 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 37031 4 4/18/04 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171 RentalLine Note: replication TransID VideoID Copy# Title Rent 1 1 2 2001: A Space Odyssey $1.50 1 6 3 Clockwork Orange $1.50 2 8 1 Hopscotch $1.50 2 2 1 Apocalypse Now $2.00 2 6 1 Clockwork Orange $1.50 3 9 1 Luggage Of The Gods $2.50 3 15 1 Fabulous Baker Boys $2.00 3 4 1 Boy And His Dog $2.50 4 3 1 Blues Brothers $2.00 4 8 1 Hopscotch $1.50 4 13 1 Surf Nazis Must Die $2.50 4 17 1 Witches of Eastwick $2.00 Note: replication

28. 2nd Split Column depends on entire (whole) key. RentalLine(TransID, VideoID, Copy#, Title, Rent ) VideosRented(TransID, VideoID, Copy# ) Videos(VideoID, Title, Rent ) TransID VideoID Copy# 1 1 2 1 6 3 2 2 1 2 6 1 2 8 1 3 4 1 3 9 1 3 15 1 4 3 1 4 8 1 4 13 1 4 17 1 VideoID Title Rent 1 2001: A Space Odyssey $1.50 2 Apocalypse Now $2.00 3 Blues Brothers $2.00 4 Boy And His Dog $2.50 5 Brother From Another Planet $2.00 6 Clockwork Orange $1.50 7 Gods Must Be Crazy $2.00 8 Hopscotch $1.50

29. 3rd Split RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode ) Rentals(TransID, RentDate, CustomerID ) Customers(CustomerID, Phone, Name, Address, City, State, ZipCode ) Rentals TransID RentDate CustomerID 1 4/18/04 3 2 4/30/04 7 3 4/18/04 8 4 4/18/04 3 Customers CustomerID Phone LastName FirstName Address City State ZipCode 1 502-666-7777 Johnson Martha 125 Main Street Alvaton KY 42122 2 502-888-6464 Smith Jack 873 Elm Street Bowling Green KY 42101 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171 4 502-333-9494 Adams Samuel 746 Brown Drive Alvaton KY 42122 5 502-474-4746 Rabitz Victor 645 White Avenue Bowling Green KY 42102 6 615-373-4746 Steinmetz Susan 15 Speedway Drive Portland TN 37148 7 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 37148 8 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 37031 9 502-222-4351 Chavez Juan 673 Industry Blvd. Caneyville KY 42721 10 502-444-2512 Rojo Maria 88 Main Street Cave City KY 42127

30. 3NF Tables

31. DBMS Input Screen Text/Labels Data Variables Command Buttons Record Selectors - Subform - Main Scrolling Region/Subform

32. DBMS Report Writer • Report header • Page header • Break/Group header • Detail • Footers

33. Sample Report with Groups

34. Designing Menus for Users Main Menu Customer Information 1. Setup Choices 2. Data Input 3. Print Reports 4. DOS Utilities 5. Backups Daily Sales Reports Friday Sales Meeting Monthly Customer Letters Quit As a secretary, which menu is easier to understand?

35. Database Administration • Database Administrator • Testing • Backup • Recovery • Standards • Access Controls

36. E-Business Databases • E-business is transaction-based • Databases support multiple users and protect transactions • Modern websites are driven by databases

37. E-Business Databases Order Form Descriptions Prices Web page Internet Customer Page request Web Server Web program script <HTML> Text <% Database connection %> Queries and data

38. Cases: Airlines

39. Cases: American AirlinesSouthwest Airlines www.americanair.com www.iflyswa.com What is the company’s current status? What is the Internet strategy? How does the company use information technology? What are the prospects for the industry?

40. Appendix: Building Forms in Access Begin with the form wizard. Select everything from the Sale table.

41. Selecting Columns Select additional data from the other tables. But be careful with key columns. For example, do not include the CustomerID from the Customer table or the ItemID from the Item table. Instead, use CustomerID from the Sale table and ItemID from the SaleItem table.

42. Form Wizard: First Pass The wizard builds the Sale form with a sub form to enter multiple Items being sold. But it needs some work with layout.

43. Form Design View • Lookup values • Customer table • CustomerID, LastName, FirstName, Phone • Store value in: CustomerID In design view, you can move the labels and boxes to make the form easier to read. Then, delete the original CustomerID box and use the wizard to add a Combo box to let users select a customer from a list.

44. Form Design: Sub Form and SubTotal Edit the subform and add a Combo box for ItemID. Delete the Description box. Add an ItemValue text box to multiply Quantity * List Price. Add a text box to compute the subtotal [=Sum([Quantity]*[ListPrice])]. Be sure to set the correct names and formats in the property window.

45. Sale Form The final sale form adds a text box that copies the subtotal from the sub form to the main form. Just add a text box and set its value to =[SaleItem Subform].[Form].[SubTotal] and set its name and format properties.