Download
1 / 14
140 likes | 233 Views
Creating Databases for Web applications. SQL (Classwork). XML with / vs Databases (Classwork) Homework: Keep working on projects. Post constructive feedback on other projects. SQL UNION. Assume a database with table for full-time faculty and another table for part-time faculty.
E N D
Creating Databases for Web applications SQL (Classwork). XML with / vs Databases (Classwork) Homework: Keep working on projects. Post constructive feedback on other projects.
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.
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…
SELECT …. BETWEEN • Can combine two tests in a WHERE or HAVING clause using the BETWEEN operator • SELECT sname, sgpa FROM students WHERE sgpa BETWEEN 2.0 and 3.0 • SELECT department, COUNT(*) as c FROM students GROUP BY department HAVING c BETWEEN 5 AND 10 • Alternative toSELECT department, COUNT(*) as c FROM students GROUP BY department HAVING c >= 5 AND c<=10.
SELECT … BETWEEN • Assume table with fields that hold dates. • SELECT * FROM orders WHERE orderdate BETWEEN '7/20/08' AND '8/05/08’
Classwork Consider tables: 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 2009. • ?
XML • Template for a structured form of data, namely tree: • nodes and child nodes • attributes • text content • Developers create and agree on structure • Intended for use by different applications • JavaScript, php, ActionScript, other languages have built-in methods. • Also 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.
Class work • What are uses for XML?
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 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.
So…. • You may come into a situation with this question already resolved. OR • 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?
XML in a database • One field in the table is suitable for XML data • Used XML in the grid design project • Used a long text datatype called BLOB • In this example, MySql doesn't know that this is XML. It just stores and returns the text.
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.
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 • Ajax • Cloud computing • ???
Homework • Keep working on final projects. • Extra credit. • Post constructive feedback on other projects.
