Databases

1. Databases Class 5 LBSC 690 Information Technology

2. Agenda • Questions • Databases • Relational database design • Implementation using Microsoft Access • Programming Concepts • The mythical person-month • Object oriented programming

3. Relational Databases • Tables represent relations • Name, project • Name, email address, phone number • Relations can be “joined” (**most important**) • Name, project, email address, phone number • Relations can be “projected” • Name, email address • Relations can be “restricted” • Name = “Doug Oard”

4. Why use Join? • Forces consistency • Doug Oard, project 18, oard@glue, 57590 • Doug Oard, project 22, oard@wam, 57590 • Limits the chance of error • Doug Oard, project 18, oard@glue, 57590 • Doug Oard, project 19, oard@glue, 57490 • Avoids lots of duplicated entry and updates • Can save a lot of storage space compared to a flat file

5. Problems with Joins • Data modeling for joins is complex • Taught in LBSC 670 • Joins are expensive to compute • Both in time and storage space • But it is joins that make databases “relational” • Projection and restriction also used in flat files

6. Key Fields • Primary Key uniquely identifies line to join • May group several fields to get a unique key • Social security number • First and last name • “Foreign” key must appear in the other table • But it need not be unique there • Join makes a new table • Line specified by foreign key is tacked on

7. Example of a Join on “Team” Name Team Team Name Team Project Project Chris A A Database Chris A Database Chris A B Web Chris Database A Camile A C Web Camile A Database Eileen B Eileen B Web Natalie C Natalie C Web David B David B Web Tonya C Tonya C Web Skip C Skip C Web

8. Keep Just Two Fields Team Team Name Project Name Project Chris A A Database Chris Database Chris A B Web Chris Database Camile A C Web Camile Database Eileen B Eileen Web Natalie C Natalie Web David B David Web Tonya C Tonya Web Skip C Skip Web

9. Restrict to Web Pages Team Team Name Project Name Project Chris A A Database Eileen Web Chris A B Web Natalie Web Camile A C Web David Web Eileen B Tonya Web Natalie C Skip Web David B Tonya C Michelle Skip C

10. How to Split Tables • The idea is to remove redundancy • For simple problems (like the homework) • Start with “binary relationships” • Pairs of fields that are related • Group together wherever possible • Add keys where necessary • For more complicated problems • Entity relationship modeling (LBSC 670)

11. FlightFinder Exercise • Design a database to match passengers with available flights on corporate jets • Companies phone in available seats • They want to know about interested passengers • Passengers call up looking for flights • They want to know about available flights • These things happen in no particular order

12. Exercise Goals • Identify the tables you will need • First decide what data you will save • What questions will be asked? • Then decide how to group it into tables • Start with binary relations if that helps • Design the queries • Using join, project and restrict • Add primary and foreign keys where needed

13. Exercise Logistics • Work in groups of 3 or 4 • Brainstorm data requirements for 5 minutes • Do customers care about the price? • Do companies care what passengers weigh? • Develop tables and queries for 15 minutes • Don’t get hung up on one thing too long • Compare you answers with another group • Should take about 5 minutes

14. One Possible Answer Tables: • Surely you didn’t expect this in the notes :) Flight: Flight Number, Origin, Destination, Departure Time, Arrival Time, Available Seats, Company Name, Price Passenger: Passenger Number, Name, Address, Phone Number Company: Company Name, Company Address, Company Phone Booking: Flight Number, Passenger Number Queries: Passenger calls: Join: Flight, Company Project: Departure Time, Company Phone Restrict: Origin, Destination, Available Seats>0 Company calls: Join: Flight, Passenger, Booking Project: Flight Number, Name, Phone Number Restrict: Company Name

15. Microsoft Access • Start Access • Icon in the Microsoft Office folder • Name your database planes.mdb in M:\ • Click “Table” and then “New” • Select “New Table” • Table wizards can be helpful (query wizards are not) • Enter the fields

16. Entering Fields • Field Names must be unique in a table • Select field type from a menu • Use date/time for times • Use text for phone numbers • Use right mouse button for primary key • Save the table when you’re done • That’s when you get to name it • Exit, then reselect to enter the data

17. Building Queries • Copy N:\share\class\flight.* to M:\ • Select “Queries” then “New” • Skip the query wizard • Choose two tables • Flight and Company • Pick each field you need using the menus • Unclick the X to “unproject” • Enter a criteria to “restrict” • Save, exit, and reselect to run the query

18. Fun Facts about Queries • Joins are automatic if field names are same • Otherwise, drag a line between the fields • Sort order is easy to specify • Use the menu • Queries form the basis for reports • Reports give good control over layout • Use the report wizard - the formats are complex

19. Other Things to Know • “Referential integrity” assures joins will work • Need to specify this when defining tables • Forms manage input better than raw tables • Invalid data can be identified when input • Graphics can be incorporated

