Web Database Programming Week 9

1. Web Database Programming Week 9 DB Administration – Multiple Tables

2. Table Relationships • One to many • Use foreign key at the Many side of the relationship • E.g. pets & species • Many to many • Use an extra relational table • This table contains foreign keys for Both sides of relationship • E.g. pets ownership table captures relationship between pets & owners

3. Display Table w/ Foreign Keys (FKs) • Need to join the table(s) containing FKs with the table(s) providing FKs • Must specify key relationships in join SELECT ownership.id, owners.lastname, pets.name FROM ownership, owners, pets WHERE ownership.ownerId = owners.id AND ownership.petId = pets.id

4. Rows Aggregation • GROUP BY clause • Rows with the same value will be grouped together • Enable the use of aggregation functions SELECT city from customer GROUP BY city;

5. Aggregation Functions • Count() • Sum() • Min() • Max() • Avg() SELECT city, COUNT(*) from customer GROUP BY city;

6. Example • Pet Ownership Administration: • Create an ownership • Update an ownership • Delete an ownership • Summary report • E.g. a table listing the number of pets owned by each owner

7. Additional SQL Criteria for WHERE Clause • Direct comparison: =, <>, <=, <, >, >= • Existence: IS NULL, IS NOT NULL • Between WHERE aNumber BETWEEN 234 AND 999 • Combining: AND, ORWHERE (LastName = ‘Smith’ AND FirstName = ‘Jack’) OR (LastName = ‘Jones’ AND FirstName = ‘Kim’) • Negation: WHERE NOT( LastName >= ‘Jones’) • Set membership: WHERE LastName In (‘Jones’, ‘Smith’,‘$formValue’) • Like: WHERE LastName LIKE ‘Johns[oe]n’WHERE LastName LIKE ‘Ben%’