Bài giảng Introduction to MIS - Chapter 4: Database Management Systems

Introduction to MISTechnology Toolbox: Creating Forms in AccessTechnology Toolbox: Creating Database ReportsCases: Pharmaceutical IndustryChapter 4Database Management SystemsJerry PostOutlineHow 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?How are databases used in cloud computing?Database Management SystemsDatabaseDBMSProgramsSales and transaction dataReports and ad hoc queriesCentral Role of DBMSDataDatabaseManagementSystemProgramProgramBusiness OperationsAd Hoc Queriesand ReportsProgrammerAnalystDatabase AdministratorBusiness NeedsPrograms& RevisionsManagers(Standards, Design, and Control)Data Collectionand TransactionProcessingCustomer TableSales TableRelational DatabasesTablesRowsColumnsPrimary keysData typesTextDates & timesNumbersObjectsCustomerIDNameAddressCity12345Jones125 ElmChicago28764Adamz938 MainPhoenix29587Smitz523 OakSeattle33352Sanchez999 PineDenver44453Kolke909 WestDenver87535James374 MainMiamiSaleIDCustomerIDDateSalesperson117123453/3/12887125875354/4/12663157123454/9/12554169295875/6/12255All Data FilesDatabase ManagementSystemInvoiceProgramBillingProgramDatabase AdvantagesFocus on dataStable dataPrograms change.Data independenceChange programs without altering data.Data integrityAccuracy.Time.Concurrency.Security.Ad hoc queriesSpeed of developmentReport writers.Input forms.Data manipulation.Flexibility & QueriesData Quality: Concurrent AccessTransaction ACustomer AccountsSanchez: BalanceTransaction B1) Receive 300 payment2) Read Balance (500)Sanchez: 5003) New Purchase (350)4) Read Balance (500)5) Subtract payment6) Store new results (200)Sanchez: 200Sanchez: 8507) Add purchase8) Store new result (850)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?Four questions to create a queryDatabase QueriesSingle TableComputationsJoining TablesSample Data: CustomersCustomerIDNamePhoneCityAccountBalance12345Jones312-555-1234Chicago197.5428764Adamz602-999-2539Phoenix526.7629587Smitz206-676-7763Seattle353.7633352Sanchez303-444-1352Denver153.0044453Kolke303-888-8876Denver863.3987535James305-777-2235Miami255.93Single Table Query IntroductionAccess Query Screen (grid)Query: List all of the customers.File: C04E15.mdbResults: Customer QueryQuery ConditionsWhich customers owe more than $200?Query ResultsQuery: ANDWhich customers from Denver owe more than $200?Query ResultsQuery: ORList customers from Denver or Chicago.Query ResultsQuery: SortingList customers from Denver or Chicago, sort the results.SQL General FormSELECT columnsFROM tablesJOIN link columnsWHERE conditionsGROUP BY columnORDER BY column (ASC | DESC)SQL IntroductionList all customers.SQL: SELECT * FROM CustomersSQL: AND ConditionSELECT Name, Phone, City, AccountBalance FROM CustomersWHERE (AccountBalance>200) AND (City=”Denver”)SQL: OR ConditionSELECT Customers.CustomerID, Customers.Name, Customers.Phone, Customers.City, Customers.AccountBalanceFROM CustomersWHERE (Customers.City = "Denver") OR (Customers.City = "Chicago")Common Query ConditionsOperatorMeaningExamples=EqualsCity=’Denver’Salary=60000Less thanGreater thanSalary 15000Not equalCity ‘Denver’BETWEENBetween x and ySaleDate BETWEEN ‘01-Jan-2012’ AND ‘28-Feb-2012’Sales BETWEEN 10000 AND 20000LIKESimple pattern matching % or * matches any characters _ or ? matches oneLastName LIKE ‘J%’ProductID LIKE ‘BL_ _DR _ _ _’NullMissing dataCity Is NullNOTNegationNot City=’Denver’Conditions: BETWEENList sales in June.Commonly used for date conditions:WHERE SaleDate BETWEEN ‘6/1/2012’ AND ‘6/30/2012’Query ResultsStandard Aggregation FunctionsSUM total value of itemsAVG average of valuesMIN minimum valueMAX maximum valueCOUNT number of rowsSTDEV standard deviationVAR variance of itemsSample Data: Sales AmountAmount$197.54$526.76$353.76$153.00$863.39$255.93Query: AggregationCountAvgSum6$391.73$2,350.38Aggregation Query in Access GridClick Totals ButtonTotals RowRow-by-Row ComputationsType formulaThen change row headingCategoryPriceEstCostElectronics$1,000.00700Electronics$50.0035SQL: AggregationSELECT Count(CustomerID) AS NCustomers, Avg(AccountBalance) AS AverageOwedFROM Customers How many customers are there and want is the average balance?SQL: Row-by-Row CalculationsSELECT Category, Price, 0.7*Price AS EstCostFROM ItemsWHERE (Category=”Electronics”) Estimate the cost of clothing items as 70 percent of the price.Subtotals: SQLSELECT City, Sum(AccountBalance) AS SumOfAccountBalanceFROM CustomersGROUP BY City CitySumOfAccountBalanceChicago$197.54Denver$1,016.39Miami$255.93Phoenix$526.76Seattle$353.76How much money is owed by customers in each city?Multiple TablesCID LastName Phone City AccountBalance12345 Jones 312-555-1234 Chicago $197.5428764 Adamz 602-999-2539 Phoenix $526.76Smitz 206-656-7763 Seattle $353.7633352 Sanchez 303-444-1352 Denver $153.0044453 Kolke 303-888-8876 Denver $863.3987535 James 305-777-2235 Miami $255.98CustomersSPID LastName DateHired Phone Commission255 West 5/23/05 213-333-2345 5452 Thomas 8/15/04 213-343-5553 3554 Jabbar 7/15/01 213-534-8876 4663 Bird 9/12/03 213-225-3335 4887 Johnson 2/2/02 213-887-6635 4ItemID Category Description Price1154 Shoes Red Boots $100.002254 Clothes Blue Jeans $12.003342 Electronics LCD-40 inch $1,000.007653 Shoes Blue Suede $50.008763 Clothes Mens’ Work Boots $45.009987 Electronics Blu-Ray Player $400.00SaleID CID SPID SaleDate117 12345 887 3/3/2012125 87535 663 4/4/2012157 12345 554 4/9/2012169 29587 255 5/5/2012178 44453 663 5/1/2012188 29587 554 5/8/2012201 12345 887 5/28/2012211 44453 255 6/9/2012213 44453 255 6/10/2012215 87535 887 6/9/201228764 663 5/27/2012285 28764 887 6/15/2012SaleID ItemID Quantity117 1154 2117 3342 1117 7653 4125 1154 4125 8763 3157 7653 2169 3342 1169 9987 5178 2254 1SalespeopleItemsSalesItemsSoldLinking TablesThe Sales to ItemsSold relationship enforces referential integrity.One Sale can list many ItemsSold.Query ExampleQBESELECT CustomerID, SaleDateFROM SalesWHERE SaleDate >= #6/1/2012# ;SQLResultsCustomerID SaleDate44453 6/9/201244453 6/10/201287535 6/9/201228764 6/15/2012Which customers (CustomerID) have placed orders since June 1, 2012?Query ExampleGridSELECT DISTINCT Customers.CustomerID, Name, SaleDateFROM Sales INNER JOIN Customers ON Sales.CustomerID = Customers.CustomerIDWHERE SaleDate >= #6/1/2012# ;SQLResultsCustomerID Name OrderDate28764 Adamz 6/15/201244453 Kolke 6/9/201244453 Kolke 6/10/201287535 James 6/9/2012What are the names of the customers who placed orders since June 1, 2012?QBESELECT DISTINCT Salespeople.Name, Customers.NameFROM Salespeople INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID=Sales.CustomerID) ON Salespeople.SalespersonID = Sales.SalespersonIDORDER BY Salespeople.Name, Customers.Name ; SQLResultsSalesName Cust.NameBird AdamzBird JamesBird KolkeJabbar JonesJabbar SmitzJohnson AdamzJohnson JamesJohnson JonesWest KolkeWest SmitzQuery ExampleList the salespeople (sorted alphabetically) along with the names of customers who placed orders with that salesperson.Multiple Tables, GROUP BY, WHEREWho are the top salespeople in June?Begin by listing the sales in June.Sales RowsSalespersonIDNameSaleDateQuantityPriceValue255West6/9/20122$1,000.00$2,000.00255West6/9/20125$50.00$250.00255West6/9/20121$12.00$12.00887Johnson6/9/20121$12.00$12.00887Johnson6/9/20121$50.00$50.00The quantity and price columns are shown temporarily to ensure the computation is specified correctly for the Value column.Subtotal in SQLSELECT Salespeople.SalespersonID, Salespeople.Name, Sum([Quantity]*[Price]) AS [Value]FROM Items INNER JOIN ((Salespeople INNER JOIN Sales ON Salespeople.SalespersonID = Sales.SalespersonID) INNER JOIN ItemsSold ON 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;Subtotals: Access Grid First AttemptWho are the top salespeople in June?Set the totals and set the Sum column.Incomplete. See resultsInitial ResultsSalespersonIDNameSaleDateValue255West6/9/2012$2,250.00255West6/10/2012$12.00887Johnson6/9/2012$62.00Salesperson #255 (West) shows up multiple times because of multiple days. GROUP BY Day, SalespersonGROUP BY Conditions: WHEREUse WHERE instead of GROUP BYBest Salesperson ResultsSalespersonIDNameValue255West$2,262.00887Johnson$62.00Converting Business Questions to QueriesSELECT FROM INNER JOIN WHERE GROUP BY ORDER BY What do you want to see?What constraints are you given?What tables are involved?How are the tables joined? 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 end—after verifying the rows. Look for “for each” or “by” to use GROUP BY for subtotals.Database Design: NormalizationNotationTable nameTable columnsCustomer (CustomerID, LastName, Phone, Street, City, AccountBalance)CustomerIDLast NamePhoneStreetCityAccountBalance12345Jones(312) 555-1234125 Elm StreetChicago$197.5428764Adamz(602) 999-2539938 Main StreetPhoenix$526.7629587Smitz(206) 676-7763523 Oak StreetSeattle$353.7633352Sanchez(303) 444-1352999 Pine StreetDenver$153.0044453Kolke(303) 888-8876909 West AvenueDenver$863.3987535James(305) 777-2235374 Main StreetMiami$255.93SaleIDSaleDateCustomerIDNamePhoneStreetItemIDQuantityDescriptionPrice1173/3/201212345Jones(312) 555-1234125 Elm Street11542Red Boots$100.001173/3/201212345Jones(312) 555-1234125 Elm Street33421LCD-40 inch$1,000.001173/3/201212345Jones(312) 555-1234125 Elm Street76534Blue Suede$50.001254/4/201287535James(305) 777-2235374 Main Street11544Red Boots$100.001254/4/201287535James(305) 777-2235374 Main Street87633Men's Work Boots$45.001574/9/201212345Jones(312) 555-1234125 Elm Street76532Blue Suede$50.001695/6/201229587Smitz(206) 676-7763523 Oak Street33421LCD-40 inch$1,000.001695/6/201229587Smitz(206) 676-7763523 Oak Street99872Blu-Ray Player$400.001785/1/201244453Kolke(303) 888-8876909 West Avenue22541Blue Jeans$12.001885/8/201229587Smitz(206) 676-7763523 Oak Street33421LCD-40 inch$1,000.001885/8/201229587Smitz(206) 676-7763523 Oak Street87634Men's Work Boots$45.002015/23/201212345Jones(312) 555-1234125 Elm Street11541Red Boots$100.001st: RepeatingSaleForm(SaleID, SaleDate, CustomerID, Phone, Name, Street, (ItemID, Quantity, Description, Price ) )Repeating SectionCauses duplicationFirst NormalSaleIDSaleDateCustomerIDNamePhoneStreetItemID, Quantity, Description, Price1173/3/201212345Jones(312) 555-1234125 Elm Street1154, 2, Red Boots, $100.003342, 1, LCD-40 inch, $1,000.007653, 4, Blue Suede, $50.001254/4/201287535James(305) 777-2235374 Main Street1154, 4, Red Boots, $100,008763, 3, Men’s Work Boots, $45.001574/9/201212345Jones(312) 555-1234125 Elm Street7653, 2, Blue Suede, $50.001695/6/201229587Smitz(206) 676-7763523 Oak Street3342, 1, LCD-40 inch, $1,000.009987, 2, Blu-Ray Player, $400.001785/1/201244453Kolke(303) 888-8876909 West Avenue2254, 1, Blue Jeans, $12.001885/8/201229587Smitz(206) 676-7763523 Oak Street3342, 1, LCD-40 inch, $1,000.008763, 1, Men’s Work Boots, $45.002015/23/201212345Jones(312) 555-1234125 Elm Street1154, 1, Red Boots, $100.00SalesForm with repeating dataNot in First Normal FormSaleIDItemIDQuantityDescriptionPrice11711542Red Boots$100.0011733421LCD-40 inch$1,000.0011776534Blue Suede$50.0012511544Red Boots$100.0012587633Men's Work Boots$45.0015776532Blue Suede$50.0016933421LCD-40 inch$1,000.0016999872Blu-Ray Player$400.0017822541Blue Jeans$12.0018833421LCD-40 inch$1,000.0018887634Men's Work Boots$45.0020111541Red Boots$100.001st: SplitSaleIDSaleDateCustomerIDNamePhoneStreet1173/3/201212345Jones(312) 555-1234125 Elm Street1173/3/201212345Jones(312) 555-1234125 Elm Street1173/3/201212345Jones(312) 555-1234125 Elm Street1254/4/201287535James(305) 777-2235374 Main Street1254/4/201287535James(305) 777-2235374 Main StreetSaleForm2(SaleID, SaleDate, CustomerID, Phone, Name, Street)SaleLine(SaleID, ItemID, Quantity, Description, Price)Note: replicationNote: replicationSaleForm(SaleID, SaleDate, CustomerID, Phone, Name, Street, (ItemID, Quantity, Description, Price ) )2nd SplitSalelLine(SaleID, ItemID, Quantity, Description, Price)ItemsSold(SaleID, ItemID, Quantity )Items(ItemID, Description, Price)Column depends on entire (whole) key.SaleIDItemIDQuantity11711542117334211177653412511544125876331577653216933421169998721782254118833421ItemIDDescriptionPrice1154Red Boots$100.002254Blue Jeans$12.003342LCD-40 inch$1,000.007653Blue Suede$50.008763Men's Work Boots$45.009987Blu-Ray Player$400.003rd SplitSales(SaleID, SaleDate, CustomerID )Customers(CustomerID, Phone, Name, Address, City, State, ZipCode)SaleForm2(SaleID, SaleDate, CustomerID, Phone, Name, Street)SaleIDSaleDateCustomerIDSalespersonID1173/3/2012123458871254/4/2012875356631574/9/2012123455541695/6/2012295872551785/1/2012444536631885/8/201229587554CustomerIDNamePhoneStreetCityAccountBalance12345Jones(312) 555-1234125 Elm StreetChicago$197.5428764Adamz(602) 999-2539938 Main StreetPhoenix$526.7629587Smitz(206) 676-7763523 Oak StreetSeattle$353.7633352Sanchez(303) 444-1352999 Pine StreetDenver$153.0044453Kolke(303) 888-8876909 West AvenueDenver$863.3987535James(305) 777-2235374 Main StreetMiami$255.933NF TablesDBMS Input ScreenText/LabelsData VariablesScrolling Region/SubformCommandButtonsRecord Selectors - Subform - MainDBMS Report WriterReport headerPage headerBreak/Group headerDetailFootersSample Report with GroupsDesigning Menus for Users1. Setup Choices2. Data Input3. Print Reports4. DOS Utilities5. BackupsMain MenuDaily Sales ReportsFriday Sales MeetingMonthly Customer LettersQuitCustomer InformationAs a secretary, which menu is easier to understand?Database AdministrationDatabase AdministratorTestingBackupRecoveryStandardsAccess ControlsE-Business DatabasesE-business is transaction-basedDatabases support multiple users and protect transactionsModern websites are driven by databasesE-Business DatabasesCustomerWeb ServerWeb program scriptTextDataOrder FormDescriptionsPricesDatabase ServerSQLResultsCloud Computing with DatabasesGoogle: BigTable, developed for internal storage AppEngine: Best for complex documents/objects It is not SQL; no JOINs Three keys: Row, Column, Time/versionGeneric: Hadoop (Apache)Amazon: S3 Files, particularly large media files SimpleDB Similar to BigTable RDS Relational data service MySQL or Oracle 11g Microsoft: Azure SQL Server Cloud Database Service BenefitsNo fixed costsNo hardware or softwareNo maintenanceEasy administrationPricing based on usageMonthly data storage (size)Monthly data transfer (usage)ScalableMultiple, distributed serversMultiple, high-speed Internet connectionsReliableDistributedRun by expertsSecurity monitoringCloud Database LimitsCosts increase based on usageAt some point, it might be cheaper to buy and run everything yourselfNot pay profit/overhead to third-partyBut be sure to measure all fixed costsCloud Database PricingExample: Amazon RDS (MySQL), U.S. East1 Extra large instance20 hours/day20 GB/month at 50 million I/O per month10 GB/month data transfer in500 GB/month data transfer out20 GB/month regional transfer=> $616 per month ($7400/year)Example: Microsoft SQLAzure Business Edition1 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)All values are estimates and might not include all fees.You get a relatively large database with T1-level data transfer for less than 10 percent of the cost of a DBA.Technology Toolbox: Building Forms in AccessStart the Form WizardSelect the SalesTable, all columnsCustomer Table: PhoneItemsSold, all columns except SaleIDProduct Table, CategoryDesign View, rearrange, add Combo boxesQuick 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.Technology Toolbox: Creating Database ReportsStart the Report WizardSelect the columns for the report: CustomerID, Name, Phone, SaleDate, Description, Price, QuantityChoose a layoutClick Summary Options to sum QuantityUse Design View to clean up the layout and formatAdd a text box for Value: =Price*QuantityEdit the Sums to use the same calculationQuick 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.Cases: Pharmaceuticals