Ch. 6 - Database Design: Relationships
Information-level design method
1. Represent the user view as a collection of tables 2. Normalize these tables 3. Identify all the keys in the tables 4. Merge the results of steps 1-3 into the cumulative design
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. • visually represents the structure of the database
view (query)
A particular collection of fields and records from one or more entities created for a particular user, program, or purpose
orphan record
A record in the "many" (child) table that has no match in the "one" (parent) table in a one-to-many relationship.
referential integrity
A relational database concept that sets rules called integrity constraints on table relationships primarily to prevent the creation of orphan records. • prevents the creation of orphan records, records in the "many" side of a relationship that do not have a matching value in the "one" side of the relationship
Database Design Language (DBDL)
A relational-like language that is used to represent the result of the database design process.
many-to-many relationship
A relationship between three entities in which each occurrence of each entity can be related to many occurrences of each of the other entities. • create a new table whose primary key is a combination of the primary keys of the original tables
form
A screen object used to maintain and view data from a database.
top-down design method
Begins with general database that models overall enterprise Refines model until design supports all necessary applications
One approach for expanding the definition of each table uses an extended notation called
Database Design Language (DBDL)
An ___________ is a visual representation of the entities and relationships between entities in a relational database.
E-R diagram
Lookup properties (field properties in Table Design View)
Field properties that allow you to supply a drop-down list of values for a field.
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.
______________ relationships cannot be directly created between two tables in a relational database management system
Many-to-many
__________ relationships manifest themselves in an Access database allowing you to build small applications on a relational database system
One-to-many
User View
The specific data needed by a person or process for a particular task. • the set of requirements that is necessary to support the need of a particular database user • logical way of looking at the database set up to support the activities of the user or groups of users
2. Normalize these tables
To third Normal Form
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.
Which of the following relational database tools does not help you document the relationships between tables? a. SQL b. DBDL c. E-R diagram d. Entity-relationship model
a
How do you identify the primary key field in DBDL notation? a. with a circle b. with an underline as well as the abbreviation PK c. with a double underline d. with a key symbol
b
The ERM emphasizes what part of a relational database? a. attributes b. relationships c. primary key fields d. foreign key fields
b
In E-R diagrams, columns (fields) that aren't the primary key appear
below the line
Which of the following is not a technique that would be used in the process of developing a relational database? a. Normalize the data. b. Review existing data forms and reports. c. Interview those who use the data to understand business rules. d. Combine all attributes into one large table.
d
Which of the following statements is true about a one-to-many relationship? a. Every primary key field participates on the "one" side. b. Every foreign key field participates on the "one" side. c. Every primary key field participates on the "many" side. d. Every foreign key field participates on the "many" side.
d
The ________ key field is always on the "many" side of the relationship.
foreign
Alternate keys in DBDL
identified by the abbreviation AK, followed by the column(s) that make up the alternate key
Secondary keys in DBDL
identified by the abbreviation SK, followed by the column(s) that make up the secondary key
Indexes are created to
improve overall database performance when queries request a sort order for records
In a physical implementation of the relational database, the composite entity is called a _____________
junction table
In E-R diagrams, the dashed line indicates
the relationship
E-R diagram
uses rectangles to represent the entities (tables) and lines to represent the relationships between the tables
Two tables have a one-to-one relationship
when the primary key fields of each table match
When referential integrity is enforced,
you may not delete a record in the "one" table that has related records in the "many" table
Types of primary keys
• Natural key • artificial key • surrogate key
Surrogate key
A system-generated primary key that is usually hidden from users.
physical-level design
Implementing the entities, attributes, constraints, and relationships in an RDBMS. • designer adapts the information-level design for the specific DBMS that the company will use
To implement a many-to-many relationship in a relational database, you must
insert a physical entity between the two tables to link them together
Natural key
logical key that uniquely identifies an entity
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; • 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.
primary key field
A field that contains unique information for each record. • cannot contain a null entry.
one-to-one relationship
A relationship between two entities in which each occurrence of the first entity is related to one occurrence of the second entity, and each occurrence of the second entity is related to at most one occurrence of the first entity. should probably be combined into a single table
1. Represent the user view as a collection of tables
A. Determine the entities involved and create a table for each type of entity B. Determine the primary key for each table C. Determine the properties (or fields) for each entity D. Determine relationships for each entity
One-to-many relationships are implemented in Access using the
Relationships window.
junction table
The table 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 relationships between tables.
High cardinality
a field contains many unique or uncommon values
low cardinality
a field contains only a few unique values
The DBDL notation is
a way to define the entity and field names, as well as the primary, alternate, secondary, and foreign key fields for an entity
In E-R diagrams, primary keys appear
above the line
In E-R diagrams, the entity (table) name is given
above the rectangle
bottom-up design method
specific user requirements are integrated into a design
In E-R diagrams, rectangles represent
tables
Merge the result into the design
• Combine all the tables together that have the same primary key. • Remove any duplicate fields. • Tables should be in 3NF after merge
crow's foot notation
• E-R diagram implementation • Symbol on the side of the "many" entity • Specific attributes for each entity are not listed within each entity rectangle
You can create a view of data from a relational database with
• SQL SELECT FROM WHERE commands • Or by using a QBE tool such as Access Query Design View
Foreign keys in DBDL
• identified by the abbreviation FK, followed by the column(s) that make up the foreign key. • followed by an arrow pointing to the table identified by the foreign key
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.
one-to-many relationship
A relationship between two entities in which each occurrence of the first entity is related to many occurrences of the second entity, and each occurrence of the second entity is related to at most one occurrence of the first entity. • primary key in the "one" table is foreign key in the "many" table
Unique property (field property in Table Design View)
An Access index property that when set to Yes, requires every value in the index to be unique.
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. uses rectangles for entities and diamonds for relationships
composite entity
An entity in the entity-relationship model used to implement a many-to-many relationship.
Information-level design (conceptual design)
Describing the attributes needed for each entity as well as the relationships between the entities based on business rules. • designer designs a database that meets the organization's requirements as cleanly as possible • completed independent of any particular DBMS the company will use
E-R diagram in Microsoft Access
Displays the number 1 and the infinity symbol at the ends of the link line to identify the two sides of a one-to-many relationship
_____________ provides a more visual representation of the entities, attributes, and relationships between the entities than DBDL notation
E-R Diagram
A one-to-many relationship with referential integrity means that
a record in the "one" table (also called the parent table) must be established before a related record in the "many" table (also called the child table) can be entered
Artificial key
column created for an entity to serve as the primary key and that is visible to users
Resolving a ______________ relationship between two tables is perhaps the most difficult mental task in building a relational database
many-to-many
The process of database design involves
normalizing data into distinct entities as well as defining relationships between the entities
The __________ key field is always on the "one" side of the relationship.
primary
An ERM focuses more on the _____________ than a typical E-R diagram.
relationships between entities
In E-R diagrams, lines joining the tables represent
relationships, which are determined by foreign keys
In E-R diagrams, the dot at the end of the line indicates
the "many" side of the one-to-many relationship
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.
Choosing which fields to index
• Because some overhead is required to create and maintain an index, not every field should be indexed • Only those fields that are commonly used for sorting should be indexed • Primary key fields are automatically indexed • DBDL notation helps you determine other fields that should be indexed
Access exploits the power of one-to-many relationships, including the ability to
• show related records in subdatasheets • create drop-down lists using field Lookup properties • connect related records in subforms • ability of Query Datasheet View to automatically update and refresh related data
The expanded rules for defining tables using DBDL are as follows:
•Tables (relations), columns (attributes), and primary keys are written by first listing the table name and then, in parentheses, listing the columns that make up the table. The column(s) that make up the primary key are underlined
Relational database design documentation generally identifies:
•The attributes needed for each normalized entity •The primary key field for each entity •The indexes and other properties needed to describe each attribute and entity •The proper one-to-many relationships between the entities