lecture 81 table search (1) 02art history 04biology 19chemistry 21civil engineering 24comp inf sys...
TRANSCRIPT
![Page 1: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/1.jpg)
lecture 8 1
Table Search (1)02 ART HISTORY04 BIOLOGY19 CHEMISTRY21 CIVIL ENGINEERING24 COMP INF SYS32 ECONOMICS39 FINANCE43 MANAGEMENT49 MARKETING54 STATISTICS
05 COURSES OCCURS 10 TIMES INDEXED BY ARR-SUB.
10 CALL-NUM PIC 99
10 SUBJECT PIC X(15)
![Page 2: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/2.jpg)
lecture 8 2
Table Search (2)
• What subject has value 39?
• Find a record in the table that matches a query– The search key is the entry value we search on
• What algorithm will find this record?
![Page 3: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/3.jpg)
lecture 8 3
Seqential Search
02 ART HISTORY
04 BIOLOGY
19 CHEMISTRY
21 CIVIL ENGINEERING
24 COMP INF SYS
32 ECONOMICS
39 FINANCE
43 MANAGEMENT
49 MARKETING
53 STATISTICS
39
1st try
2nd try
3rd try
4th try
5th try
6th try
7th tryFINANCE
Match
![Page 4: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/4.jpg)
lecture 8 4
Search Key : CALL-NUM
MOVE “N” ITEM-FOUND.
PERFORM
VARYING ARR-SUB FROM 1 BY 1
UNTIL ITEM-FOUND = “Y”
OR ARR-SUB = 10
IF CALL-NUM (ARR-SUB) = 39
MOVE “Y” TO ITEM-FOUND
END-PERFORM
![Page 5: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/5.jpg)
lecture 8 5
SEARCH Statement (1)
SET ARR-SUB TO 1.
SEARCH COURSES
AT END
MOVE “N” TO ITEM-FOUND
WHEN CALL-NUM (ARR-SUB) = 39
MOVE “Y” TO ITEM-FOUND.
![Page 6: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/6.jpg)
lecture 8 6
SEARCH Statement (2)
SEARCH ident-1 VARYING {ident-2 | index-name}{ AT END statement-1}{ WHEN cond-1 statement-2}
END-SEARCH.
• Sequential search starts at the entry denoted by the current index value• AT END is executed when the end of the table is reached• One or more WHEN clauses allowed
– When any of one them are satisfied the search ends
• VARYING clause names the index– May associate more than one index with the table– Uses the first index named in the INDEX BY clause if omitted
![Page 7: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/7.jpg)
lecture 8 7
Binary Search
02 ART HISTORY
04 BIOLOGY
19 CHEMISTRY
21 CIVIL ENGINEERING
24 COMP INF SYS
32 ECONOMICS
39 FINANCE
43 MANAGEMENT
49 MARKETING
53 STATISTICS
39
1st try
FINANCEMatch
2nd try
3rd try
![Page 8: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/8.jpg)
lecture 8 8
SEARCH Statement (3)
05 COURSES OCCURS 10 TIMES ASCENDING KEY IS CALL-NUM INDEXED BY ARR-SUB.
10 CALL-NUM PIC 9910 SUBJECT PIC X(15)
SEARCH ALL COURSESAT END
MOVE “N” TO ITEM-FOUNDWHEN CALL-NUM (ARR-SUB) = 39
MOVE “Y” TO ITEM-FOUND.
![Page 9: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/9.jpg)
lecture 8 9
SEARCH Statement (4)
L# table-name OCCURS integer TIMES
{ASCENDING/DESCENDING} KEY IS ident-1
INDEXED BY {index-1 …}
SEARCH ALL table-name
{ AT END statement-1}
{ WHEN statement-2}
END-SEARCH.
![Page 10: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/10.jpg)
lecture 8 10
SEARCH Statement (5)
• For Binary Search use SEARCH ALL
• The entries in the table should be sorted in ascending/descending order on the key field
• AT END is executed if no entry satisfies the condition
• The search ends if the WHEN clause is satisfied
![Page 11: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/11.jpg)
lecture 8 11
Binary Search : Worst Case
NUMBER OF ELEMENTS MAXIMUM NUMBER OF COMPARISONS
8 - 15
16 - 31
32 - 63
64 - 127
128 - 255
256 - 511
512 - 1023
1024 - 2047
2048 - 4095
(less than 24)
(less than 25)
(less than 26)
(less than 27)
(less than 28)
(less than 29)
(less than 210)
(less than 211)
(less than 212)
4
5
6
7
8
9
10
11
12
![Page 12: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/12.jpg)
lecture 8 12
Sort (1)
NAME YEAR MAJOR
Smith 1 Liberal artsJones 4 EngineeringAdams 3 BusinessHowe 2 Liberal artsFrank 1 EngineeringZev 4 BusinessBenjamin 4 BusinessGrauer 3 Liberal artsCrawford 2 EngineeringDeutsch 4 BusinessMakoske 1 Business
Unsorted Data
![Page 13: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/13.jpg)
lecture 8 13
Sort (2)Primary Key: Name (Ascending)
NAME YEAR MAJOR
Adams 3 BusinessBenjamin 4 Business Crawford 2 EngineeringDeutsch 4 BusinessFrank 1 Engineering Grauer 3 Liberal arts Howe 2 Liberal artsJones 4 EngineeringMakoske 1 BusinessSmith 1 Liberal artsZev 4 Business
Sorted Data, One Key
![Page 14: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/14.jpg)
lecture 8 14
Sort (3)
Sorted Data, Two Keys
Primary key: Year (descending)Secondary key: Name (ascending)
NAME YEAR MAJOR
Benjamin 4 Business Deutsch 4 BusinessJones 4 EngineeringZev 4 Business Adams 3 Business Grauer 3 Liberal arts Crawford 2 EngineeringHowe 2 Liberal artsFrank 1 Engineering Makoske 1 BusinessSmith 1 Liberal arts
![Page 15: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/15.jpg)
lecture 8 15
Sort (4)Primary Key: major (Ascending)Secondary Key: year (Descending)Tertiary Key: name (Ascending)
NAME YEAR MAJOR
Benjamin 4 Business Deutsch 4 BusinessZev 4 Business Adams 3 BusinessMakoske 1 Business Jones 4 Engineering Crawford 2 Engineering Frank 1 Engineering Grauer 3 Liberal arts Howe 2 Liberal artsSmith 1 Liberal arts
Sorted Data, Three Keys
![Page 16: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/16.jpg)
lecture 8 16
ASCII Table(space)
“ (quotation mark) $ (currency symbol)‘ (apostrophe) ( (left parenthesis) ) (right parenthesis) * (asterisk)+ (plus symbol) , (comma) - (hyphen, minus symbol) . (period, decimal point)/ (slash)
0 through 9; (semicolon)< (less than)= (equal sign)> (greater than)
A through Z (upper case)a through z (lower case)
![Page 17: Lecture 81 Table Search (1) 02ART HISTORY 04BIOLOGY 19CHEMISTRY 21CIVIL ENGINEERING 24COMP INF SYS 32ECONOMICS 39FINANCE 43MANAGEMENT 49MARKETING 54STATISTICS](https://reader035.vdocuments.us/reader035/viewer/2022071715/56649efc5503460f94c0f520/html5/thumbnails/17.jpg)
lecture 8 17
SORT : A Table
SORT table-name
{ ON ASCENDING/DESCENDING KEY ident-1… }
{ WITH DUPLICATES IN ORDER}.
• ASCENDING/DESCENDING may be defined at the table declaration– Key fields at the SORT statement override
• DUPLICATES clause order by table order