1 / 33

Exploring Microsoft Access 2003

Exploring Microsoft Access 2003. Chapter 4 Proficiency: Relational Databases, External Data, Charts, Pivot, and the Switchboard. Objectives (1 of 2). Describe one-to-many relationships Create a one-to-many relationship Use the Get External Data command

Download Presentation

Exploring Microsoft Access 2003

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. Exploring Microsoft Access 2003 Chapter 4 Proficiency: Relational Databases, External Data, Charts, Pivot, and the Switchboard

  2. Objectives (1 of 2) • Describe one-to-many relationships • Create a one-to-many relationship • Use the Get External Data command to get data from Microsoft Office Excel • Create and modify a multiple-table selectquery.

  3. Objectives (2 of 2) • Use aggregate functions to create a totals query • Use Microsoft graph to create a chart based on a table or query • Use the Switchboard Manager to create and/or modify a switchboard

  4. Overview • Share data between Microsoft Office applications • Display data from two tables in one query • Total query aggregates results from groups of records to create summary information • Create Chart and Pivot • Create a user interface

  5. Multiple-Table Queries • One-to-many relationship • Primary key (PK) • Foreign key (FK) i) Consider two relation schemas R1 and R2; ii) The attributes in FK in R1 have the same domain(s) as the primary key attributes PK in R2; the attributes FK are said to reference or refer to the relation R2.

  6. Referential integrity • A value of FK in a tuple (record) t1 of the current state r(R1) either occurs as a value of PK for some tuple t2 in the current state r(R2) or is null. In the former case, we have t1[FK] = t2[PK], and we say that the tuple t1 references or refers to the tuple t2. FK Example: Employee(SSN, …, Dno) Dept(Dno, … )

  7. Get External Data • Get External Data command • Export command • Import Spreadsheet Wizard • Import Text Wizard • Importing versus linking

  8. Importing & Exporting Hands-On Exercise 1 • Open the Investment Database • Import Spreadsheet Wizard • Create the Relationship • Print the Relationship • Add the New Data • Create & Complete the Multiple-Table Query • Export the Query and Modified Tables • View the Excel Workbook

  9. Multiple Table Query Relationshipbetween tables Each field & table to display

  10. Total Queries A total query • Summary functions • Total row • Group By • Count function • Sum function

  11. Total Queries & Charts Hands-On Exercise 2 • Copy Assets Under Management Query • Create a Total Query • Check Your Progress • Start the Chart Wizard • Complete the Chart Wizard • Increase the Plot Area • Change the Data

  12. Total Query Run button Select Count from drop-down menu

  13. SELECT Consultants.Lastname, Count(Clients.LastName), Sum(Assets) FROM Consultants, Clients WHERE Consultant.ConsultantID = Clients.ConsultantID GROUP BY Consultants.Lastname

  14. SELECT Consultants.Lastname, Consultants.Status, Clients.Lastname, Clients.CountType, Clients.Assets FROM Consultants, Clients WHERE Consultant.ConsultantID = Clients.ConsultantID GROUP BY Consultants.Lastname

  15. 4 5 1 5 4 The results are grouped according to the last name of the consultants. The records with the same last name are in the same group.

  16. Referential Integrity Delete Record button Click + to display related records You cannot delete a Consultant without first deleting related Clients

  17. EMPLOYEE fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno DEPARTMENT Dname, dnumber, mgrssn, mgrstartdate Dnumber, dlocation PROJECT DEPT _LOCATIONS Pname, pnumber, plocation, dnum WORKS_ON Essn, pno, hours DEPENDENT Essn, dependentname, sex, bdate, relationship

  18. Updating and constraints • delete • Delete the WORK_ON tuple with Essn = ‘999887777’ and pno = 10. • When deleting, the referential constraint will be checked. • - The following deletion is not acceptable: • Delete the EMPLOYEE tuple with ssn = ‘999887777’ • - reject, cascade, modify (cascade update)

  19. ... ssn 123456789 ... delete delete Essn Pno 123456789 5 ... ... Cascade delete – a strategy to enforce referential integrity Employee Works-on

  20. Employee delete delete delete ... ... ... ... ssn ssn supervisor supervisor 234589710 234589710 123456789 123456789 ... ... ... ... null null 234589710 234589710 Cascade delete – a strategy to enforce referential integrity Employee not reasonable

  21. Modify – a strategy to enforce referential integrity ... ssn Employee 123456789 ... delete Works-on Works-on Essn Pno Essn Pno 5 null 123456789 5 ... ... ... ... This violates the entity constraint.

  22. ... ssn 123456789 ... Department Department ... ... Dno chairman Dno chairman null 5 123456789 5 ... ... Modify – a strategy to enforce referential integrity Employee delete This does not violate the entity constraint.

  23. Chart Wizard View button Modified Y and X axis now match the query data Chart from Wizard

  24. The User Interface • Switchboard • Switchboard Manager • Switchboard Items table

  25. Other Access Utilities • Convert Database command • Compact and Repair Database command

  26. Compact the Database View button Select the database Click compact

  27. The Switchboard Manager Hands-On Exercise 3 • Start the Switchboard Manager • Complete the Switchboard • Test the Switchboard • Insert the Clip Art • Complete the Design • The Completed Switchboard • Compact the Database

  28. Switchboard Manager Each button corresponds to a command

  29. Chapter 4 Summary (1 of 2) • One-to-many relationships • A query can display data from multipletables • Get External Data command • A total query performs calculations on • a group of records using summaryfunctions

  30. Chapter 4 Summary (2 of 2) • Switchboard Manager creates the userinterface (Switchboard) • Convert Database command changes anAccess 2000 file to a previous version • Compact and Repair Database command

  31. Practice with Access 1. The Client Master List 2. The HMO Database 3. Creating a Switchboard 4. The Look Ahead Databas 5. Linking Versus Importing 6. Pivot Tables 7. Pivot Charts

  32. Case Studies • Your First Consultant’s Job • The Wellness Center • The Database Wizard • Compacting Versus Compressing

More Related