Document 7574728
Download
Report
Transcript Document 7574728
CSC 2720
Building Web Applications
Database and SQL
Database and DBMS
Database – Structured collection of records or data
Database Management System (DBMS) –
Specialized software for managing databases
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.
Advantages of using DBMS
Web App
Web App
(Java Servlet)
SQL
SQL
(PHP)
DBMS
SQL
Stand alone App
SQL
SQL
(Java / C++ / C )
Web App
(ASP.NET)
Database
Management Tools
Standardized query language for defining and
manipulating data
Network accessible
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
Row /
Record
ItemID
Name
Price
Quantity
0123456
Coffee
4.50
100
0222222
Soy Milk
4.40
50
0142562
Tea
5.00
40
A table has a name.
A field has a name and a type.
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.
Some Common MySQL Types
Type
Size (bytes)
Description
CHAR[Length]
Length
A fixed-length field from 0 to 255
characters long
VARCHAR[Length]
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
DECIMAL[Length, 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 HH:MM:SS
DATETIME
8
In the format of
YYYY-MM-DD HH:MM:SS
TIMESTAMP
4
In the format of YYYYMMDDHHMMSS;
acceptable range ends in the year 2037
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.
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
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.
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
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