Creating Databases for web applications

1. Creating Databases for web applications [Complete presentations] More SQL Class time: discuss final projects. Do posting if you have not done it.

2. Relationships, etc. • Can specify relationships (e.g., foreign keys) and the DBMS will check and throw an error if not met. • My assumption: the php code does the checking so my examples generally don't set relationships. • Similarly, the DBMS checks for required fields and for unique primary keys (when key is not set by DBMS)

3. Database multi-user • DBMS are built to handle multiple users at a time! • Need rules to prevent updating of same field • Example: two people sharing a bank account make withdrawals at the same time. • Default can be to freeze access to database / table / record while transaction is in process. • LOCK/UNLOCK SQL commands • This is something to focus on when doing production systems!

4. Performance considerations • The CREATE table statement can specify one or more fields to be INDEX. This forces the DBMS to build an index (!) for improved speed. Trade off: more space and slightly more time when records added but shorter times during SELECT operations. • OPTIMIZE table. Assumption that tables become less efficient over time. Similar to disk defrag • EXPLAIN SELECT gives information on how this SELECT works. May lead you to revising the statement OR defining an INDEX (use ALTER table) • Other techniques…. • Some DBMSs have stored procedures…

5. INSERT Multiple formats • INSERT INTO tablename VALUES (all the values in order specified at create time) • Use 0 for an auto increment field(s) • INSERT INTO tablename (fieldnamea, fieldnameb, …) VALUES (fields corresponding to those field names) • Better include all required fields.

6. INSERT, continued • INSERT INTO tablename SET fieldaname=valuea, fieldbname=valueb, etc. • Better set all required values • INSERT INTO tablename1 (fieldaname, fieldbname, …) SELECT fa, fb, … FROM tablename2 WHERE … • Has to be same number of field names and types must match.

7. Select operators =, >, <, >=, <=, != or >< IS NOT NULL, IS NULL BETWEEN IN, NOT IN LIKE (has wild card character: %, others) REGEXP

8. DISTINCT SELECT DISTINCT category FROM questions; • Examines all the values in the category fields and returns a recordset of just those SELECT category, COUNT(*) FROM questions GROUP BY category • returns a recordset with a row for each category. Each row has 2 fields: category and a number representing the number of times each value of category occurred in the table.

9. Select aggregate functions • AVG, COUNT, MIN, MAX, STD, SUM SELECT AVG(score) in players; SELECT COUNT(*) in players WHERE score > 100; • Get these as 0th field, 0th row of recordset SELECT AVG(score), MIN(score), MAX(score), STD(score), COUNT(score) in players; • Get these as 0th, 1st, 2nd, 3rd, 4th, 5th fields of 0th row of recordset

10. Select control • grouping SELECT order_id, SUM(quantity) FROM ordereditems GROUP BY order_id; • limit: SELECT product_id, quantity FROM ordereditems LIMIT 10; • limit: starting from 1st record fitting conditions and returning 10 records SELECT product_name, product_description, product_cost FROM catalog LIMIT 1, 10; For paging, repeat with variables indicating 1st and last entries: "SELECT product_name, product_description, product_cost FROM catalog LIMIT $FIRST, 10"

11. SELECT order_id, SUM(quantity) FROM ordereditems GROUP BY order_id; Query result: • 11 • 9 Original data

12. Multiple tables Consider songs, featuresinsongs, features application. 1 This land is your land2 Where have …3 Thriller 1 1 12 2 13 3 2 1 Folk/political2 Michael Jackson This land is your land and Where have are each tagged as Folk/political. Thriller is tagged Michael Jackson.

13. Single table select SELECT sid, fid from featuresinsongs would produce 1 1 2 1 3 2 not too interesting, though might be good for debugging

14. Multiple tables conditions • List the name of the song with the name of the tag feature. One row for each tagging… • SELECT s.sname as a, f.fname as b FROM songs as s, featuresinsongs as t, features as f WHERE s.sid=t.sid AND t.fid=f.fid

15. Alternate • Use INNER JOIN and ON • Note: the , between tables is equivalent to INNER JOIN BUT • The way the operation is done is different and probably more efficient: this way doesn't create as many records. The WHERE way creates records for each row in one table with ALL rows of other table and then removes the ones not matching the WHERE conditions!

16. INNER JOIN ON • SELECT s.sname as a, f.fname as b FROM songs as s INNER JOIN featuresinsongs as t ON s.sid=t.sid INNER JOIN features as f ON t.fid=f.fid NOTE: can say simply JOIN and not INNER JOIN • SELECT s.sname as a, f.fname as b FROM songs as s JOIN featuresinsongs as t ON s.sid=t.sid JOIN features as f ON t.fid=f.fid

17. Results in all cases This land is your land Folk/political Where have … Folk/political Thriller Michael Jackson

18. UPDATE Change one or more fields in [certain] records in a table. Assume certain names for fields. • UPDATE products SET price=price*1.1 increases all prices by 10% • UPDATE products SET price=price*1.1 WHERE price <100increases all prices that were under 100

19. UPDATE (simple) To change a specific person's address. Assume that previous php obtained the pid field as $pidx and entered the new address as $naddress $query = "UPDATE people SET address='$naddress' WHERE pid=$pidx"

20. UPDATE The address probably came in separate fields, so the UPDATE would be $query = "UPDATE people SET address1='$naddress1', address2='$naddress2', state='$state', zip='$zip' WHERE pid=$pidx"

21. Project assignment • Design and develop your own database php project • work individually and then gather team to determine general idea • Make posting to moodle with idea and names of people on team • YOU MAY WORK BY YOURSELF. From more, more is expected. • Develop database design (ER diagram) and Data flow diagram • Presentations on 11/16 • Complete project • Presentations on 12/7