Transcript PowerPoint
Microsoft Access CS 110 Fall 2005 Review ERM: Entity-relationship model • What attributes do your entities have? • What type of relationship exists between entities? Normalization • Improving the form of a database so it reduces its size and propensity for errors Today Build DB from scratch Create “join” relationships Create queries Create parameter queries Forms Create two tables Accounts information Transactions information • Remember to define Primary Keys • Accounts.Account Number • Transactions.UniqueID Create a relationship There are many transactions for each account • Many-to-one • Really: zero, one, or many-to-one • Transactions.account_number -> Accounts.account_number Queries Basic, single-table query • To protect customers’ IDs create a table consisting of: Transactions.amount and Transactions.category Queries Refine this by limiting to those purchases between 0 and $100 Queries Refine this by limiting to transactions between 0 and $100 and spent on electronics Query using “join” Add the address field from the accounts table Query using “join” Limit the query to addresses in 94017 zip code • Like “*” & “94017” & “*” • Limit it to between 0 and $15 Note the names Account could come from the “many” side or from the “one” side • Can update the “many” side Creating new fields with queries Join the First_Name and Last_Name using a query • Why not make this an attribute of the Accounts table? • Full: [First_Name] & “ “ & [Last_Name] Creating new fields with queries USD to CAD conversion • 1 -> 1.17 • CAD: [Amount] * 1.17 Parameter queries Use user-specified variable values to control the results of a query • Place variable name in [brackets] Forms Creating a form in Design View • Do everything manually • Let Access do it for you Tutorial Info http://www.sfubusiness.ca/motmba/ courses/bus756/shared/pages/tutori als.html • Basic QBE • Calculated Fields • Forms • Parameter Queries • Forms