This Lecture SQL The SQL language SQL, the relational model, and - PDF document

This Lecture • SQL • The SQL language • SQL, the relational model, and E/R diagrams SQL Data Definition • CREATE TABLE • Columns • Primary Keys Database Systems • Foreign Keys • Further Reading Michael Pound • Database Systems, Connolly & Begg, Chapter 7.3 • The Manga Guide to Databases, Chapter 4 Last Lecture SQL Name Address • ANSI Standards and a • Entity Relationship • Originally ‘Sequel’ - number of revisions Diagrams Student Structured English ID Year • SQL-89 • Entities query Language, part of • SQL-92 (SQL2) an IBM project in the • Attributes Has • SQL-99 (SQL3) 70’s • Relationships • ... ID Enrolment Code • Sequel was already • Example • SQL:2008 (SQL 2008) taken, so it became SQL • Students take many • Most modern DBMS In - Structured Query Modules use a variety of SQL • Modules will be taken by Language • Few (if any) are true to Module Code Credits many Students the standard Title SQL Database Management Systems • SQL is a language based • SQL provides • A DBMS is a software • There are many DBMSs, on the relational model • A Data Definition Language system responsible for some popular ones • Actual implementation is (DDL) allowing users access to include: provided by a DBMS • A Data Manipulation data • Oracle • SQL is everywhere Language (DML) • A DBMS will usually • DB2 • A Data Control Language • Most companies use it for • Microsoft SQL Server • Allow the user to access (DCL) data storage • Ingres data using SQL • All of us use it dozens of • Allow connections from • PostgreSQL times per day other programming • MySQL • You will be expected to languages • Microsoft Access (with SQL know it as a software • Provide additional Server as storage engine) developer functionality like concurrency 1

MySQL SQL Case • During this module we will use MySQL as our • SQL statements will be written in BOLD COURIER FONT • SQL keywords are not case- sensitive, but we’ll write SQL DBMS keywords in upper case for emphasis • Free to use • Table names, column names etc. are case sensitive • Source code available under General Public License • For example: • Extremely popular and widely used • Easy to set up on the school servers SELECT * FROM Students • In most cases is as functional as commercial DBMSs WHERE Name = “James”; • The school also has Access, Oracle and PostgreSQL installed. Important: MySQL in Windows is not case sensitive. Do not be complacent during the coursework. SQL Strings Non-Procedural Programming • Strings in SQL are surrounded by single quotes: • SQL is a declarative • Example: Given a (non-procedural) database with tables • 'I AM A STRING' language • Student with attributes • Single quotes within a string are doubled or • Procedural – tell the ID, Name, Address escaped using \ • Module with attributes computer what to do • 'I''M A STRING' using specific successive Code, Title • 'I\'M A STRING' instructions • Enrolment with • Non-procedural – attributes ID, Code • '' is an empty string describe the required • Get a list of students • In MySQL , double quotes also work (this isn’t the result (not the way to who take the module compute it) ANSI standard) ‘Database Systems’ Procedural Programming Non-Procedural (SQL) Set M to be the first Module Record /* Find module code for */ SELECT Name FROM Student, Enrolment Code = '' /* 'Database Systems' */ While (M is not null) and (Code = '') If (M.Title = 'Database Systems') Then WHERE Code = M.Code Set M to be the next Module Record (Student.ID = Enrolment.ID) Set NAMES to be empty /* A list of student names */ Set S to be the first Student Record AND While S is not null /* For each student... */ Set E to be the first Enrolment Record While E is not null /* For each enrolment... */ (Enrolment.Code = If (E.ID = S.ID) And /* If this student is */ (E.Code = Code) Then /* enrolled in DB Systems */ (SELECT Code FROM Module WHERE NAMES = NAMES + S.NAME /* add them to the list */ Set E to be the next Enrolment Record Title = „Database Systems‟)); Set S to be the next Student Record Return NAMES 2

