CSE 190: Internet E-Commerce

CSE 190: Internet E-Commerce Lecture 12: SQL, DB Design

SQL: Simple Calculations • SQL allows not only selection from database tables, but also basic calculations over all the rows of those tables • Select count(*) from customerswhere total_loans < 5500; • Returns the count of customers who have loans of less than 5500 • Select sum(total_loans) from customers; • Returns the sum of all the loans outstanding for all customers • Select avg(total_loans) from customers; • Returns the average of total_loans over all customers • Reference • http://www.w3schools.com/sql/default.asp

SQL: GROUP BY Have Query results collated by the value of specified field Syntax: SELECT column, calc(column) FROM table GROUP BY column Example: SELECT company, sum(amount) from sales GROUP by company Sales Table Yields:

SQL: Views • Views: A named table that results from applying criteria to a SELECT clause. • Example: • CREATE VIEW YOUNG_PEOPLE ASSELECT * FROM PEOPLE WHERE DOB > DateValue('1.1.1980'); • May then be used in subsequent selects: • SELECT * FROM YOUNG_PEOPLE;

DB Design • DB Design involves two activities • Logical design • Physical design • Logical design: describing the schemas, tables, and relationships amongst the tables • Typically done by application developer • Physical design: Deciding what disks tables are located on, how to create indexes, which queries to optimize on

DB Design: Entity Relationships • Entity: Logical object captured within a table • Relationships between other entities are of certain category: • 1:1 • Implies entities should be merged • 1:M • Commonly used to represent lists • M:1 • Entity is part of some list of the other entity • M:M • Avoid this kind of relationship • Creating these Entity-Relationship diagrams known as Database modeling; basically similar to object-oriented analysis • Object oriented databases: reduce the effort to map objects to relational tables • However, still immature compared to relational databases

DB Design: Normal Forms • Normalization: step-wise refinement of our database schema to eliminate redundancy and reduce the costs of change • Good DB Design usually reaches 3rd Normal Form

DB Design: Zero Normal Form • Zero normal form • No normal form rules have been applied yet • Example (Users table) • Problem: List of URLs, capacity is hard coded to be two. Need to add a new column to support three URLs!

DB Design: First Normal Form Rules: • Eliminate repeating groups in individual tables • Create a new table for each set of related data • Identify each set of related data with a primary key Problem: Adding new URL means we have to copy the same user, company and company address values. This is redundant and could lead to inconsistent values.

DB Design: Second Normal Form Rules: • Create separate tables for sets of values that apply to multiple records • Relate these tables with a foreign key Users: URLs Problem: Adding new employee means we have to copy address information, leading to redundancy

DB Design: Third Normal Form Rules: • Eliminate fields which do not depend on the primary key Note: Once you’ve applied 3rd Normal Form, your database schema is in relatively good health Problem: Note how Abc.com and Xyz.com are copies. If this comes from open data entry, then nothing to do about this. But if it’s from a fixed set of choices, more may be done.

DB Design: 4th, 5th Normal Form 4th Normal Form: Independent entities may not be stored in the same table 5th Normal Form: Original table must be reconstructed from the tables it has been decomposed into.

DB Design: Inheritance • What about subclasses of objects, with subsets (or supersets) of properties • Two solutions: • Master table of properties with object id selecting for property ID • One new table for each subclass • Second generally better for relational integrity

CSE 190: Internet E-Commerce