Advanced Database Design: Delimiters, Logical Operators, and Multi-table Queries Overview

1. Database Design 1 CMS 476 Dr. Karl Horak, Instructor

2. Session 3 • Week in Review • Application du Jour • Lecture: • Delimiters • Logical operators and expressions • Multiple table queries • Demonstration: AND, OR, NOT & Null • Demonstration: Relationships & Joins • Exercises

3. Week in Review In a word, frantic • RPT goes to Merry Old England • Web portal end-of-FY deadline • On-going portals

4. Lecture Topic A: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

5. 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, you get 0.0007257

6. 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

7. 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

8. 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#)

9. Lecture Topic B:Logical Operators and Expressions • Only two values in Boolean logic: • True & False • Yes & No • On & Off • 0 & -1* • But then there’s that pesky Null thingie * Clever trick #437: Often any non-zero value is treated as True

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

11. Venn Diagrams p = True Not(p) = True p = False NOT

12. AND p = True p and q q = True

13. OR p = True p or q q = True

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

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

16. Boolean Logic in the QBE SELECT [IT survey].Name FROM [IT survey] WHERE ([IT survey].Staff=False AND [IT survey].A<>False) OR ([IT survey].Staff=False AND [IT survey].B<>False);

17. 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 • Tests for Null values • Isnull() in expressions • ‘Is Null’ in WHERE clauses

18. 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

19. Lecture Topic C:Multi-table Queries—Joins • Observation #1—You can keep adding tables (and queries) to the QBE grid What is the percentage of Iranian enrichment capacity relative to the U.S?

20. Joining Tables • Observation #2—You can drag-and-drop lines connecting tables in the QBE grid What are the enrichment facilities in Western Europe?

21. Relationships • Observation #3—You can connect more than two tables in the QBE grid What are the enrichment facilities in Europe?

22. Key Fields • The “key” to joining tables is to have common information that references the same data in each table. • A “primary key” is a unique identifier for a record. • A “foreign key” is used as a look-up value and matches a primary key in another table.

23. Compound Keys • Keys may be made up of more than one field [Last Name] + [First Name] [State] + [City] • Keys may include expressions Left([Last Name],1)

24. Relationships • A “relationship” means that there are corresponding primary and foreign keys between tables. • Temporary relationships may be created “on the fly” in the QBE • Permanent relationships may be created with the Tools | Relationships

25. Joining Tables with Where Clauses* SELECT [EFAC data].[Plant Name/Location], MemberStates.IAEAregion FROM [EFAC data], MemberStates WHERE [EFAC data].Country = MemberStates.Country; * Access will automatically convert these into JOIN statements

26. Inner Joins SELECT [EFAC data].[Plant Name/Location], MemberStates.IAEAregion FROM [EFAC data] INNER JOIN MemberStates ON [EFAC data].Country = MemberStates.Country;

27. Outer Joins • May be either Left or Right Joins SELECT [EFAC data].[Plant Name/Location], MemberStates.Country FROM [EFAC data] LEFT JOIN MemberStates ON [EFAC data].Country = MemberStates.Country;

28. Use the Force, Luke Inner Join Left Join

29. Kick it up another notch NB: All arrows “point” in the same direction.

30. Gives you… SELECT [EFAC data].[Plant Name/Location], Continents.Continent FROM ([EFAC data] LEFT JOIN MemberStates ON [EFAC data].Country = MemberStates.Country) LEFT JOIN Continents ON MemberStates.IAEAregion = Continents.IAEAregion;

31. Next Week • Database Design & Relationships • Modeling Relationships • Entity-Relationship (ER) Diagrams • Normalization • 3rd Normal Form • Object-Role Modeling for perfect 3NF

32. Demonstration • Boolean expressions • Date logic • Relationships—Joins http://70.56.215.209/khorak/CSF/SQLExercises.mdb (Download new version—lots of new tables are included.)

33. 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?

34. 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 has a ‘K’ or ‘k’ in their name? • Provide a list of persons sorted by first name.

35. Still More Exercises • List employees and their department names and locations. • List all departments, including one without any current employees. • Draw a map of the five hypothetical countries showing which ones are adjacent. • In the Inventions table, write a query that will fill in the columns Year and Inventor from the original Scratch field (copied out of Wikipedia)