Databases (02- Fundamentals (SQL: Structured Query Language (CRUD),…
SQL: Structured Query Language
Transaction: all steps must be done or reverted. Example: I give money, I should get the product.
many to many- author>book
to create many-to-many relationship, you need to introduce a new table, a linking table, author>authorbook>book
one to many- one customer can have many orders, category>products, department>employees, classroom>students
Primary keys becomes Foreign keys in other table to define relationship
primary key is a unique value for each row of data
Keys are use to define relationship between tables. Example Customer & Order table.
Database is a collection of tables. Tables has rows and columns. Each column has a type.
DDL (Data Definition Language): CREATE, ALTER, DROP
INSERT, UPDATE, DELETE
COUNT(*), MAX, MIN, AVG, SUM
SELECT * from Product ORDER BY Price ASC / DESC
Check for null: SELECT * from Employee WHERE MiddleName IS NULL / IS NOT NULL
LIKE clause: SELECT * from Employee WHERE LastName LIKE 'Black%'
AND or OR for conditional logic
use single = sign and not ==
use single quotes for strings
01- Understanding Databases
Relational DBMS, NoSQL DBMS
DBMS helps us create & manage databases
Oracle, MySql, DB2, mongodb etc are database management systems. They are not databases.
Why we need a database?: size, ease of updating, accuracy, security, redundancy, importance
data can be saved in file, excel etc organized in folder structure
Examples of Data: Customers, products, employees, audio, video etc
07- Indexing & Optimization
Stored Procedure- collection of queries stored into a database. Like functions.
Stored Procs helps avoid SQL Injection
CREATE PROCEDURE <<NAME>>
Non-clustered Index- sorting a column for easy access
one column for a table
Example: primary key column
Index is similar to index at the back of the book
Example: Look for a word like "query" and get the page numbers where the same word is used.
03- Database Modeling
Choosing Primary Keys
Identifying Columns & datatypes
Examples: Customer, Order, Order Item, Product, Comment, Blog Post, Author, Paycheck, Employee, Department etc
Planning your database
Q's: Whats the point?
What do you already have?
08- Database Options
XML & Object Oriented DB's
BaseX, Sedna, eXist / Objectivity, Versant, VelocityDB
Oracle, DB2, MySQL, SQL Server
Filemaker, Access: Desktop databases
-: Many users, large data, website database
+: Targeted for business user, easy to use, template starters, database & UI Tools, reporting options
primary key used as a foreign key should exist
create a linking table
Add a primary key as a foreign key to the table on many side
third normal form
non-key fields shouldnt depend on other non key fields
second normal form
related to composite primary key
first normal form
one column should have single unit of data
Example: create a new table with one-to-many relationship