1 / 28

Chapter 7

Chapter 7. Multiple Tables. Two Tables FoxPro Chap. 7 Q.10. STAFF( staff _ id , name, shop_id ) 01 Chan TM mk 02 Wong ML cb 03 Leung DD wts 04 Chow YC mk 05 Fung YO cb SHOP( shop _ id , location) cb Causeway Bay mk Mong Kok wts Wong Tai Sin. Work Area.

kaia
Download Presentation

Chapter 7

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. Chapter 7 Multiple Tables Foxpro Chapter 7

  2. Two Tables FoxPro Chap. 7 Q.10 • STAFF(staff_id, name, shop_id) 01 Chan TM mk 02 Wong ML cb 03 Leung DD wts 04 Chow YC mk 05 Fung YO cb • SHOP(shop_id, location) cb Causeway Bay mk Mong Kok wts Wong Tai Sin Foxpro Chapter 7

  3. Work Area • If 2 or more tables are to be opened, 2 or more work areas are needed • 1 table uses 1 work area • work area are named 1, 2, 3, ... Foxpro Chapter 7

  4. Work Area 1 USE staff && in work area 1 USE shop && staff will be closed, shop opened in work area 1 LIST && records of shop are listed, but not those of staff USE staff && shop will be closed, staff opened in work area 1 LIST && records of staff are listed, but not those of shop ONE work area cannot hold TWO files Foxpro Chapter 7

  5. SELECT a Working Area SELECT 1 USE staff && staff is stored in area 1 SELECT 2 USE shop && shop is stored in area 2 LIST && records of shop are listed SELECT 1 LIST && records of staff are listed TWO files, ONE in each of the TWO working areas Foxpro Chapter 7

  6. Alias • Alias is used to refer to a work area using a meaningful or shorter name instead of numbers • If alias is not provided by the user, the name of the opened table becomes the alias for that work area Foxpro Chapter 7

  7. Alias SELECT 1 USE staff alias sta && default is alias staff SELECT 2 USE shop alias sh && default is alias shop SELECT sta && c.f. SELECT 1 SELECT sh && c.f. SELECT 2 It is difficult to remember which table is in working area 1 and which is in 2 Foxpro Chapter 7

  8. Access Data from Another Work Area • By using SELECT, we don’t need to close a file when we open another • Two files can be opened at the same time • However, only ONE table is currently selected • Use qualified field names to access ? Name, shop_id, sh->location Chan TM mk Causeway Bay • What’s Wrong? Does mk mean Causeway Bay? Foxpro Chapter 7

  9. Not Linked • STAFF(staff_id, name, shop_id) && currently selected 01 Chan TM mk • SHOP(shop_id, location) && not currently selected cb Causeway Bay ? Name, shop_id, sh->location Chan TM mk Causeway Bay ? Name, shop_id, sh->shop_id, sh->location Chan TM mk cb Causeway Bay Foxpro Chapter 7

  10. SET RELATION TO … INTO ... SELECT sh INDEX ON shop_id TO shop SELECT sta SET RELATION TO shop_id INTO sh ?Name, shop_id, sh->location Chan TM mk Mong Kok skip ?Name, shop_id, sh->location Wong ML cb Causeway Bay Foxpro Chapter 7

  11. Parent Table - Currently selected • STAFF(staff_id, name, shop_id) 01 Chan TM mk 02 Wong ML cb 03 Leung DD wts 04 Chow YC mk 05 Fung YO cb • Note the TWO entries of mk and cb (many) • STAFF and SHOP have common field shop_id Foxpro Chapter 7

  12. Child Table - not currently selected but linked to • SHOP(shop_id, location) cb Causeway Bay mk Mong Kok wts Wong Tai Sin • Note the uniqueness of cb, mk and wts (one) • Child table should be indexed according to the common key expression (shop_id) • Common key expression is usually the Primary Key of the child table Foxpro Chapter 7

  13. Points to Note Staff ---------> Shop Parent ---------> Child Currently Selected--------->not currently selected foreign Key = (usually) Primary Key MANY to ONE May not be sorted Sorted(indexed) • SELECT Parent • SET RELATION TO common key expression INTO child • If key expression in child is not unique, given 1st matching record Foxpro Chapter 7

  14. Common Errors • ONE ----> MANY, child NOT indexed (Wrong) USE one SET RELATION TO key INTO many • Cyclic Relation (not allowed) TableA ---> TableB TableB ---> TableA Foxpro Chapter 7

  15. Correct Relations MANY parent ONE child, indexed on key • This is correct (MANY ---> ONE, ONE indexed on key) USE one INDEX ON key TO one USE many SET RELATION TO key INTO one Foxpro Chapter 7

  16. After SET RELATION... • STAFF(staff_id, name, shop_id) fptr --> 01 Chan TM mk 02 Wong ML cb 03 Leung DD wts 04 Chow YC mk 05 Fung YO cb • SHOP(shop_id, location) cb Causeway Bay fptr --> mk Mong Kok wts Wong Tai Sin Foxpro Chapter 7

  17. SKIP • STAFF(staff_id, name, shop_id) 01 Chan TM mk fptr --> 02 Wong ML cb 03 Leung DD wts 04 Chow YC mk 05 Fung YO cb • SHOP(shop_id, location) fptr -->cb Causeway Bay mk Mong Kok wts Wong Tai Sin Foxpro Chapter 7

  18. Other Ways to Open Tables (1) SELECT 0 && lowest unused number=1 USE staff SELECT 0 && lowest unused number = 2 USE shop SELECT staff && work area 1 SELECT shop && work area 2 • Note the default alias names for work area 1 and 2 Foxpro Chapter 7

  19. Other Ways to Open Tables (2) USE staff in 0 && staff in work area 1 USE shop in 0 && shop in work area 2 • Note: currently selected table is staff SELECT 1 USE staff SELECT 2 USE shop • currently selected table is shop Foxpro Chapter 7

  20. Store RelationshipsUsing VIEW Files • The View Files (with extension .VUE) enables us to save the current environment settings, all opened tables and any relationships between them • CREATE VIEW infofilename To restore the environment • SET VIEW TO infofilename • The infofilename works like a Data Dictionary Foxpro Chapter 7

  21. Data Dictionary • A data dictionary contains information (meta-data) about the tables and their relationships • Meta-data = data about data To check or change the information about related files in a Data Dictionary • SET VIEW TO infofilename • SET VIEW ON Foxpro Chapter 7

  22. COPY STRUCTURE EXTENDED TO ... • Manually create field descriptions USE staff COPY STRUCTURE EXTENDED TO a:\staDict USE a:\staDict … • Create table from a data dictionary CREATE newstaff FROM a:\staDict Foxpro Chapter 7

  23. UPDATE • Update one table from another • Requires 2 tables open at the same time • The 2 tables should have a common key field, both indexed UPDATE ON key FROM fileA ; REPLACE field1 WITH eExpr && fileB->field2 Foxpro Chapter 7

  24. Limitations of UPDATE • Linked by key fields, not Key expressions • Inflexible, ‘FOR’ cannot be used • Can handle only TWO tables at one time • SET RELATION TO + REPLACE can do the same job Foxpro Chapter 7

  25. Three Tables • STUDENT(class, no, name) 7A 01 Chan 7A 02 Lee 7A 03 Cheung 7S 01 Wong 7S 02 Ho 7S 03 Au • SUBJECT(code, sub_name) ca Computer Applications ms Maths and Stat his History phy Physics Foxpro Chapter 7

  26. ONE <-- MANY ---> ONE • STUD_SUBJ(class, no, code) 7A 01 ca 7A 01 ms 7A 02 his 7A 03 ca 7S 01 ms 7S 02 phy 7S 03 ca 7S 03 ms STUDENT <-- STUD_SUBJ --> SUBJECT Foxpro Chapter 7

  27. How to Set Relations? • Select 1 • Use student alias stu • Index on class+no to student (first time) • Select 2 • Use subject alias sub • Index on code to subject (first time) • Select 3 • Use stud_subj alias ss • Set relation to class+no into stu, code into sub (one line!) && Set relation to class+no into stu && Set relation to code into sub (two lines not ok!) • List fields class, no, stu->name, sub->sub_name Foxpro Chapter 7

  28. Always from many to one • Select 1 • Use student alias stu && one 7A 01 • Set index to student • Select 2 • Use subject alias sub && one ca • Set index to subject • Select 3 • Use stud_subj alias ss && many 7A 01, many ca • Set relation to class+no into stu, code into sub (one line!) && Set relation to class+no into stu && Set relation to code into sub (two lines not ok!) • List fields class, no, stu->name, sub->sub_name Foxpro Chapter 7

More Related