Database Design Basics

Database Design

• Database logical design involves determining the schema for a database application, including the number of tables, table names, number of columns per table, column names, and data types.
• Relational database management systems organize data in tables.
• Tables are multi-sets of rows or records, not lists.
• Rows or records contain fields that correspond to the table's columns.
• Columns or fields have a name and an implicit position determined by their order in creation statements.
• Columns or fields have a domain, which is a type with the possibility of a NULL value.
• Logical design involves selecting an appropriate schema for the database application.

Constraints

• A consistent state of the database adheres to the business rules defined by structural and integrity constraints in the schema.
• SQL supports five types of integrity constraints: NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.
• A primary key is a set of columns that uniquely identifies a record in a table.
• Each table can contain at most one primary key.
• An instance of a table cannot have two records with the same value or combination of values in the primary key columns.
• Primary key columns cannot contain null values.

Update and Delete Actions

• For more powerful generalization, triggers can be used.
• ON UPDATE CASCADE can cascade the update or deletion to other tables using foreign key relationships.
• Other options include NO ACTION, SET DEFAULT, and SET NULL, but these may cause chain reactions.
• It is generally considered good practice to constrain all attributes to be not NULL unless there is a compelling design or tuning reason for not doing so.
• It is generally considered good practice to defer all constraints that can be deferred.
• DELETE FROM deletes the content of a table without altering its definition.
• DROP TABLE deletes both the content and definition of a table.
• DELETE is used to delete the content of a table, while DROP is used to delete both the content and definition of a table.

Foreign Keys

• ON DELETE CASCADE propagates the update or deletion to other tables.
• A unique constraint on a column or combination of columns ensures that a table cannot contain two records with the same value in the corresponding column or combination of columns.
• According to SQL standards, PRIMARY KEY is equivalent to UNIQUE and NOT NULL, but there are differences in side-effects such as foreign-key, index, deferrability, etc.

Transactions

• Transactions in SQL or application code can be specified using blocks with keywords such as BEGIN, END, COMMIT, ABORT, and ROLLBACK.
• Integrity constraints are usually immediate (checked after each operation) but should ideally be deferred to be checked after the end of transactions.
• Most systems, like PostgreSQL, only allow certain constraints to be deferred.

Single Table Operations

• Basic Operations
  • A simple SQL query includes SELECT, FROM, and WHERE clauses.
  • SELECT clause indicates the columns to be output.
  • FROM clause indicates the tables to be queried.
  • WHERE clause indicates the conditions on the records.
  • The asterisk (*) is a shorthand for selecting all columns.

Duplicate Ordering

• Ordering columns may result in duplicate rows.
• Columns can be ordered in ascending (ASC) or descending (DESC) order.
• Default order is ASC.
• If ordering on all columns, the result is a distinct ordering
• If only ordering on a subset of columns, the unmentioned columns are in no particular order.

Boolean Operations

• De Morgan's Law: ¬(A ∧ B) ≡ ¬A ∨ ¬B and ¬(A ∨ B) ≡ ¬A ∧ ¬B
• The IN operator can be used to check if a value is within a set of values.
• NOT operator negates a condition.
• BETWEEN operator checks if the value is within a range.

Joins

• Outer Join
  • Conditions in the ON clause are not equivalent to conditions in the WHERE clause.
  • Conditions in the ON clause determine which rows are dangling.
• LEFT JOIN is a synonym for LEFT OUTER JOIN
• RIGHT JOIN is a synonym for RIGHT OUTER JOIN
• FULL JOIN is a synonym for FULL OUTER JOIN

Set Operators

• UNION, INTERSECT, and EXCEPT return the union, intersection, and non-symmetric difference of the results of two queries, respectively.
• Set operators eliminate duplicates unless annotated with the keyword ALL.

Aggregation

• Aggregate functions can be used in conditions, but not in WHERE clause.
• Aggregate functions are evaluated after groups are formed.
• HAVING clause is performed after GROUP BY clause.
• HAVING clause can only use aggregate functions, columns listed in the GROUP BY clause, and subqueries.
• The order of columns in GROUP BY clause does not change the meaning of the query.
• The logical groups remain the same.

Aggregation Functions

• The following query does not work in PostgreSQL (but works in SQLite with potentially incorrect result).
  • SELECT c.customerid, c.first_name, c.last_name, SUM(g.price) FROM customers c, downloads d, games g WHERE c.customerid = d.customerid AND d.name = g.name and d.version = g.version GROUP BY c.first_name, c.last_name;
• The above query works only because first_name and last_name are guaranteed unique.
  • Do NOT write such queries for readability and portability.

Grouping

• Renamed columns can be used in GROUP BY clause.
• The following query displays the number of downloads by country and year of birth (using EXTRACT).
  • SELECT c.country, EXTRACT(YEAR FROM c.since) AS regyear, COUNT(*) AS total FROM customers c, downloads d WHERE c.customerid = d.customerid GROUP BY c.country, regyear ORDER BY regyear ASC, c.country ASC;

