Creating Databases for Web applications

1. Creating Databases for Web applications SQL. XML. Linked Lists. NoSQL. Homework: Keep working on projects. Post constructive feedback on other projects.

2. SQL UNION • Assume a database with table for full-time faculty and another table for part-time faculty. • Reasons not to do this: some overlap of fields. • Reasons to do this: most processing involves just one or the other. Why have optional fields? Why waste space? • SQL UNION can be used when application calls for information from both tables.

3. SELECT UNION • SELECT column_name(s) FROM table_name1UNION SELECT column_name(s) FROM table_name2 • SELECT fname, start_date FROM faculty WHERE start_date < 2005 UNION adjname, start_date FROM adjuncts WHERE start_date < 2005 • NOTE: the start_date expression may be wrong…

4. SELECT …. BETWEEN • Can combine two tests using the BETWEEN operator • SELECT department, COUNT(*) as c FROM students GROUP BY department HAVING c BETWEEN 5 AND 10 • SELECT department, COUNT(*) as c FROM students GROUP BY department HAVING c >= 5 AND c<=10.

5. SELECT … BETWEEN • Assume table with fields that hold dates. • SELECT * FROM orders WHERE orderdate BETWEEN '7/20/08' AND '8/05/08'; • Need to research date and date/time 'arithmetic'

6. Classwork Consider customers (cid, cname, zipcode), products (pid, pname, price), orders (oid,cid,date), orderedproducts (opid, oid, pid, quantity) example. • Generate list of zipcodes with total number of orders. • Generate list of product names, with total ordered for each zipcode • Generate list of customers who had orders made in the summer of 2013. • ?

7. Hints • Decide on tables needed • Some fields in some tables, maybe even all the fields in some tables, may not be part of the result. They are needed to make up the JOINed table to obtain the results. • Generally, how tables are JOINed, the ON conditions is fixed • Decide on WHERE conditions . These are conditions on individual records. • Decide if any GROUP operations: any aggregations • NOTE: DISTINCT also does an aggregation • Decide if any HAVING conditions: conditions on aggregated results • Decide on any functions, e.g., COUNT(*)

8. XML • Template for a structured form of data, namely tree: • nodes and child nodes • attributes • text content • Note: some call this semi or partially structured • some elements may NOT have all attributes or child nodes • Developers create and agree on structure • Intended for use by different applications • JavaScript, php, other languages have built-in methods. • Human readable • Not particularly compact, nor speedy in terms of processing. • A [long] text field is read in and interpreted. • Generally, a copy of the XML file is generated and sent to be used by an application.

9. More… • Many technologies/standards around XML, including ways to specify definitions, do queries, etc. • Posting opportunity….

10. Question? • What are uses for XML?

11. Class work • Define XML for corporate 'family' tree • a company can have subsidiaries • decide on the information • in attributes • in element contents • in child elements

12. Linked lists Example • Information is parent and children • Data contained in one big array of arrays • Each component array is [name, index first child, index next sibling] • Use -1 to indicate no child or no next sibling.

13. My family • ignore in-laws…. • one parent for each child 0 Esther 1 -1 • Anne 3 2 • Jeanine 4 -1 • David -1 5 • Daniel 7 6 • Tom 9 8 • Aviva -1 -1 • Annika -1 -1 • Note: other orders would be valid

14. Your family • Leave out a parent and in-laws • OR come up with alternative • One possibility is to include all half/step siblings or • Another possibility is to add fields for siblings from the father versus siblings from the mother • OR ???

15. Contrast with Relational Database • Tables, records, fields with some fields indicating relationships, i.e., pointing to records using primary key as the foreign key • Commercial products and open source products (MySQL) store data efficiently, perform operations quickly, provide many functions. • DBMS also holds the data itself. One copy of information is maintained and accessed by the different applications.

16. Relational Database family • Two tables • People and relationships • Relationships have fields for two people PLUS label for relationship. • Allow multiple relationships.

17. My family: people table • Esther • Anne • Jeanine • Daniel • Aviva • David • Tommy • Debbie • Annika • Liam • Grant • Allison • …

18. My family: relationships • r_id id for Esther id for Jeanine mother • r_id id for Esther id for Anne mother • ….

19. Your family • Write out at least 5 family members and at least 5 relationships…

20. NoSQL • Alternatives to SQL • MongoDB • http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart

21. Key-value pairs • The data is a set of key-value pairs • The input values passed from a form to a php file • Associative array • Options to set up map using Google Maps API • They may or may not be unique, that is, a specific key may have more than one value

22. Parallel structures • Recall simple states capitals quiz • May have more than 2 arrays of corresponding data

23. So…. • You may come into a situation with this question already resolved. Data exists in some form already OR your teacher says use this… OR you need to decide • Parallel structures? Key-value? Does the tree structure suit the data? Or would tables be better? • Is the place of the data important or sending copies around okay, even appropriate • read-only most of the time? • ?

24. XML in a database • A field in the table is suitable for XML data • Can use a long text datatype called BLOB • MySql stores and returns the text.

25. MySQL XML functions These use a special language called XPATH to indicate how to interpret the XML. • EXTRACTVALUE • SELECT EXTRACTVALUE(fieldname,XPATH expression) FROM tablename • SELECT ExtractValue(xml_text,'/person[@id="3"]/firstname') as fname FROM my_table WHERE row_id=10 • The resultset reflects the results! • UPDATEXML • UPDATEXML(fieldname,XPATH expression indicating replacement in places where there are matches, replacement text) • UPDATE my_table SET xml_text = UpdateXML(xml_text,'//age','<age>30</age>') WHERE row_id=10 • These can be used in direct MySQL coding including coding to be saved as STORED PROCEDURE.

26. Extra credit opportunities NOT just a link. Read and report on what you learn. • expressions involving DATE, TIMESTAMP, etc. • stored procedures in general • MySql standalone coding • XML in MySQL • NoSQL or MongoDB • Hadoop • Ajax • Cloud computing • ???

27. Homework • Keep working on final projects. • Extra credit. • Post constructive feedback on other projects.