Cheatography
Login Register

Keys. Relations. Relationships. SQL Data Types Cheat Sheet

A Programming › SQL cheat sheet in English by mehalyna

Cheat Sheet to the topic 'Keys. Relations. Relationships. SQL Data Types'

SQL Database
10,970 views 1,220 downloads 3 Jul 2023 History
PRs
Download

Relational DB - Main Terms

Relational Database (RDB):
A type of database that organizes data into tables with predefined relati­onships between them. Tables consist of rows (records) and columns (attri­butes) that store data in a structured manner.
Table:
A collection of related data organized into rows (records) and columns (attri­butes). Each table has a unique name and consists of a predefined set of columns with specific data types.
Row (Record):
A single entry or instance of data in a table. Represents a unique entity or record within the table. Each row consists of values corres­ponding to the columns defined in the table's schema.
Column (Attri­bute):
A named data element within a table that holds a specific type of data. Defines the type of inform­ation that can be stored in the column, such as numbers, text, or dates.
Schema:
The structure or blueprint of a database. Defines the tables, columns, data types, relati­ons­hips, and constr­aints within the database.
Query:
A request for data retrieval or manipu­lation from a database. Written in SQL (Struc­tured Query Language) to specify the desired data and the operations to be performed.

Normal­ization

1NF (First Normal Form): Ensures that each column in a table contains only atomic values (no repeating groups).
2NF (Second Normal Form): Requires meeting 1NF and ensuring that non-key attributes depend on the entire primary key.
3NF (Third Normal Form): Requires meeting 2NF and removing transitive depend­encies, ensuring that non-key attributes depend only on the primary key.

Keys

Primary Key: A unique identifier for each record in a table. Ensures data integrity and enables efficient data retrieval.
Foreign Key: A field in one table that refers to the primary key in another table. Establ­ishes relati­onships between tables.
Composite Key: A combin­ation of two or more columns that together form a unique identifier for a record.
Candidate Key: A column or set of columns that can uniquely identify a record in a table.
Surrogate Key: A system­-ge­nerated unique identifier used as a primary key, often an auto-i­ncr­emented number.

Keys

Keys

Relati­ons­hips:

One-to­-One: A relati­onship where each record in one table is related to exactly one record in another table.
One-to­-Many: A relati­onship where each record in one table is related to multiple records in another table.
Many-t­o-Many: A relati­onship where multiple records in one table are related to multiple records in another table. Requires a junction table.

Relati­onships

Relationships

SQL Data Types:

Numeric Data Types:
INT: whole numbers (e.g., 1, -5, 1000).
DECIMAL(p, s): fixed-­point numbers with a specified precision (p) and scale (s).
FLOAT(p): floati­ng-­point numbers with a specified precision (p).
Character Data Types:
CHAR(n): fixed-­length character strings with a maximum length of n charac­ters.
VARCHA­R(n): variab­le-­length character strings with a maximum length of n charac­ters.
TEXT: large variab­le-­length character strings.
Date and Time Data Types:
DATE: a date value in the format YYYY-M­M-DD.
TIME: a time value in the format HH:MM:SS.
DATETIME: a combin­ation of date and time values (YYYY-­MM-DD HH:MM:SS).
TIMESTAMP: a unique value that changes whenever the row is updated.
Binary Data Types:
BLOB: large binary objects (e.g., images, videos, files).
BINARY(n): fixed-­length binary strings with a maximum length of n bytes.
VARBIN­ARY(n): variab­le-­length binary strings with a maximum length of n bytes.

Comments

No comments yet.

Cheatography n. The art of mapping complex subjects into clear, instantly navigable reference guides.