Module 6 Review - Exam 2

erm one to many relationship

1 = one part of the relationship n = many part of the relationship

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.

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.

primary key

a field that contains unique data for each record and is underlined in the notation

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. The reverse of this is also true. When referential integrity is enforced, you may not delete a record in the "one" table that has related records in the "many" table.

index

a separate file that keeps track of the alphabetical order of the values in a particular field created to improve overall database performance when queries request a sort order for records not every field should be indexed Primary key fields are automatically indexed, but as a database developer, the DBDL notation helps you determine other fields in each table that should be indexed.

referential integrity prevents..

created 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 enforcing referential integrity means that you could not enter a record in the Employees table with a DeptNum value that didn't first exist in the Departments table.

relationships and set primary and foreign key fields

elationships in a relational database consist of one-to-many relationships between the entities. While many-to-many relationships exist, they are physically implemented using a junction table with two one-to-many relationships to the original two tables. One-to-one relationships can also exist but should be examined to determine if the two tables can be combined into one table. 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

many-to-many relationship

exists when one record is one entity can be related to many other records in another entity cannot be directly created between two tables in a relational database management system. 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.

erm many to many relationship

m = many part of the relationship n = many part of the relationship

one-to-many relationship

that one record in the table on the "one" side of the relationship can be linked or related to many records in the table on the "many" side of the relationship created with a field that is common on each table although they do not need to have the same name The linking field in the table on the "one" side of a one-to-many relationship is always the primary key field for that table. The linking field in the table on the "many" side of a one-to-many relationship is called the foreign key field

composite entity

the middle entity in the erm used to implement a many-to-many relationship. in a physical implementation of the relational database, the composite entity is called a junction table

view

view of data is a particular collection of fields and records from one or more entities SELECT FROM WHERE commands

many-to-to many relationships

resolving is the most difficult mental task when building a relational database

foreign key field

used to connect a record in one table to more information on another table

break down of crows foot notation

- dash dash symbols represents one and only one - the ring and crows food represent zero/one/many - entities are represented by rectangles, but attribute details are not listed

relational database design documentation identifies:

1. the attributes needed for each normalized entity 2. the primary key field for each entity 3. the indexes and other properties needed to describe each attribute and entity 4. the proper one to many relationships between the entities

secondary key

A field or combination of fields that may not be unique, but which is commonly used for retrieval and thus should be indexed. ex: first name and last name

alternate key

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.

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. provides a more visual representation of the entities, attributes, and relationships between the entities than DBDL notation.

one to many relationship/constraints

One-to-many relationships are implemented in Access using the Relationships window. The most important relationship constraint is called referential integrity and prevents the creation of orphan records in the table on the "many" side of the relationship.

rules for defining tables and their keys using DBDL

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. Alternate keys are identified by the abbreviation AK, followed by the column(s) that make up the alternate key. Secondary keys are identified by the abbreviation SK, followed by the column(s) that make up the secondary key. Foreign keys are identified by the abbreviation FK, followed by the column(s) that make up the foreign key. Foreign keys are followed by an arrow pointing to the table identified by the foreign key.

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. When you build a relational database, collecting current company forms and reports helps identify all of the current user views and attributes you need to consider in the database design ex: an employee may need a particular user view of data to enter or report on a certain business issue.

cardinality

The uniqueness of data values contained in a single field. Also sometimes used to refer to the actual relationships between tables. is another term that describes relational databases A primary key field has high cardinality because all values in the field are, by definition, unique. Foreign key fields have low cardinality given they are specifically created to allow for repeating values. also sometimes refers to the actual relationships between tables. For example, some database designers refer to the relationships between tables as a one-to-one, one-to-many, or many-to-many cardinality.

er diagram breakdown

A rectangle represents each entity, including one rectangle for the Employees entity and a second rectangle for the Departments entity. The name of each entity appears above the rectangle. The primary key for each entity appears above the line in the rectangle for each entity. EmpID is the primary key of the Employees entity, and DeptNum is the primary key of the Departments entity. The other columns in each entity appear below the line within each rectangle. The letters AK (alternate key), SK (secondary key), and FK (foreign key) appear in parentheses following the field name as needed. For each foreign key, a dotted line leads from the rectangle that corresponds to the table being identified to the rectangle that corresponds to the table containing the foreign key. The dot at the end of the line indicates the "many" part of the one-to-many relationship between the Departments and Employees entities. While it's not required to place the Departments table on the left side of the diagram, the English language is read from left to right, and a LEFT join in SQL always refers to the table on the "one" side of the relationship, so placing the "one" or "parent" table on the left side of diagram when possible improves readability.

database design language (DBDL)

A relational-like language that is used to represent the result of the database design process. expands the definition of an entity by specifying different types of key fields

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. primary keys on tables match decide if the tables can be combined temporary one-to-one relationships can be useful when connecting/converting/updating data from two separate sources should be avoided

unique

An Access index property that when set to Yes, requires every value in the index to be unique.

crow's foot notion

An E-R diagram implementation that uses a symbol on the side of the "many" entity resembling a crow's foot. specific attributes for each entity are not listed

er diagrams review

An E-R diagram is a visual representation of the entities and relationships between entities in a relational database. An E-R diagram uses squares for entities and lines for relationships. It may or may not include details on attributes. Several versions of the E-R diagram are popular including one called crow's foot notation given the "many" symbol represents a crow's foot.

erm review

An ERM 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 ERM uses rectangles to represent entities and diamonds to represent relationships.

entity-relationship (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. uses rectangles for entities and diamonds for relationships. the lines that connect the entities contain notation to identify whether they are on the "one" or "many" side of a relationship also called ERM