1 / 41

ISMT E-120

ISMT E-120 . Desktop Applications for Managers. Introduction to Microsoft Access. Introduction to Microsoft Access. We’ll Cover the Minimum Relational Databases Access Objects Creating a Database Designing Databases. Introduction to Microsoft Access. The Minimum Relational Databases

kelvin
Download Presentation

ISMT E-120

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access

  2. Introduction to Microsoft Access • We’ll Cover the Minimum • Relational Databases • Access Objects • Creating a Database • Designing Databases

  3. Introduction to Microsoft Access • The Minimum • Relational Databases • Access Objects • Creating a Database • Designing Databases

  4. The Absolute Minimum • Data Contained in Tables • Tables have fields or columns • Tables have rows or records • Record is a row in a table, a set of fields • Table is made up of a set of records • Query > which fields from which rows • Sort and Group records

  5. The Minimum • MSQuery • connect to data source • choose from tables or queries saved in database • Access • drag-and-drop to write queries • Or write SQL

  6. Introduction to Microsoft Access • The Absolute Minimum • Relational Databases • Access Objects • Creating a Database • Designing Databases

  7. What’s “Relational”? • Simple Database = 1 Subject • Complex Database = Many Subjects • 1 Subject = 1 “Entity”  1 Table • “Relationships” Link Tables

  8. What’s an Entity? • Subject of a Database and table • Noun (person, place thing) • Important Topic • Something To Be Described

  9. Grade Database Instructors Classes Assignments Students Enrollments Results Music Collection Music genres Music mediums Artists Songs Instruments Examples of Entities

  10. What’s a Relationship? • A verb describes a relationship • They are linkages between entities • Types of relationships • one-to-one • one-to-many • many-to-many

  11. Examples of Relationships • Instructors Teach Classes • Students Enroll in Classes • Classes Require Assignments • Students Submit Assignments • Students Receive Results

  12. Introduction to Microsoft Access • The Minimum • Relational Databases • Access Database Objects • Creating a Database • Designing Databases

  13. Access Objects • Tables • Queries • Forms • Reports • Macros • Modules

  14. Tables • One Table, One Entity/Topic/Subject • Can Have Base or Reference Tables • Made up of Fields and Attributes • Fields May Have Many Properties • Table Must Have a Primary Key • Relationship = same key in more than one table to link them – Primary key in one table and foreign key in second table

  15. Queries • Types of Queries • Select - get data out • Append - put data in • Update - change data • Delete - delete data • SQL does the work

  16. Reports • Based on Table or Query • Format Query Results • One Query, Many Reports • One Report, Many Queries

  17. Forms • Based on Table or Query • Forms help users to: • View data • Insert data • Update data • Delete data • Customary User Interface

  18. Macros • Stored Sequence of Operations • Can be executed any time

  19. Modules • Specialized Software Program • Beyond the scope of ISMT E-120

  20. Introduction to Microsoft Access • The Minimum • Relational Databases • Access Objects • Creating a Database • Designing Databases

  21. Creating a Database • Define Tables and Keys • Begin with pencil and paper or a white board • Link Tables • Write Queries • data input, reports • Develop Forms • Write Reports

  22. Entities projects clients status reports This is highly simplified Tasks Milestones Relationships client sponsors project project accountable to client status report updates project project documented by status report Example: Project Tracking

  23. ID Name Client Service Code Start Date End Date Complete Manager Deliverables Core Technology Budget Billing Method Notes Project Entities

  24. Establishing Relationships • Tools  Relationships… • Normally Include All Tables • Relationship Properties • Relationships Carry Over to Queries

  25. Writing Queries • Choose Tables • Choose Fields • Sort • Filter with Criteria • Parameters

  26. Creating Forms • Based on Tables • walk through table one row at a time • Based on Queries • more powerful • update several tables at once

  27. Creating Reports • Based on Tables • Based on Queries • more powerful • link several tables at once • select fields • add calculations, etc.

  28. Introduction to Microsoft Access • The Minimum • Relational Databases • Access Objects • Creating a Database • Designing Databases

  29. Designing Databases • Understand the Application • Identify Data Elements • Normalize Data • Design the Interface

  30. Understand the Application • [Document Sequence of Steps] • [Map Information Flows] • [Identify Decision Points] • [View From Audience Perspectives]

  31. Identify Data Elements Might use: • Manual Input Forms • Reports • Memos

  32. Normalize Data • Crucial Design Process • Art and Science • Requires In-Depth Application Knowledge

  33. Benefits of Normalization • Easier to Maintain Information • Easier to Query Database • Extensible Design • Promotes Data Integrity

  34. What is Normalization? • Eliminating Redundancy • Grouping Data Items into Manageable Collections • Verifying Completeness of data

  35. Normalization: Step 1 • List Every Data Item in One Table • columns are fields • rows are “records” • 1 Cell = 1 Piece of Data • Add Duplicate Information as Needed • Find or Create a “Key” • field or combination of fields uniquely identifying each row (primary key) • First Normal Form

  36. Normalization: Step 2 • Which Keys Determine Which Fields? • “functional dependencies” • one key value determines one field value • Could Be > 1 Set of Keys • Break Into Separate Tables According to Functional Dependencies • Second Normal Form

  37. Normalization: Step 3 • Look for Functional Dependencies Among Non-Key Fields • Remove Any Dependent Non-Key Fields • Make Sure No Loss of Information • i.e., fields should be in some other table • Third Normal Form

  38. Normalization: Sanity Check • Make Sure No Information Lost • Make Sure Tables “Connect” • one-to-one • one-to-many • NOT many-to-many • Make Sure Keys are Unique • Referential Integrity

  39. ID Name Client Service Code Start Date End Date Complete Manager Deliverables Core Technology Budget Billing Method Notes Normalization: More Realism (project database)

  40. Multiple Clients per Project Multiple Deliverables per Project Multiple Tasks per Deliverable Status Reports Due Date Extensions & Changes Budget Extensions & Reductions Staff Assigned to Tasks, Deliverables Where Does It End? What Else Should We Track?

  41. Design Interface • The 4th part of database design • Designing the user interface • Beyond this Course

More Related