SQL Commands and Data Types

SQL Commands

• SELECT extracts data from a database.
• DELETE FROM table_name WHERE condition removes data from a table.
• CREATE TABLE table_name is used to make a new table.
• DROP DATABASE database_name deletes a database.
• UPDATE is a SQL command used to extract data.
• DROP TABLE removes a table.
• The VALUES keyword follows column names in the INSERT INTO syntax.
• The WHERE clause filters records based on a specified condition.
• UPDATE Employees SET Salary = 65000 WHERE EmployeeID = 2; will update an employee's salary.
• CREATE TABLE AS is an SQL statement that makes a new table based on an existing one.
• The WHERE clause specifies conditions for data retrieval or modification.

Data Types

• INT is a valid data type in SQL.
• VARCHAR is a valid data type in SQL.
• BOOLEAN is a valid data type in SQL.
• IMAGE is not a valid data type in SQL.
• VARCHAR stands for Variable-length Character.
• FLOAT stores floating-point numbers.

Primary and Foreign Keys

• A PRIMARY KEY uniquely identifies each row in a table.
• Setting a column as AUTO_INCREMENT automatically generates a unique numeric value for each new row, when defining a primary key.
• A foreign key references the primary key of another table.
• If a table is created from another table using "CREATE TABLE AS SELECT * FROM...", the primary key constraint is not copied and must be defined manually.
• A composite primary key consists of two or more columns.

ERD

• A rectangle represents an entity in an ERD
• An Oval/Ellipse represents attributes in a ERD
• A Diamond represents relationships in an ERD.
• Indexes are not a component of an ERD.
• Relationships represents the connections between entities in ERDs
• Cardinality indicates the number of occurrences of an entity in a relationship within an ERD.

Normal Forms & Dependencies

• Second Normal Form ensures all non-key attributes are fully dependent on the primary key.
• The primary goal of normalization is to minimize data redundancy and dependency.
• Anomalies in databases is an error resulting from data redundancy.
• First Normal Form (1NF) ensures each column contains only individual values.
• "Atomic value" refers to an attribute that cannot be further subdivided.
• Third Normal Form (3NF) eliminates transitive dependency.
• In the context of normalization, a partial dependency occurs when a non-key attribute depends on only part of a composite primary key.
• Transitive dependency happens when a non-key attribute depends on another non-key attribute.

Relationships

• A customer and their orders is an example of a one-to-many relationship.
• In a one-to-one relationship, one entity is related to only one other entity, and vice versa.
• The example "A student can enroll in multiple courses, and each course can have multiple students" represents a many-to-many relationship.

Additional

• Data redundancy means storing the same data in multiple locations.
• More than one value in cells is a common issue with cells in UNF tables.
• Poor maintenance causes anomalies in a database.
• Anomalies are typically categorized into three main types.
• Combining columns on a Composite Primary key ensures uniqueness.