1 / 39

ISMT E-120

ISMT E-120 . The Influence of Software and Hardware Technologies on Business Productivity. Introduction to Microsoft Access & Relational Databases. Introduction to Microsoft Access & Relational Databases. The Basics – Database, Query & Entity Relational Databases Database Design

cheri
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 The Influence of Software and Hardware Technologies on Business Productivity Introduction to Microsoft Access & Relational Databases

  2. Introduction to Microsoft Access & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  3. Introduction to Microsoft Access & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  4. Database Basics • Database is made up of 1 or more tables containing data • Tables have fields, also called columns • Tables have rows, also called records • Record is a set of fields in a row

  5. Query Basics • Query means get data from which fields from which rows • Query Engine • Connects database to a data source • Allows us to choose from tables or queries saved in database • Access • Use a design grid to create queries • Drag-and-drop or write SQL

  6. Entity Basics • Key concept – types of info in the database • What are they? • Subject of the database • Subject of each table • Nouns (person, place, thing) • Important topics • Unique representation of a single real-world object

  7. Grade Database Instructors Classes Assignments Students Enrollments Results Grades Music Collection Database Music genres Music mediums Artists Song Titles Instruments Year recorded Examples of Entities

  8. Introduction to Microsoft Access & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  9. What is a RelationalDatabase? • A database is made up of tables • Relationships link tables together • Simple database = 1 subject • Complex database = many subjects • 1 subject = 1 entity per table

  10. Table Relationships • Use verbs to describe a relationship between tables/entities/subjects • Linkages • Types of relationships • one-to-one • one-to-many** • many-to-many **Most common and almost always best

  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 & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  13. Designing Databases • Understand the application you’re building • Identify data elements

  14. Design: Understand the Business Application • Document sequence of steps • Map information flows • Identify decision points • View from user or audience perspective

  15. Design: Identify Data Elements Might use: • Manual Input Forms • Reports • Interviews • General Knowledge

  16. Introduction to Microsoft Access & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  17. Entity Relationship Analysis • Identify distinct, relevant entities; name them and identify a primary key • Determine relationships between entities – describe them as verbs in whole sentences; create new entities as needed • Add appropriate attributes to entities; if an attribute has repeating values, create a new entity with a 1-to-many relationship

  18. Entity Relationship Analysis • Refer to ERA handout

  19. Introduction to Microsoft Access & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  20. Database Objects • Tables • Queries • Forms • Reports • Macros • Others • Build a database

  21. Tables • One Table, One Entity/Topic/Subject • Base or reference tables • Made up of fields/attributes • Must have a primary key • Same key in more than one table to link them – primary key in one table and foreign key in second table

  22. Queries • Types of Queries • Select - get data out (view, export …) • Append – add data to existing table • Update - change existing data • Delete - delete data • SQL does the work

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

  24. Forms • Customary user interface • Base on a table or query • Forms help users to: • View data • Insert data • Update data • Delete data

  25. Creating Forms • Based on tables • Walk through table one row at a time • Based on queries • More powerful • Manage data from several tables at once • Use form wizard or design palette

  26. Reports • Presents data in an easy to read, attractive manner • Can be based on a table or query • Format query results • One query can be used for multiple reports • One report can be made from multiple queries

  27. Creating Reports • Can be based on tables • Can be based on queries • More powerful • Link several tables at once • Select fields • Add calculations, etc.

  28. Macros • Stored Sequence of Operations • Can be executed any time • Easy to use interface to create them

  29. Introduction to Microsoft Access & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  30. Create An Access Database • What’s the topic/subject/entity of the database? • Define Tables and Keys

  31. Create An Access Database • Establish Relationships • Link All Tables • Database Tools  Relationships… • Relationship Properties • Relationships Carry Over to Queries

  32. Create An Access Database • Create queries for use with forms and reports • Develop forms • Write reports

  33. Introduction to Microsoft Access & Relational Databases • The Basics – Database, Query & Entity • Relational Databases • Database Design • Entity Relationship Analysis • Database Objects • Create a Database • Normalization

  34. What is Normalization? • Process to Eliminate Redundancy • Group Data Items into Manageable Collections • Verify Completeness of Data

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

  36. Normalization: Step 1 • List Every Data Item in One Table • Columns/fields • Rows/records • 1 Cell = 1 Piece of Data • Add Duplicate Information as Needed • Find or Create a Primary Key • A field or combination of fields uniquely identifying each row • This is First Normal Form

  37. Normalization: Step 2 • Which Keys Determine Which Fields? • Functional dependencies • One key value determines one field value • May be > 1 Set of Keys • Break Into Separate Tables According to Functional Dependencies • This is Second Normal Form

  38. 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

  39. 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

More Related