• Functions:
• Data Dictionary Management
• Data Abstraction and dependence removals
• Data Storage Management
• Performance Tuning
• Data Transformation and Presentation
• Security Management
• UAC
• Integrity Managemnt
• Backup and Recovery Management

1. Hierarchical
• Contains levels, parents and segments(children)
• 1:M
2. Network
• Allows a record to have more than one parent
3. Relational
• Uses relation tables, tuples and attributes
4. ERDs
• Graphical representations of entities and their relationships
• 
5. OOM
• Objects, attributes, classes, methods
6. Big Data
• 3 Vs
7. NoSQL
• Not Only SQL
• Relational vs Cassandra
• Relational:
• Data is stored in a database/schema which contains at least one table
• Tables can be joined
• Cassandra:
• Data is stored in a keyspace which contain column families
• Join is not supported
• Rows are not required to contain same number of columns
• No foreign keys

• 
• The minimum will always be on the inside and the maximum on the outside.
• A zero would indicate an optional relationship

• Define and manage DB structures
• Main SQL commands:
• CREATE
• Create DB objects
• CREATE TABLE Employee(empId Integer NOT NULL, empName Char(25) NOT NULL, CONSTRAINT empPk PRIMARY KEY(empID));
• ALTER
• Modify structure of existing DB objects
• ALTER TABLE Employee ADD CONSTRAINT empPk PRIMARY KEY(empId);
• DROP
• Delete existing objects

• Define, update and retrieve data
• Main SQL commands for defining and updating:
• INSERT INTO
• Add a new row
• INSERT INTO Employee (empId, salaryCode, lastName) VALUES (62, 11, 'Halpert');
• UPDATE
• Update rows
• UPDATE Employee SET phone = '123-867-5309' WHERE empId = 29;
• DELETE FROM
• Delete rows
• DELETE FROM Employee WHERE empId = 29;
• Main SQL commands for retrieving:
• SELECT
• Queries a table and returns result as a 2D relation table.
• SELECT empName FROM Employee WHERE empId = 29;
• SELECT * FROM Employee;
• * = All Columns
• DISTINCT
• Added to SELECT to suppress duplicates
• SELECT DISTINCT deptId FROM Employee;
• WHERE
• Allows matching
• =
• <> or !=
• >
• <
• >=
• <=
• IN
• SELECT empName FROM Employee WHERE deptId IN (4, 8, 9);
• AND
• Intersection
• OR
• Union
• NOT
• Exclusion
• BETWEEN
• Range
• LIKE
• Allows wildcard search
• %
• Match all
• _
• Match letter
• GROUP BY
• HAVING
• Same functionality as WHERE
• SELECT salespersonId, salespersonLastName, SUM(saleAmount) AS totalSales FROM Sales GROUP BY salespersonId, salespersonLastName HAVING SUM(saleAmount) >= 10000;
• ORDER BY
• ORDER BY empName DESC;
• ORDER BY empName ASC;
• Default
• COUNT, MIN, MAX, SUM, AVG, STDEV
• JOIN
• Combine data from multiple tables
• Outer Join:
• Left Outer Join
• SELECT empName, deptName FROM Employee E LEFT OUTER JOIN Department D ON E.deptId = D.deptId;
• Full Outer Join
• Right Outer Join
• Inner Join:
• Must have matching values from both tables
• Implicit join via WHERE:
• SELECT empName, deptName FROM Employee AS E, Department AS D WHERE E.deptId = D.deptId
• Explicit join:
• SELECT empName, deptName FROM Employee E INNER JOIN Department D ON E.deptId = D.deptId WHERE D.deptName NOT LIKE 'Account%';

• Create user accts and manages permissions

• Unique
• Primary Key
• Main key that identifies a row in a table
• never changing, never null
• Candidate Key
• Has the potential to become a primary key
• Smallest combination of columns for uniqueness
• Composite Key
• Composed of two or more columns
• Surrogate Key
• Added to a relation to act as the primary key
• Not a natural key
• Non-Unique
• Foreign Key
• A primary key that links multiple tables.
• Referential integrity ensures foreign keys refer to an entity
• DELETE, UPDATE, INSERT

Keys and Relationships

• 1:1
• Optional side will recieve the foreign key
• 1:M
• The Many side recieves the foreign key
• M:M
• The intersection has a composite key

• Filter, sort, group, and make simple calc
• Summaries, comparisons, classifications
• Report delivery crucial
• OLAP
• OnLine Analytical Processing
• Used for dynamically examining database data
• Input = dimensions; Output = measures
• Results in OLAP Cube/Report

• Analyze, predict and decision-making
• Uses sophisticated math and stats