20. Key Ideas • Databases are a good choice when you have • Lots of data • A problem that contains inherent relationships • Design before you implement • This is just another type of programming • The mythical person-month applies! • Join is the most important concept • Project and restrict just remove undesired stuff

21. Database Projects • Design for large amounts of data • Must use multiple tables • Need a plan for long-term maintenance • How will data be removed? • Attention to the user interface • Forms and reports • Web interfaces are hard • Enough content to verify proper operation

22. Software • Software models aspects of reality • Input and output represent the state of the world • Software describes how the two are related • Examples • Ballistic computations • Homework 6 • Alta Vista • Microsoft Word

23. Programming Languages • Used to specify every detail of the model • Special purpose • Able to specify an entire class of models • Spreadsheets (Excell, Quatro Pro, ...) • Databases (Access, Paradox, ...) • General purpose • Able to specify any possible model • Pascal, C, Java, ...

24. The Mythical Person-Month • If it would take one person three months, why does it take four people SIX months? • Four causes • It wouldn’t have taken 3 months anyhow! • Partitioning strategy • Training time • Communications effort

25. How long will it take? • Rules of thumb • 1/3 specification • 1/6 coding • 1/2 test planning, testing, and fixing! • Add time for coding to learn as you go, but don’t take time away from the other parts! • Reread the section on “gutless estimating” if you are tempted

26. Training Time • Simple Example • Full time person = 2,000 hours/year • Part time person = 288 hours per year • No training -> 7 part time people • With training -> 10 part time people • Learning the specification takes lots of time • Learning organizational “rules” takes longer

27. Communications • Sort of like continuous training • Who needs to know what I just learned? • Can be minimized by good partitioning • Limit the number of interfaces • Can be facilitated by computers • Asynchronous communication techniques • Email, BBS, voice mail

28. History of Programming • Machine code • Zeroes and Ones • Assembly language • “Assembler” changes names to machine code • High-level languages • “Compiler” translates math to machine code • Independent of machine “architecture” • FORTRAN, COBOL

29. History of Programming • Structured Programming • Group instructions into meaningful abstractions • C, Pascal • Object oriented programming • Group “data” and “methods” into “objects” • C++, Java

30. Object Models • Represent facts about the world as “data” • Combine data into “data structures” • Data structures model things • Represent actions using “operations” • Combine operations into “methods” • Methods model what can be done to things • “Classes” are data structures with methods • Classes model aspects of reality

31. A Simple Example • Data: Height, Weight, Shoe size • Data structure: All three together • Operations: Multiply, Divide • Method: Shoe size=4*Weight/Height • Class: Method & data structure

32. Instances • Classes actually model kinds of things • “person” in the example is a class • Objects are instances of a class • Object representing me is an instance of person • Object Oriented Programming • Define classes (data structures and methods) • Create objects • Perform some methods

33. Data Types • int • Like integers, but there is a biggest and smallest • float • Like real numbers, but there are a finite number • char • Any character in any language (UNICODE) • boolean • True or false

34. Data Structures • Arrays • Lists of some type of data • Every element must be the same type • Each is assigned a number (0, 1, 2, ...) • An array of type “char” is called a “string” • Structures • Groups of (possibly) different data types • Each must be given a name by the programmer

35. Operations • Same idea as methods • But applied to data types rather than structures • - int produces int • int + int produces int • int * float produces float • int < int produces boolean • char == char produces boolean

36. Making Methods • Actions can be operations or methods • Three ways to combine actions • Sequential (...; ...) • Conditional (if...then...else) • Loop (do...while, for ...) • Every possible model can be built this way!

37. Java Objects • Represent things in the real world • Those things may be fairly abstract, though • Encapsulate data and methods • Data are the facts you seek to model • Methods represent actions • Classes are types of objects • When instantiated, you get an individual object

38. Java Data Types • boolean • True or false • int • Like integers, but there is a biggest and smallest • float • Like real numbers, but there are a finite number • char • Any character in any language (UNICODE)

39. Java Data Structures • Array • A list of things • Every element must be the same type • Each is assigned a number (0, 1, 2, ...) • String • An array of characters • Methods are provided for printing, etc.

40. Java Operations • Things you can do to data • Examples: • int < int produces boolean • char == char produces boolean • int + int produces int • int * float produces float • - int produces int

41. Java Statements • Assignment • numberOfBirds = numberOfHawks + numberOfOrioles; • Call to a method • airForceOne = new Airplane(“747”); • Altitude = aifForceOne.readAltitude(); • Return a valule from a method • return numberOfBirds;

42. Combining Statements • Three ways to combine statements • Sequential (...; ...) • Separate each with a semicolon (statement1; statement2) • Group using braces ({statement1;statement2}) • Conditional (if ... then ... else or try ... catch ...) • Loop (do ... while, for ...) • Every possible model can be built with these 3!