Chapter 6 - Database Design - Relationships
index
A behind-the-scenes, system-generated copy of a selected field or fields organized in ascending or descending order so that when data in that field is used in a search or sorted, the process is much faster than it would be without the predeveloped index.
subdatasheet (Access)
A datasheet that is nested within another datasheet to show related records; the subdatasheet shows the records on the "many" side of a one-to-many relationship.
secondary key (SK)
A field or combination of fields that may not be unique, but which is commonly used for retrieval and thus should be indexed.
alternate key (AK)
A field that contains unique data for each record in the table but is not used as the primary key field for security or other reasons.
subform
A form placed within a form that shows related records from another table or query; generally displays many records at a time in a datasheet arrangement.
E-R diagram
A graphic model for database design in which entities are represented as rectangles and relationships are represented as either arrows or diamonds connected to the entities they relate.
entity-relationship (E-R) diagram
A graphic model for database design in which entities are represented as rectangles and relationships are represented as either arrows or diamonds connected to the entities they relate.
view
A particular collection of fields and records from one or more entities created for a particular user, program, or purpose; also called a query.
with an underline as well as the abbreviation PK
How do you identify the primary key field in DBDL notation?
foreign key field (FK)
In a one-to-many relationship between two tables, the field in the "many" table that links the table to the primary key field in the "one" table.
Implement one-to-many relationships and relationship constraints:
One-to-many relationships are implemented in Access using the Relationships window.
DBDL (Database Design Language) notation
The _____________ is a way to define the entity and field names, as well as the primary, alternate, secondary, and foreign key fields for an entity.
referential integrity
The most important relationship constraint is called _____________ and prevents the creation of orphan records in the table on the "many" side of the relationship.
Define the process and goals of database design:
The process of database design involves normalizing data into distinct entities as well as defining relationships between the entities. The process requires constant review as existing and new needs for data as well as existing and desired business rules are understood. The overall goal of database design is to organize data in a healthy relational database that meets the current and desired data analysis needs of the people and processes of an organization.
user view
The specific data needed by a person or process for a particular task.
junction table
The table that serves on the "many" side of a one-to-many relationship with each of the two original tables in a many-to-many relationship.
cardinality
The uniqueness of data values contained in a single field. Also sometimes used to refer to the actual relation-ships between tables.
Cartesian join
When two tables in a query have no instructions about how to connect their records, each record in one table connects with each record in the other table.
DBDL
Which of the following is not considered a type of E-R diagram?
Every foreign key field participates on the "many" side.
Which of the following statements is true about a one-to-many relationship?
The table on the "many" side has values in the foreign key field with no matching value in the primary key field of the table on the "one" side.
Why might you not be able to enforce referential integrity on a one-to-many relationship?
crow's foot notation
an E-R diagram implementation that uses a symbol on the side of the "many" entity resembling a crow's foot.
Orphan record
A record in the "many" (child) table that has no match in the "one" (parent) table in a one-to-many relationship.
Database Design Language (DBDL)
A relational-like language that is used to represent the result of the database design process.
form
A screen object used to maintain and view data from a database.
composite entity
An identifier consisting of a combination of two or more fields that is used when more than one column is necessary to make a row unique in a table; also, an index with more than one key field.
entity-relationship models (ERMs)
Create _______________________ to visually represent a database design: An __________is another type of visual representation of the entities and relationships between entities in a relational database, and focuses more on the relationships between entities than a typical E-R diagram. An ______________ uses rectangles to represent entities and diamonds to represent relationships.
entity-relationship (E-R)
Create __________________________ diagrams to visually represent a database design: An _____________ is a visual representation of the entities and relationships between entities in a relational database. An ______________ uses squares for entities and lines for relationships. It may or may not include details on attributes. Several versions of the _________________ are popular including one called crow's foot notation given the "many" symbol represents a crow's foot.
information-level design
Describing the attributes needed for each entity as well as the relationships between the entities based on business rules.
Lookup properties (field properties in Table Design View)
Field properties that allow you to supply a drop-down list of values for a field.
A primary key field has two functions.
First and foremost, it uniquely identifies each record. Secondly, a primary key is necessary to link tables in one-to-many relationships. The primary key field is always on the "one" side of the relationship. The foreign key field is always on the "many" side of the relationship.
subdatasheets drop-down subforms
Access provides many application development features that exploit the power of one-to-many relationships, including the ability to show related records in ______________________, the ability to create ___________ lists using field Lookup properties, the ability to connect related records in _________, and the ability of Query Datasheet View to automatically update and refresh related data.
E-R model
An approach to representing data in a database that uses E-R diagrams exclusively as the tool for representing entities, attributes, and relationships.
entity-relationship model (ERM)
An approach to representing data in a database that uses E-R diagrams exclusively as the tool for representing entities, attributes, and relationships.
relationships
The ERM emphasizes what part of a relational database?
