Transcript ppt

CSE 190: Internet E-Commerce
Lecture 10: Data Tier
Data Tier
• Rationale
– All data will be stored in central location, the
database, with no duplication of state. Careful
attention will be paid to keeping data access atomic,
consistent, isolated, and durable. (ACID)
– Database will provide high level access to querying,
filtering, and summarizing the data
– Common commercial products:
Oracle, IBM’s DB2, Microsoft SQL Server
– Free products:
mysql, PostgresSQL
Choosing data tier representation
• When might a data base be contraindicated?
– Very simple patterns of data access
– Loss of data not catastrophic
– Low latency an absolute requirement
– Examples:
• Amazon, Yahoo, Hotmail, Google
• For most sites, a database backed web
site is a good idea
Relational databases
• All data is described as
relations between one set
of facts (a table) and
another set of facts
• Each relation is called a
table.
• Each row (sometimes
called a tuple) has a set
of fields, such as name,
date of birth
• Each field (column) has a
value of some intrinsic
DB type (string, integer,
date)
Primary keys, foreign keys
• Primary key: A field of a table designated
to provide a unique identifier for a specific
row of the table
• Foreign key: A field of a table whose value
matches the primary key of another table.
From foreign keys, we see relationships
between one table and another.
• Design: primary keys should not encode
meaning in their representation
Joins
• A join is the cross product
of all the rows of a set of
tables.
• By selecting a subset of
the rows of the join that
match some criteria, we
can answer simple
questions about our data.
This is the core of the
relational model.
Two tables
Inner Join of two tables
Left outer join
Left outer join
Schemas
•
•
By creating several
tables, with the
appropriate Primary
keys, foreign keys, we
can model the data
needs of our
application
Database especially
suited for representing
the relationship
between our entities
(objects) when the
number of rows in our
schemas very large
compared to the
number of schemas
SQL
• A standard mechanism for querying and manipulating
relational databases
• Queries, Changes (DML: Data Manipulation Language)
– SELECT id, firstname, lastname, networth
from customers
where networth > 1000000
– INSERT into customers
values( 17, “Alice”, “Crypto”, 400 )
– UPDATE customers
SET networth = networth + 1000
• Note: Results from a SQL query (SELECT) are a table in
their own right, a view of the data. This table may also be
used to perform joins against other tables.
SQL Schema Manipulation
(DDL: Data Definition Language)
• Example table creation:
CREATE TABLE Customers (
CustNo NUMBER(3) NOT NULL,
CustName VARCHAR2(30) NOT NULL,
Street VARCHAR2(20) NOT NULL,
City VARCHAR2(20) NOT NULL,
State CHAR(2) NOT NULL,
Zip VARCHAR2(10) NOT NULL,
Phone VARCHAR2(12),
PRIMARY KEY ( CustNo ) );