1 / 29

2mis6010-L Databases and Spreadsheets

2mis6010-L Databases and Spreadsheets. Lecturer Nishant Pithia. Week 5. Problem-solving and Revision for Database u sing Access Catch-up p ractical session for Week 1 – 4. Workshop 5 for MS Access - Report. Key Highlights from Previous Lectures. Stepwise database design Approach

sabin
Download Presentation

2mis6010-L Databases and Spreadsheets

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. 2mis6010-L Databases and Spreadsheets Lecturer Nishant Pithia

  2. Week 5 • Problem-solving and Revision forDatabase using Access • Catch-up practical session for Week 1 – 4. • Workshop 5 for MS Access - Report

  3. Key Highlights from Previous Lectures • Stepwise database design Approach • Simple Entity Relationship Diagram for database design • Entity; Its Attributes with data types • Making Assumption for relationship types • Constrains for Database Design • Entity Integrity (unique and non-null) • Referential Integrity (RI) and FK • Realise relationship in table view under the Constraints. ????? ?????

  4. Problems from Last Week • Queries last week: • Run query to Join tables for the order information of a particular customer (e.g. Anderson) • Run query to join multiple tables. • Problem: Retrieve no data

  5. Causes of the Problem • Causes: • Referential integrity is not enforced • Unmatched data types and properties between Primary Key (PK) and its corresponding Foreign Key (FK) • No value for FK Attributes/Fields, i.e. no matching values between tables • Use inappropriate matching fields between tables, i.e. even set non FK attribute as a matching field • Reasons: • Lack of full understanding of RI and FK

  6. Cause 1: Set Relationship without Enforcing Referential Integrity

  7. Run Query to Display Anderson’s Order

  8. Cause 1: Referential integrity is not enforced Where is Anderson???

  9. Benefit of Enforcing RI • Realize the relationship • Set Clear Matching Fields between tables • Automatically Check Matching Records for given relationship

  10. Cause: Unmatched data types between matching fields

  11. Cause 2: Unmatched data types between matching fields

  12. Enforced Referential integrity

  13. Run Query again ….Still No record!!!!!!!!?????

  14. Cause 3: Use inappropriate matching fields

  15. ONLY

  16. Now Query is working!!

  17. Implications and Reminders • Join Query joins tables by: • Finding the matching fields between tables based on FK and PK • Searching the matching values between the matching fields End of Problem-solving

  18. Recommended Approach for Assignment 1– Stage 1 Planning Stage 1: Planning on paper before entering data (i.e. Step 1 and 2 in Lecture 2) • Entity and Relationship mapping with clear assumptions and understanding of business cases. • Check some rules (fan shape connection?)

  19. Stage 1: Planning (cont’d) • Step 3 in the Lecture 2: • Plan for your tables view with PK and FK • Make sure: the table with PK in one end and the table with FK in many end Table with PK Table with FK

  20. Stage 2: From Paper to Access • Define each table (entity) with primary key, also foreign Key, other fields with their data types. • Make sure PK and FK have the same data types and properties

  21. Stage 2: From Paper to Access (Cont’d) • Create Relationship between the defined tables • Enforce the referential integrity

  22. Step 3 • Enter data to the table with PKin one end first, then to the table with FK in many end. • Avoid missing values • The values under the FK in a table must match those for PK in another table.

  23. Additional Notes for Assignment 1 • Tables with clear relationships • Interrelated tables with FK as the link • FK and corresponding PK have the same data types • Referential integrity is enforced • Query: make sure joining tables is working fine • Keep Form and Report simple and clear • Broad Reading for Relational Database

  24. Essential Reading • Colin Ritchie (2002), Relational Database Principle, 2nd Edition, Thomas Learning. • More books in Library

  25. Week 6 – 10 • Practical sessions on Ms Excel • Lectures by Request Reminders: • 7th December 2005 (Week 9) Deadline for DB Assignment!!! • Catch UP!! • Problems??? Drop in and see me!

More Related