13b Designing Databases(Projects).md

Chapter 13b Designing Databases(Projects)

Project: Flight Booking System

ch.13-PROJECT Flight Booking System

Solutions: Conceptual Model

Solutions: Logical Model

Project: Radio Rental Application

ch13-PROJECT Video Rental Application

Solutions: Conceptual Model

Solutions: Logical Model

Creating Databases

Create

CREATE DATABASE IF NOT EXISTS sql_store2;

Delete

DROP DATABASE IF EXISTS sql_store2;

Creating Tables

CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;
DROP TABLE IF EXISTS customers;
CREATE TABLE IF NOT EXISTS customers 
(
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50) NOT NULL,
    points      INT NOT NULL DEFAULT 0,
    email       VARCHAR(255) NOT NULL UNIQUE
);

Altering Tables

ALTER TABLE customers
    ADD last_name VARCHAR(50) NOT NULL AFTER first_name,
    ADD city      VARCHAR(50) NOT NULL,
    MODIFY COLUMN first_name VARCHAR(55) DEFAULT '',
    DROP points;

Creating Relationships

DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
	order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY fk_orders_customers (customer_id)
	    REFERENCES customers (customer_id)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);

Altering Primary/Foreign Key

ALTER TABLE orders
	ADD PRIMARY KEY (order_id),
    DROP PRIMARY KEY,
	DROP FOREIGN KEY fk_orders_customers,
    ADD FOREIGN KEY fk_orders_customers (customer_id)
		REFERENCES customers (customer_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE

Character Sets and Collations

Character set: A table that map each character to number.

SHOW CHARSET

Sometimes need change the character sets size for a smaller storage size. Example(database/table/column):

ALTER DATABASE db_name CHARACTER SET latin1;
    
ALTER TABLE table1 CHARACTER SET latin1;

CREATE TABLE IF NOT EXISTS customers 
(
	customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50) CHARACTER SET latin1 NOT NULL,
    points 		INT NOT NULL DEFAULT 0,
    email		VARCHAR(255) NOT NULL UNIQUE
);

Storage Engines

MylSAM for previous version database(5.5 and earlier). InnoDB for current version.

SHOW ENGINES

Change engine:

ALTER TABLE customers
ENGINE = InnoDB