Database Concepts: Data, Models, Keys, and Normalization

Data

Raw facts; typically text or numbers lacking context. 35, Smith, 10:30 AM**

Information

Processed, structured data presented in context to make it meaningful. Employee ID 35, Customer Smith, Appointment at 10:30 AM**

Knowledge

Ability to understand information and make inferences or decisions. Understanding that employee 35 has a consistent tardiness issue.

End-user data

The raw facts of interest to the user (the actual values). The value *'iPhone 15'* in a product name field.

Metadata

Data about data; describes the characteristics and structure of the data. The field name *'CUST_LNAME'* is defined as `VARCHAR(20)`.

DBMS

Database Management System; programs that manage the database structure and control access. *MySQL, Oracle, SQL Server*

Data dictionary

An automatically updated storehouse of *metadata* describing the database structure. A record stating `Product_ID` is an `INTEGER` and the Primary Key.

Relational algebra

Theoretical set of operations used to manipulate relations (tables). Operations like *SELECT (Restrict), PROJECT, JOIN*.

DB design operations

The processes of structuring the data, relationships, and constraints for a specific environment. Steps like *Conceptual, Logical, and Physical Design*.

Data redundancy

Duplication of data, which wastes space and leads to anomalies. Storing the *Department Manager's name* in every employee's row.

Data anomalies

Problems resulting from data redundancy (Insert, Delete, Update). Deleting the last employee in a department removes the only record of the department's name (Deletion Anomaly).

Data model

A conceptual tool used to represent data structures, relationships, and constraints graphically. The *Entity-Relationship Model (ERM)*.

Entity

A person, place, thing, or event for which data is collected (a table). *CUSTOMER, PRODUCT, ORDER*

Attribute

A characteristic of an entity (a column). For `CUSTOMER`: *CUST_ID, CUST_LNAME, CUST_PHONE*

Relationship

An association between entities A *CUSTOMER places an ORDER*.

Constraint

A restriction placed on the data to ensure data integrity. `ORDER_DATE` must be a date on or after the current date.

Relationship types

Classification of relationships based on cardinality. *1:1 (One-to-One), 1:M (One-to-Many), M:N (Many-to-Many)*

Business rules

Precise descriptions of policies or procedures that help define data requirements.

An employee must be assigned to one department.

An example of a Business rule

