CSC 3084: Web Development and Programming

1. CSC 3084:Web Development and Programming Chapter 17: How to Design a Database

2. Working with Databases • Common data types: INT (integer), DECIMAL (real number) VARCHAR or TEXT (plain text up to 65,353 chars), DATE, TIME, DATETIME • There is no boolean type, so you can use TINYINT instead, which can represent -127 through 128 • How to create a database:CREATE DATABASE my_guitar_shop2; • How to delete a database:DROP DATABASE my_guitar_shop2; • How to select a database to execute queries on:USE my_guitar_shop2;

3. Creating Tables CREATE TABLE customers ( customerID INT, firstName VARCHAR(60), lastName VARCHAR(60) ); CREATE TABLE customers ( customerID INT NOT NULL UNIQUE, firstName VARCHAR(60) NOT NULL, lastName VARCHAR(60) NOT NULL );

4. Creating Tables CREATE TABLE orders ( orderID INT NOT NULL UNIQUE, customerID INT NOT NULL, orderNumber VARCHAR(50) NOT NULL, orderDate DATE NOT NULL, orderTotal DECIMAL(9,2) NOT NULL, paymentTotal DECIMAL(9,2) DEFAULT 0 ); • DECIMAL(9,2)indicates we want a real number with at most 9 digits, two of which can be stored to the right of the decimal point

5. Creating Primary Keys • A column-level primary key is coded as part of the definition of the column • A table-level primary key is coded as if it were a separate column definition

6. Creating Primary Keys • A table with a column-level primary key: CREATE TABLE customers ( customerID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, emailAddress VARCHAR(255) NOT NULL UNIQUE ); • A table with a table-level primary key: CREATE TABLE customers ( customerIDINT NOT NULL AUTO_INCREMENT, emailAddress VARCHAR(255) NOT NULL UNIQUE,   PRIMARY KEY (customerID) );

7. Creating Primary Keys • A table with a two-column primary key: CREATE TABLE orderItems ( orderID INT NOT NULL, productID INT NOT NULL, itemPrice DECIMAL(10,2) NOT NULL, discountAmount DECIMAL(10,2) NOT NULL, quantity INT NOT NULL, PRIMARY KEY (orderID, productID) );