K204 Chapter 5

Data Types

• determine kind of values that can be held in a column(field)

• govern the way data is stored, validated, + handled in a relational database

Proper selection of data types can provide following benefits (6):

1. Data consistency + Validation

2. Storage Optimization

3. Query Performance

4. Space Efficiency

5. Compatibility + Interoperability

6. Data Integrity Constraints

Data Consistency + Validation

• ensures values stored in a column adhere to specific rules

• consistency helps maintain data integrity, prevent incorrect data

Storage Optimization

• different data types have varying storage requirements

• choosing the right data type can minimize storage overhead

Query Performance

• data types impact execution time

• efficiency → faster database operations

Space Efficiency

• saves space

• good for larger databases

Compatibility + Interoperability

• data types allow defining constraints

  • maintain data quality

Metadata

• field names

• data types

• descriptions

• keys (P+F)

• field + record properties

Table Relationships

• transform a collection of isolated tables into a well-organized info. network

• to form relationship b/tw 2 tables, must have one field in common

  • often times PK of one and FK of another

  • common field must have same (or compatible) data type + same field size

Referential Integrity

• ensures data references in one table always correspond to valid entries in another table

  • prevents creations of “orphan records” that point to N/A data

Many-to-many relationships

many employees can access different data/classes

Junction Tables

provide a clean + efficient way to manage complex relationships where many records in one table connect to many records in another

• not something that can be executed in a relational database

Primary Key

field or combo of fields used to uniquely ID each record in database rtable

• guarantees each record in table will have unique IDier

• helps maintain integrity of databases

• guarantees records will auto. appear in PK sequence even if entered in random

• allows queries to run more quickly if table inclided in query design

Dirty Data

happens when there are incomplete, inconsistent, outdated, suplicated, or even missing values in database tables

Composite Key

type of PK composing 2 or more fields

• create unique combo that ID each record in a table

• ensure no duplicate records

• also enforce company policy

“Enforce Referential Integrity”

• used most often in relationships

• ensures when adding a record to table on “many” side, there’s a matching record in “one” side

“Cascade Update Related Fields”

• if you change a product’s thing, Access auto. updates each instance of that

• consistency + reliability

“Cascade Delete Related Records”

• deleting product results in deletion of all assigned to product

• rarely used