Capstone Database

What is RDBMS?

Relational Data Base Management Systems

ALTER table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

How to Normalize Data

first normal form, second normal form, third normal form

CRUD

Create, Read, Update, Delete

Main Goals: Normalization

1) Eliminate redundant data 2) Eliminate insert, delete and update anomalies

Why use a database?

1.Preserve data integrity - assurance that data is consistent, correct, and accessible 2.Eliminate data redundancy -unnecessary repetition of data that slows data processing and takes up space which is more costly 3.Limit data view (query, report) so that users only see what they need to see, as cleanly and clearly as possible 4. Efficiently stores and manages large amounts of data

Foreign Key

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

Normalization

A specific process that allows database architects to turn unstructured data into a properly designed set of tables and data elements

1NF

A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. AVG, MIN, MAX, COUNT, SUM

NOT Null

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

EF/Hibernate/Spring

Hibernate: Idiomatic persistence for Java and relational databases. Hibernate is a suite of open source projects around domain models. The flagship project is Hibernate ORM, the Object Relational Mapper; Entity Framework Core: *Lightweight and cross-platform version of the popular Entity Framework *. It is a lightweight, extensible, open source and cross-platform version of the popular Entity Framework data access technology. It can serve as an object-relational mapper (O/RM), enabling .NET developers to work with a database using .NET objects, and eliminating the need for most of the data-access code they usually need to write. Hibernate and Entity Framework Core can be primarily classified as "Object Relational Mapper (ORM)" tools. Entity Framework Core is an open source tool with 8.01K GitHub stars and 2.05K GitHub forks. Here's a link to Entity Framework Core's open source repository on GitHub.

REST

In a REST environment, CRUD often corresponds to the HTTP methods POST, GET, PUT, and DELETE, respectively.

What does RDBMS do?

It is a database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read more here)

SQL Constraints

NOT NULL - Ensures that a column cannot have a NULL value UNIQUE - Ensures that all values in a column are different PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table FOREIGN KEY - Uniquely identifies a row/record in another table CHECK - Ensures that all values in a column satisfies a specific condition DEFAULT - Sets a default value for a column when no value is specified INDEX - Used to create and retrieve data from the database very quickly

What is an ORM?

Object-relational mapping. Coverts data between incompatible type systems using object-oriented programming languages. Creates a "virtual object database" that can be used from within the programming language.

Scalar Functions

Operate on a single value and then return a single value. Used wherever an expression is valid.

REST Create

POST creates a new resource of the specified resource type.

REST-Update

PUT is the HTTP method used for the CRUD operation, Update. For example, if the price of Avocado Toast has gone up, we should go into the database and update that information. We can do this with a PUT request.

What does SQL Do?

SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

What is SQL?

SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

Table Data Types

String Data that contains a combination of letters, numbers, and special characters. CHARACTER: Fixed-length character strings. The common short name for this data type is CHAR. VARCHAR: Varying-length character strings. Numeric Data that contains digits. SMALLINT: for small integers. INTEGER: for large integers. BIGINT: for bigger values. DECIMAL(p,s) or NUMERIC(p,s), where p is precision and s is scale: for packed decimal numbers with precision p and scale s. Precision is the total number of digits, and scale is the number of digits to the right of the decimal point. DECFLOAT: for decimal floating-point numbers. REAL: for single-precision floating-point numbers. DOUBLE: for double-precision floating-point numbers. Datetime Data values that represent dates, times, or timestamps. DATE: Dates with a three-part value that represents a year, month, and day. TIME: Times with a three-part value that represents a time of day in hours, minutes, and seconds. TIMESTAMP: Timestamps with a seven-part value that represents a date and time by year, month, day, hour, minute, second, and microsecond.

CREATE database

The CREATE DATABASE statement is used to create a new SQL database.

REST-Delete

The CRUD operation Delete corresponds to the HTTP method DELETE. It is used to remove a resource from the system. Let's say that the world avocado shortage has reached a critical point, and we can no longer afford to serve this modern delicacy at all. We should go into the database and delete the item that corresponds to "Avocado Toast", which we know has an id of 1223.

DROP

The DROP DATABASE statement is used to drop an existing SQL database.

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Primary Key

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

SELECT

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements. -Each SELECT statement within UNION must have the same number of columns -The columns must also have similar data types -The columns in each SELECT statement must also be in the same order

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in each SELECT statement must also be in the same order

WHERE

The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition.

2NF

The entity should be considered already in 1NF, and all attributes within the entity should depend solely on the unique identifier of the entity. Taking a table which has a productID, product and a brand, to a 1) productID product 2) brandID brand 3) productbrandID, ProductID, brandID

3NF

The entity should be considered already in 2NF, and no column entry should be dependent on any other entry (value) other than the key for the table. If such an entity exists, move it outside into a new table. 3NF is achieved, considered as the database is normalized.

REST Read

To read resources in a REST environment, we use the GET method. Reading a resource should never change any information - it should only retrieve it. If you call GET on the same information 10 times in a row, you should get the same response on the first call that you get on the last call. GET can be used to read an entire list of items:

What is CRUD

When we are building APIs, we want our models to provide four basic types of functionality. The model must be able to Create, Read, Update, and Delete resources. Computer scientists often refer to these functions by the acronym CRUD. A model should have the ability to perform at most these four functions in order to be complete. If an action cannot be described by one of these four operations, then it should potentially be a model of its own.

Cross Join

produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.