Transcript Database amd SQL

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
 Relational Database – A type of database in which
data are organized as related tables
 Most widely used type of 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
 Crash Recovery
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
 Data are stored in 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.
 Besides some standard types, different DBMS (e.g.,
Oracle and MySQL) may also have their own specific
types.
Characteristics of a Table
 A field is said to contain a null value when it contains
nothing at all.
 When defining a table, we can set whether a field can contain a null
value or not.
 Keys are special fields that serve a specific purpose within
a table.
 A Primary key is a field (or combination of fields) that uniquely
identifies a record within a table.
 A Foreign key is the field that is used to establish a relationship
between a pair of tables.A foreign key has to be a primary key of
another table.
 Row orders are not important.
Structured Query Language (SQL)
 A standardized language that can be used to build,
modify, maintain 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 case insensitive
 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