1 / 27

Building SQL Databases for Web Applications: A Comprehensive Overview

This guide explores the essential principles of SQL database design for web applications. It covers critical concepts such as the SELECT statement, table design, JOIN operations for connecting tables, and data aggregation using GROUP BY and HAVING clauses. Learn how to build effective queries to include conditions for individual records and groups, ensuring clarity and accuracy in results. Examples are provided for generating lists from clubs, students, and membership data, as well as operational definitions and planning for SQL usage in application development.

rue
Download Presentation

Building SQL Databases for Web Applications: A Comprehensive Overview

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

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

  5. 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 • ???

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

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

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

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

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

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

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

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

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

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

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

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

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

  19. 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 ("&nbsp;</td>"); } print("<td>".$row['b']."</td></tr>"); } print("</table>");

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

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

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

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

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

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

  26. Classwork • Work alone or in teams on systems design!

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

More Related