Database

1
Database SQL introduction

• Web Programming course

Acknowledgement this presentation uses examples
from the w3c website available at -
http//www.w3schools.com
2
Outline

• Database introduction
• Motivation
• History
• Implementation
• SQL introduction

3
Utility of Databases

• Data have value independent of use
• Organized approach to data management (e.g., data
  mining)
• Advantages
• Eliminate redundancy in data
• Share data
• Archive data
• Security of data
• Integrity of data

4
File Based Systems

• DEFINITION
• A collection of application programs that perform
  services to end users.
• Each program defines and manages its own data.

5
File Based Processing
File handling Routines
Data Entry Reports
File Definition
physical structure and storage of the data files
are defined in the program code
Sales Files
File handling Routines
Data Entry Reports
File Definition
Lease Files
6
Limitations of File Based Systems

• Data Dependence
• Duplication of Data
• Incompatible file formats

How can these problems be resolved?
7
The Database Approach

• A shared collection of logically
• related data designed to meet the
• information requirements of an
• organisation

8
Database Processing
Data Entry reports
Sales
Application Programs
DBMS
Database
Data Entry reports
App. Programs
Leases
9
Database Management System (DBMS)

• DEFINITION
• A software system that enables users to define,
  create and maintain the database and which
  provides controlled access to the database

10
Facilities of a DBMS

• Allows users to define the database (DDL)
• Allows users to insert, update, delete retrieve
  data (DML)
• Provides controlled access
• a security system
• an integrity system
• a concurrency control system
• a recovery system

What does it mean to define a database?
11
Components of a DBMS

• Hardware
• Software
• Data
• Procedures
• People

12
Advantages

• Minimal data redundancy
• Consistency of data
• Integration of data
• Improved integrity
• Consistent security
• Standards
• Increased productivity

13
Disadvantages

• Complexity
• Additional Hardware Costs
• Size
• Performance
• Experts -Specialised Personnel
• Potential organisational Conflict
• Higher impact of failure (centralized data source
  failure)

14
Database Interfaces
dedicated application
SQL command interface
SQL
SQL
web server interface prog
relationaldatabase
SQL
web browser
15
Interaction and Feedback

• Transaction non-decomposable unit of data
  manipulation
• example purchasing an airline ticket on-line
• typically small and fast for commercial
  applications
• may be long and involved in engineering
  applications
• Rollback if any part of a transaction fails, all
  completed parts are rolled back or undone
• example if you havent provided your credit card
  number, airline ticket purchase on-line
  transaction fails
• rollback ensures integrity of database
• automatically done by DBMS

16
Relational Database Model

• Database
• Database is a collection of tables (relations)
• Data are stored in tables
• Tables
• Each table has a name
• Each table has a set of columns (fields) and rows
  of data (records)
• All operations process a table to produce a new
  table
• Each table has a fixed number of columns
• Each table has an arbitrary number of rows
• Based on set theory
• SQL (Structured Query Language)
• DBMS independent language

Why?
17
Weather Sample Table
18
Database Columns (Fields)

• Columns
• Each column has a name
• Columns are accessed by name
• No standard column ordering
• Does not make sense to say the third column
  like it does in a paper table or spreadsheet
• Data in a column belongs to a particular domain
• Columns are the attributes of the dataset
• Each value in a column is from the same domain
• Each value in a column is of the same data type

19
Database Rows (Records)

• Rows
• Each row entry is either a simple value or empty
  ("null")
• Rows are sets of values for the columns
  (attribute values)
• Primary key a set of columns that uniquely
  identifies each row
• Each row must be unique given the primary key (no
  duplicates)
• Rows are referenced by the primary key
• Row order cannot be determined by the user (Does
  not make sense to say the fourth row like it
  does in a paper table or spreadsheet)

20
Data Types

• Each row value is an instance of a primitive data
  type
