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