Database Concepts: Raw Facts to Data Dictionary

• Raw facts are simple statements or observations, while information is raw facts that have been processed to reveal meaning.

DBMS, Metadata

• A Database Management System (DBMS) is software for managing databases.
• Metadata is data about data, providing descriptions and characteristics of data elements.

Table, Field, Record, Database

• A table is a structure that stores data organized in rows and columns.
• A field is a single piece of information within a record, represented by a column in a table.
• A record is a collection of related fields, represented by a row in a table.
• A database is an organized collection of related data, typically stored in tables.

Data Redundancy, Data Integrity

• Data redundancy is the duplication of data, which can lead to inconsistencies.
• Data integrity ensures the accuracy and consistency of data over its lifecycle.

Data Dictionary

• A data dictionary is a repository of metadata, providing information about the data in a database.

Database System

• A database system is an organized collection of logically related data.

Data Anomalies

• Data anomalies are inconsistencies or errors that can occur when data is not properly managed, such as update, insertion, or deletion anomalies.

Naming Conventions

• Fields should be named descriptively and consistently.
• Only include fields that are relevant and necessary for the database's purpose.

Data types in Access

• Access supports various data types, including Text, Number, Date/Time, Currency, AutoNumber, Yes/No, and OLE Object.

Access Objects

• Access objects include tables (data storage), queries (data retrieval), forms (user interface), and reports (data presentation).

Table Views

• Tables have Datasheet View (displays data in a table format) and Design View (used to define table structure).

Table Sorting

• Tables are automatically sorted by the primary key field.

Primary Key vs Foreign Key

• A primary key uniquely identifies each record in a table.
• A foreign key is a field in one table that refers to the primary key of another table, establishing a relationship between the tables.

Business Rules

• Business rules can be translated into nouns (entities) and verbs (relationships).

Schema, Subschema, DML, DDL

• A schema is the overall structure of a database.
• A subschema is a subset of the schema, providing a limited view of the database.
• Data Manipulation Language (DML) is used to retrieve, insert, update, and delete data.
• Data Definition Language (DDL) is used to define the database schema, including creating, altering, and dropping tables.

Model Notation Symbols

• Figure 2.2 illustrates symbols used in model notations, representing entities, attributes, and relationships.

Relationships

• Relationships can be one-to-one, one-to-many, or many-to-many.

Models

• External models represent the user's view of the data.
• Conceptual models provide a high-level representation of the data and relationships.
• Internal models describe how data is stored and accessed.
• Physical models detail the physical storage structures.

Crow’s Foot Diagram

• A Crow’s Foot Diagram visually represents the relationships between entities, including cardinality and optionality. Business rules are derived from these diagrams, specifying how entities relate to each other.

Referential Integrity, Cascade Update, Data Redundancy

• Referential integrity ensures that relationships between tables remain consistent.
• Cascade Update automatically updates related foreign keys whenever a primary key is changed.
• Data redundancy is the unnecessary duplication of data, which can lead to inconsistencies.

Entity Integrity

• Entity integrity ensures that each record in a table has a unique primary key and that no primary key value is null.
• Access enforces this automatically by requiring a primary key for each table.

Caption vs Description

• A caption is a user-friendly label for a field, displayed in forms and reports.
• A description provides additional information about a field, visible in Design View.

Input Mask and Default Value

• An input mask specifies a format for data input.
• A default value automatically populates a field with a pre-defined value.

Key Types

• A superkey uniquely identifies each record in a table.
• A candidate key is a minimal superkey.
• A composite key is a primary key composed of multiple fields.
• A primary key is the candidate key chosen to uniquely identify records.
• A foreign key is a field in one table that refers to the primary key of another table.

Integrity Types

• Referential integrity ensures that relationships between tables remain consistent.
• Entity integrity ensures that each record in a table has a unique primary key and that no primary key value is null.

Synonym vs Homonym

• A synonym is a different name for the same attribute.
• A homonym is the same name used for different attributes.

Operators

• SELECT retrieves rows from a table based on specified criteria.
• PROJECT selects columns from a table.
• UNION combines the results of two or more SELECT queries.

Index

• An index is a data structure that improves the speed of data retrieval.

Filter and Query

• A filter is a temporary restriction on the records displayed in a datasheet.
• A query is a more powerful and flexible tool for retrieving and manipulating data.

Filter Performance

• Filter by form allows filtering based on multiple criteria entered into a form-like interface.
• Filter by selection filters based on the currently selected value in a field.

Datasheet Formatting

• Datasheets can be formatted to change font, color, gridlines, and cell effects.

Logical Operators

• OR includes records that meet either of the specified criteria.
• AND includes records that meet all of the specified criteria.

Expressions

• Expressions in Access are created using the Expression Builder, allowing calculations and manipulations of data.

Sorting

• A single sort sorts data based on one field.
• A secondary sort sorts data based on a second field within groups defined by the primary sort.