1 / 36

Media Software Design DIG 3134

Media Software Design DIG 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. <<Yes, this lecture strongly resembles>> Information Management DIG 3563.

juana
Download Presentation

Media Software Design DIG 3134

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related