Database Modeling: Algebra, SQL & Lifecycle

• The lecture covers database and data modeling concepts for Week 12.
• Key topics include relational algebra operations, SQL implementations, and database development lifecycles.

Relational Algebra vs. SQL

• Theoretical Relational Algebra forms the basis for performing operations in SQL.
• Covered operations: SELECTION, PROJECTION, UNION, DIFFERENCE, PRODUCT, and INTERSECTION.
• Joins, including EQUIJOIN, NATURAL JOIN, and OUTER JOIN (LEFT, RIGHT, FULL), are also discussed.

Expanded Database Development Lifecycle: CONMAN_II

• This lifecycle includes creating a data model, transposing it into relations, and normalizing these relations.
• The process involves creating the relational schema within DBMS, which includes the database and tables.
• Order matters when creating tables, start with those that don't have foreign keys.
• The lifecycle continues with defining forms, queries, reports, and menus and then populating the database with user data, followed by maintenance.

Relation Characteristics and Querying

• Every cell in a relation must hold a single value.
• Relations do not allow repeating groups and all values for an attribute must be of the same logical and physical domain.
• Tuples must be unique and each attribute must have a unique name within the relation.
• The order of tuples and attributes is unimportant.

Ways to Query Data in Relations

• Relational Algebra is similar to sets, where operations can be represented by Venn diagrams and relations can be manipulated using operations (procedural).
• Relational Calculus, derived from predicate calculus, is non-procedural and is mainly used for proofs.

Relational Algebra Components

• Consists of variables representing relations.
• Operators are used to represent actions taken with these relations.
• The output of these operations must meet the characteristics of a relation.

Theoretical Relational Algebra Operators Classes

• Operators remove parts of a relation through Selection and Projection.
• Selection results in a relation with tuples meeting specified criteria.
• Projection results in a relation with only the specified attributes.
• Set-based operators include Union, Intersection, and Difference.
• Operators also join tuples in relations, such as Product and Joins.

Projection Operator

• Produces a new relation with selected columns.
• Projection can rearrange the order of columns.
• Duplicate tuples are removed from the result.

Selection Operator

• SQL DISTINCT can remove duplicate rows.
• It specifies the conditions the tuple has to meet to appear in the set.
• Conditions can use operators like =, >, <, ≥, as well as negation (¬), OR (V), and AND (A).

Union Operator

• Combines two relations to produce a relation with all tuples from each original relation.
• The relations must be Union Compatible.
• The union operation is commutative, meaning the order of relations does not matter.

Union Compatible

• Each relation must have the same number of attributes.
• Attributes must be in the same order, with corresponding attributes from the same physical and logical domains.

Difference Operator

• The result includes tuples from the first relation that are not in the second relation.
• Relations must be union compatible for the difference operation.
• The difference operator is NOT commutative.

SQL Subqueries

• Subqueries can be used to query data
• They can have many nested subqueries.
• Subqueries should be in parentheses and can be part of the WHERE clause.
• The datatype returned by the subquery must match the referenced field in the WHERE clause.
• The projection of attributes is limited to attributes from the outermost query.

Intersection Operator

• Results in a relation containing tuples present in both relations being intersected.
• Relations must be union compatible, and the operation is commutative.

Product Operator

• Combines each tuple from one relation with each tuple from another.
• It is also known as the Cartesian or cross product.
• Primarily used as part of other operators like joins.

Join Types

• Includes a combination of a relational algebra product and selection.
• Equijoin matches specified attributes using an equality test.
• Natural join is similar to equijoin but only one of the "joining" attributes appears in the final result.
• Outer joins (Left, Right, Full) are directional joins.

Join Types Details

• EQUIJOIN and NATURAL JOIN select tuples where the "ON" attributes have non-null values in both relations.
• LEFT OUTER JOIN selects tuples where the "ON" attributes have non-null values in the first relation.
• RIGHT OUTER JOIN selects tuples where the "ON" attributes have non-null values in the second relation.
• FULL OUTER JOIN selects tuples where the "ON" attribute has non-null values in either relation.

Joins in SQL

• SQL allows the combining of data through joins, connecting data from multiple tables using primary/foreign key relationships.

Outer Joins

• Cases exist where you want to look at all tuples from one relation and only matching tuples from another relation, outer joins allow that.
• EMP LEFT OUTER JOIN (EMP.dept# = DEPT.Dept#) DEPT is similar to inner join, except join type is changed.
• Left Outer Join gets tuples from the left-hand side relation and any matching rows from the right-hand side relation.
• In case there are no matching tuples from the right side, NULL will appear for "right side" attributes.
• Right Outer Join gets all tuples from the relation on the right side of the statement and any matching tuples from the relation on the left side.
• In case there are no matching tuples on the left side, attribute values for "left side" are NULL.
• Full Outer Join combines left and right outer joins into a single result set.
• Where the tuples match based on the join criteria, the values from both relations will appear and NULL values will appear for attributes from either relation where the join conditions were not met.