infote boolean logic review

15
INFOTE M01 November 1, 2010

Upload: obettmd

Post on 14-Jul-2015

187 views

Category:

Education


1 download

TRANSCRIPT

INFOTE M01November 1, 2010

• “Although many of y0u will not choose a career in the design of databases, all of you will spend a large portion of your time – whatever job you choose – accessing data in a myriad of databases.” p. 170

• Accessing data from a database requires interrogating the database.

• In this short module, we shall review the concepts behind Boolean logic.

• But before we go on to a review of Boolean logic, let us review some fundamental data concepts.

• As an example, let us use a clinic database.• Whenever I see a patient, I gather information

regarding that patient, including his name, birth date, sex, age, address, the referring doctor.

• I may see a patient several times, and for each encounter, I gather information on the date of consult, the primary problem, the diagnosis, and the therapeutic plan.

• Each bit of information – e.g. last name, first name, birth date, consult date, diagnosis, etc. – is a field.

• Each field is a grouping of related characters. • A character is the most basic logical data

element. For example, the field “firstname” that contains “Robert” consists of the characters “R”, “o”, “b”, “e”, “r”, and “t”.

• My clinic database thus consists of an integrated collection of logically related data elements.

• Within my clinic database are included the patient file – which contains information regarding my patients - and the consults file – which contain information regarding my patient consults.

Data Concepts Definition Example

Character A single alphabetic, numeric, or other symbol. The most basic logical data element.

“R”

Field A grouping of related characters that consitutes a data item and represents an attribute of some entity.

*Entity = object, person, place, or event.A “consult” is an example of an event.

Field: FirstName = “Robert”

Record A grouping of all the fields pertaining to an individual entity

Patient record 001 pertains to patient “Robert Lee”

File A grouping of related records in tabular form (row-and-column)

Patient File = all patientsConsult Files = all consults

Database An integrated collection of logically related data elements

The Clinic Database contains the patient file, the consult file, and other files (ex. Doctor File, Medication File, Laboratory Results File)

• With a database management system in place, it would now be possible to interrogate the database to come up with the information I need.

• For example, let us say I wanted to come up with a list of patients who are male & are more than 50 years old, & have a diagnosis of diabetes or hypertension, but who do not live in Quezon City

• I can enter a query that in effect asks the database to – SELECT records – FROM the Patient File – WHERE sex=“Male” AND age>50 AND Diagnosis = (“Diabetes”

OR “Hypertension”) AND NOT Address = “Quezon City”

• The foregoing query conforms to the basic form o a Structured Query Language (SQL) query:– SELECT … FROM … WHERE

• It also makes use of the three logical operators in Boolean logic– AND– OR– AND NOT

• The logical operator “AND” means that the data element must appear in each set.

• For example, if the query asks what number is in Circle A AND in Circle B, then this would mean the numbers “5” and “6”.

The Venn Diagram above consists of 3 circles, labelled A, B, and C.In Circle A are the numbers 1, 2, 3, 4, 5 and 6In Circle B are the numbers 5, 6, 7, 8 and 9In Circle C are the numbers 4, 5, 9, 10, and 11)

• The logical operator “OR” means that the data element must appear in either set, but not necessarily both.

• For example, if the query asks what number is in Circle A OR in Circle B, then this would mean the numbers 1, 2, 3, 4, 5, 6, 7, 8, and 9.

The Venn Diagram above consists of 3 circles, labelled A, B, and C.In Circle A are the numbers 1, 2, 3, 4, 5 and 6In Circle B are the numbers 5, 6, 7, 8 and 9In Circle C are the numbers 4, 5, 9, 10, and 11)

• The logical operator “AND NOT” means that the data element must not appear in the set specificed.

• For example, if the query asks what number is in Circle A AND NOT in Circle B, then this would mean the numbers 1, 2, 3 and 4.

The Venn Diagram above consists of 3 circles, labelled A, B, and C.In Circle A are the numbers 1, 2, 3, 4, 5 and 6In Circle B are the numbers 5, 6, 7, 8 and 9In Circle C are the numbers 4, 5, 9, 10, and 11)

• Indicate the numbers that are specified by the queries (3 queries in each slide).

• When we reconvene in the chat room, be ready to volunteer to post your answer.

• You may volunteer by typing “RAISE query 1”, then “RAISE query 2”, etc. until “RAISE query 6”.

• Have fun!

12 3

5

6 78

9

1011

A B

C

1. A AND B AND C

2. A AND NOT C

3. A AND (B OR C)

4

From the slide set of the Asia Pacific Center for EBM, 2003

12 3

5

6 78

9

1011

MALE DIABETIC

>50 YRS OLD

4. (MALE AND DIABETIC) OR >50 yrs old

5. (MALE AND DIABETIC) AND NOT >50 yrs old

6. (DIABETIC AND >50 yrs old) AND NOT MALE

4

From the slide set of the Asia Pacific Center for EBM, 2003