Grouping

• The order of columns in GROUP BY clause does not change the meaning of the query.
• The logical groups remain the same.
  • SELECT c.country, EXTRACT(YEAR FROM c.since) AS regyear, COUNT(*) AS total FROM customers c, downloads d WHERE c.customerid = d.customerid GROUP BY regyear, c.country ORDER BY regyear ASC, c.country ASC;

Having

• SELECT c.country FROM customers c WHERE COUNT(*) >= 100 GROUP BY c.country;
• The query on the left finds the countries in which there are more than 100 customers.

Having

• The following query on the left finds the countries in which there are more than 100 customers.
  • SELECT c.country FROM customers c GROUP BY c.country HAVING COUNT(*) >= 100;

Creating Database Tables

• Entities are mapped to relations.
• Entity set attributes are mapped to attributes of the relation.
• Keys are mapped to the primary key when creating tables.
• Relationships are mapped to relations.
• The attributes of the relation consist of the attributes of the relationship set.
• The keys of the participating entities are also included.

Exceptions to Rules

• One-to-Many: One-to-many relationship sets, without a weak entity, can be represented as separate tables containing the participating entity keys.
• Weak Entity: Weak entities are not represented with a primary key in the relationship table.
• Entities in a one-to-many relationship with a weak entity can be represented with a single table containing the weak entity key as the primary key.
• The primary key of a weak entity must be foreign key of the strong entity in the relationship.
• The table represents the relationship, not the weak entity.
• When representing one-to-many relationships with a weak entity and a non-weak entity, the table should include the keys for both entities.
• The key in the table should be the combined key of the participating entities.
• When representing one-to-many relationships with a weak entity and a non-weak entity, the primary key of the table is not the key of the weak entity.
• The table should include the weak entity’s key.
• When representing a relationship between a weak entity and its strong entity, the non-weak entity must be represented in a different table. This table's primary key should be a foreign key in the relationship table.

Schema Translation Tables

• company table:

  • name: VARCHAR(64), primary key
  • address: VARCHAR(128)

• person table:

  • first_name: VARCHAR(32)
  • last_name: VARCHAR(32), primary key
  • address: VARCHAR(128) NOT NULL, primary key

• contract table:

  • start: DATE NOT NULL, primary key
  • end: DATE NOT NULL, primary key
  • first_name: VARCHAR(32), primary key
  • last_name: VARCHAR(32), primary key
  • name: VARCHAR(64), primary key
  • Foreign key (first_name, last_name) references person table
  • Foreign key name references company table.

• work_for table:

  • start: DATE, NOT NULL, primary key
  • end: DATE, NOT NULL, primary key
  • enumber: CHAR(8)
  • cname: VARCHAR(32)
  • Foreign key enumber references employee table.
  • Foreign key cname references company table.

• employee_work_for table:

  • start: DATE, NOT NULL, primary key
  • end: DATE, NOT NULL, primary key
  • enumber: CHAR(8), primary key
  • ename: CHAR(32), NOT NULL
  • cname: VARCHAR(32), NOT NULL
  • Foreign key cname references company table.

### Splitting Query

• Subqueries can be used in the SELECT, FROM, and WHERE clauses of a query.
• Subqueries in the SELECT clause must return a single column and a single row.
• Subqueries in the FROM clause can be used to create a temporary table for the query.
• Subqueries in the WHERE clause can be used to filter the results of a query.
• Common Table Expressions (CTEs) are temporary tables that only exist for the duration of a query.
• Views are pre-compiled queries that can be used like tables.
• Subqueries can be nested, meaning a subquery can be used within another subquery.

Nesting Query

• Subqueries can be used in the WHERE clause to filter the results of a query, using the IN, ANY, ALL, and EXISTS operators.
• IN operator evaluates to true if the value in the outer query is found in the subquery.
• ANY operator evaluates to true if at least one value in the subquery matches the value in the outer query.
• ALL operator evaluates to true if all the values in the subquery match the value in the outer query.
• EXISTS operator evaluates to true if the subquery returns at least one row.
• Subqueries can be correlated, meaning that the subquery relates to the outer query, often through a common column.
• Correlated subqueries are executed for each row of the outer query.
• Subqueries can be used in the HAVING clause to filter the results of a grouped query.

### Query Performance

• Queries with nested subqueries can be harder to read and understand than simpler queries.
• Nested subqueries can be inefficient if the database needs to execute multiple queries to calculate the results.
• It is often possible to rewrite nested subqueries as simpler queries that are easier to read and more efficient.
• Consider alternative methods to achieve the same results, such as joins or CTEs.

This quiz covers essential concepts in database logical design, including schema creation, table structures, and integrity constraints. You'll explore key elements like primary keys and data types, vital for relational database management systems.