Entity Hierarchies, Keys, and SQL Query Techniques in Database Design

specialization hierarchy

Entity supertypes and subtypes are organized, which depicts the arrangement of higher-level entity supertypes and lower-level entity subtypes

Inheritance

-enables an entity subtype to inherit attributes and relationships of the supertype−

-All entity subtypes inherit their primary key attribute from their supertype

Primary Key

attribute(s) chosen to uniquely identify an entity, supports entity integrity

Composite Key

primary key made of more than one attribute. Useful for M:N relationships or weak entities

Disjoint subtypes

-contain a unique subset of the supertype entity set

Candidate Key

an attribute that could be the primary key (unique, but not chosen).

completeness constraint:

specifies whether each supertype occurrence must also be a member of at least one subtype

Partial completeness

that not every supertype occurrence is a member of a subtype

Total completeness

that every supertype occurrence must be a member of at least one subtypes

Specialization

-the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype

− is based on grouping unique characteristics and relationships of the subtypes

Generalization

the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes

− Generalization is based on grouping common characteristics and relationships of thesubtypes

entity cluster

a "virtual" entity type used to represent multiple entities and relationships in the ERD

A natural key

key or natural identifier: is a real-world identifier used to uniquely identify real-world objects

− It is familiar to end users and forms part of their day-to-day business vocabulary

-Usually, a data modeler uses it as the primary key of the entity being modeled

surrogate key

-system-generated key (usually numeric) used when natural key is unsuitable.

-a primary key created by the database designer to simplify the identification of entity instances

- has no intrinsic meaning, values for it can be generated by the DBMS to ensure that unique values are always provided

Surrogate keys are useful in the following situations:

− When there is no natural key

− When the selected candidate key has embedded semantic contents

− When the selected candidate key is too long

Foreign key

-attribute in one table that references the primary key in another table to implement relationships

-work with primary keys to properly implement relationships in the relational model

Time-variant data

data whose values change over time and for which a history of thedata changes must be retained

Precedence

Order of evaluation of arithmetic operators in SQL (multiplication/division before addition/subtraction). Used when making computed columns.

Normalization

evaluating + correcting table structures to minimize data redundancies and eliminate anomalies

— by assigning attributes to tables based on functional dependencies and moving relations through normal forms (1NF → 2NF → 3NF).

Denormalization

intentionally reversing normalization to increase performance, but it increases redundancy and requires more I/O operations.

The main goal of normalization

Because it eliminates unnecessary redundancy → which eliminates update / insertion / deletion anomalies and keeps tables well-formed.

Which form of normalization do we want?

An objective is to ensure that all tables are in at least in 3NF

- Higher forms, such as 5NF and DKNF are not likely to be encountered in business environment

partial dependency

exists when there is a functional dependence in which the determinant is only part of the primary key

-The assumption is that there is only one candidate key

transitive dependency

exists when the attribute is dependent on another attribute that is not part of the primary key

− They occur only when a functional dependence exists among non-prime attributes

Normalization starts with the following three-step procedure:

− Eliminate the repeating groups

− Identify the primary key

− Identify all dependencies

atomic attribute

an attribute that cannot be further subdivided. If a value needs to be broken down further (like emp_name into first_name / last_name), it is not atomic.

SELECT

specifies which columns to return

− used to retrieve data from table

FROM

- specifies the table(s) from which the data will be retrieved

- Only columns in the table specified in this clause are available throughout the rest of the query

- clause forms the basis for the rest of the query

− It defines the data that will be available to the remainder of the query

WHERE

filters rows based on a condition

GROUP BY

groups rows to support aggregate analysis

HAVING

filters the groups created by GROUP BY

ORDER BY

sorts the final output ascending or descending

Date Arithmetic

Values are stored as a number of days; it is possible to perform date arithmetic in a query

JOIN

used to combine data from multiple tables

Inner joins

return only rows from the tables that match on a common value

Outer joins

return the same matched rows as the inner join, plus unmatched rows from one table or the other

-A natural join

returns all rows with matching values in the matching columns and eliminates duplicate columns

BETWEEN

Used to check whether an attribute value is within a range

IN

- Used to check whether an attribute value matches any value within a value list

LIKE

- Used to check whether an attribute value matches a given string pattern

− IS NULL

Used to check whether an attribute value is null

NOT

Used to negate a condition. Example:

select * from CH07_LARGECO.testcustomer1 where not cust_balance = 0;

GROUP BY

an SQL clause used to create frequency distributions when combined with any of the aggregate functions in a SELECT statement

EXCEPT (MINUS)

statement combines rows from two queries and returns only the rows that appear in the first set but not in the second

UNION

combines rows from two or more queries without including duplicate rows

Supertype

generic entity that contains common attributes.

Subtype

specialized entity with unique attributes beyond the supertype.