Transcript Introduction

An introduction to RDBMS
• Objectives
– To learn about the history and future direction of the SQL standard
– To get an overall appreciation of a modern RDBMS and the nature of
SQL
– Familiarisation with the course environment and data to be used
• Contents
–
–
–
–
–
–
–
–
–
The need for a SQL standard
What is a Relational Database?
Components of a Relational Database
What does unnormalised/normalised data mean?
Anatomy of a table
Keys
Joins
What is ‘set oriented’?
The components of SQL92 (SQL2)
RDB/1
The need for a SQL standard
1970 - Ted Codd’s Paper
1970’s - Various IBM
Products
Oracle -1st Commercial
Implementation
1982-86 ANSI Standard
for SQL
1989 - ANSI SQL with
Integrity Enhancement
1992 - ISO/ANSI SQL2
SQL/DS for DOS/VSE
Environment
1983 - DB2 for MVS
Environment
SQL3
RDB/2
What is a Relational Database?
Database
Interactive
SQL
Static SQL
Dynamic
SQL
Data
Base
Management
System
(The engine)
Names
and
Addresses
Production
Schedules
Sales
Data
Design
Information
Drawings
Satellite
Images
Libraries
RDB/3
Components of a Relational Database
Database
Stored
Procedures
Triggers
System
Tables
sp_get_sales
tr_sale_insert
sysobjects
dept
sale
User
Tables
sales_by_dept
Views
ci_emp_deptno
Indexes
RDB/4
Anatomy of a table - (unnormalised)
A table to hold all the data about departments and employees:
emp_no fname lname dept_no dept_name
10
20
30
fred
bob
sue
smith
james
brown
1
1
2
marketing
marketing
sales
• Need to uniquely identify each row via a mandatory ‘Primary Key’ column
• How do we:
– Add new dept ‘3’ that has no employees yet ??
– Remove the ‘Sue Brown’ row but keep the ‘sales’ dept
– Change the name of ‘Marketing’, without having to do it in many places
RDB/5
Anatomy of table(s) - (normalised)
• Multiple tables, one for each entity
• One occurrence of each entity is a row of a table
• Just sufficient duplication of data maintained to keep links between tables
emp_no fname lname dept_no
10
20
30
fred
bob
sue
smith
james
brown
1
1
2
dept_no dept_name
1
marketing
2
sales
‘department’ table
‘employee’ table
• What about the three anomalies?
– Add dept ‘3’ to the ‘dept’ table
– Delete ‘Sue Brown’ from ‘employee’ table
– Change the name of ‘marketing’
RDB/6
Keys - Primary & Foreign and ‘RI’
Primary Key
Foreign Key
emp_no fname lname dept_no
10
20
30
fred
bob
sue
smith
james
brown
1
1
2
Primary Key
dept_no dept_name
1
marketing
2
sales
‘department’ table (one)
‘employee’ table (many)
• There exists a one to many relationship between ‘departments’ and ‘employees’
• A Foreign Key of the ‘many’ table has values in it that must be in the Primary
Key column of the ‘one’ table
• This concept is known as Referential Integrity (RI)
• We tell the RDBMS how to enforce this integrity in CREATE TABLE code (ch. 6)
• Declarative Referential Integrity is that feature of the RDBMS that implements RI
in the CREATE TABLE statements
RDB/7
Joins (sneak preview)
• Use ‘Key’ columns for Joins
• PK of the ‘one’ to FK of the ‘many’
– Some exceptions
Foreign Key
emp_no
10
20
30
fname
fred
bob
sue
lname dept_no
smith
1
james
1
brown
2
employee table (‘many’)
Dependent Table
Primary Key
dept_no dept_name
1
marketing
2
sales
department table (‘one’)
Parent Table
• Choose a person and decide what dept he/she is in
– You have just done a Join! (see chapter 5 for details)
RDB/8
SQL is ‘set oriented’
• SQL always operates on, and results in, complete sets of data ‘rectangles’ of rows and columns
employee
department
emp_no fname lname dept_no
10
20
30
fred
bob
sue
smith
james
brown
1
1
2
dept_no dept_name
1
2
marketing
sales
• SQL is ‘set oriented’ also when you add, change or delete data.
For example:
DELETE FROM employees
Result: all employee records are removed
RDB/9
Components of SQL2
• Data Definition Language - DDL
– CREATE, ALTER and DROP statements
– Used for Tables, Views, Columns, Indexes, Databases
• Data Manipulation Language - DML
– SELECT, INSERT, UPDATE, DELETE statements
– 95% of SQL is probably DML statements
– >95% of all DML statements are SELECT’s
• Data Control Language - DCL
– Security for database usage
– GRANT and REVOKE statements
• Programming Interface
– PROCEDURES, TRIGGERS, CURSORS, TRANSACTIONS, LOCKING
– Database specific, non standard, beyond the scope of this course
RDB/10
Practical
• Familiarisation with Query Analyzer - an MS SQL Server 2000
‘interactive query’ client application
– You will spend all your lab time on this course in Query Analyzer
• Familiarisation with the schema and data you will be working
with on this course
– ‘depts’ employ ‘salespersons’ who make ‘sales’ to ‘contacts’ who work
for ‘companies’ (our customers)
– Each of you has a userid (sqluser’n’) with password of ‘password’
– Each of you has a ‘database’ (Database’n’) with 5 tables, each
populated with data
RDB/11
Summary
• A Database
– Enables storage and manipulation of data without an application
• A Relational Database
– User data is stored and manipulated in sets called relations or tables
– All system objects stored and manipulated the same way
– Various types of system objects can be used for security/ performance
enhancement
– To take full advantage of the relational model and SQL, data is normalised
– Tables have keys (Primary and Foreign) that can be used to join them at
runtime
• The SQL Standard
– Spread of RDBMS led to the need for a standard
– Current standard SQL2 is widely implemented
– All vendors offer proprietary extensions and alternatives
RDB/12