Web Database Programming

1
Web Database Programming

• Database Basics

2
What is a Database?

• Collection of information
• Relational Database
• Most common
• Table-based
• Entities - Objects in the world
• have attributes
• we create an abstraction of the Entity
• In the database
• an entity is a row (record) in the table
• an attribute becomes a field in the record

3
Database (continued)?

• Each Table
• describes a set of entities (like students)?
• rows are records (each entity)?
• one field is the key or primary key

4
MySQL

• MySQL Database server
• MySQL command interpreter
• mysql h host u user -p
• User is your trantor login name
• Password is test
• Enter into MySQL prompt
• mysqlgt
• MySQL command ends with
• Mostly SQL (structured query language)?

5
Common MySQL commands

• SET PASSWORDPASSWORD(newpassword)
• SELECT anExpression
• E.g. version(), now(), sin(PI() / 4)?
• SHOW DATABASES
• USE aDatabase
• SHOW TABLES

6
Setting up a table

• CREATE TABLE pets
• ( ID INTEGER NOT NULL
• AUTO_INCREMENT
• PRIMARY KEY,
• name VARCHAR(20),
• birthday DATE
• )

7
SQL -- CREATE TABLE

• CREATE TABLE tableName (list-of-filed-descriptions
  )?
• Field description name type attributes
• Types
• INTEGER a whole number
• VARCHAR(n) up to n characters
• CHAR(n) exactly n characters
• DATE a date (e.g. 1999/05/26)?
• DATETIME a date and a time
• FLOAT a float point number (not currency
• TEXT up to 64K characters
• DECIMAL (n,m) up to n digits before the point
  and m digits after the point

8
SQL CREATE TABLE (cont)?

• Attributes
• NOT NULL the value is required (must be true of
  primary keys)?
• PRIMARY KEY this field is a primary key
• AUTO_INCRIMENT automatically number the primary
  keys
• For multi-valued primary keys
• PRIMARY KEY (field1, field2)?
• All the fields must be NOT NULL

9
Table Structure

• DESCRIBE tableName
• ALTER TABLE tableName alter_specs
• ADD fieldSpecs
• DROP fieldNames
• CHANGE fieldNames fieldSpecs

10
Delete a Table

• DROP TABLE tableName
• e.g.
• DROP TABLE simple

11
Load in Data

• LOAD DATA LOCAL INFILE filename INTO TABLE
  tableName
• Text file
• Fields delimited with TAB
• Each row (record) on a line

12
Insert Data in a Table

• INSERT INTO tableName (field-name-list) VALUES
  (value-list)?
• e.g.
• INSERT INTOpets(name, birthdate)VALUES
  (Mimi, 2003/04/20)
• INSERT INTO tableName SET fieldName1
  filvalue1,

13
Verify Table Contents

• SELECT FROM tableName
• e.g.
• SELECT FROM pets

14
Update Records

• DELETE FROM tableName WHERE condition
• UPDATE tableName SET fieldName1 filvalue1,
  WHERE condition

15
SQL Query

• SELECT FROM WHERE GROUP BYORDER BY
• Examples (more later)?
• SELECT FROM pets
• SELECT name, birthdateFROM petsWHERE
  namemimi AND birthdate2003/04/20
• SELECT name AS pN, weight AS pWFROM petsWHERE
  namemimi OR (weightlt10 AND photograph IS
  Null)ORDER BY weight

16
One-to-Many Relationship (1)?

• The Problem each pet belongs to one species but
  many pets belongs to the same species
• Want to avoid redundancy
• The Solution
• Add a species table
• In the pet table, indicate which species by using
  its the primary key from the species table (this
  is called a foreign key)?

17
(No Transcript)
18
One-to-Many Relationship (3)?

• ER diagram (Entity-Relationship Model)?

19
Relational Table (1)?

• Problem how to represent a Many-to-Many
  relationship
• Each owner could have many different pets
• Each pet can be owned by different people
• Think of a family
• This information cannot go in the pet table, and
  it can not go in the people table.
• An ownership is really a relationship between a
  person and a pet

20
(No Transcript)
21
Relational Table (3)?
22
Design Process

• a narrative of user interactions a story of how
  the database will be used
• look for objects and attributes (nouns)?
• look for relationships
• draw ER diagrams
• convert ER diagrams into tables
• - consider the limitations of the tools (e.g. can
  it store long text fields, pictures, multiple
  values)?
• iterate
• do it over - the first design is never the best

23
Example Design

• Customers come to the web site and register. The
  sales department sets up a list of widgets that
  customers can order from. A customer makes one or
  more orders for widgets. When the order is filled
  the order is removed from the database. Widgets
  are classified by type.