CSC425

1NF

Atomic values (no multivalued columns), unique rows

2NF

No partial dependencies (non-PK columns fully dependent on the entire PK)

3NF

No transitive dependencies (non-PK columns don’t depend on other non-PK columns)and all non-PK columns are only dependent on the primary key.

4NF

No multivalued dependencies (move to new table and reference with key)and no partial dependencies between non-key attributes.

5NF/BCNF

Eliminate join dependencies, ensure all dependencies are candidate key based

Input Stored Procedures

Accepts parameters to insert/update data

Output Stored Procedures

Uses output variables to return data

Triggers

• Event-drive procedures fired after or before DML operations (INSERT, UPDATE, DELETE)

  • Used to enforce rules or automate changes

    CREATE TRIGGER ON table_name

    AFTER INSERT ON table_name

    FOR EACH ROW

    BEGIN

    — logic

    END;

Cursors

• Process rows one at a time

• Steps: Declare, Open, Fetch, Close, Deallocate

  • Useful in complex logic/aggregation

Case

• Used for conditional logic

  SELECT CASE WHEN condition THEN result ELSE default END

Replication

• The process of copying and maintaining database objects in multiple databases. It ensures data consistency and reliability across different locations.

• Can replicate specific table

Types of Replication

Snapshot, Peer-to-Peer, Transactional, Merge

Transactional uses distributor as repository

True

Transactional replication uses snapshot agents

True

SQLite Features

Supports: Triggers, COUNT and other aggregates, works on small devices

It is NOT Firebase

MongoDB Basics

• Table → Collection, Row → Document, PK → id_ (ObjectID)

• Commands:

  • db.posts.update({ }, {$set, $inc, $rename})

  • db.posts.remove({ })

  • db.posts.find({ })

    • $elemMatch, $search, createIndex({ title: ‘text’})

SQL Injection

• Valid: ‘OR 1=1 ;—

• Invalid: OR 1=1; —

Row-Level Security

• I can’t see my colleagues salary? (Correct)

• Manager can’t see, SSN hidden, I can’t see my own salary (Incorrect)

Roles and Permissions

• Uses scripts to add users, roles, and assign permissions

• Know syntax code examples for creating and managing roles in a database context, including granting and revoking access to various resources.In a database context, roles and permissions define the access levels and actions users can perform, ensuring data security and proper authorization protocols. Scripts are often used to automate the management of these roles.

Sharding

Splits DB horizontally (not normalization) to distribute data across multiple servers or instances, improving performance and scalability.

Miscellaneous

• MongoDB handles unstructured data

  • SQL server can use key-value for unstructured data

Normalization Example

• A123400 → multiple phone number

  Split into separate rows per phone number

Student Registration Table

Composite Key: StudentID, CourseID, SemID, Year, ProfID

• Analyze Functional dependencies