ISMT E-120

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