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