1.Introduction to MIS Chapter 4 Database Management Systems Jerry PostTechnology Toolbox: Creating Forms in AccessTechnology Toolbox: Creating Database ReportsCases: Pharmaceutical Industry

2. Outline How do you store and retrieve the vast amount ofdata collected in a modern company? Why is the database management approach soimportant to business? How do you write questions for the DBMS toobtain data? How do you create a new database? How do you create business applications using aDBMS? What tasks need to be performed to keep adatabase running? Why are databases so important in e-business? How are databases used in cloud computing? 3. Database Management Systems Reports andDatabase ad hoc queriesDBMS ProgramsSales and transaction data 4. Central Role of DBMSDatabase AdministratorProgrammerAnalyst(Standards, Design, and Control)DataPrograms Database& Revisions ManagementSystem Ad Hoc Queries and ReportsManagersProgramProgram Business NeedsData Collectionand TransactionProcessing Business Operations 5. Relational Databases TablesCustomer Table RowsCustomerIDNameAddressCity Columns 1234528764JonesAdamz125 Elm938 Main Chicago Phoenix Primary keys29587 Smitz 523 OakSeattle33352 Sanchez 999 Pine Denver Data types44453 Kolke 909 West Denver Text87535 James 374 Main Miami Dates & times Sales Table NumbersSaleIDCustomerID Date Salesperson Objects117 123453/3/12 887 125 875354/4/12 663 157 123454/9/12 554 169 295875/6/12 255 6. Database Advantages Focus on data Stable data Programs change. Data independence Change programs withoutAll Data Filesaltering data. Data integrityDatabase Management Accuracy. System Time. Concurrency.InvoiceBilling Security. Program Program Ad hoc queries Speed of development Report writers. Input forms. Data manipulation. Flexibility & Queries 7. Data Quality: Concurrent Access Customer AccountsTransaction ASanchez: BalanceTransaction B1) Receive 300 payment2) Read Balance (500)Sanchez: 500 3) New Purchase (350) 4) Read Balance (500)5) Subtract payment6) Store new results (200) Sanchez: 200 7) Add purchase Sanchez: 8508) Store new result (850) 8. Database QueriesFour questions to create a query 1) What output do you want to see? 2) What do you already know? (constraints) 3) What tables are involved? 4) How are the tables joined? Single Table Computations Joining Tables 9. Sample Data: CustomersCustomerID Name PhoneCityAccountBalance12345 Jones 312-555-1234 Chicago 197.5428764 Adamz 602-999-2539 Phoenix 526.7629587 Smitz 206-676-7763 Seattle 353.7633352 Sanchez 303-444-1352 Denver153.0044453 Kolke 303-888-8876 Denver863.3987535 James 305-777-2235 Miami 255.93 10. File: C04E15.mdbSingle Table Query IntroductionQuery: List all of the customers. Access Query Screen (grid) 11. Results: Customer Query 12. Query ConditionsWhich customers owe more than $200? 13. Query Results 14. Query: ANDWhich customers from Denver owe more than $200? 15. Query Results 16. Query: ORList customers from Denver or Chicago. 17. Query Results 18. Query: SortingList customers from Denver or Chicago, sort the results. 19. SQL General Form SELECT columns FROM tables JOIN link columns WHEREconditions GROUP BY column ORDER BY column (ASC | DESC) 20. SQL Introduction List all customers.SQL:SELECT *FROM Customers 21. SQL: AND ConditionSELECT Name, Phone, City, AccountBalanceFROM CustomersWHERE(AccountBalance>200) AND (City=Denver) 22. SQL: OR ConditionSELECT Customers.CustomerID, Customers.Name, Customers.Phone, Customers.City, Customers.AccountBalanceFROM CustomersWHERE(Customers.City = "Denver") OR (Customers.City = "Chicago") 23. Common Query ConditionsOperator MeaningExamples=Equals City=DenverSalary=60000Greater than Sales > 15000 Not equalCity DenverBETWEENBetween x and ySaleDate BETWEEN 01-Jan-2012 AND 28-Feb-2012Sales BETWEEN 10000 AND 20000LIKE Simple pattern LastName LIKE J%matching % or * ProductID LIKE BL_ _DR _ _ _matches anycharacters _ or ?matches oneNull Missing data City Is NullNOTNegation Not City=Denver 24. Conditions: BETWEENList sales in June.Commonly used for date conditions:WHERE SaleDate BETWEEN 6/1/2012 AND 6/30/2012 25. Query Results 26. Standard Aggregation FunctionsSUM total value of itemsAVG average of valuesMIN minimum valueMAX maximum valueCOUNT number of rowsSTDEV standard deviationVAR variance of items 27. Sample Data: Sales AmountAmount $197.54 $526.76 $353.76 $153.00 $863.39 $255.93 28. Query: AggregationCount Avg Sum6 $391.73 $2,350.38 29. Aggregation Query in Access GridClick Totals ButtonTotals Row 30. Row-by-Row ComputationsType formulaThen changerow headingCategoryPrice EstCostElectronics $1,000.00 700Electronics $50.00 35 31. SQL: AggregationHow many customers are there and want is the average balance?SELECT Count(CustomerID) AS NCustomers, Avg(AccountBalance) AS AverageOwedFROM Customers 32. SQL: Row-by-Row CalculationsEstimate the cost of clothing items as 70 percent of the price.SELECT Category, Price, 0.7*Price AS EstCostFROM ItemsWHERE(Category=Electronics) 33. Subtotals: SQL How much money is owed by customers in each city?SELECT City, Sum(AccountBalance) AS SumOfAccountBalanceFROMCustomersGROUP BY City CitySumOfAccountBalance Chicago$197.54 Denver $1,016.39 Miami$255.93 Phoenix$526.76 Seattle$353.76 34. Multiple Tables Customers SalesCID LastNamePhoneCityAccountBalance SaleID CIDSPIDSaleDate12345 Jones 312-555-1234 Chicago$197.54 117 12345 887 3/3/201228764 Adamz 602-999-2539 Phoenix$526.76 125 87535 663 4/4/201229587 Smitz 206-656-7763 Seattle$353.76 157 12345 554 4/9/201233352 Sanchez 303-444-1352 Denver $153.00 169 29587 255 5/5/201244453 Kolke 303-888-8876 Denver $863.39 178 44453 663 5/1/201287535 James 305-777-2235 Miami$255.98 188 29587 554 5/8/2012201 12345 8875/28/2012211 44453 255 6/9/2012Salespeople 213 44453 2556/10/2012215 87535 887 6/9/2012SPID LastNameDateHired PhoneCommission280 28764 6635/27/2012255West5/23/05 213-333-23455285 28764 8876/15/2012452Thomas8/15/04 213-343-55533554Jabbar7/15/01 213-534-88764663Bird9/12/03 213-225-33354 ItemsSold887Johnson 2/2/02213-887-66354 SaleIDItemIDQuantity 117 11542 Items 117 33421ItemIDCategoryDescriptionPrice 117 765341154Shoes Red Boots$100.00 125 115442254Clothes Blue Jeans $12.00125 876333342Electronics LCD-40 inch $1,000.00157 765327653Shoes Blue Suede $50.00169 334218763Clothes Mens Work Boots $45.00169 998759987Electronics Blu-Ray Player $400.00 178 22541 35. Linking TablesThe Sales to ItemsSold relationship enforces referential integrity.One Sale can list many ItemsSold. 36. Query Example Which customers (CustomerID) have placed orders since June 1, 2012?SQLQBESELECT CustomerID, SaleDateFROM SalesWHERE SaleDate >= #6/1/2012# ; Results CustomerID SaleDate 444536/9/2012 44453 6/10/2012 875356/9/2012 28764 6/15/2012 37. Query Example What are the names of the customers who placed orders since June 1, 2012? SQLSELECT DISTINCT Customers.CustomerID, Name, SaleDateFROMSalesINNER JOIN Customers ON Sales.CustomerID = Customers.CustomerIDWHERE SaleDate >= #6/1/2012# ;GridResultsCustomerID Name OrderDate28764Adamz6/15/201244453Kolke 6/9/201244453Kolke6/10/201287535James 6/9/2012 38. Query Example List the salespeople (sorted alphabetically) along with the names of customers who placed orders with that salesperson. SQLSELECT DISTINCT Salespeople.Name, Customers.NameFROMSalespeopleINNER JOIN (Customers INNER JOIN Orders ONCustomers.CustomerID=Sales.CustomerID)ON Salespeople.SalespersonID = Sales.SalespersonIDORDER BY Salespeople.Name, Customers.Name ;ResultsSalesName Cust.Name QBEBird AdamzBird JamesBird KolkeJabbar JonesJabbar SmitzJohnsonAdamzJohnsonJamesJohnsonJonesWest KolkeWest Smitz 39. Multiple Tables, GROUP BY, WHEREWho are the top salespeople in June?Begin by listing the sales in June. 40. Sales RowsSalespersonI Name SaleDat Quantity PriceValue De 255 West6/9/201 2 $1,000.0 $2,000.0 2 00 255 West6/9/201 5 $50.00 $250.00 2 255 West6/9/201 1 $12.00$12.00 2 887 Johnso6/9/201 1 The quantity and price columns are shown$12.00$12.00 temporarily to ensure the computation is specified n 2 correctly for the Value column. 887 Johnso6/9/201 1 $50.00$50.00 n 2 41. Subtotal in SQLSELECT Salespeople.SalespersonID, Salespeople.Name,Sum([Quantity]*[Price]) AS [Value]FROM Items INNER JOIN ((SalespeopleINNER JOIN SalesON Salespeople.SalespersonID = Sales.SalespersonID)INNER JOIN ItemsSoldON Sales.SalesID = ItemsSold.SaleID)ON Items.ItemID = ItemsSold.ItemIDWHERE (Sales.SaleDate Between #6/1/2012# And #6/30/2012#)GROUP BY Salespeople.SalespersonID, Salespeople.NameORDER BY Sum([Quantity]*[Price]) DESC; 42. Subtotals: Access Grid First AttemptWho are the top salespeople in June?Set the totals and set the Sum column.Incomplete. See results 43. Initial ResultsSalespersonIDName SaleDate Value 255West6/9/2012 $2,250.00 255West 6/10/2012 $12.00 887Johnson 6/9/2012 $62.00Salesperson #255 (West) shows up multiple timesbecause of multiple days. GROUP BY Day,Salesperson 44. GROUP BY Conditions: WHEREUse WHERE insteadof GROUP BY 45. Best Salesperson Results SalespersonIDName Value255West$2,262.00887Johnson $62.00 46. Converting Business Questions to Queries1.What do you want to see? SELECT FROM2.What constraints are you given?INNER JOIN3.What tables are involved?WHERE GROUP BY4.How are the tables joined? ORDER BY Start with the parts you know how to do. Verify the results at each step. Add more tables and columns as needed Do aggregate totals at the endafter verifying the rows. Look for for each or by to use GROUP BY for subtotals. 47. Database Design: Normalization 48. Notation Table Table namecolumns Customer (CustomerID, LastName, Phone, Street, City, AccountBalance) Last AccountCustomerID Phone Street City Name Balance 12345 Jones (312) 555-1234 125 Elm StreetChicago$197.54 28764 Adamz (602) 999-2539 938 Main Street Phoenix$526.76 29587 Smitz (206) 676-7763 523 Oak StreetSeattle$353.76 33352 Sanchez (303) 444-1352 999 Pine Street Denver $153.00 44453 Kolke (303) 888-8876 909 West Avenue Denver $863.39 87535 James (305) 777-2235 374 Main Street Miami$255.93 49. 1st: Repeating SaleForm(SaleID, SaleDate, CustomerID, Phone, Name, Street, (ItemID, Quantity, Description, Price ) )Repeating Section Causes duplicationSaleID SaleDateCustomerID NamePhoneStreetItemID Quantity DescriptionPrice117 3/3/201212345 Jones (312) 555-1234 125 Elm Street 11542Red Boots $100.00117 3/3/201212345 Jones (312) 555-1234 125 Elm Street 33421LCD-40 inch$1,000.00117 3/3/201212345 Jones (312) 555-1234 125 Elm Street 76534Blue Suede $50.00125 4/4/201287535 James (305) 777-2235 374 Main Street11544Red Boots $100.00125 4/4/201287535 James (305) 777-2235 374 Main Street87633Mens Work Boots $45.00157 4/9/201212345 Jones (312) 555-1234 125 Elm Street 76532Blue Suede $50.00169 5/6/201229587 Smitz (206) 676-7763 523 Oak Street 33421LCD-40 inch$1,000.00169 5/6/201229587 Smitz (206) 676-7763 523 Oak Street 99872Blu-Ray Player$400.00178 5/1/201244453 Kolke (303) 888-8876 909 West Avenue22541Blue Jeans $12.00188 5/8/201229587 Smitz (206) 676-7763 523 Oak Street 33421LCD-40 inch$1,000.00188 5/8/201229587 Smitz (206) 676-7763 523 Oak Street 87634Mens Work Boots $45.002015/23/201212345 Jones (312) 555-1234 125 Elm Street 11541Red Boots $100.00 50. First Normal SalesForm with repeating dataSaleID SaleDateCustomerID NamePhoneStreetItemID, Quantity, Description, Price117 3/3/201212345 Jones (312) 555-1234 125 Elm Street1154, 2, Red Boots, $100.00 3342, 1, LCD-40 inch, $1,000.00 7653, 4, Blue Suede, $50.00125 4/4/201287535 James (305) 777-2235 374 Main Street 1154, 4, Red Boots, $100,00 8763, 3, Mens Work Boots, $45.00157 4/9/201212345 Jones (312) 555-1234 125 Elm Street7653, 2, Blue Suede, $50.00169 5/6/201229587 Smitz (206) 676-7763 523 Oak Street3342, 1, LCD-40 inch, $1,000.00 9987, 2, Blu-Ray Player, $400.00178 5/1/201244453 Kolke (303) 888-8876 909 West Avenue 2254, 1, Blue Jeans, $12.00188 5/8/201229587 Smitz (206) 676-7763 523 Oak Street3342, 1, LCD-40 inch, $1,000.00 8763, 1, Mens Work Boots, $45.002015/23/201212345 Jones (312) 555-1234 125 Elm Street1154, 1, Red Boots, $100.00 Not in First Normal Form 51. 1st: SplitSaleForm(SaleID, SaleDate, CustomerID, Phone, Name, Street, (ItemID, Quantity, Description, Price ) )SaleForm2(SaleID, SaleDate, CustomerID, Phone, Name, Street)SaleID SaleDateCustomerID NamePhoneStreetSaleLine(SaleID, ItemID, Quantity, Description, Price)117 3/3/201212345 Jones (312) 555-1234 125 Elm Street117 3/3/201212345 Jones (312) 555-1234 125 Elm StreetSaleID ItemID Quantity DescriptionPrice117 3/3/201212345 Jones (312) 555-1234 125 Elm Street117 11542Red Boots$100.00125 4/4/201287535 James (305) 777-2235 374 Main Street 117 33421LCD-40 inch$1,000.00125 4/4/201287535 James (305) 777-2235 374 Main Street 117 76534Blue Suede$50.00 125 11544Red Boots$100.00Note: replication 125 87633Mens Work Boots$45.00 157 76532Blue Suede$50.00 169 33421LCD-40 inch$1,000.00 169 99872Blu-Ray Player $400.00 178 22541Blue Jeans$12.00Note: replication188 33421LCD-40 inch$1,000.00 188 87634Mens Work Boots$45.00 201 11541Red Boots$100.00 52. 2nd SplitColumn depends on entire (whole) key.SalelLine(SaleID, ItemID, Quantity, Description, Price) ItemsSold(SaleID, ItemID, Quantity )Items(ItemID, Description, Price)SaleID ItemID Quantity ItemID DescriptionPrice 11711542 1154 Red Boots $100.00 11733421 2254 Blue Jeans $12.00 11776534 3342 LCD-40 inch$1,000.00 12511544 7653 Blue Suede $50.00 12587633 8763 Mens Work Boots $45.00 15776532 9987 Blu-Ray Player$400.00 16933421 16999872 17822541 18833421 53. 3rd SplitSaleForm2(SaleID, SaleDate, CustomerID, Phone, Name, Street)Sales(SaleID, SaleDate, CustomerID )SaleID SaleDate CustomerID SalespersonID1173/3/2012123458871254/4/2012875356631574/9/2012123455541695/6/2012295872551785/1/2012444536631885/8/201229587554Customers(CustomerID, Phone, Name, Address, City, State, ZipCode) CustomerID NamePhoneStreetCityAccountBalance12345 Jones (312) 555-1234 125 Elm StreetChicago$197.5428764 Adamz (602) 999-2539 938 Main Street Phoenix$526.7629587 Smitz (206) 676-7763 523 Oak StreetSeattle$353.7633352 Sanchez (303) 444-1352 999 Pine Street Denver $153.0044453 Kolke (303) 888-8876 909 West Avenue Denver $863.3987535 James (305) 777-2235 374 Main Street Miami$255.93 54. 3NF Tables 55. DBMS Input ScreenText/LabelsData Variables Command ButtonsRecord SelectorsScrolling Region/Subform- Subform- Main 56. DBMS Report Writer Report header Page header Break/Group header Detail Footers 57. Sample Reportwith Groups 58. Designing Menus for Users Main MenuCustomer Information 1. Setup ChoicesDaily Sales Reports 2. Data Input Friday Sales Meeting 3. Print ReportsMonthly Customer Letters 4. DOS Utilities 5. BackupsQuitAs a secretary, which menu is easier to understand? 59. Database Administration Database Administrator Testing Backup Recovery Standards Access Controls 60. E-Business Databases E-business is transaction-based Databases support multiple users andprotect transactions Modern websites are driven bydatabases 61. E-Business DatabasesSQLResults Database Server Web ServerWeb program scriptTextDataOrder FormDescriptionsPricesCustomer 62. Cloud Computing with DatabasesGoogle: BigTable, developed for internal storage AppEngine: http://code.google.com/appengine/ Best for complex documents/objects It is not SQL; no JOINs Three keys: Row, Column, Time/versionGeneric: Hadoop (Apache)Amazon:S3Files, particularly large media fileshttp://aws.amazon.com/s3/ SimpleDB Similar to BigTablehttp://aws.amazon.com/simpledb/ RDSRelational data serviceMySQL or Oracle 11ghttp://aws.amazon.com/rds/Microsoft: Azure SQL Serverhttp://www.microsoft.com/windowsazure/ 63. Cloud Database Service Benefits No fixed costs No hardware or software No maintenance Easy administration Pricing based on usage Monthly data storage (size) Monthly data transfer (usage) Scalable Multiple, distributed servers Multiple, high-speed Internet connections Reliable Distributed Run by experts Security monitoring 64. Cloud Database Limits Costs increase based on usage At some point, it might be cheaper tobuy and run everything yourself Not pay profit/overhead to third-party But be sure to measure all fixed costs 65. Cloud Database PricingExample: Amazon RDS (MySQL), U.S. East 1 Extra large instance 20 hours/day 20 GB/month at 50 million I/O per month 10 GB/month data transfer inAll values are estimates and 500 GB/month data transfer outmight not include all fees. 20 GB/month regional transfer => $616 per month ($7400/year)Example: Microsoft SQLAzure Business Edition 1 Extra large instance ($0.96/hour = $576/month) 20 GB/month ($200/month) 10 GB/month data transfer in ($1/month) 500 GB/month data transfer out ($75/month) => $852 per month ($10,224/year)You get a relatively large database with T1-level data transfer forless than 10 percent of the cost of a DBA. 66. Technology Toolbox: Building Forms inAccess1. Start the Form Wizard2. Select the SalesTable, all columns3. Customer Table: Phone4. ItemsSold, all columns except SaleID5. Product Table, Category6. Design View, rearrange, add Combo boxes 67. Quick Quiz: Forms in Access1. 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. 68. Technology Toolbox: Creating Database Reports1. Start the Report Wizard2. Select the columns for the report: CustomerID, Name, Phone, SaleDate, Description, Price, Quantity3. Choose a layout4. Click Summary Options to sum Quantity5. Use Design View to clean up the layout and format6. Add a text box for Value: =Price*Quantity7. Edit the Sums to use the same calculation 69. Quick Quiz: Creating a Report1. 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. 70. Cases: Pharmaceuticals Annual Revenue80706050 Pfizer Billion $40 GlaxoSmithKline30 Bristol-Myers Squibb20 Eli Lilly10 01994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 Net Income / Revenue 0.6 0.5 0.4 0.3 PfizerRatio GlaxoSmithKline 0.2 Bristol-Myers Squibb 0.1 Eli Lilly 01994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 -0.1 -0.2