Creating Databases for Web applications

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.cidm.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.cidm.cid GROUP BY c.cid HAVING ngt5
• 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.cidm.cid JOIN students
  as s ON m.sids.sid WHERE s.credits lt30
• Creating a table using the clubmembership table
  with information added from other tables.
• Records only go into the table if the students
  record has creditslt30.
• 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.cidm.cid JOIN students
  as s ON m.sids.sid WHERE s.credits lt30
• Creating a table using the clubmembership table
  with information added from other tables.
• Records only go into the table if the students
  record has creditslt30.
• 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 30lt ??? lt60
• Use two comparisons if this isnt 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.cidm.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.sidm.sid JOIN clubs as c ON m.cidc.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("lttable border'1'gtlttrgtltthgtStudentlt/thgtltthgtC
  lublt/thgtlt/trgt")
• while (rowmysql_fetch_array(result))
• print("lttrgtlttdgt")
• if (curstudent!row'a')
• curstudentrow'a'
• print("curstudentlt/tdgt")
• else
• print ("nbsplt/tdgt")
• print("lttdgt".row'b'."lt/tdgtlt/trgt")
• print("lt/tablegt")

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
• AD Painting 17
• AD 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
• AD Painting 17
• AD Sculpture 4
• AD 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!