Database Systems- Chapter 4

Composite Key

A key that consists of more than one column(attribute).

Surrogate Key

A random or arbitrary key often generated by just incrementing numbers.

Naming Conventions

A set of rules or suggestions that promote consistency in the naming of database objects.

The name of the entity The primary key The attributes

An entity is represented as a rectangle divided into three parts:

Domain Entities

An entity that captures a chief element of the business problem.

Weak Entities

An entity that depends on another entity for its meaning.

Linking Entity

An entity that resolves a many-to-many relationship into two one-to-many relationships.

Lookup Entity

Are used to store a set of values that can be looked up, such as state abbreviations or zip codes. -help ensure data integrity and consistency

Triggers

Executable scripts of SQL code that are triggered by an event such as an insert, update, or delete. They can be used to enforce business rules that cannot be enforced by database design alone.

one-to-many relationship

It means that for every one record in the parent entity, there can be zero to infinity records in the child entity. -the normal relationship between tables.

one-to-many

Many-to-many relationships must be resolved into two ____-___-____ relationships. -To do this, it is necessary to create a linking between the two tables that have many-to-many relationships.

Crow's Feet Notation

Notation for relationships that uses lines and circles to depict cardinality.

ERDS (Entity Relation Diagrams)

One common method of depicting entities and relations in diagrams. -a common way of diagramming entities, their attributes, and their relationships.

Cardinality

Refers to the number of permitted records in a related entity.

child

Tables with the foreign keys are referred to as "____" tables.

Physical Design

The design adapted to the RDBMS and system constraints and features.

Logical Design

The entity relation design without regard to what RDBMS or system it will be on.

Maximum Cardinality

The highest number permitted. -max number of permitted relationships -(For example, a customer can have no more than 4 listed emails.)

parent

The primary key table is sometimes referred to as the "_____" table.

Minimum Cardinality

The smallest number permitted. -min number of permitted relationships -(For example, each customer must have at least one purchase in the purchase table.)

One to One One to Many Many to Many

There are three types of relationships between entities:

Entities; tables

_____ and _____ are named as single nouns like Tutor, Student, and Session.

Attributes

________ are named with the entity name followed by the attribute name. There are no underscores between. Each new word is capitalized: TutorLastName, StudentLastName, and so on.

Primary keys

end with the word "Key": TutorKey, StudentKey, and so on.

Relationship

established by repeating one field, usually the primary key field, from one table in a second table, usually as a foreign key.

many-to-many relationship

means that each record in the primary entity can have many related records in a second entity and each record in the second entity can have many related records in the primary entity. -legal in logical design, but no DBMS can implement them.

one-to-one relationship

means that for every one record in the primary key table, there is no more than one related record in the foreign key table. -They can be used to rid an entity of null (empty) attributes that inevitably result when contents of an entity have different attributes. -They are sometimes used when data is split between entities for security reasons.

Foreign Keys

retain the name of the primary key.