Database Design 1

Database Design 1 CMS 476 Dr. Karl Horak, Instructor

Session 2.5 • Delimiters • Logical operators and expressions • Demonstration: AND, OR, NOT • Exercises

Lecture Topic:Delimiters • Most compilers (and database engines) never allow variables to begin with a digit (or a minus sign). • Hence, anything starting with a digit is either: • An integer or • Floating point • Things beginning with characters can be all sorts of other stuff

Delimiters • Things with digits or +/- signs are always numeric. • Everything else needs to be characterized for the database engine. • [xyz] in Access indicates that xyz is a field name or a parameter (more on that later) • “xyz” and ‘xyz’ tell the system that xyz is a string of characters • #1/26/53# is how one indicates a date in Access • Otherwise, the database engine divides 1 by 26 by 53 and you get a mess • Actually, 0.0007257 is returned

String Delimiters • ‘Moby Dick’—a string • “Moby Dick”—a string • [Moby Dick]—the Moby Dick(!) field • but “Prisoner’s Dilemma” is needed for an embedded single apostrophe • ‘Prisoner’s Dilemma’ returns an error for unmatched delimiters • [First Name] will indicate a field name, parameter, or variable • Hint: Omit spaces in field names and [ ] are not needed

Dates and Times While we’re talking about dates: • ‘<’ (less than) is “before” • ‘>’ (greater than) is “after” Always remember and don’t ever forget: • Dates are stored as floating point numbers, usually based on the number of days since something like January 1, 1900 • Times are stored as decimal fractions of a day • 6:00 AM is 1/4 of a day, so its 0.25 • Some time functions are accurate to seconds • Other time functions are accurate to milliseconds

Intervals An interval has: • A start time AND • A finish time Example: #1/1/1714# >= Mydate <= #3/26/1837# or (#1/1/1714# >= Mydate) AND (Mydate <= #3/26/1837#)

Lecture Topic:Logical Operators and Expressions • Only two values in Boolean logic: • True & False • Yes & No • On & Off • But then there’s that pesky Null thingie

Boolean Operators • AND—True if both are true • OR—True if either or both are true • NOT—TrueFalse & FalseTrue • XOR—Exclusive OR; true if either one or the other but not both are true

Venn Diagrams p = True p and q q = True AND

NOT p = True Not(p) = True p = False

OR p = True p or q q = True

XOR p = True p xor q p xor q q = True What the heck is this gray area?

Some more examples p = True B? A? D? q = True C? r = True

Null • An unknown value • Not 0 • Neither true nor false • Not an empty string, “” • Not ASCII 0 (or \0) • Not /dev/null • Not a Nil pointer • Frankly, its not anything except Null • Almost any operation or function on Null will return a surprising and often incorrect value • Isnull() tests for Null values when all else fails

Operations on Null • A = Null is neither True nor False • A <> Null is neither True nor False • Null and False = False • Null or True = True

Demonstration • Boolean expressions • Date logic • Complex expressions http://70.56.215.209/khorak/CSF/BooleanExercises.mdb

Exercises and Q&A • List all the inventions created between 1835 and 1845 inclusive along with their inventors. • Which was invented first, the microscope or the thermometer? • Who invented the seed drill? (What the heck is a ‘seed drill’ anyway?) • Did DeGuerre (of DeGuerrotype fame) invent photography? • Claude Bernard is rarely credited with inventing what process with a more famous colleague?

More Exercises • List the names of those in IT who are also in Distribution Group A. • List the names of staff who are characterized as ‘B’. • List all those in ‘C’ or those who did not respond (neither in ‘A’, ‘B’, nor ‘C’). • Who are staff in ‘B’? • Who are in ‘A’ (staff or otherwise? • Who has a ‘K’ or ‘k’ in their name? • Provide a list of persons sorted by first name.

Database Design 1