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.