Topics

1. Topics Quick overview on relational databases and XML. HW: [quick] research & post on SQL or XML. MATNME 3530.45

2. What is a data base? • A data base is organized information. A data base management system (DBMS) is a product that supports implementation & use of databases. • HOWEVER, generally, the term database is reserved for something using a standard relational DBMS product • DBMS is the product/tool: MySQL, Access, (Oracle, DBII, etc.) • The specific database, for example, the [toy] database examples for this course, are implemented using a particular DBMS MATNME 3530.45

3. Relational Database as distinct from: • Flat file • For example, will show php code used for the state capital quiz. This is an example of parallel structures: two arrays, one for the names of the states and one for the names of the capitals. • XML file • Linked lists • Hash table • ? MATNME 3530.45

4. A Database • …consists of tables • Tables hold records (= rows) • Records hold fields = attributes (= columns) • A relational database supports fields in a record that point to/link/refer to records in the same or other tables. • Database(s) most probably exist on campus • student table: major, gpa, address. • course table: section, instructor, time, location • enrollments: section & student, semester MATNME 3530.45

5. All together now • Database • Tables • Records • Fields • Relationships: fields that refer to records in the same or other tables. MATNME 3530.45

6. Database terminology • Primary key: field that uniquely defines a record. Often generated automatically by DBMS • foreign key: field in record in table that ‘is’/points to a record in another database • orderlist = table of orders. Each order includes as one of the fields a customer id. This customer made the order. MATNME 3530.45

7. Work up example • Information is: courses, sections, enrollments, teachers, schedule, more? • What are the tables? • What are records in each table? • NOTE: it takes time to design a database. MATNME 3530.45

8. Structured Query Language • Unlike much else in computing, databases follow standards. Everything said so far applies to MySQL, Access, Oracle, PostgreSQL etc. • SQL is … [a] structured query language. • SELECT question, answer, points FROM questions WHERE category = chosencategory • INSERT INTO customers VALUES (fname, lname, billing, email, pass) Syntax (format & punctuation) is tricky! MATNME 3530.45

9. MySQL • Open source dbms we will use with php. • (some actions) done using phpmyadmin • May do initial definition of tables • May use for debugging • did my php script put something in my database? • We (our php code) create(s) SQL to access / modify the database MATNME 3530.45

10. Players table player_id Player name Score lastplayed date ER diagram Question databank table question_id Question Answer (answer pattern) Value Category 0 history table question_id player_id whenplayed correct 0 MATNME 3530.45

11. ER diagram for on-line store Customer list Customer ID first name last name Billing information E-mail Password Product catalog Product id Product name Picture Cost 0 0 Order list Order ID Customer ID Date Status Total Ordered items Order ID Product Quantity MATNME 3530.45

12. Reprise: Web terminology: standard • Web files are stored on the server computer. • The browser (IE, Firefox, Opera, etc.) is on the client computer. • Hypertext Markup Language (HTML) files are requested by the browser from the server and interpreted by the browser. This could include display of image files, FLASH, etc. • Stateless system: server does not ‘remember’ anything between requests. MATNME 3530.45

13. but, stateless-ness wasn’t good enough • … to support real, practical applications involving • files and databases • state information—information valid across multiple pages • Need for so-called middleware / server-side • Alternatives were/are Common Gateway Interface (cgi) programming and Java applets. MATNME 3530.45

14. Three-tier implementation model • Code to be run on the client (by browser) • HTML and JavaScript • Code to be run on the server • php • Code (queries) executed by the DBMS • SQL queries constructed by php code MATNME 3530.45

15. Three tier logic model • Presentation • Business logic • Information MATNME 3530.45

16. extra credit opportunity Server-side / Middleware • Files (aka scripts) ‘in’ PHP (, ASP, cold fusion, ruby on rails, perl, etc.) are requested by browser. However, the server processes the PHP instructions in the files to • produce an HTML file for interpretation by the browser and • access & modify data (files, databases) on the server. NOTE: database(s) are on the server! • store & access so-called cookies on the client computer. Cookie is a special, small file. • HTML5 term: localStorage • Flash term: sharedstorage MATNME 3530.45

17. PHP • Personal Home Page  PHP: Hypertext Preprocessor • Language plus a set of built-in procedures and properties • language includes support for user-defined objects. • Open Source MATNME 3530.45

18. Warnings • SQL is a very powerful language. • It may take time to produce 1 SQL statement • The equivalent of many lines of code in another programming language. • Writing php code, including code generating SQL statements, can involve complex syntax • For example, single quotes within double quotes • References to variables within quoted strings. MATNME 3530.45

19. General format of SELECT SELECT [modifier such as DISTINCT] field1, field2, expression [COUNT(field)]FROM tablea, tableb, …WHERE condition(s) GROUP BY fieldxORDER BY fieldyHAVING conditionLIMIT n, m MATNME 3530.45

20. $sel="SELECT question_id, question, ans, value from questions "; $sel= $sel . " WHERE category= '" . $pickedcategory. "'"; $result=mysql_db_query($DBname, $sel, $link); $NoR=mysql_num_rows($result); Single quote within double quotes MATNME 3530.45

21. Confirmation on information so far • What is a database? • A database is made up of …. • Describe 3 tier model • operationally (implementation) in terms of [what] code • functionally (logic) in terms of what function MATNME 3530.45

22. continued… • What does browser do? What does it interpret? • What is done on/by the server? • What is done by the DBMS (MySQL)? MATNME 3530.45

23. XML • eXtended Markup Language • General term for anything with properly nested tags. • Elements defined by • opening tag, possibly containing attributes • contents of element • closing tag MATNME 3530.45

24. Example: XHTML • refers to proper HTML • What's not proper? • browser will accept HTML that is not well-nested, missing quotation marks, etc. • <p> This next is <b> <i> bold </b> </i> MATNME 3530.45

25. Example: SVG • scalar vector graphics • view-source:http://faculty.purchase.edu/jeanine.meyer/html5/html5logoscalesvg.html • Will do more of this later MATNME 3530.45

26. Example: anything … … that people agree to • RSS feeds • various Web services MATNME 3530.45

27. Note on XML • XML is all text. • Good news: pretty much human readable • Bad news: requires processing to use it. So, not appropriate for large amounts of data and/or data requiring speed. MATNME 3530.45

28. So what's the problem? Big Data (next topic) • refers to data that is (has) • volume • velocity • variability • Generally is not owned by one company/organization. • Not neatly organized and maintained in tables. • See previous slide: Too much for XML MATNME 3530.45

29. Homework • For this week's posting, find • unique source on limits on relational databases • XML example • comparison of XML and relational databases • any related topic • Big Data • READ the information and write summary in your own words!!! MATNME 3530.45