Checkpoint 2 - Relational Design

Vocabulary flashcards for Checkpoint 2 - Relational Design, detailing field properties, validation rules, key definitions, and relational database concepts.

Required

A field property that prevents a value being left Null when a value is required. Example: Setting ProductName Required = Yes stops a product being saved without a name.

Validation Rule

A condition that entered data must satisfy. Example: A StockLevel rule of >=0 prevents negative stock.

Validation Text

A useful message shown when a validation rule is broken. Example: 'Stock cannot be negative' explains why -3 was rejected.

Default Value

A value automatically supplied as the starting value for a new record. Example: Active could default to Yes.

Input Mask

A pattern that guides how data is entered or displayed. Example: An input mask can guide a consistent telephone-number format, but cannot prove the number is real.

Entity

A real-world thing or concept about which data is stored. Example: Customer, Product and Order are possible entities.

Multiple tables

Separate tables store facts about different entities and reduce unnecessary duplication. Example: Customer details belong in tblCustomer rather than being repeated on every order.

Foreign key

A field that stores the primary-key value from a related table. Example: CustomerID is a PK in tblCustomer and an FK in tblOrder.

One-to-many relationship

One record on the one side can relate to many records on the many side. Example: One customer can place many orders.

Foreign key placement

In a 1:M relationship, the foreign key normally goes on the many side. Example: CustomerID is stored in tblOrder because many orders can belong to one customer.

Referential integrity

Rules that help keep relationships valid by preventing orphan references. Example: Access can prevent an Order being given CustomerID 999 if customer 999 does not exist.

Many-to-many relationship

Many records on each side can relate to many records on the other side. Example: Many students can join many clubs.

Junction table

A table used to resolve a many-to-many relationship into two one-to-many relationships. Example: StudentClub can link Student and Club.

Junction-table keys

A junction table normally contains foreign keys to both parent tables. Example: StudentClub contains StudentID and ClubID.

Composite primary key

A primary key made from more than one field. Example: OrderID + ProductID can identify an OrderLine if each product occurs once per order.

Relationship data

A junction table can also store facts about the relationship itself. Example: OrderLine can store Quantity as well as OrderID and ProductID.

Compatible key types

Related PK and FK fields must use compatible data types/sizes. Example: An AutoNumber PK is normally linked to a Number/Long Integer FK in Access.

Orphan record

A child record whose foreign key does not match an existing parent record. Example: An order referring to a deleted/non-existent customer would be orphaned.

Validation limitation

Validation can reject impossible formats/ranges but cannot guarantee factual accuracy. Example: Age 35 may pass a 0-120 rule even if the person's real age is 36.

Relational design

Store each fact in an appropriate table and link tables using keys. Example: Customer details in Customer; order facts in Order; linked using CustomerID.