An introduction to RDBMS Objectives To learn about

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 SQL 92 (SQL 2) RDB/1

The need for a SQL standard 1970 - Ted Codd’s Paper 1970’s - Various IBM Products Oracle -1 st Commercial Implementation SQL/DS for DOS/VSE Environment 1983 - DB 2 for MVS Environment 1982 -86 ANSI Standard for SQL 1989 - ANSI SQL with Integrity Enhancement 1992 - ISO/ANSI SQL 2 SQL 3 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 Stored Procedures Triggers System Tables sp_get_sales dept tr_sale_insert sale sysobjects 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 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 10 20 30 fname fred bob sue lname dept_no smith 1 james 1 brown 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 emp_no 10 20 30 fname fred bob sue Foreign Key lname dept_no smith 1 james 1 brown 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 emp_no 10 20 30 fname fred bob sue lname dept_no smith 1 james 1 brown 2 department dept_no dept_name 1 marketing 2 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 SQL 2 • 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 SQL 2 is widely implemented – All vendors offer proprietary extensions and alternatives RDB/12