ERD (Chen - Crow's Foot)

digram a graphical representation of the database model. Chen uses diamonds for relationships; Crow's Foot uses a specific symbol for 'many.'

Naming conventions

Established guidelines for naming database objects for clarity and consistency. Using UPPERCASE for table names and singular nouns.

Data model evolution

The historical development of data models (e.g., Hierarchical → Network → Relational → Object-Oriented). Moving from the Hierarchical Model to the current Relational Model.

Connectivity

The classification of the relationship between entities. Expressed as 1:M (One-to-Many).

Cardinality

The minimum and maximum number of entity occurrences associated with another entity. A customer can place (0, N) orders.

Relationship strength

Determined by whether the Foreign Key is part of the dependent entity's Primary Key. Identifying (Strong) vs. Non-identifying (Weak).

Relationship degree

The number of entities participating in a relationship. Unary (recursive), Binary, or Ternary.

Multivalued attributes

Attributes that can have more than one value for a single entity occurrence (violates 1NF). An employee's list of Skills.

Characteristics of a relational table

Properties like: distinct column names, atomic values, and order independence. Every row represents a unique entity occurrence.

Dependencies

The relationship where one attribute's value determines another's (Functional Dependency). ---> SSN --->Employee Name

Keys

An attribute or set of attributes that uniquely identifies an entity.

Primary Key (PK)

An attribute (or combination) that uniquely identifies any given row; must be unique and NOT NULL (Entity Integrity). CUST_ID.

Foreign Key (FK)

An attribute in one table that links to the Primary Key of another table (ensures Referential Integrity). DEPT_ID in the EMPLOYEE table.

Candidate Key

An attribute (or combination) that could serve as the PK (unique and non-redundant). Social Security Number or Driver's License Number.

Surrogate Key

A system-assigned, auto-incremented integer PK that has no business meaning. An Auto_ID field.

Composite Key

A Primary Key composed of two or more attributes. (ORDER_NUM, PROD_CODE) in a LINE_ITEM table.

Integrity rules

Rules that maintain the quality and consistency of data.

Entity Integrity

Rule stating that the Primary Key cannot contain null values. → The `CUST_ID` field cannot be blank.

Referential Integrity

Rule stating that a Foreign Key value must either match a PK value in the related table or be NULL. → An employee's `DEPT_ID` must exist in the `DEPARTMENT` table.

Normalization

Process of structuring tables to minimize data redundancies and eliminate anomalies. → Decomposing a table with anomalies into two or more well-structured tables.

Normal forms

States of a table achieved through normalization. → *1NF, 2NF, 3NF* (the most common goal).

1NF

_________ Eliminates repeating groups and ensures all attributes are atomic.

2NF

In 1NF and no non-key attribute is dependent on only part of a composite PK (eliminates *partial dependencies*).

3NF

In 2NF and no non-key attribute is dependent on another non-key attribute (eliminates *transitive dependencies*).

Post normalization improvements

Steps taken after full normalization to fine-tune performance, often involving denormalization.

Denormalization

Purposefully introducing *redundancy* to a table to improve query performance. → Adding the `DepartmentName` to the `Employee` table.

Atomic attributes

Attributes that cannot be further subdivided. → `CUST_PHONE` is atomic; `CUST_ADDRESS` is not (should be broken into Street, City, State, etc.).

Granularity

The level of detail represented by the data. → *Fine-grained (every transaction) vs. Coarse-grained* (monthly sales totals).

Entity supertypes - entity subtypes

A hierarchy where a general entity (Supertype) is subdivided into specific entities (Subtypes). → *PERSON (Supertype) is divided into EMPLOYEE and CUSTOMER* (Subtypes).

Inheritance

The property where subtype entities automatically share all attributes and relationships of their supertype. → Both `EMPLOYEE` and `CUSTOMER` inherit the `Name` and `Address` attributes from `PERSON`.

Disjoint constraint

An entity occurrence can belong to *only one of the subtypes (mutually exclusive). → A `PERSON` is either an `HOURLY_EMPLOYEE` OR* a `SALARIED_EMPLOYEE`.

Overlapping constraint

An entity occurrence can belong to *multiple subtypes. → A `PERSON` can be both a STUDENT and an EMPLOYEE*.

Completeness constraint

Specifies whether every supertype occurrence must be a member of at least one subtype. → *Total (must be a subtype) vs. Partial* (doesn't have to be a subtype).

Entity clustering

A virtual entity used to represent multiple entities and relationships to simplify a complex ERD.

What type of relationship 1:1

from one table becomes FK in the other table (with a UNIQUE constraint).

What type of relationship 1:M

from the 'One' side becomes the FK in the 'Many' side table.

What type of realationship M:N

Resolved by a Junction Table, whose PK is the composite of the FKs from both original tables.

What defines an identifying relationship in databases?

An identifying relationship exists when the Foreign Key (FK) of the dependent entity is part of its Primary Key (PK).

What is a key characteristic of an identifying relationship?

The dependent entity cannot exist without the entity it references (the parent).

How is the Foreign Key implemented in an identifying relationship?

The FK from the parent table becomes part of the Composite Primary Key of the child table.

Give an example of an identifying relationship.

A Line Item cannot exist without its parent Order.

What is the Primary Key of the Order Table?

Order_ID

What is the Primary Key of the Line Item Table?

Composite PK: (Order_ID as FK, mandatory part of PK), Item_Number.

What defines a non-identifying relationship in databases?

A non-identifying relationship exists when the Foreign Key (FK) of the dependent entity is NOT part of its Primary Key (PK).

What is a key characteristic of a non-identifying relationship?

The dependent entity can often exist independently or is uniquely identified by its own attributes.

How is the Foreign Key implemented in a non-identifying relationship?

The FK from the parent table is simply a non-key attribute in the child table.

Give an example of a non-identifying relationship.

An Employee is assigned to a Department.

What is the Primary Key of the Department Table?

Dept_ID

What is the Primary Key of the Employee Table?

Emp_ID (its own unique key).

What is the Foreign Key in the Employee Table?

Dept_ID (just a regular column).

Unary

Relationship between instances of the same entity. → 1 entity.

Binary

Relationship between two distinct entities. → 2 entities.

Ternary

Relationship between three distinct entities. → 3 entities.

Relationship Degree

The number of entities participating in a relationship.