- Normalization Rules
- First Normal Form (1NF)
Values in each cell should be atomic and tables should have no repeating groups.
- Second Normal Form (2NF)
No value in a table should depend on only part of a key that can be used to uniquely identify a row.
- Third Normal Form (3NF)
Value should not be stored if they can be calculated from another non-key field.
- Denormalization
The process of intentionally duplicating information in a table, in violation of normalization rules.
- Structured Query Language (SQL)
- Data Definition Language (DDL)
- Creating a table (CREATE)
CREATE TABLE Customers (
CustomerID INT(6) NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(200) NOT NULL,
LastName VARCHAR(200) NOT NULL,
Email VARCHAR(200),
City VARCHAR(200),
State CHAR(2)
Phone VARCHART(20) NOT NULL,
Birthday DATE,
FavoriteDish INT(6) REFERENCES Dishes(DishID),
PRIMARY KEY(CustomerID)
);
- ALTER
- DROP
- TRUNCATE
2. Data Manipulation Language (DML)
- Pulling Data
SELECT * FROM Customers;
- Narrow Query Result
a. SELECT FirstName, LastName, State FROM Customers WHERE State= “CA”;
b. SELECT FirstName, LastName, State FROM Customers WHERE State LIKE “C%”;
c. SELECT FirstName, LastName, State FROM Customers WHERE ‘Date’ > “2019-02-06” AND ‘Date’ < “2019-02-07”;
- Sorting Result
a. SELECT * FROM Reservations ORDER BY ‘Name’ ASC;
b. SELECT * FROM Reservations ORDER BY ‘Name’ DESC;
c. SELECT * FROM Reservations
WHERE ‘Date’ > “2019-02-06” AND ‘Date’ < “2019-02-07”
ORDER BY ‘Date’;
- Aggregate Functions
a. SELECT COUNT(FirstName) FROM Customers WHERE State= “CA”;
b. SELECT SUM(Price), AVG(Price), MIX(Price), MAX(Price) FROM Dishes;
- Join Tables
a. SELECT FirstName, LastName, FavoriteDish, Dieshes.’Name’ FROM Customers;
JOIN Dishes ON Customers.FavoriteDish = Dishes.DishID;
b. SELECT FirstName, LastName, Reservations.’Date’, Reservations.PartySize
FROM Customers
JOIN Reservations ON Reservations.CustomerID = Customers.CustomerID
ORDER BY Reservations.’Date’;
c. SELECT OrderDishes.OrderID, Orders.OrderDate, Customers.FirstName, Customers.LastName, Customers.Phone, GROUP_CONCAT(Dishes.’Name’ SEPARATOR ‘,’) AS Items,
COUNT(OrderDishes.DishID) AS Qty,
SUM(Dishes.Price) AS Total
FROM OrdersDishes
JOIN Dishes ON OrderDishes.DishID=Dishes.DishID
JOIN Orders ON Orders.OrderID=OrdersDishes.OrderID
JOIN Customers ON Orders.CustomerID-Customers.CustomerID
GROUP BY(Orders.OrderID);
- Modify Data
- Create New Data (INSERT)
INSERT INTO Customers(FirstName, LastName, Email, Phone)
VALUES( “Jane”, “Smith”, “jsmith2019@landonhotel.com”, “415-55-1234”);
- Updating Data (UPDATE)
Step 1: SELECT * FROM Customers WHERE FirstName=”Taylor” AND LastName=”Jenkins”; // Two rows of data pop up with same first name & last name.
Step 2: SELECT * FROM Customers WHERE CustomerID= 1;
Step 3: UPDATE Customers SET Email = “tjenkins@landonhotel.com” WHERE CustomerID = 1;
- Delete Data (DELETE)
DELETE FROM Cusomters WHERE CustomerID=26;
- Stored Procedures
- Are a series of commands stored on the database
- Allow reuse of long or detailed queries instead of writing them for each use
- Provide a safe way to deal with sensitive data
- Access Control and Compliance
- Consider business requirements when granting access to database and data
- Important to understand compliance regulations
- Personally Identifiable Information (PII) is strictly regulated in some regions and industries
- HIPAA in the US and GDPR in the EU have strict rules about how PII is handled
- SQL Injection: Type of attach that includes part of a SQL command entered as a value to hijack a query and change how it works.
- *Example:NORMAL:
FirstName: Ana
LastName: Lopez
INSERT INTO Customers (FirstName, LastName) VALUES(‘Ana’, ‘Lopez’);
HIJACK CODE:
FirstName: Tom
LastName: ‘); DROP TABLE Customers; —
INSERT INTO Customers (FirstName, LastName) VALUES(‘Tom’, ”); DROP TABLE Customers; –‘, ”); // Which will delete the whole table
- SQL Software
- Common Relational Database Management Systems (RDBMSs)
- Microsoft SQL Server
- Oracle
- dBase
- FileMaker Pro
- Microsoft Access
- MySQL
- MariaDB
- SAP HANA
- SQLite
- Desktop Database
- Used for smaller solutions
- Hosted on workstation
Examples: Access FileMaker Pro
- Enterprise Database
- Used by large number of people
- Services millions of interactions
Examples: SQL Server, Oracle, SAP HANA
- Solutions for different database sizes
- SQLite is great for mobile applications
- “Big Data” may require frameworks like Hadoop or Spark
- NoSQL (Not Only SQL)
- Unstructured Data
- Key-value pairs
- Graphs
- Objects
- Geographic data points
- Posts and comments -> Streamed data