Database

Keys

• Primary key

  • The primary key consists of one or more columns whose data contained within is used to uniquely identify each row in the table.

• Foreign key

  • A foreign key is a set of one or more columns in a table that refers to the primary key in another table.

• Composite key

  • a combination of two or more columns in a table that can be used to uniquely identify each row in the table.

Data Types

• Strings

  • CHAR

  • VARCHAR(200)

• DATE/DATETIME

• Numbers

  • DECIMAL(3,2)

  • INT(6)

Relational

One record associates with another record

one-to-many

many-to-many

linking table of two one-to-many tables

one-to-one

Database Optimization

First normal form (1NF) enforces these criteria:

• Eliminate repeating groups in individual tables

• Create a separate table for each set of related data

• Identify each set of related data with a primary key

Second normal form (2NF)

A relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key.

Bad:

Good:

Query

CREATE TABLE Customers (
Customer ID INT(6) NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(200) NOT NULL,
LastName VARCHAR(200) NOT NULL,
State VARCHAR(2),
Email VARCHAR(200),
Birthday DATE,
FavoriteDish INT(6) REFERENCES Dishes(DishID),

PRIMARY KEY(CustomerID)
)

SELECT FirstName, State FROM Customers WHERE State="CA" OR State="CO";
SELECT * FROM Reservations WHERE `Date` > "2019-02-06" AND `Data` < "2019-02-07";
SELECT SUM(Price), AVG(Price), MIN(Price), Max(Price) FROM Dishes;

SELECT FirstName, FavoriteDish, Dishes.`Name` FROM Customers
JOIN Dishes ON Customers.FavoriteDish = Dishes.DishID

Advanced Query Example:

// Update a record
UPDATE Customers SET Email = "[e-mail]" WHERE CustomerID=1;

// Delete a record
DELETE FROM Customers WHERE CustomersID=26;