lecture 81 table search (1) 02art history 04biology 19chemistry 21civil engineering 24comp inf sys...

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)

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?

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

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

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.

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

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

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.

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.

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

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

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

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

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

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

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)

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