Creating Databases for Web Applications

1. Creating Databases for Web Applications Go over midterm. Left join: motivation, example. Classwork/Homework: Confirm teams for enhancement projects. Make proposal. Do not track watching.

2. Join • Making a new table out of 2 or more tables. • Make new record by combining fields in 2 (or more) records based on the ON condition. • Generally, this is matching primary key with a foreign key. • Left Join: make the new table the same way BUT if no matches for a record in the “left” table (the one mentioned first), extend the record with NULL values. • Right Join: same, but adding one more record for each unmatched record in the “right” table (the one mentioned second)

3. Sample database • Assume 2 tablesSongssong_idsong_namesong_artist could be groupAwardsaward_idaward_namesong_id could have another nameaward_date

4. Join examples. Produce list of songs with any awards wonSELECT s.song_name, a.award_name FROM songs as s JOIN awards as a ON s.song_id = a.song_id • If a song has not won any awards, it does not appear on the resultset produced by this query. SELECT s.song_name, a.award_name FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id • All songs will be on this list. Some may have more than 1 row. If a song won just one award, it will have exactly one row. • Any that did not win any awards, will be present in exactly one row, with a NULL value for the a.awards_name. • The php program can detect the NULL value and produce a table with some indication, such as a zero, for the songs with only the row with NULL values.

5. Conditions • WHERE specifies a condition on each individual records. Only records satisfying the condition will be included. • ON specifies a condition relating the two tables. Records will be joined together only if condition is met, unless it is a LEFT or RIGHT JOIN. • HAVING is a condition on a GROUP. Example: if records are groups and the requested fields include a COUNT(*) as c, then the HAVING condition can refer to c.

6. DISTINCT, GROUP BY, ORDER BY • DISTINCT returns a resultset with the distinct values for a field • GROUP BY combines (aggregates) according to a field. It generally is used with one or more of the aggregate functions: COUNT, SUM, AVG. • COUNT(*) calculates number of rows. COUNT(fieldname) does NOT count any null values. • ORDER BY does not combine, but puts rows in order. • Standard order is low to high for numbers, alphabetical for strings. Add DESC to reverse this. • ORDER BY fieldname will put rows with the same fieldname together, but DOES NOT aggregate any rows. • It does make sense to use GROUP BY and ORDER BY together.

7. Example (made up data) Previous example • SELECT s.song_name, a.award_name FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id ”Hello” “Best Album” “Hello” “Best Record” “Lemonade” null • SELECT s.song_name, COUNT(a.award_name) FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id GROUP BY s.song_name “Hello” 2“Lemonade” 0 • SELECT s.song_name, COUNT(*) FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id GROUP BY s.song_name “Hello” 2“Lemonade” 1

8. Hints • Single table or more than one table. • More than one, need JOIN and an ON condition • Do we need records without matches, if yes, use LEFT JOIN or RIGHT JOIN or FULL JOIN • Are there conditions on individual records: use WHERE • Are we combining (aggregating) records: need one or more of the aggregate functions (COUNT, AVG, SUM) and need GROUP BY • Do we want to put records in order: use ORDER. • Not standard order, add DESC • Are there conditions on the groups? Use HAVING

9. Pop quiz • Give the SELECT query for all songs winning awards since January 1, 2015. Return the song_name, award_name, date. Order by date, oldest first. • You may look up or take a guess on how to handle dates. Give the online source. • Give the SELECT query for any song that has won at least 2 awards since January 1, 2015, giving the count of awards won. Order by number of awards, highest number first.

10. Do not track • You don’t have to give any information, but it is more interesting if you do. • Consider erasing cookies (any cached information) after doing this and periodically. • If given a choice on permitting geolocation, consider saying no OR just for the one time, each time. • Check settings for these permissions. • Note: you can “white list” a specific site.

11. Classwork / Homework • Finish the do_not_track watching and make posting. • You can comment on other postings. • Study sources, especially on SELECT. • Get in team and make proposal. • Look at the proposals already made. • Look at the sample php projects. • Work on enhancement project.