1.5 DataBases GMETRIX

Entity Relationship Dia

Entities represent tables in a database

Attributes represent entity properties

Relationship are a vital part of an ERD as they show how entities relate to each other

Database Normalization

the process of organizing the attributes and tables of a relational database to minimize data redundancy

First Normal Form (1NF)

each field has a single value and each record is unique

Second Normal Form (2NF)

unmatched data is moved into another table and related to original table

Third Normal Form (3NF)

the prefizes would need to be broken out into another table as there could be duplicate values in the PREFIX field

Fourth Normal Form (4NF)

there are no multi-valued dependencies from one table to another

Indexing

Fields that are often used as criteria in queries should be considered for indexing as having tables indexed by those fields can make queries using those fields run faster.

Indexig is not usually based data type, length or field, or whether a field is a foreign key

Primary Keys

Ensures each record in a table in unique

Can consist of multiple fields

The key value cannot be null

Foreign Keys

Represents the many side of a one-to-many relationship

Can contains duplicate values unless it serves as the primary key for the table in which it resides

Referential Integrity

Ensures a value on a one side of a one-to-many relationship exists before the value can exist on the many side of a relationship

CHECK constraint

puts a limitation on the info that can be entered into a data field

CHECK (InterviewDate>=GETDATE())

DEFAULT

sets a default value for the field

GETDATE()

IN SQL Server is today's date

ItemID and Price fields

need to be broken out into another table with the ItemID staying in this table because the Price has a transitive dependency on the ItemID, meaning that if an ItemID is changed, the price has to be changed as well

Data Definition Language (DDL)

keywords control a database schema

CREATE

is a DDL keyword because it adds an object to a database

SELECT

a Data Manipulation Language (DML) keyword

VIEW

is an object

WHERE

serves as a conditional filter for a query

Records with that have an matching OrderIDs in both tables

The query is an inner order between the Orders and the OrderDetails tables, so only the records that have an OrderID appearing in both tables will display the query results

CREATE NONCLUSTERED INDEX NDX_Inventory_LocationID ON Inventory (LocationID);

A non-clustered index uses key-values to point to data rows, thus speeding up searches on fields that aren't primary key fields. An index is created on the table with the field being indexed inside parentheses. Thus, the correct code for this index is as follows:

CREATE NONCLUSTERED INDEX NDX_Inventory_LocationID ON Inventory (LocationID);

SELECT id, datePlaced, amount FROM Order

The retrieve only the id, datePlaced, and amount

UPDATE Inventory SET Location = 'San Jose' WHERE Location = 'Fremont'

In SQL, an UPDATE statement updates data using the keyword SET on a field inside the table. The WHERE clause sets the criteria for the values that need to be updated.

SELECT * FROM Product ORDER BY productName, id

In order to retrieve all the data from the table, you can use the asterisk * symbol

Relational Data Format

Is present in a SQL Server Database

SQL Server

is a relational database engine

Excel

builds flat files

Azure COSMOS DB

a schema-free database engine that stores data in key-value pairs

TRIGGER

enforces a business role by taking action on an insert, update, or delete

Stored Procedures, Functions, and Cursors

Are not based on actions

CURSOR

looks through and processes data on row-by-row basis

GOTO

goes to a specific block of code

WAITFOR

blocks execution of code until a certain time or for a certain amount of time

Data Manipulation Language (DML)

these keywords change data, but not the structure of it

ex. INSERT, DELETE

CREATE/DROP

are DDL keywords, where those keywords create and delete database objects such as tables and views

EXEC PROCEDURE selectshoes 'Kicks'

EXEC PROCEDURE selectshoes @brand = 'Kicks'

The EXEC command runs a stored procedure

A parameter can be specified by name, or with just the value following the order of the parameters within the stored procedures.

Thus, the EXEC PROCEDURE selectShoes @brand = 'Kicks and EXEC PROCEDURE selectShoes 'Kicks' both work.

Parameter values don't need to include parameters

CREATE PROCEDURE selectShoes @brand nvarchar(20) AS SELECT * FROM Shoes WHERE ShoeBrand = @brand;

The CREATE PROCEDURE keywords add a new stored procedure to a database. ADD adds pieces to existing objects. EXEC executes a stored procedure that has already been created

Scalar Functions

return a single value only

Table-Value Function

returns several fields of data

Multi-Value Function

Transaction Pieces

An Exclusive Lock helps provide concurrency to transactions

When a transaction is committed, all changes made within it take place

The SAVE TRANSACTION command defines at what point to which a transaction can return if part of the transaction is conditionally canceled.

Serializable transaction level

ensures other transactions with key-value pairs in the range of keys read by a current transaction are not processed until the current transaction completes

Snapshot transaction level

allows other transactions to make modifications

Repeatable Read transaction level

specifies statements that cannot read data that has been modified but not committed by other transactions but doesn't protect key values

Read Committed transaction level

specifies that data has been modified but not committed cannot be read

Transaction

combines multiple statements into one atomic action

cane be rolled back before it is committed

can contain select, insert, update, delete, create, and exec statements

Transaction Isolation Levels: Read Committed, Read Uncommitted

Allows two transactions to retrieve the same row in a database simultaneously

Connection Pooling: Pooler

maintains ownership of the physical connection and manages database connections

Connection Pooling: Server

Connection Pooling: Web Service

Connection Pooling: Data Manager

Language Integrated Query (LNQ)

a technology in which SQL code can be used inside of a programming language, such as C#, to retrieve data from a database

Document Database

is best suited for data that lacks consistent structure

Key-Value Database

stores data in key-value pairs

Relational Database

requires tables with set columns

Flat Database

a single large table with set columns

Key-Value Database

Allow for horizontal scaling of data

Its rows can have varying numbers of atttributes

Even though they aren't operational, they still use primary keys

NoSQL Databases:Document & Key-Value

Are both prominent NoSQL databases

Relational Databases

are more SQL oriented