ITEC 334 Fall 2010 Online (Business) Application Development

1. ITEC 334 Fall 2010Online (Business) Application Development Database Review Professor J. Alberto Espinosa

2. Agenda • Quick review of database design concepts • Quick review of SQL SELECT command for queries • Learn SQL DELETE, INSERT and UPDATE commands • Learn how to embed an SQL statement in a script statement

3. DBMS with Web Access (DBMS server + Web server):Very common when there are large numbers of users and would be impractical to deploy and install a DBSM client  access to the database is done through a browser (e.g., on-line purchases) e.g., Microsoft’s Internet Information Server (IIS) with Active Server Pages (ASP) + Microsoft’s SQL Server Request (ex. get a price quote, place an order) Response (ex. query results with HTML-formatted product price or order confirmation notice)

4. Database Design Review

5. Entity Integrity • Is ensuring that every record in each table in the database can be addressed (i.e., found) – this means that there each record has to have a unique identifier that is not duplicate or null (i.e., not blank) • Examples: every student has an AU ID; every purchase order has a unique number; every customer has an ID Primary key (PK) helps enforce Entity Integrity: • Field(s) that uniquely identifies a record in a table (e.g., AU user ID) • Entity integrity = PK is not duplicate & not blank • PK can be: • A single field (e.g., UserID), or • Or more than one field (e.g., OrderNo, LineItem)

6. Referential Integrity • Is ensuring that the data that is entered in one table is consistent with data in other tables • Examples: purchase orders can only be placed by valid customers; accounting transactions can only be posted to valid company accounts Foreign key (FK)  helps enforce referential Integrity: • A field in a table that is a PK in another table • That is, a field that “must” exist in another table • This is how referential integrity is maintained

7. Data Model Example (Entity Relationship Diagram--ERD): Course Registration System Cardinality 1 toMany Enrolls Includes Teaches Entities Relationships

8. Cardinality • Is an important database concept that helps understand how two entities (i.e., tables) relate to each other • Cardinality: describes how many records of one entity (i.e., table) can be associated with records from another table • Examples:1 student can only park 1 car 1 to 1 relationship1 professor can teach many courses 1 to many relationship 1 client can place many orders  1 to many relationship

9. Cardinality: 1 to 1 (MS Access notation)

10. Cardinality: 1 to many(MS Access notation)

11. Example:Course Registration System

12. Example:Package Delivery Tracking System

13. Example:Airline Reservation System

14. Update Rules What can be updated/modified in the database and when? • It is OK to update values in any non-PK fields, provided that referential integrity and business rules are respected • It is OK to update values in the PK in one table if it is not linked to a FK in another table, provided that entity integrity, referential integrity and business rules are respected • If a PK is linked to a FK in another table, we need to ensure that referential integrity is maintained. Depending on what makes sense for business and the data itself, you can either: • Disallow updates of values in the PK, or • Allow updates, but cascade the change – i.e., change the corresponding values the related FK

15. Delete Rules What can be deleted in the database and when? • It is OK to delete records in a table [only] if its PK is not linked to a FK in another table • If its PK is linked to a FK in another table, we need to ensure that referential integrity is maintained when we delete the record. Depending on what makes sense for business and the data itself, either: • Disallow deletion of records in the table that has the PK • Allow deletions of records but cascade deletions – i.e., delete the corresponding records in the related table that contain the FK

16. Important decisions to make when designing a web database • Where do you implement entity integrity, referential integrity, relationships, update rules, delete rules, etc.? • Two choices: • In the database design itself – but then if your program is allowing bad data to be submitted to the database you will get cryptic error messages. • In the script itself – but then if your program doesn’t enforce these good database design principles you will end up with bad data • Decisions, decisions, decisions – the correct answer depends on your programming orientation and experience • I personally favor 1 because it is best to prevent bad data to be entered into the database.

17. SQL Queries Review

18. Useful SQL Commands for Scripting • You will need to know how to write SQL commands to (1) display; (2) delete; (3) enter; and (4) modify data • SELECT:Display existing records • DELETE: Delete records • INSERT:Enter new records • UPDATE: Modify existing records

19. SQL SELECTCommand: Displaying Data SELECTcolumn1, column2, etc.– columns you want to display FROM table1, table2, etc. – tables that contain the data WHERE condition1 – which records (i.e., rows) to retrieve AND condition2 etc – you can combine more than one condition using AND or OR ORDER BY field1, field2, etc. – to sort the query results SELECT can be followed by:DISTINCT (eliminates duplicate rows from result)TOP x (lists only the top x rows of result, e.g. TOP 5)* (lists all columns in the table)

20. “Simple”SELECT Queries:When all the data is available in a single table and there is no need to aggregate data Example: list the product ID, product name, product type and price from the Products table, for items that are price at $300 or more: SELECT ProdID, ProdName, Type, Price FROM Products WHERE Price>=300; Click here to download this database [local copy]

