Introduction to SQL 2 What is SQL Structure

Introduction to SQL

2 What is SQL? • Structure Query Language – programming language used to access a relational database • RDBMS = Relational Database Management System – widely popular – mostly standard – several dialects support useful non-standard features • My. SQL – – most popular open / free good support by PHP version 5. 5

3 Relational Databases • history: – files -> hierarchical -> relational -> objects? • concepts – based on tables – mathematical underpinnings • relational algebra • joins • RDBMS – Relational Database Management System • main commercial products – Oracle, Ingress, Microsoft SQL Server, Access

4 Tables • tables – identified by a name – consist of columns – may / should conform to normalization rules • columns – identified by name • each row is a data record • e. g. , table Students id name major gpa 1 John Doe ICS 3. 4 2 Sue Smith EE 4. 0 3 Joe Brown ICS 2. 6

5 Keys and Indices • Keys – used as references – one or more columns – primary key • • reference to a record must have a value unique (typically a number) fixed – foreign key • typically a primary key in another table – candidate key • uniquely identifies each record • one candidate key is the primary key. • Index – improves performance for queries

6 Normalization & BCNF • Goals – eliminate redundant data • data stored in only one table • data contains only related data – improve performance • BCNF (Boyce-Codd Normal Form) – every key that uniquely determines each record is a candidate key

7 1 NF, 2 NF, 3 NF & 4 NF • 1 st normal form (1 NF) – no duplicate columns in a table + separate tables for each related data set + primary key in each table row • 2 nd normal form (2 NF) + in separate tables for data duplicated in several rows + foreign keys to relate new and original tables • 3 rd normal form (3 NF) + No columns independent on the primary key. • 4 th normal form (4 NF) + no multi-valued dependencies.

8 Database Naming Conventions • table names – syntax: letters, digits, _ • < 64 bytes – unique • column names – syntax: letters, digits, _ • < 64 bytes – unique within a table • but same name in different tables is common – if the purpose is the same or similar • choose meaningful but concise names

9 Main Data Types • Strings – VARCHAR(length) • for variable size up to length – CHAR(length) • for fixed length; faster, but uses more storage – TEXT • < 65 K characters – MEDIUMTEXT • < 16 M characters – LONGTEXT • < 4 G characters – quoted • Date & Time – DATETIME • yyyy-mm-dd hh: mm: ss – DATE • yyyy-mm-dd – TIME • hh: mm: ss – TIMESTAMP • • yyyymmddhhmmss set to current time and date each time a record is updated

10 Main Data Types • Numbers – INT, INTEGER • can be UNSIGNED – MEDIUMINT • unsigned, <=0, 224 values – DEC(length, decimals), DECIMAL(length, decimals) • stored as string • Set – ENUM(value 1, value 2, . . . ) • e. g. , gender ENUM('male', 'female') DEFAULT 'female' – SET(value 1, value 2, . . . ) • like ENUM, but more values • Binary – BLOB, MEDIUMBLOB, LONGBLOB, TINYBLOB • as the corresponding xx. TEXT types

11 SQL Syntax • • sequence of commands not case-sensitive – but caps for keywords are standard • • commands must be terminated with ; command keywords – e. g. , SELECT, CREATE, USE, INSERT, UPDATE • special keywords – WHERE • denotes condition – VALUES • brackets – attributes • NOT NULL, AUTO_INCREMENT – syntactic sugar • FROM, INTO

12 Data Definition Commands • used to manipulate and access tables CREATE USE ALTER DROP creates new database creates new table makes existing table accessible modifies table columns deletes table

13 Data Manipulation Commands • used to – make a query – change records in a table SELECT INSERT UPDATE DELETE returns the result of a query inserts new record into a table modifies records in a table deletes records from a table