SQL TUTORIALS

What is a Relational Database? What are it's Primary Components?

Summary:

• A Relational Database is a system comprised of tables that represent real-world objects and contain Attributes (Columns) and rows of data.
• A Table is a database object that represents an entity (such as products). Tables contain Columns (AKA Attributes) that describe each feature of the entity, Rows (or records) that represent a single data point, and an ID field to uniquely identify each record in the table.
• Structured Query Language (SQL) is the primary coding language used to query one or more tables in a relational database.
• Relationships are defined between the ID fields in various tables. The most common type of relationship is one-to-many. In this relationship, one record in Table A matches with multiple records in Table B.
• Another popular type of relationship is one-to-one. In this relationship, one record in Table A matches ONLY ONE record in Table B.
• Relationships are created using Primary Keys and Foreign Keys. A Primary Key is the field in the table that uniquely identifies each record, whereas a Foreign Key is the Primary Key in Table A being represented in Table B and is used to create an explicit relationship between the two tables.
• NOTE: An Index is used on commonly used fields to help speed up queries. These take each unique value in the column and stores it in a cached "lookup" table for reference. Don't worry, we'll go over indices in much greater detail in later tutorials.

What Are the Most Common Data Types Found in Relational Databases?

Summary:

• INT/INTEGER data types represent numerical data without decimal places. This data type can traditionally hold values between -2,147,483,648 and 2,147,483,647. The SMALLINT variation is used when you want to save space on disk or limit the range of values.
• DECIMAL/NUMERIC/FLOAT data types represent numerical data with decimal points. These are used interchangeably. These data types accept optional parameters called Precision and Scale. Precision refers to how many numbers can be shown in total (between the numbers before and after the decimal point), whereas Scale is used to specify the count of numbers allowed AFTER the decimal point.
• The CHAR data type represents textual data. This is used when you want the text to be a fixed length (such as a phone number or state abbreviation).
• The VARCHAR data types also represents textual data. This is used when you want the text to fit a variable length with a spcified maximum (much like Tweets).
• DATE/TIME/DATETIME/TIMESTAMP data types represent time-series data, or data that occurs along a timeline. This may include both date and time data or simply just dates or times separately.

How is Data in a Relational Database Kept Consistent?

Summary:

• First Normal Form states that all rows in a given table are unique and all cells (intersections between columns and rows) must contain only one value (no lists, arrays, or dictionaries).
• Second Normal Form states that once a database is in first normal form, all attribute values should be dependent on the primary key (not another common attribute). Rather than having a composite key (two columns that are used together as a Primary Key) that describes two different entities, it's better to separate the entities into two different tables and create a relationship.
• Third Normal Form states that once a database is in second normal form, all attributes must be dependent only on the primary key, not on other keys.

How are Database Relations Visualized?

Summary:

• DISCLAIMER: Don't worry about the shapes mentioned in the video, they differ between databases and are rarely used when designing ERDs.
• An Entity Relationship Diagram (ERD) is a visualization that describes the relationships between tables in a database.
ERDs are built on three main components: Entities, Attributes, and Relationships.
• An Entity is an object in an ERD that represents a table in the database. This table almost always represents real-world objects (such as students, courses, or products). A Weak Entity is what is created when splitting tables to fit your database to first or third normal forms. These are commonly known as lookup tables.
• Attributes are what describe the entity. For instance, a table titled "students" may have the following attributes: First Name, Last Name, Age, Gender.
• NOTE: When deploying ERDs in the real world, Key Attributes, or Primary/Foreign Keys, are designated with "PK" or "FK", respectively.
• NOTE: In the real world, Composite Attributes and Multivalued Attributes are rarely used as they do not conform to first normal form.
• NOTE: Although it's good to know about Derived Attributes, they are rarely pointed out in an ERD. They are typically shown as just another attribute.
• NOTE: In the real world, one-to-many and many-to-many relationships are most commonly visualized using Crow's Foot Notation. Crow's Foot Notation takes a single line from Table A and puts three "prongs" at the end (such as this: -------<-) where the "many" part of the relationship is shown. This allows the relationship to be shown intuitively.

Next Topic