NoSQL Relations, Entities and Tables • SQL is by no means perfect • The terminology changes from the Relational Model • Edgar Codd hated it – It’s actually a pretty poor through to SQL, but usually means the same thing implementation of the relational model • Implementations vary wildly. For example, while Relations E/R Diagrams SQL Oracle and MySQL both use SQL, there are commands Relation Entity Table that won’t work on both systems. • It’s extremely easy to trigger vast joins or delete large Tuple Instance Row numbers of rows by mistake Attribute Attribute Column or Field • NoSQL is a term used to describe database Foreign Key M:1 Relationship Foreign Key systems that attempt to avoid SQL and the Primary Key Attribute Primary Key relational model Implementing E/R Diagrams CREATE TABLE Name Address • Given an E/R design CREATE TABLE <table-name> ( • You supply • The entities become SQL <col-name 1> <col-def 1>, Student • A name for the table ID Year tables • A name and <col-name 2> <col-def 2>, • Attributes of an entity Has : definition for each become columns in the corresponding table column <col-name n> <col-def n>, • We can approximate the • A list of constraints ID Enrolment Code domains of the attributes <constraint-1>, (e.g. Keys) by assigning types to each : column In • Relationships may be <constraint-k> represented by foreign ); Module Code Credits keys Title Column Definitions Types • There are many types in MySQL, but most are <col-name> <type> • Each column has a variations of the standard types name and a type [NULL | NOT NULL] • Numeric Types • Most of the rest of [DEFAULT default_value ] • TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT the column [NOT NULL | NULL] • FLOAT, REAL, DOUBLE, DECIMAL definition is • Dates and Times [AUTO_INCREMENT] optional • DATE, TIME, YEAR [UNIQUE [KEY] | • There’s more you • Strings [PRIMARY] KEY] can add, like • CHAR, VARCHAR storage and index • Others instructions • ENUM, BLOB ([] optional , | or ) 3

Types Column Definitions • We will use a small subset of the possible • Columns can be • Columns can be given a specified as NULL or default value types: NOT NULL • You just use the Type Description Example • NOT NULL columns keyword DEFAULT TINYINT 8 bit integer -128 to 127 cannot have missing followed by the value, INT 32 bit integer 2147483648 to 2147483647 values eg: CHAR (m) String of fixed length m “Hello World ” • NULL is the default if VARCHAR (m) String of maximum length m “Hello World” col-name INT DEFAULT 0, REAL A double precision number 3.14159 you do not specify ENUM A set of specific strings (‘Cat’, ‘Dog’, ‘Mouse’) either DATE A Day, Month and Year ‘1981 -12- 16’ or ‘81 -12- 16’ Example AUTO_INCREMENT • If you specify a column as AUTO_INCREMENT , a value CREATE TABLE Student ( (usually max(col) + 1) is automatically inserted when data sID INT NOT NULL, is added. This is useful for Primary Keys sName VARCHAR(50) NOT NULL, • For example: sAddress VARCHAR(255), col-name INT AUTO_INCREMENT, • When it comes to inserting values, you should use NULL, sYear INT DEFAULT 1 0 or nothing to ensure you don’t override the automatic ); value Name Address Note: The table auto_increment value isn’t recalculated during deletes. You might want to reset it using: ID Student Year ALTER TABLE <name> AUTO_INCREMENT=1; Example Constraints CREATE TABLE Student ( CONSTRAINT • Each constraint is given sID INT NOT NULL <name> a name. If you don’t AUTO_INCREMENT, Name Address <type> sName VARCHAR(50) NOT NULL, specify a name, one will sAddress VARCHAR(255), ID Student Year <details> be generated sYear INT DEFAULT 1 ); • Constraints which refer • MySQL Constraints CREATE TABLE Module ( to single columns can • PRIMARY KEY mCode CHAR(6) NOT NULL, Code Module Credits be included in their mCredits TINYINT NOT NULL • UNIQUE DEFAULT 10, definition • FOREIGN KEY mTitle VARCHAR(100) NOT Title NULL • INDEX ); 4