creating databases for web applications n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Creating Databases for Web applications PowerPoint Presentation
Download Presentation
Creating Databases for Web applications

Loading in 2 Seconds...

play fullscreen
1 / 14

Creating Databases for Web applications - PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Creating Databases for Web applications' - elke


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
creating databases for web applications

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
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 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
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 between1
SELECT … BETWEEN
  • Assume table with fields that hold dates.
  • SELECT * FROM orders WHERE orderdate BETWEEN '7/20/08' AND '8/05/08’
classwork
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.
  • ?
slide7
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
Class work
  • What are uses for XML?
relational database
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.
slide10
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
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
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
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
Homework
  • Keep working on final projects.
  • Extra credit.
  • Post constructive feedback on other projects.