CSC 2720 Building Web Applications

1
CSC 2720Building Web Applications

• Database and SQL

2
Database and DBMS

• Database Structured collection of records or
  data
• Database Management System (DBMS) Specialized
  software for managing databases

3
Advantages of using DBMS

• Efficient Data Access
• Support multiple users and Data Security
• Protect access to data in which only authorized
  users can access the data
• Concurrent Access
• Support mechanisms to allow multiple users to
  safely read/write the data. (e.g., record
  locking)
• Transaction
• Comprises of multiple indivisible operations
• A transaction must succeed or fail as a complete
  unit it cannot remain in an intermediate state.

4
Advantages of using DBMS
Web App (PHP)
Web App (Java Servlet)
SQL
SQL
DBMS
SQL
SQL
Stand alone App (Java / C / C )
Web App (ASP.NET)
SQL
Database Management Tools

• Standardized query language for defining and
  manipulating data
• Network accessible

5
Introduction to Relational Database

• Relational Database A type of database in which
  data are organized into related tables (a.k.a.
  relations).

Column / Field / Attribute
ItemID Name Price Quantity
0123456 Coffee 4.50 100
0222222 Soy Milk 4.40 50
0142562 Tea 5.00 40
Row / Record

• A table has a name.
• A field has a name and a type.

6
Designing a Table Deciding column names and
types

• Name
• Should contain only letters, digits and
  underscore
• Meaningful
• Should not be a reserve word
• Should be less than 64 characters
• Should assume the name is case sensitive
• Three Primary Types
• Text (or Strings) CHAR, VARCHAR, etc.
• Numbers INT, DOUBLE, DECIMAL, etc.
• Dates and Times TIME, DATE, DATETIME, etc.

7
Some Common MySQL Types
Type Size (bytes) Description
CHARLength Length A fixed-length field from 0 to 255 characters long
VARCHARLength Length 1 or 2 A variable-length field from 0 to 65535 characters long
TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT 1 / 2 / 3 / 4 / 8 Signed integers
FLOAT / DOUBLE 4 / 8 Single and Double precision floating point numbers
DECIMALLength, Decimals Length 1 or 2 A DOUBLE stored as a string, allowing for a fixed decimal point (Length of digits, Decimals of decimal places)
DATE 3 In the format YYYY-MM-DD
TIME 3 In the format of HHMMSS
DATETIME 8 In the format of YYYY-MM-DD HHMMSS
TIMESTAMP 4 In the format of YYYYMMDDHHMMSS acceptable range ends in the year 2037
8
Additional Field Properties

• NOT NULL
• Every field must be assigned a value or else an
  error will occur.
• DEFAULT default_value
• If a field is not assigned any value,
  default_value is assumed.
• AUTO_INCREMENT
• Use an integer that is one more than the current
  largest integer as the default value
• UNSIGNED
• A field can only hold non-negative integer.
• The range of positive integers is doubled.

9
SQL query to create a "Users" table
CREATE TABLE users ( user_id MEDIUMINT UNSIGNED
NOT NULL AUTO_INCREMENT, first_name VARCHAR(20)
NOT NULL, last_name VARCHAR(40) NOT NULL, email
VARCHAR(60) NOT NULL,pass CHAR(40) NOT
NULL, reg_date DATETIME NOT NULL, PRIMARY KEY
(user_id) )

• PRIMARY KEY (user_id)
• Indicates that the field user_id is to be used as
  the primary key to identify the records in the
  table

10
Characteristics of a Table

• A NULL value means no value.
• When defining a table, we can set whether a field
  can contain a null value or not.
• Keys are used to identify records in a table.
• A Primary key is a field (or combination of
  fields) that uniquely identifies a record within
  a table.
• A Foreign key is a field (or combination of
  fields) that serves as a primary key in another
  table. It is used to establish a relationship
  between a pair of tables.
• Records are not ordered in a table.

11
Structured Query Language (SQL)

• A language that can be used to build, modify,
  query, and manipulate a database
• SQL supported by different DBMS may vary
  slightly.
• With SQL, you can
• Create/delete a database
• Create/delete tables in a database
• Retrieve data from a database
• Insert new records in a database
• Delete records from a database
• Update records in a database

12
SQL (con't)

• SQL is not case sensitive
• Multiple statements are separated by semicolon
• Important commands for manipulating data
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database
  table
• Important commands for manipulating table
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• Examples SQL Tutorial at W3Schools
• http//www.w3schools.com/sql/default.asp