exam2 copy

A primary key is a unique identifier for each record in a table, while a foreign key is a field that refers to the primary key of another table.
When creating tables, you should identify the fields that are unique to each record, and make those fields the primary key.

To establish a relationship between two tables, you need to identify a field in one table that refers to the primary key of the other table. For example, if you have a table of orders and a table of customers, you might use the customer ID as a foreign key in the orders table to link the two tables together.

Normalization is the process of organizing data in a relational database to minimize redundancy and dependency. The general idea behind normalization is to break down a larger table into smaller, more manageable tables with specific relationships between them.
Normalization is important for a number of reasons. First, it reduces data redundancy, which can save storage space and reduce the likelihood of errors in the data. Second, it ensures that each table has a single purpose or "responsibility," making the database easier to understand and maintain. Third, it helps to prevent data anomalies and inconsistencies that can arise from storing data in a denormalized form.

First Normal Form (1NF): Each column in a table contains only atomic values, meaning each value cannot be further divided into smaller parts.

Second Normal Form (2NF): Every non-key attribute is fully dependent on the primary key. It means that there should not be any partial dependencies on any part of the primary key.

Third Normal Form (3NF): Every non-key attribute is dependent only on the primary key and not on any other non-key attribute. In other words, there should not be any indirect dependencies (col 1 depends on col 2 which depends on col 3, so col 3 has transitive dep on col 1) between non-key attributes.

COUNT: Counts the number of rows in a table or a subset of rows that meet a certain condition.
SUM: Calculates the sum of all values in a column or a subset of rows that meet a certain condition.
AVG: Calculates the average of all values in a column or a subset of rows that meet a certain condition.
MAX: Finds the maximum value in a column or a subset of rows that meet a certain condition.
MIN: Finds the minimum value in a column or a subset of rows that meet a certain condition.

You typically need to use a GROUP BY clause when you want to aggregate data based on one or more columns in your table. For example, if you want to find the total sales for each salesperson in a table of sales transactions, you would use the GROUP BY clause to group the transactions by salesperson and calculate the sum of their sales.

You can recognize the need for a GROUP BY clause by asking yourself whether you need to aggregate data based on one or more columns in your table. If you need to calculate the sum, count, or average of a column for each distinct value in another column, you will need to use a GROUP BY clause.

You would need to use multiple group columns when you need to aggregate data based on more than one column in your table. For example, if you want to find the total sales for each salesperson in each region, you would need to group the transactions by both salesperson and region.

You can recognize the need for multiple group columns by asking yourself whether you need to aggregate data based on multiple criteria. If you need to calculate the sum, count, or average of a column for each distinct combination of values in two or more columns, you will need to use multiple group columns.

WHERE clause is used to filter rows based on a condition, while the HAVING clause is used to filter groups based on a condition. The WHERE clause is used with the SELECT statement to specify which rows should be retrieved, while the HAVING clause is used with the GROUP BY clause to specify which groups should be selected.

A table-valued subquery is a subquery that returns a table instead of a single value. The point of using a table-valued subquery is to use the results of the subquery as a table in another query.

For example, if you want to find the total sales for each salesperson who sold more than the average sales for their region, you would use a table-valued subquery to find the average sales for each region and then join that table with the table of sales transactions.

Table-valued subqueries can be used to simplify complex queries and reduce the need for temporary tables or multiple queries.

You would use a CASE statement when you need to perform conditional logic in your SQL query. For example, if you want to categorize customers based on their purchase history, you might use a CASE statement to create a new column with a category for each customer based on their total spending.

You would use an EXTRACT statement when you need to extract a specific part of a date or time value. For example, if you want to find the number of orders placed in each month, you might use the EXTRACT statement to extract the month from the order date column.

Both CASE and EXTRACT statements can be useful for manipulating and transforming data in your SQL queries.

FROM clause: The query will start by retrieving the data from the tables specified in the FROM clause.
JOIN clauses: If there are any JOIN clauses specified in the query, the relevant tables will be joined according to the specified conditions.
WHERE clause: The query will filter the rows based on the conditions specified in the WHERE clause.
GROUP BY clause: If there is a GROUP BY clause specified in the query, the data will be grouped by the specified columns.
HAVING clause: If there is a HAVING clause specified in the query, the grouped data will be filtered based on the conditions specified in the HAVING clause.
SELECT clause: The query will select the columns specified in the SELECT clause.
ORDER BY clause: If there is an ORDER BY clause specified in the query, the data will be sorted according to the specified columns.
LIMIT clause: If there is a LIMIT clause specified in the query, the query will return only the specified number of rows.

• Identify the attributes of the class and their data types
• Determine a suitable name for the table
• Create a table with columns corresponding to the attributes
• Set the primary key for the table

• Identify the entities involved and their attributes
• Determine the primary key for each entity based on the unique identifier
• Ensure that the primary key does not change over time

One-to-One Association:
Each record in the first table is associated with only one record in the second table, and vice versa. We can use a foreign key in each table to link them together.

One-to-Many Association:
Each record in the first table can be associated with many records in the second table, but each record in the second table can only be associated with one record in the first table. We can use a foreign key in the second table to reference the primary key in the first table.

Many-to-Many Association:
Each record in the first table can be associated with many records in the second table, and vice versa. To implement this, we need a third table (junction table) that contains foreign keys referencing the primary keys in the two original tables.

• Identify the entities and their relationships
• Normalize the data model to eliminate redundancy
• Create tables for each entity and its attributes
• Create foreign keys to represent relationships between entities

• First Normal Form (1NF): Atomic values are stored in each cell of the table
• Second Normal Form (2NF): Every non-key attribute is fully dependent on the primary key
• Third Normal Form (3NF): Every non-key attribute is dependent only on the primary key

• Use the SELECT statement to retrieve data from a table
• Use the WHERE clause to filter rows based on a condition
• Use the LIKE operator for partial matching
• Use comparison operators (=, <>, >, <, >=, <=) to compare values
• Use logical operators (AND, OR, NOT) to combine conditions

• Use INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN to combine tables
• Use GROUP BY to group data based on a column
• Use HAVING to filter grouped data based on a condition
• Use COUNT, SUM, AVG, MIN, and MAX to calculate aggregates
• Use subqueries to perform queries within queries

• Identify any duplicated data or tables that can be combined
• Normalize the data model to eliminate redundancy
• Check that foreign keys are correctly defined to ensure referential integrity

• Identify the most commonly used queries and the columns involved
• Choose the columns that are most frequently used in the WHERE or JOIN clauses
• Create an index on those columns to improve query performance

• Atomicity: Each transaction is all or nothing (Either all the changes made by the transaction are committed, or none of them are)
• Consistency: Each transaction preserves the integrity of the database (Rules of the schemea apply always)
• Isolation: Each transaction operates independently of others
• Durability: Each transaction is permanently stored in the database

• Vertical scaling: Adding more resources to a single server
• Horizontal scaling: Adding more servers to distribute the workload
• Sharding: Partitioning the data across multiple servers
• Replication: Creating redundant copies of the database on multiple servers