Creating Databases

1. Creating Databases Confirm teams. Review of diagrams. SELECT problems.

2. Schedule • Next class: present idea for project, including • ER diagram • Data flow diagram • Extra credit opportunity: bulk populate tables • Use files? • Access websites? • Scheduled finals day: present final project

3. Final Presentations • Prepare 1-pager: • abstract • screen shot(s) • Show • (possibly modified) data flow diagram • (possibly modified) ER diagram • storyboard • Demonstrate • Show at least 1 interesting part of the code • May indicate steps to enhance project • Task for the audience

4. Data flow diagram • Who are the (distinct) agents, aka users • For example: customers, administrators • What are the processes, aka tasks • For example: authorize users, remove records…, view some selection, add information.. • What is the data store • Could simply list the tables • Note: may be data that isn’t in a table, • File • localStorage

5. Storyboard • Make a block for each script (aka file, aka program) • Can label your connect script as being included in every php script • Put in arrows from script to script • Invokes through a form or • Invokes with a hyperlink

6. Documentation • Each type of diagram focuses on specific issues. • Data Flow Diagram does not feature sequence of operations. • Entity Relationship features what is in each table and any relations (fields in one table pointing / representing another). • Storyboards feature implementation. • Organizations generally have standards for documentation!

7. 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.

8. 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…

9. 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.

10. SELECT … BETWEEN • Assume table with fields that hold dates. • SELECT * FROM orders WHERE orderdate BETWEEN '7/20/08' AND '8/05/08'; • Will this work for late July, early August? Need to research date and date/time 'arithmetic’.

11. Exercise/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. • ?

12. 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(*)

13. An example: citations The citations system provides visualizations of how articles reference other articles. The “archivists” input journals, articles and links from original article to referenced article. The “viewers” request a visualization. Note: this can be a base project for enhancement.

15. ER Articles id title symbol journal Links id Original article Referenced article Jo Journals id name …

16. Data flow diagram in words • Who are the agents? • Archivists (my term): those who put in the information (and potentially edit it, though I haven’t done that yet) • Viewers (my term): people looking at the links • What are the processes (aka task)? • Enter journals. Enter articles. Enter links. • View links • What are the data stores? • Journal table. Article table. Links table.

17. DFD archivist Enter journals Journal table Enter articles Article table Enter links Links table View links viewer

18. Decisions • Visualization consists of nodes (rounded corners of different colors) and arrows. • Ask archivists to make up short (3 character) symbols for each article. • Have program assign colors to journals. The node color is the journal color. • Viewer can click on node for more information • Article title and journal name.

19. Programs Go to Database projects and click on Journals, articles,… links http://faculty.purchase.edu/jeanine.meyer/db/examples.html

20. Reflection • Very preliminary • Such systems (probably) already exist • Want to add • Edit feature • Adjusting visualization by moving nodes • Data report: for example, number of in-links and out-links • Some action done client side, some server side • I had an arrow program from origami examples • Time to talk to my client (that is, my son)

21. Classwork / Homework • Form teams and decide on general idea and post proposal. • Prepare planning presentations for next class.