Introduction to MIS - Chapter 6: Database Management Systems

Introduction to MISChapter 6Database Management SystemsDatabase Management SystemsDatabaseDBMSProgramsSales and transaction dataReports and ad hoc queriesOutlineRelational DatabasesQueriesDesigning a DatabaseDatabase ApplicationsDatabase AdministrationDatabase and e-BusinessCases: AirlinesAppendix: Building Forms in AccessDBMS & PeopleDataDatabaseManagementSystemProgramProgramBusiness OperationsAd Hoc Queriesand ReportsProgrammerAnalystDatabase AdministratorBusiness NeedsPrograms& RevisionsManagers(Standards, Design, and Control)Data Collectionand TransactionProcessingPhone Name Address City312-555-1234 Jones 123 Main Chicago502-555-8876 Smith 456 Oak Glasgow602-555-9987 Juarez 887 Ribera Phoenix612-555-4325 Olsen 465 Thor MinneapolisCustomer TableCustomer Date Salesperson Total_sale502-555-8876 3/3/04 2223 157.92602-555-9987 4/4/04 8876 295.53612-555-4325 4/9/04 8876 132.94502-555-8876 5/7/04 3345 183.67Orders TableRelational DatabasesTablesRowsColumnsPrimary keysData typesTextDates & timesNumbersObjectsInvoiceProgramBillingProgramfile 1customersfile Afile BProgramming ApproachProblems with ProgrammingData and programsUndocumented filesFiles & many programsMultiuserChangesAll 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 & Queries1) 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?Four questions to create a queryDatabase QueriesSingle TableComputationsJoining TablesSingle Table Query IntroductionCID Name Phone City AccountBalance28764 Adamz 602-999-2539 Phoenix 197.5487535 James 305-777-2235 Miami 255.9344453 Kolke 303-888-8876 Denver 863.3929587 Smitz 206-676-7763 Seattle 353.76Sample DataAccess Query Screen (QBE)Query: Which customers have balances greater than $200?File: C05E15a.mdb“AND” Conditions and SortingSample DataAccess Query Screen (QBE)Query: Which Denver customers have balances greater than $200?CID Name Phone City AccountBalance28764 Adamz 602-999-2539 Phoenix 197.5487535 James 305-777-2235 Miami 255.9344453 Kolke 303-888-8876 Denver 863.3929587 Smitz 206-676-7763 Seattle 353.76SQL IntroductionQuery: 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 ;CID Name Phone City AccountBalance28764 Adamz 602-999-2539 Phoenix 197.5487535 James 305-777-2235 Miami 255.9344453 Kolke 303-888-8876 Denver 863.3929587 Smitz 206-676-7763 Seattle 353.76Useful WHERE ConditionsComparisons , , BETWEENNumbers AccountBalance > 200TextCommon Name > “Jones”LIKEMatch all Name LIKE “J*”Match one Name LIKE “?m*”Dates Odate between #8/15/95# and #8/31/95#Missing data City is NULLNOT Name is NOT NULLUse with QBE or SQLSQL General FormSELECT columnsFROM tablesJOIN link columnsWHERE conditionsGROUP BY columnORDER BY column (ASC | DESC)ComputationsSumAvgMinMaxCountStDevVarQBESELECT Count(C#), AVG(AccountBalance)FROM Customers ;SQLGroups or SubtotalsQBESELECT City, AVG(AccountBalance)FROM CustomersGROUP BY City ;SQLCity AVG(AccountBalance)Chicago 197.54Denver 863.39Miami 255.93Phoenix 526.76Seattle 353.76Sample OutputGroups with ConditionsQuery: What is the average account balance for customers from Denver?Multiple TablesCID Name Phone City AccountBalance12345 Jones 312-555-1234 Chicago $197.5428764 Adams 602-999-2539 Phoenix $526.7629587 Smitz 206-656-7763 Seattle $353.7644453 Kolke 303-888-8876 Denver $863.3987535 James 305-777-2235 Miami $255.98CustomersSID Name DateHired Phone Commission225 West 5/23/75 213-333-2345 5452 Zeke 8/15/94 213-343-5553 3554 Jabbar 7/15/91 213-534-8876 4663 Bird 9/12/93 213-225-3335 4887 Johnson 2/2/92 213-887-6635 4ItemID Description Price1154 Corn Broom $1.002254 Blue Jeans $12.003342 Paper Towels--3 rolls $1.007653 Laundry Detergent $2.008763 Men's Boots $15.009987 Candy Popcorn $0.50OrderID CID SID Odate Amount117 12345 887 3/3/2004 $57.92125 87535 663 4/4/2004 $123.54157 12345 554 4/9/2004 $297.89169 29587 255 5/5/2004 $89.93178 44453 663 5/1/2004 $154.89188 29587 554 5/8/2004 $325.46201 12345 887 5/28/2004 $193.58211 44453 255 6/9/2004 $201.39213 44453 255 6/9/2004 $154.15215 87535 887 6/9/2004 $563.27280 28764 663 5/27/2004 $255.32OID ItemID Quantity117 1154 2117 3342 1117 7653 4125 1154 4125 8763 3157 7653 2169 3342 1169 9987 5178 2254 1SalespeopleItemsOrdersItemsSoldLinking TablesThe Orders to ItemsSold relationship enforces referential integrity.One Order can list many ItemsSold.Query ExampleWhich customers (CID) have placed orders since June 1, 2004?QBESELECT CID, ODateFROM OrdersWHERE Odate >= #6/1/2004# ;SQLResultsCID ODate44453 6/9/200444453 6/9/200487535 6/9/200428764 6/27/2004Query ExampleWhat are the names of the customers who placed orders since June 1, 2004?QBESELECT DISTINCT Name, OdateFROM Orders INNER JOIN Customers ON Orders.CID = Customers.CIDWHERE Odate >= #6/1/2004# ;SQLResultsName OdateAdamz 6/27/2004James 6/9/2004Kolke 6/9/2004QBESELECT DISTINCT Salespeople.Name, Customers.NameFROM Salespeople INNER JOIN (Customers INNER JOIN Orders ON Customers.CID=Orders.CID) ON Salespeople.SID = Orders.SIDORDER BY Salespeople.Name ; SQLResultsSalesName Cust.NameBird AdamzBird JamesBird KolkeJabbar JonesJabbar SmitzJohnson JamesJohnson JonesWest KolkeWest SmitzQuery ExampleList the salespeople (sorted alphabetically) along with the names of customers who placed orders with that salesperson.Aggregation QueryWhat is the total amount of orders placed from customers who live in Miami?QBESELECT SUM(Amount)FROM Orders INNER JOIN Customers ON Orders.CID = Customers.CIDWHERE City = “Miami” ; SQLResults$2,418.84Database DesignPrimary keysOne value per cellColumn depends on whole key and nothing but the key.CustomersCID name city home business fax service11 Jones Chicago 111-1111 222-2222 222-35534 876-345622 Smith Chicago 111-4567 444-535333 James Chicago 111-2567 222-897644 Ricci Chicago 333-8765CID name city11 Jones Chicago22 Smith Chicago33 James Chicago44 Ricci ChicagoCustomers(CID, name, city)CID phone_type number11 home 111-111111 business 222-222211 fax 222-353411 service 876-345622 home 111-458722 service 444-535333 home 111-256744 fax 333-8765Phones(CID, phone_type, number)Database Design: NormalizationFile: C05Vid.mdbNotationTable namePrimary key is underlinedTable columnsCustomer (CustomerID, Phone, Name, Address, City, State, ZipCode)CustomerID Phone LastName FirstName Address City State ZipCode1 502-666-7777 Johnson Martha 125 Main Street Alvaton KY 421222 502-888-6464 Smith Jack 873 Elm Street Bowling Green KY 421013 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 421714 502-333-9494 Adams Samuel 746 Brown Drive Alvaton KY 421225 502-474-4746 Rabitz Victor 645 White Avenue Bowling Green KY 421026 615-373-4746 Steinmetz Susan 15 Speedway Drive Portland TN 371487 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 371488 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 370319 502-222-4351 Chavez Juan 673 Industry Blvd. Caneyville KY 4272110 502-444-2512 Rojo Maria 88 Main Street Cave City KY 421271st: RepeatingRentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) )Repeating SectionCauses duplicationTransID 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.501 4/18/04 3 Washington 502-777-7575 95 Easy Street 6 3 Clockwork Orange $1.502 4/30/04 7 Lasater 615-888-4474 67 S. Ray Drive 8 1 Hopscotch $1.502 4/30/04 7 Lasater 615-888-4474 67 S. Ray Drive 2 1 Apocalypse Now $2.002 4/30/04 7 Lasater 615-888-4474 67 S. Ray Drive 6 1 Clockwork Orange $1.503 4/18/04 8 Jones 615-452-1162 867 Lakeside Drive 9 1 Luggage Of The Gods $2.503 4/18/04 8 Jones 615-452-1162 867 Lakeside Drive 15 1 Fabulous Baker Boys $2.003 4/18/04 8 Jones 615-452-1162 867 Lakeside Drive 4 1 Boy And His Dog $2.504 4/18/04 3 Washington 502-777-7575 95 Easy Street 3 1 Blues Brothers $2.004 4/18/04 3 Washington 502-777-7575 95 Easy Street 8 1 Hopscotch $1.504 4/18/04 3 Washington 502-777-7575 95 Easy Street 13 1 Surf Nazis Must Die $2.504 4/18/04 3 Washington 502-777-7575 95 Easy Street 17 1 Witches of Eastwick $2.00First NormalCustomer RentalsNamePhoneAddressCityStateZipCodeVideoID Copy# Title Rent1. 6 1 Clockwork Orange 1.502. 8 2 Hopscotch 1.503. 4. 5. {Unused Space}Not in First Normal Form1st: SplitRentalForm(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 )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 421712 4/30/04 7 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 371483 4/18/04 8 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 370314 4/18/04 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171RentalForm2TransID VideoID Copy# Title Rent1 1 2 2001: A Space Odyssey $1.501 6 3 Clockwork Orange $1.502 8 1 Hopscotch $1.502 2 1 Apocalypse Now $2.002 6 1 Clockwork Orange $1.503 9 1 Luggage Of The Gods $2.503 15 1 Fabulous Baker Boys $2.003 4 1 Boy And His Dog $2.504 3 1 Blues Brothers $2.004 8 1 Hopscotch $1.504 13 1 Surf Nazis Must Die $2.504 17 1 Witches of Eastwick $2.00RentalLineNote: replicationNote: replication2nd SplitRentalLine(TransID, VideoID, Copy#, Title, Rent )VideosRented(TransID, VideoID, Copy# )Videos(VideoID, Title, Rent )TransID VideoID Copy#1 1 21 6 32 2 12 6 12 8 13 4 13 9 13 15 14 3 14 8 14 13 14 17 1VideoID Title Rent1 2001: A Space Odyssey $1.502 Apocalypse Now $2.003 Blues Brothers $2.004 Boy And His Dog $2.505 Brother From Another Planet $2.006 Clockwork Orange $1.507 Gods Must Be Crazy $2.008 Hopscotch $1.50Column depends on entire (whole) key.3rd SplitRentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode )Rentals(TransID, RentDate, CustomerID )Customers(CustomerID, Phone, Name, Address, City, State, ZipCode )TransID RentDate CustomerID1 4/18/04 32 4/30/04 73 4/18/04 84 4/18/04 3CustomerID Phone LastName FirstName Address City State ZipCode1 502-666-7777 Johnson Martha 125 Main Street Alvaton KY 421222 502-888-6464 Smith Jack 873 Elm Street Bowling Green KY 421013 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 421714 502-333-9494 Adams Samuel 746 Brown Drive Alvaton KY 421225 502-474-4746 Rabitz Victor 645 White Avenue Bowling Green KY 421026 615-373-4746 Steinmetz Susan 15 Speedway Drive Portland TN 371487 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 371488 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 370319 502-222-4351 Chavez Juan 673 Industry Blvd. Caneyville KY 4272110 502-444-2512 Rojo Maria 88 Main Street Cave City KY 42127RentalsCustomers3NF 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 ControlsTablesCustomers(C#, Name,Address)Products(P#, Description, Price)Sales(O#, P#, Sdate, Quantity, C#)Part 1 SalesC# P# Q Price Price*Q SubTotal11 22 1 15.95 15.95 15.9511 35 2 5.75 11.50 27.4531 18 1 25.95 25.95 53.40Part 2 ProductsP# Description Prices18 shorts 25.9522 shirt 15.9535 laces 4.75Part 3 CustomersC# Name11 Smith31 TorrezRetrieve the three tables (if they fit).1) Select by date2) Sort By O#, P#3) Look up prices4) Put into Part 15) Calculate total6) Sort for highest total7) Look up namesDatabase versus SpreadsheetStorage v calculationMultiple tablesDBMSSELECT Sum(Price*Quantity), C#,NameFROM Customers INNER JOIN (Sales INNER JOIN Products ON Sales.P# = Products.P#) ON Customers.C# = Sales.C#WHERE Sdate > Now() - 30GROUP BY C#ORDER BY Sum(Price*Quantity) DESC;DBMS versus SpreadsheetObjectsHypertext & Massive textPictures & GraphsObjectsVideoSoundUser definedSample OO DatabasePatient X-Rays/Images IDDateTechnicianCommentsPatient Data IDName Address DoB Medical HistoryphotoPatient VisitsID DatePhysicianProblems CommentsPatient TreatmentsID Date Procedure DoctorE-Business DatabasesE-business is transaction-basedDatabases support multiple users and protect transactionsModern websites are driven by databasesE-Business DatabasesInternetCustomerWeb ServerWeb program scriptTextOrder FormDescriptionsPricesPage requestQueriesand dataWeb pageCases: AirlinesCases: American AirlinesSouthwest AirlinesWhat 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?www.americanair.comwww.iflyswa.comAppendix: Building Forms in AccessBegin with the form wizard. Select everything from the Sale table.Selecting ColumnsSelect 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.Form Wizard: First PassThe wizard builds the Sale form with a sub form to enter multiple Items being sold. But it needs some work with layout.Form Design ViewIn 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.Lookup valuesCustomer tableCustomerID, LastName, FirstName, PhoneStore value in: CustomerIDForm Design: Sub Form and SubTotalEdit 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.Sale FormThe 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.