1 / 13

CSE 190: Internet E-Commerce

This lecture covers SQL queries for simple calculations, group by statements, views, entity relationships, and normal forms in database design.

studer
Download Presentation

CSE 190: Internet E-Commerce

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. CSE 190: Internet E-Commerce Lecture 12: SQL, DB Design

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

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

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

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

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

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

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

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

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

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

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

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

More Related