21. SQL Queries With Aggregate Functions • These queries yield a single number result(i.e., a table with 1 column and 1 row) • The only thing you can include in the SELECT line are the fields you are aggregating • Aggregate functions you can use: Avg, Sum, Min, Max, Count • These functions aggregate vertically a column of (usually numeric) values (e.g., salaries, payment amounts, etc.) • Note: the AS clause is optional; it does not change the query results; it only changes the column label in the results • SELECT Avg(Price) AS AvgPrice • FROM Products • WHERE Price>=120 • AND Type=“Percussion”; • Note: you can use more than one aggregate function in one SELECT command • SELECT Max(Price) AS MaxPrice, Avg(Price) AS AvgPrice • FROM Products • WHERE Type=“Guitars”; • Note: the Count function counts how many rows meet the Where criteria, so it you can use any column you wish to count and you will get the sameresults – the easiest thing is to use Count(*) • SELECTCount(*) as TotOrders • FROMOrders • WHERE OrderStatus = “Top Priority”

22. SQL Queries With Aggregate Functions and Grouping • The ONLY things you can include in the SELECT line are:(1) the fields you are aggregating [e.g., Avg(Price)](2) and the fields you are using to group [e.g, Type] SELECT Type, Avg(Price) AS AvgPrice, Max(Price) AS MaxPrice FROM Products WHERE Price>=1000 GROUP BY Type SELECT Type, Avg(Price) AS AvgPrice, Max(Price) as MaxPrice FROM Products GROUP BY Type HAVING Avg(Price)>1000 • Note: the WHERE clause is evaluated BEFORE the grouping • Note: the HAVING clause is evaluated AFTER the grouping

23. “Complex”SELECT (“Join”) QueriesPractical rule: you should query the minimum number of tables possible to get the data you need. If you need to get the data from more than one table, you MUST JOIN the tables: Tables:Orders (OrderNo, ClientID, OrderDate, OrderStatus) LineItems (OrderNo, LineItem, ProdID, Qty) Table Join(2 ways): SELECTOrders.OrderNo, ClientID, LineItem, ProdID, Qty FROM Orders, LineItems WHERE Orders.OrderNo = LineItems.OrderNo; Join Condition Table Product(This query is wrong – NEVER OMIT THE JOIN CONDITION!!) : SELECTOrders.OrderNo, ClientID, LineItem, ProdID, Qty FROM Orders, LineItems;

24. Complex SELECT Queries with Joins: TIPS COMPLEX queries that JOIN 2 tables are identical to SIMPLE queries, except for 2 additional rules you MUST ALWAYS apply: • The two tables need to be JOINED through the common field that links theme.g., WHERE Orders.OrderNo = LineItems.OrderNo • ANY time you refer to a COMMOND FIELD that exists in both tables, you must use a TABLE PREFIX to eliminate the ambiguity e.g., SELECT Orders.OrderNo; WHERE Orders.OrderNo = 990001 For complex queries that JOIN 3 or more tables apply rule 1 for EACH link, and always apply rule 2 – e.g., SELECT Clients.ClientID, ClientName, Orders.OrderNo, OrderStatus, LineItem, ProdID, Qty FROM Clients, Orders, LineItems WHERE Clients.ClientID = Orders.ClientID AND Orders.OrderNo = LineItems.OrderNo

25. SQL DELETE Command: Deleting Data DELETE * FROM table1, table2, etc.– use wildcard * WHERE condition1 – which records (i.e., rows) to delete AND condition2 etc – you can combine more than one condition using AND or OR e.g.: DELETE * FROM Products WHERE ProdID=“bongos” Note: the DELETE command is IDENTICAL to the SELECT command – the only difference is that SELECT displays the selected records, whereas DELETE deletes them. Strong suggestion: write a SELECT command first, visually inspect the records display to ensure that they are the ones you want to delete, then replace the SELECT keyword with DELETE.

26. SQL Insert Command: Entering New Data Syntax: INSERT INTOtable_name (column1, column2, etc., columnx, etc.) VALUES(value1, value2, etc., valuex, etc.) Ex.: Insert (add) a complete record (values in all fields): INSERTINTO Friends VALUES(“ae”, “Espinosa”, “Alberto”, 12/12/2002, “885-1958”, “Looks tired, needs a vacation”) Ex.: Insert (add) partial record (values in some fields only – strongly recommended alternative): INSERT INTOFriends (FriendID, LastName, FirstName) VALUES(“ae”, “Espinosa”, “Alberto”)

27. SQL Update Command: Modifying Existing Data Ex.: Update (modify) record with new values: UPDATEtable_nameSETcolumn1 = value1, columnx= valuex, etc. WHEREcondition AND condition2 etc. Ex.: Update (modify) record with new values: UPDATEFriendsSETLastName=“Espinosa”, FirstName=“Alberto”WHEREFriendID = “alberto” Friends is the table name, goes after UPDATE SET indicate the columns where to make the changes and the values you want to change to WHERE indicates in which rows the change will take place; this WHERE keyword works exactly like the WHERE keyword in the SELECT command