DS220 Exam 1 Notes: Relational Databases & SQL

Posted on Oct 16, 2024 in Computers

DS220 Exam 1 Notes:

Ch. 1, 2, 3 Key Terms:

A Relation is a 2D table with rows and columns. Columns contain attributes, each with a unique name and the same datatype. Rows contain data about cases, cells hold one value, and no two rows can be identical.

Relational Databases join tables together using values to:

  • Reduce redundancy
  • Preserve relationships
  • Allow for partial data

Keys represent data in a relational database and are one or more columns that identify a row. Composite keys contain 2 or more columns. Candidate keys (stylized in italics) are potential primary keys, not used if a key is a subset. Primary keys are the relation’s sole main key. Surrogate keys assign a numeric identifier. Foreign keys establish a relationship by being a primary key from another relation. Each foreign key must match an existing primary key.

Determinants determine the value of other attributes. Candidate keys need to be a determinant, but a determinant need not be a candidate key. Tables should be split so every determinant is a candidate key. Normalization means making a relation well-formed with respect to determinants. Subtables need to be created if this is not the case.

EXAMPLE_TABLE(Col1, Col2, Col3, …, ColN)

Normalization Process:

  1. Identify all candidate keys
  2. Identify functional dependencies
  3. Examine determinants for functional dependencies
  4. If Relation not well-formed:
    1. Place all columns of dependencies in a new relation
    2. Make the determinant of the dependency the primary key of the new relation
    3. Leave a copy of the determinant as a foreign key in the original relation to bind them
  5. Repeat until normalized

SQL Basics:

Create:

CREATE TABLE Department (DepartmentID INT NOT NULL PRIMARY KEY IDENTITY(1,1), DepartmentName VARCHAR(100) NOT NULL, Location VARCHAR(50));

[Identity is the Surrogate Key. Arguments: FirstValue, IncrementValue]

CREATE TABLE Employee (EmployeeID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DepartmentID INT, HireDate DATE, Salary DECIMAL(10, 2), Email VARCHAR(100) UNIQUE,

CONSTRAINT FK_Department FOREIGN KEY (DepartmentID)

REFERENCES Department(DepartmentID) ON DELETE SET NULL ON UPDATE CASCADE);

[Unique ensures distinct non-duplicate values. Constraint creates a reference to a foreign key and references it to another table’s primary key. On Delete and On Update specify what happens to records associated with the child table and foreign key when the corresponding record in the parent table is deleted or updated. Arguments: Cascade – applies the same action to related records. Set Null – Sets the foreign key to NULL in child records if the parent is deleted. No Action]

Drop:

DROP TABLE Employee; DROP TABLE Department;

[Need to drop child tables before dropping parent tables to maintain data integrity]

Insert:

INSERT INTO Department (DepartmentID, DepartmentName, Location) VALUES (1, ‘Finance’, ‘Building A’);

Multiple Insert:

INSERT INTO Employee (EmployeeID, FirstName, LastName, DepartmentID, HireDate, Salary, Email) VALUES (1001, ‘Alice’, ‘Smith’, 1, ‘2020-05-15’, 75000.00, ‘alice.smith@example.com’);

Select/From/Where:

SELECT FirstName, LastName, Salary FROM Employee WHERE DepartmentID = 1;

SELECT FirstName, LastName, Salary FROM Employee WHERE Salary > 70000;

  • Common Where Operators: Equals (=), Not Equals (<>), Greater (>), Less (<), GTOE/LTOE (>=, <=)

Distinct:

SELECT DISTINCT DepartmentID FROM Employee;

[Removes duplicate values from the result set and returns only unique values]

In and Not-In for Subqueries:

SELECT FirstName, LastName FROM Employee WHERE DepartmentID IN (1, 2, 3);

SELECT PropertyName FROM Property WHERE PropertyID NOT IN (SELECT PropertyID FROM Service);

[IN specifies criteria to include, NOT IN returns all non-criteria]

Is Null:

SELECT FirstName, LastName FROM Employee WHERE DepartmentID IS NULL;

[Returns where a column contains null values]

Like:

SELECT FirstName, LastName FROM Employee WHERE LastName LIKE ‘B%’;

SELECT FirstName, LastName FROM Employee WHERE FirstName LIKE ‘A_i_e’;

[Searches for patterns in text, where % represents zero or more characters, _ for single characters]

Ordering:

SELECT FirstName, LastName, Salary FROM Employee WHERE Salary >= 65000 ORDER BY LastName DESC, FirstName ASC;

Group-By:

SELECT DepartmentID, COUNT(*) AS EmployeeCount FROM Employee GROUP BY DepartmentID;

[Groups records with the same values in columns and allows aggregate functions. The AS keyword assigns an alias to a column or table]

  • Common Aggregate Functions: COUNT, SUM, AVERAGE, MIN, MAX (column_name) FROM (table_name)

SELECT Department, SUM(Salary) AS TotalSalary FROM Employee GROUP BY Department;

Having:

SELECT Department, COUNT(*) AS EmployeeCount FROM Employee GROUP BY Department HAVING COUNT(*) > 10;

[Groups records with the same values in columns and allows aggregate functions. HAVING filters aggregate function results of GROUP BY]

Manual Join:

SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employee AS e, Department AS d WHERE e.DepartmentID = d.DepartmentID;

[This query joins Employee and Department by listing both in FROM and linking them with e.DepartmentID = d.DepartmentID in WHERE. It returns FirstName, LastName, and DepartmentName for employees with matching DepartmentIDs]