• Integer
• Real (e.g., number, currency
• Character (e.g., text, hyperlink, yes/no)
• Date/Time
• No complex types in standard DBMS (matrix,
  drawing)
• Object oriented databases may allow objects and
  structures
• Non existent value is null

21
Domain types

• char(n) fixed length char string
• varchar(n) variable-length char string
• int or integer
• smallint
• numeric(p,d) fixed-point number of given
  precision
• real, double precision
• float(n) floats with a given precision
• date containing year,month, and date
• time in hours, minutes, and seconds
• Null value is part of each domain

22
SQL

• Structured Query Language
• Communicate with databases
• Used to created and edit databases.
• Also used to create queries, forms, and reports

23
DDL and DML

• SQL consists of two types of statements
• SQL Data Definition Language (DDL)
• permits database tables to be created or deleted
  inserts new data into a database table
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• SQL Data Manipulation Language (DML)
• 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

24
Define new domains and tables

• CREATE DOMAIN personDom CHAR(20)
• CREATE TABLE emp
• (ename personDom,
• dno int default 0,
• sal real
• )

25
Select clause 1

• Specify attributes to project onto

SELECT columns FROM table WHERE condition
SELECT FROM Emp WHERE Emp.Sal lt 60K
use to denote all attributes
Emp (ename, dno, sal)
26
Select clause 2

• SELECT does not automatically eliminate
  duplicates.

Select dno From Emp
Use keyword distinct to explicitly remove
duplicates
Select distinct dno From Emp
Emp (ename, dno, sal)
27
FROM clause
SELECT columns FROM table WHERE condition

• Specify relations

Renaming relations Use as to define tuple
variables, to disambiguate multiple references
to the same relation
?
Who makes more money than his boss?
SELECT E1.ename FROM Emp as E1, Dept, Emp
as E2 WHERE E1.dno Dept.dno AND Dept.mgr
E2.ename AND E1.sal gt E2.sal
E1 Emp (ename, dno, sal)
Dept(dno, dname, mgr)
E2 Emp (ename, dno, sal)
28
WHERE clause

• Specify optional conditions
• Employees who work for Sally and have a salary lt
  90K

SELECT ename FROM Emp, Dept WHERE
Emp.dnoDept.dno AND D.mgr Lisa AND sal lt
90000
Lisa
Emp (ename, dno, sal)
Dept(dno, dname, mgr)
29
Conditions Used In Where Clause

• equals
• gt greater than
• lt less than
• gt greater than or equal to
• lt less than or equal to
• ltgt not equal to

30
Like

• Used to make complex searching easy. If you are
  trying to find all peoples names which begin
  with E for example
• SELECT firstname FROM employee
• WHERE firstname LIKE 'E'

31
Ordering output tuples

• Order the tuples by dno. Within each dept, order
  salaries from highest to lowest. For salary ties,
  use alphabetical order on the name.

SELECT FROM Emp order by dno, sal, ename
32
Set Operations

• Union ?.
• Intersect ?.
• Except -

(select mgr from D where dnametoy) union (selec
t mgr from D where dname sells)
Find names of people who are managers of either
the toy or the sales department.
(select mgr from D where dnametoy) intersect (s
elect mgr from D where dname sales)
Find names of people who are managers of both
the toy and the sales departments.
(select mgr from D where dnametoy) except (sele
ct mgr from D where dname sells)
Find names of people who are managers of the
toy but of the sales department.
33
Conserving Duplicates

• The UNION, INTERSECT, and EXCEPT operators use
  the set semantics, not bag semantics.
• To keep duplicates, use ALL after the
  operators
• UNION ALL, INTERSECT ALL, EXCEPT ALL

(SELECT ssno FROM student) UNION (SELECT ssno
FROM ta)
ALL
Result
TA (ssno, name)
Student (ssno, name)
34
Aggregation functions

• MIN, MAX, SUM, COUNT, AVG
• input collection of numbers/strings (depending
  on operation)
• output relation with a single attribute with a
  single row

What is the minimum, maximum, average salary of
employees in the toy department
SELECT MIN(sal), MAX(sal), AVG(sal) FROM Emp,
Dept WHERE Emp.dno Dept.dno and D.dname
Toy
35
JOIN

• Sometimes we have to select data from two or more
  tables to make our result complete. We have to
  perform a join
• Tables in a database can be related to each other
  with keys. A primary key is a column with a
  unique value for each row. The purpose is to bind
  data together, across tables, without repeating
  all of the data in every table.

36
INNER JOIN

• The INNER JOIN returns all rows from both tables
  where there is a match.

If there are rows in Employees that do not have
matches in Orders, those rows will not be listed
Employees
SELECT Employees.Name, Orders.Product FROM
Employees INNER JOIN Orders ON
Employees.Employee_IDOrders.Employee_ID
Result
Orders
37
Left\Right Join

• The LEFT JOIN returns all the rows from the first
  table (Employees), even if there are no matches
  in the second table (Orders).
• The RIGHT JOIN returns all the rows from the
  second table (Orders), even if there are no
  matches in the first table (Employees).

SELECT Employees.Name, Orders.Product FROM
Employees LEFT JOIN Orders ON Employees.Employee_
IDOrders.Employee_ID
SELECT Employees.Name, Orders.Product FROM
Employees RIGHT JOIN Orders ON
Employees.Employee_IDOrders.Employee_ID
38
INSERT INTO

• The INSERT INTO statement is used to insert new
  rows into a table

INSERT INTO table_name VALUES (value1,
value2,....)
Or specify the columns for which you want to
insert data
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
39
Insert Into Example
40
UPDATE

• The UPDATE statement is used to modify the data
  in a table.

UPDATE table_name SET column_name new_value
WHERE column_name some_value
41
Person
UPDATE Person SET FirstName 'Nina' WHERE
LastName 'Rasmussen'
UPDATE Person SET Address 'Stien 12', City
'Stav' WHERE LastName 'Rasmussen'
42
DELETE

• The DELETE statement is used to delete rows in a
  table.

DELETE FROM table_name WHERE column_name
some_value
DELETE FROM Person WHERE LastName 'Rasmussen'
43
Summary

• Why uses databases instead of file systems, what
  are the pro-cons, when to use each alternative?
• What is a relational database? How can you define
  its semantics?
• What is SQL? What are DDL and DML? What
  statements are related to each SQL rule

44

• Questions?