Media Software Design DIG 3134

1. Media Software DesignDIG 3134 Lecture 12: Databases J. Michael Moshell University of Central Florida Original image* by Moshell et al . Imagery is fromWikimedia except where marked with *. Licensing is listed.

2. <<Yes, this lecture strongly resembles>> Information ManagementDIG 3563 Lecture xx: Databases J. Michael Moshell University of Central Florida -- But that was theory. Now you're Actually going to DO it -- Original image* by Moshell et al . Imagery is fromWikimedia except where marked with *. Licensing is listed.

3. Why is a Database not just a File System? • Data needs to be searchable • ... and sortable and mergeable • Dumb techniques (e. g. just using • 'flat files') lead to slow results.

4. Example: If I had 10,000 names and member numbers in a text file Like this (tab characters separate the columns) Adams Aeleron Baker Bowie Chester Cristie Doodle … 10232 31740 33221 49800 83230 33217 44802 … Eddie Avion Cookie Sharpie Charlie Roger Betty … And you wanted To be able to look Up a NAME From a MEMBER NUMBER …

5. You COULD do it this way: $textarray=file("memberlist.txt"); foreach ($textarray as $textline) { $textrow=explode("\t",$textline); $lastname=$textrow[0]; $firstname=$textrow[1]; $memnum=$textrow[2]; $LNlist[$memnum]=$lastname; $FNlist[$memnum]=$firstname; }

6. Result If I had 10,000 names and member numbers in a text file Like this (tab characters separate the columns) Adams Aeleron Baker Bowie Chester Cristie Doodle … 10232 31740 33221 49800 83230 33217 44802 … Eddie Avion Cookie Sharpie Charlie Roger Betty … Now you would have $LNlist[10232] Adams $LNlist[31740] Aeleron Etc .. $FNlist[10232] Eddie $FNlist[31740] Avion -6 -

7. Now you have two 'hash tables' Or associative arrays $memnum=33044; $whofirst=$FNlist[$memnum]; $wholast=$LNlist[$memnum]; Seems efficient, eh? But it took you A LONG TIME To read in 10,000 records from the text file, just To look up one firstname and one lastname. And – what if there were 100 million names in the table? (Credit card companies have that kind of problem.) -7 -

8. And you still can't look up a name! You built an array with member number index. If you needed to look up a name, you'd have to loop through the whole array. (You could have also built a "cross reference" array like this: (inside the reading loop) $key="$lastname,$firstname"; $MNlist[$key]=$memnum; and then look up member-numbers when needed.) -8 -

9. So: if flat-files are not the way to go, what is? Databases, of course. That's what this lecture is about. -9 -

10. A relational DB consists of tables: Database:”football"

11. A relational DB consists of tables: One record, or "tuple" (a ROW)

12. A relational DB consists of tables: One field, or "attribute"

13. A relational DB consists of tables: An attribute is selected from a DOMAIN (e.g. integers; strings; e-mail addresses..)

14. Queries: How we interact with a DB: Example Query: SELECT mascot FROM teams WHERE university=‘UCF’

15. Queries: How we interact with a DB: Example Query: SELECT mascot FROM teams WHERE university=‘UCF’ Knights Result: -15 -

16. WHAT we seek Example Query: SELECT mascot FROM teams WHERE university=‘UCF’ Result: Knights -16 -

17. WHICH TABLE it is in Example Query: SELECT mascot FROM teams WHERE university=‘UCF’ Result: Knights -17 -

18. WHICH ROW(s) do we want? Example Query: SELECT mascot FROM teams WHERE university=‘UCF’ Result: Knights -18 -

19. Practice with mySQL Queries: Example Query: SELECT mascot FROM teams WHERE university=‘UCF’ Task: Make a query to find mascot of Texas

20. Practice with mySQL Queries: Example Query: SELECT mascot FROM teams WHERE university=‘UCF’ Task: Make a query to find mascot of Texas Write on paper, show to your neighbor.

21. Practice with mySQL Queries: Result Query: SELECT mascot FROM teams WHERE university=‘Texas’ Task: Yah ... it’s simple (SO FAR)

22. Practice with mySQL Queries: Task: Make query: find university(ies) with Bulldog mascot. Write your query on paper, show to your neighbor. (Also write the result!) -22 -

23. Practice with mySQL Queries: Result Make query: find university(ies) with Bulldog mascot. select university from teams where mascot=‘Bulldogs’ But ... it will return MULTIPLE ROWS! <<This is GOOD>> -23 -

24. Practice with mySQL Queries: Task: Make query: find universities in Florida (you have 30 seconds) -24 -

25. Practice with mySQL Queries: Make query: find universities in Florida select university from teams where state=‘Florida’ Result: -25 -

26. Your next query practice 1) Think up a question, based on ONE of the tables. 2) Write it down in English. Example: ”What city is U. of Texas in?" 3) Hand it to your neighbor. 4) Write down the answer to the question you just received. 5) Check your neighbor's answer. LET ME KNOW when you have a correct answer.

27. More things queries can do: Example SELECT university, mascot FROM teams WHERE state=‘Florida’

28. More things queries can do: Example SELECT university, mascot FROM teams WHERE state=‘Florida’ Response: UCF, Knights Florida, Gators

29. More things queries can do: Example SELECT * FROM teams WHERE state=‘Texas’ Response: 3, Texas, Longhorns, Austin, Texas

30. More things queries can do: Example SELECT * FROM teams WHERE (state=‘Texas) OR (state=‘Mississippi’) Response: 3, Texas, Longhorns, Austin, Texas 6, Miss. State, Bulldogs, Starkville, Mississippi

31. More things queries can do: Example SELECT * FROM teams Response: Dumps the whole table

32. Joins: using two tables at once I want the name of the opponent of a game played 9/1/10 But: Part of the information is in the ‘games’ table and part of it is in the ‘teams’ table. How to JOIN them?

33. Joins: using two tables at once SELECT teams.university from teams, games WHERE games.date=‘9/1/10’ AND games.opponentnumber=teams.teamnumber

34. Joins: Practice Problems • Produce the names of opponents scoring more than 12 pts • Produce the mascots of teams who beat UCF • List the scores of teams from Florida • List the states with universities who lost by 10 points or more

35. For Homework (due Thurs) Whichever problems on the previous page you did NOT Do, you must prepare the answers for Thursday. We will do some more extensive practice problems and learn how to design tables.

36. Joins: using two tables at once Exam warning There WILL be questions about relational database tables and queries on the Final Exam! But more important, we will need them for Project IV as well.