Creating Databases for Web applications

Creating Databases for Web applications More SQL Classwork: Systems Logic Design. Diagrams. Homework: Present plan for project

General principles for SQL • You need to build the Select statement • pencil in hand, work in steps • Decide the tables needed • Decide on logical conditions for records contributing to the result • WHERE • Decide on conditions connecting tables • JOIN ON

General principles, cont. • The GROUP BY command combines / aggregates records based on common values, using aggregate operations such as COUNT, AVG, SUM,etc. • If you want to maintain individual records but bunch them together, use ORDER • Remember order high to low requires DESC

General principles, cont. • For condition in which individual records to use: WHERE • For condition on aggregated (GROUP BY) records: HAVING • DISTINCT will extract [just] one from a table for the specified field

Operational definition • … is what you call the definition of something that is used in a process. • For example: • freshmen, sophomores, juniors, seniors • Dean's list, academic probation • ???

Students in clubs • students: sid, sname, deptname, gpa, credits • Note: could have department table • Note: could have first and last names • clubs: clubid, clubname, clubdesc • clubmemberships: mid, sid, clubid

Generate a list of names of clubs • Just need one table SELECT clubname FROM clubs But what if I wanted clubs with members? • need the clubs table and the clubmemberships table • Join on cid

List of names of clubs with members SELECT DISTINCT clubname FROM clubs as c JOIN clubmemberships as m ON c.cid=m.cid • could write c.clubname, but not needed since there is no ambiguity. Don't worry about this.

List names of clubs with at least 5 members SELECT c.cid, c.clubname, COUNT(*) as n FROM clubs as c JOIN clubmemberships as m ON c.cid=m.cid GROUP BY c.cid HAVING n>=5 • This produces more information than requested. • Could probably leave out the c.cid and instead GROUP by clubname. This would be better if there is a chance of ambiguity.

List names of any club with at least one freshmen SELECT DISTINCT clubname FROM clubs as c JOIN clubmemberships as m ON c.cid=m.cid JOIN students as s ON m.sid=s.sid WHERE s.credits <30 • Creating a table using the clubmembership table with information added from other tables. • Records only go into the table if the students record has credits<30. • Use DISTINCT to get the distinct club names. Don't need other information.

Answer question • Sophomores, juniors, seniors • What about upperclassmen? • What about upperclassmen with at least B averages? • Consider BETWEEN operand

List names of any club with at least one freshmen SELECT DISTINCT clubname FROM clubs as c JOIN clubmemberships as m ON c.cid=m.cid JOIN students as s ON m.sid=s.sid WHERE s.credits <30 • Creating a table using the clubmembership table with information added from other tables. • Records only go into the table if the students record has credits<30. • Use DISTINCT to get the distinct club names. Don't need other information.

List all clubs by name with number of sophomores • Need to use all 3 tables • Need to use WHERE clause to get sophomores • Operational definition: sophomore means between 30 and 60 credits. • EXTRA CREDIT: BETWEEN inclusive or not? • ANSWER is yes: this means 30<= ??? <=60 • Use two comparisons if this isn’t what you want (or you forget about BETWEEN. • Use LEFT JOIN to pick up clubs with no matches since for this example, I want clubs with no sophomores to show up! • Use COUNT(m.id) to NOT count any NULL fields!

[should check use of ( ) ] SELECT clubname, COUNT(m.mid) as num from CLUBS as c LEFT JOIN (clubmemberships as m JOIN students as s ON m.sid = s.sid WHERE s.credits BETWEEN 30 AND 60) ON c.cid=m.cid GROUP BY clubname

Possible result • Young Dems 40 • Young Repubs 3 • Origami 10 • Hiking 0

Generate list of students with the clubs each one belongs to…

Planning • Will need SQL and then php • Need 3 tables (need club names and student names) • Need to place all records for each student together, but not aggregate. Use ORDER BY

SQL SELECT s.sid, s.sname as a,c.clubname as b FROM students as s JOIN clubmemberships as m ON s.sid=m.sid JOIN clubs as c ON m.cid=c.cid ORDER BY s.sid s.sname • Note: information just in the clubmembership table isn't part of the resultset BUT those records are the only ones represented in the resultset.

php … // assume $result is the result of query $curstudent=""; print("<table border='1'><tr><th>Student</th><th>Club</th></tr>"); while ($row=mysql_fetch_array($result)) { print("<tr><td>"); if ($curstudent!=$row['a']) { $curstudent=$row['a']; print("$curstudent</td>"); } else { print (" </td>"); } print("<td>".$row['b']."</td></tr>"); } print("</table>");

Misc. • Can order using multiple fields ….. ORDER BY lname, fname • WHERE or HAVING condition can use • IN …. WHERE clubname IN ('Hiking', 'Origami') • BETWEEN … WHERE gpa BETWEEN 3 AND 4 ALSO can use BETWEEN for dates and times NOTE: other ways to do these.

ROLLUP • ROLLUP can be used with GROUP to generate different levels of aggregation • students table: sid, sname,school,bos SELECT sname, COUNT(*) from students GROUP BY school, bos NSS Math/CS 5 NSS Biology 10 A&D Painting 17 A&D Sculpture 4

with ROLLUP SELECT sname, COUNT(*) from students GROUP BY school, bos WITH ROLLUP NSS Math/CS 5 NSS Biology 10 NSS null 15 A&D Painting 17 A&D Sculpture 4 A&D null 21 null null 36

Alternative to ROLLUP • is to do calculations and formatting in php or other middleware program. • May be easier to get exactly what you want. • Note: the MySQL way MAY be faster.

Planning • Planning (system design where design has broad meaning, not specifically the look) • comes first! • can change • Document the structure of database using ER diagram and the potential functions of the application using the DFD

Demonstrate • Draw tools in Powerpoint • Draw tools in Google presentation

Classwork • Work alone or in teams on systems design!

Homework • Proposals overdue!!!! • Presentations on Monday!

Creating Databases for Web applications