Relational DBs and SQL Designing Your Web Database (Ch. 8)

Relational DBs and SQL • Designing Your Web Database (Ch. 8) • Creating and Working with a MySQL Database (Ch. 9, 10)

Relational DBs and SQL • Topics: • Relational database concepts and terminology • Designing your web database • Web database architecture – three-tier web applications • SQL → MySQL, command line • Creating tables • Inserting data into the database • Updating records from the database • Deleting records from the database • Retrieving data from the database • Dropping tables

The Relational Model • A RDBMS exposes to the user: • A collection of independent tables • A set of relationships between the tables • A powerful and flexible 4GL, called the SQL language (Structured Query Language),for managing and querying data in tables.

The Relational Model • The database schema for the Book-O-Rama database we use in chapters 9 and 10: • Customers (CustomerID, Name, Address, City) • Orders (OrderID, CustomerID, Amount, Date) • Books (ISBN, Author, Title, Price) • Order_Items (OrderID, ISBN, Quantity) → composite PK, 2 FKs to Orders and Books! • Book_Reviews (ISBN, Reviews) → a PK which is also a FK!

MySQL • After designing the relational DB, you need a RDBMS to implement, populate, and query your database → we use MySQL • Installed on cscdb.nku.edu server • The MySQL DBMS: • Can support many different databases → usually there is one database per application Each one of you has a database called db_yourusername (ex db_campana1) • Can have many users, each has an account & password → usually at least one user per web application Each one of you has an account yourusername (ex campana1) with same password as for FTP access

MySQL • The MySQL DBMS: • Supports a sophisticated privilege system. Privilege = the right to perform a particular action on a particular db object (table, index etc) and is associated with (aka granted to) a particular user. • Principle of least privilege = a user should have the lowest level of privilege required to perform his assigned task. • Each one of you has been granted: • The privilege to connect to the MySQL server running on cscdb.nku.edu • The privileges to create/drop tables and execute insert/delete/update/select commands on tables in your own database only!

MySQL – Connect and Log In • A command line client, for setting up tables … • Find client mysql.exe at http://www.nku.edu/~frank/csc301/Handouts/mysql.zip. → download, set up path environment variable • Connect from a command line interface (= command window) → mysql –h cscdb.nku.edu –u yourusername –p → password will be automatically requested → • Go to your database with command use db_yourusername; → • You can issue now SQL commands to create tables, manipulate and query data → all commands should end with “;” ! • Web script connections – later!

SQL • Structured Query Language • = the “programming language” for relational databases • SQL is a nonprocedural language = the user specifies what must be done (ex. create a table), but not how it is to be done. • SQL Standards: SQL-99 or SQL3 → portability • Several SQL dialects exist, in different RDBMSs; minor differences among them.

SQL • SQL is a command language; its functions fit into two broad categories: • Data definition language (DDL)→ commands to: • Create database objects, such as tables, indexes, and views • Define access rights to database objects • Data manipulation language (DML) → commands to insert, update, delete, and retrieve data within database tables • At the heart of SQL is the concept of a query=any SQL command (statement) that must be executed; can be: • a question that must be answered • an action to be executed (e.g. adding or deleting table rows).

SQL – Creating DB Tables • Use CREATE TABLE command for each db table, basic syntax: CREATE TABLE tablename ( column_1 data_type [constraints] [, column_2 data_type [constraints], … column_k data_type [constraints] ] [, PRIMARY KEY (column_i [, column_j …]) ] ); • Notes: • End each SQL command with a semicolon to execute it; • A comma separates all table element (column, PK) definitions; • […] indicates an optional parameter / part of the statement

SQL – Creating DB Tables • Constraints: • not null = all the rows in the table must have a value in that column → all primary key columns should be not null; are automatically. • auto_increment = if column set to null or no value provided for it when inserting a new row, MySQL sets it to (last value generated for column | max value) + 1 → can be used for at most one integer column in a table (usually for the PK) • primary key • unsigned = only values  0 allowed → for an integer type only

SQL – Creating DB Tables • Example: CREATE TABLE CUSTOMERS ( customer_id int unsigned not null auto_increment primary key, name char(50) not null, address char(100) not null, city char(30) not null ); • A SQL script with all Book-O-Rama db table definitions: • http://www.nku.edu/~frank/csc301/Examples/MySQL/bookorama.sql • To run a SQL command file sql_cmd_file.sql: mysql –h host –u username –D database –p < sql_cmd_file.sql

MySQL Identifiers • Most identifiers have a 64-character length limit • Identifiers for: • Databases – databases in MySQL map to directories in the underlying OS file structure • Tables – tables in MySQL map to files in the underlying file structure  case-sensitivity for database and table names is same as OS’s  names & chars allowed: those allowed for directory/file names in OS • Columns, indexes , aliases • identifiers are case insensitive, but different cases not allowed in the same SQL query • most ASCII chars allowed, but recommended to use letters and “normal” & meaningful names

MySQL Identifiers – cont. • Do not use the /, \, . characters in database and table names • Avoid using mathematical symbols in column names, such as +, - • CUSTOMER-ID error! • Avoid using SQL reserved words as column names; • Reserved words = words used by SQL to perform specific functions; for example, CREATE.

MySQL – Common Column Data Types • Numeric types TypeOptionsDescription INT UNSIGNED An integer  between +/- 2 billion or 0 to 4 billion if UNSIGNED ZEROFILL adds zeros on left to pad to full field width TINYINT -same- -127.. 128 or 0..255 if UNSIGNED FLOAT (precision) Floating point nr. Precision: 4 or 8 (# bytes) FLOAT(width, #decimals) Same as FLOAT(4) with specified display width and # of decimal places DOUBLE(width, #decimals) Same as FLOAT(8) with specified display width and # of decimal places

MySQL – Common Column Data Types • Date and Time types TypeDescription DATE Date in format YYYY-MM-DD DATETIME Date and time in format YYYY-MM-DD HH:MM:SS • String types Type OptionsDescription CHAR (maxl) BINARY A string 'maxl' characters long; fixed length. maxl: 0 to 255 Default is case-insensitive; BINARY makes the field case-sensitive. VARCHAR(maxl) BINARY A string 'maxl' characters long; variable length. TEXT A text block up to 65535 characters long. ENUM('val1','val2','val3‘ ...) A string column that can only have values from the specified list or NULL.

MySQL - SHOW and DESCRIBE • After you create the tables in your database, you can view: • All tables in the currently used database with: show tables; • The structure of any of the tables in the current database with: describe table_name;

SQL – Deleting DB Tables • Use DROP TABLE command for each db table you want to delete; basic syntax: DROP TABLE tablename; • Note: • This command deletes all the rows in the table tablename and the table tablename itself!

Relational DBs and SQL Designing Your Web Database (Ch. 8)