Database Management

1. Chapter 4 Database Management Technology Toolbox: Creating Forms in Access Technology Toolbox: Creating Database Reports Cases: Pharmaceutical Industry

2. Outline • How do you store and retrieve the vast amount of data collected in a modern company? • Why is the database management approach so important to business? • How do you write questions for the DBMS to obtain data? • How do you create a new database? • How do you create business applications using a DBMS? • What tasks need to be performed to keep a database running? • Why are databases so important in e-business?

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

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 CustomerID Name Address City 12345 Jones 123 Elm Chicago 28764 Adamz 938 Main Phoenix 29587 Smitz 523 Oak Seattle 33352 Sanchez 999 Pine Denver 44453 Kolke 909 West Denver 87535 James 374 Main Miami Orders Table OrderID CustomerID Date Salesperson Total_sale 117 12345 3/3/06 887 57.92 125 87535 4/4/06 663 123.54 157 12345 4/9/06 554 297.89 169 29587 5/6/6 255 89.93

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: C04E15.mdb Single Table Query Introduction CID Name Phone City AccountBalance 28764 Adamz 602-999-2539 Phoenix 526.76 87535 James 305-777-2235 Miami 255.93 12345 Jones 312-555-1234 Chicago 197.54 44453 Kolke 303-888-8876 Denver 863.39 33352 Sanchez 303-444-1352 Denver 153.00 29587 Smitz 206-676-7763 Seattle 353.76 Sample Data 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 526.76 87535 James 305-777-2235 Miami 255.93 12345 Jones 312-555-1234 Chicago 197.54 44453 Kolke 303-888-8876 Denver 863.39 33352 Sanchez 303-444-1352 Denver 153.00 29587 Smitz 206-676-7763 Seattle 353.76 Query: Which Denver customers have balances greater than $200? Access Query Screen (QBE)

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

11. SQL Introduction CID Name Phone City AccountBalance 28764 Adamz 602-999-2539 Phoenix 526.76 87535 James 305-777-2235 Miami 255.93 12345 Jones 312-555-1234 Chicago 197.54 44453 Kolke 303-888-8876 Denver 863.39 33352 Sanchez 303-444-1352 Denver 153.00 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 ;

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

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 • CID Name Phone City AccountBalance • 12345 Jones 312-555-1234 Chicago $197.54 • 28764 Adams 602-999-2539 Phoenix $526.76 • Smitz 206-656-7763 Seattle $353.76 • 33352 Sanchez 303-444-1352 Denver $153.00 • 44453 Kolke 303-888-8876 Denver $863.39 • 87535 James 305-777-2235 Miami $255.98 Orders OrderID CID SID OrderDate Amount 117 12345 887 3/3/2006 $57.92 125 87535 663 4/4/2006 $123.54 157 12345 554 4/9/2006 $297.89 169 29587 255 5/5/2006 $89.93 178 44453 663 5/1/2006 $154.89 188 29587 554 5/8/2006 $325.46 201 12345 887 5/28/2006 $193.58 211 44453 255 6/9/2006 $201.39 213 44453 255 6/9/2006 $154.15 215 87535 887 6/9/2006 $563.27 280 28764 663 5/27/2006 $255.32 Salespeople SID Name DateHired Phone Commission 255 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 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 Items 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 (CustomerID) have placed orders since June 1, 2006? QBE SQL SELECT CustomerID, OrderDate FROM Orders WHERE Orderdate >= #6/1/2006# ; Results CustomerID OrderDate 44453 6/9/2006 44453 6/9/2006 87535 6/9/2006 28764 6/27/2006

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

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

21. What is the total amount of orders? Aggregation Query QBE Results $2,416.84 SELECT SUM(Amount) FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; 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: C04Video.mdb Database Design: Normalization

24. Notation Table name Table columns Customer (CustomerID, Phone, FirstName, LastName, Address, City, State, ZipCode) Primary key is underlined

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/06 3 Washington 209-777-7575 95 Easy Street 1 2 2001: A Space Odyssey $1.50 1 4/18/06 3 Washington 209-777-7575 95 Easy Street 6 3 Clockwork Orange $1.50 2 4/30/06 7 Lasater 209-888-4474 67 S. Ray Drive 8 1 Hopscotch $1.50 2 4/30/06 7 Lasater 209-888-4474 67 S. Ray Drive 2 1 Apocalypse Now $2.00 2 4/30/06 7 Lasater 209-888-4474 67 S. Ray Drive 6 1 Clockwork Orange $1.50 3 4/18/06 8 Jones 209-452-1162 867 Lakeside Drive 9 1 Luggage Of The Gods $2.50 3 4/18/06 8 Jones 209-452-1162 867 Lakeside Drive 15 1 Fabulous Baker Boys $2.00 3 4/18/06 8 Jones 209-452-1162 867 Lakeside Drive 4 1 Boy And His Dog $2.50 4 4/18/06 3 Washington 209-777-7575 95 Easy Street 3 1 Blues Brothers $2.00 4 4/18/06 3 Washington 209-777-7575 95 Easy Street 8 1 Hopscotch $1.50 4 4/18/06 3 Washington 209-777-7575 95 Easy Street 13 1 Surf Nazis Must Die $2.50 4 4/18/06 3 Washington 209-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/06 3 209-777-7575 Washington Elroy 95 Easy Street Lodi CA 95240 2 4/30/06 7 209-888-4474 Lasater Les 67 S. Ray Drive Valley Springs CA 95226 3 4/18/06 8 209-452-1162 Jones Charlie 867 Lakeside Drive Walnut Creek CA 94595 4 4/18/06 3 209-777-7575 Washington Elroy 95 Easy Street Lodi CA 95240 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 209-666-7777 Johnson Martha 125 Main Street Tracy CA 95304 2 209-888-6464 Smith Jack 873 Elm Street Merced CA 95340 3 209-777-7575 Washington Elroy 95 Easy Street Lodi CA 95240 4 209-333-9494 Adams Samuel 746 Brown Drive Sutters Creek CA 95685 5 209-474-4746 Rabitz Victor 645 White Avenue Ione CA 95640 6 209-373-4746 Steinmetz Susan 15 Speedway Drive Jackson CA 95642 7 209-888-4474 Lasater Les 67 S. Ray Drive Valley Springs CA 95226 8 209-452-1162 Jones Charlie 867 Lakeside Drive Walnut Creek CA 94595 9 209-222-4351 Chavez Juan 673 Industry Blvd. Lockeford CA 95238 10 209-444-2512 Rojo Maria 88 Main Street Angels Camp CA 95221

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. Technology Toolbox: Building Forms in Access • Start the Form Wizard • Select the Order Table, all columns • Customer Table: Phone • ItemsSold, all columns except OrderID • Product Table, Category • Design View, rearrange, add Combo boxes

39. Quick Quiz: Forms in Access 1. Create a simple customer form and enter data to test it. 2. Create a basic order form and add a combo box to select customers.

40. Technology Toolbox: Creating Database Reports • Save a query with Order, Customer, ItemsSold, Product, add Value:Price*Quantity • Start the Report Wizard • Select the new query • Add the columns to the report • Choose a layout • Click Summary Options to sum Value • Use Design View to clean up the layout and format

41. Quick Quiz: Creating a Report 1. Create a report that prints all of the items ordered by each customer. 2. Create a report that prints each customer, followed by the orders for that customer. 3. Create a report that displays a chart of total sales by customer.

42. Cases: Pharmaceuticals How do you store and retrieve huge amounts of data?