1 Data Modeling and Database DesignINF1343, Winter 2011 Data Modeling and Database Design Yuri Takhteyev University of Toronto This presentation is licensed under Creative Commons Attribution License, v To view a copy of this license, visit This presentation incorporates images from the Crystal Clear icon collection by Everaldo Coelho, available under LGPL from

2 Week 8 Relational Databases and Documents

3 (See handout on the website.)Assignment 2 (See handout on the website.)

4 PHPMyAdmin

5 (See last week's slides.)Web UI to MySQL (See last week's slides.)

6 Onto this week's topic: Documents

7 http://upload. wikimediapg

8

9 Digital can be digitally signed name: Barack Hussein Obama IIdate_of_birth: August 4, 1961 sex: male location: Honolulu mother: Stanley Ann Dunham father: Barack Hussein Obama hour_of_birth: 7:24 pm ... can be digitally signed

10 Full Text Search Can be useful. SQL support is coming.Matching sub-strings Can be done with LIKE Bag-of-words search Like doing LIKE for every term – very slow without a proper index! Natural language search Stop words (“the”, “of”, “to”, “end”) Stemming (“monkey” vs “monkeys”) Synonyms (“monkey” vs “simian” vs “Affe”) Ranking “tf-idf”, “Okapi BM25”, probabilistic models Can be useful. SQL support is coming.

11 http://commons.wikimedia.org/wiki/File:Invoice- Esso.jpg

12 order invoice payment Alice Bob

13 order payment Alice Bob invoice (HTML)

14 Alice Bob

15 machine-readable documentsAlice Bob order invoice payment machine-readable documents

16 XML Bills Microdevices Spring St. 413 Elgin 60123 IL Joes Office Supply Lakeshore Dr 32 W. Chicago 60022 Example.xml

17 XML 479.50 479.50 527.45 1 5 12.50 Pencils, box #2 red 2.50 ...

18 XML Anatomy 15 12.50 Pencils, box #2 red 2.50

19 XML Anatomy 15 12.50 Pencils, box #2 red 2.50

20 Validation Syntactic: form Semantic: meaning12.50 Semantic: meaning Male

21 Namespaces Bills Microdevices Spring St. 413 Elgin 60123 IL

22 XML and RDBMS Externalization: export, import, messaging Internal use:XML storage and retrieval (less common for now)

23 Alice Bob HTML application software (e.g., using PHP) database

24 Alice Bob database application software (e.g., using PHP) HTTP requestHTML HTML application software (e.g., using PHP) SQL database

25 Alice Bob database application software (e.g., using PHP) HTTP requestHTML HTML application software (e.g., using PHP) SQL database

26 Alice Bob database application software (e.g., using PHP) HTTP requestXML HTML application software (e.g., using PHP) SQL database

27 Alice Inc. Bob database application software (e.g., using PHP)HTTP request HTTP request XML HTML application software (e.g., using PHP) SQL “Web services”, “APIs”, “REST”, “Semantic web,” “Service-oriented architecture” database

28 Examples Twitter: http://twitter.com/public_timeline vs.

29

30

31

32

33

34 Examples Yahoo! Geocoding API:. %2C+Mississauga%2C+ON vs. appid=YD- 9G7bey8_JXxQP6rxl.fBFGgCdNjoDMACQA-- &street=3359+Mississauga+Rd+N&city=Mississauga &state=ON&country=Canada

35

36

47 YAML 1: name: Obi-Wan Kenobi occupation: Jedi Master species: Humangender: M homeworld: Stewjon size: 2 2: name: Yoda size: 1 3: name: Jabba occupation: crime lord species: Hutt homeworld: Nal Hutta size: 4 4: name: Chewbacca occupation: co-pilot species: Wookiee homeworld: Kashyyyk 5: name: Luke Skywalker occupation: Jedi Knight homeworld: Tatooine 6: name: Padmé Amidala occupation: queen gender: F homeworld: Naboo 7: name: Han Solo occupation: smuggler homeworld: Corellia 8: name: Jango Fett occupation: bounty hunter homeworld: Concord Dawn 9: name: Leia Organa occupation: princess homeworld: Alderaan 10: name: Admiral Akbar occupation: admiral species: Mon Calamari homeworld: Dac 11: name: R2-D2 occupation: astromech species: droid 12: name: C-3PO occupation: protocol droid homeworld: Affa 13: name: Jar Jar Binks occupation: senator species: Gungan 14: name: Chirpa occupation: tribal chief species: Ewok homeworld: Endor 15: name: Max Rebo occupation: musician species: Ortolan homeworld: Orto 16: name: Droopy McCool species: Kitonak 17: name: Sy Snootles occupation: singer 18: name: Anakin Skywalker type: humanoid sentient: 1 homeworld: Coruscant lifespan: 100 type: furry biped lifespan: 600 lifespan: 80 type: gastropod homeworld: Varl lifespan: 1000 homeworld: Kirdo III type: pachydermoid biped species: Tusken Raider species: Falumpaset type: large mammal sentient: 0 size: 3 species: Tauntaun type: reptomammal homeworld: Hoth species: Sarlacc size: 100 lifespan: 30000 species: Krayt Dragon type: reptile size: 45 species: Bantha lifespan: 90 species: Wampa world_name: Tatooine region: Outer Rim distance_to_core: 43000 coordinates: R-16 diameter: 11456 percent_water: 1 population: world_name: Coruscant region: Core Worlds distance_to_core: 10000 coordinates: L-9 diameter: 12240 percent_water: 29 population: world_name: Endor coordinates: H-16 diameter: 4900 percent_water: 8 population: world_name: Naboo region: Mid Rim distance_to_core: 34000 coordinates: O-17 diameter: 12120 percent_water: 85 population: world_name: Dac coordinates: U-6 diameter: 11030 percent_water: 90 population: world_name: Kashyyyk distance_to_core: 32000 coordinates: P-9 diameter: 12765 percent_water: 60 population: world_name: Nal Hutta coordinates: S-12 diameter: 12150 population: world_name: Alderaan distance_to_core: 5000 coordinates: M-10 diameter: 12500 world_name: Dagobah distance_to_core: 50200 coordinates: M-19 diameter: 8900 population: 0 world_name: Corellia distance_to_core: 7000 coordinates: M-11 diameter: 11000 percent_water: 75 world_name: Kamino distance_to_core: 70000 coordinates: S-15 diameter: 19270 percent_water: 100 population: world_name: Hoth distance_to_core: 50250 coordinates: K-18 diameter: 7200 world_name: Test

48 SQL Support in RDBMS Direct XML export mysql --xml -e "use starwars; select * from persona;" mysqldump --xml kenobio monkeys Direct XML import LOAD XML command in MySQL 5.5 (to be installed soon). Storing XML as a datatype Available in some systems, but not in MySQL currently. (Meanwhile, you can store XML a BLOB.)

49 Questions?