Quiz 2: Database Design

Normalization

- a technique for producing a set of relations (tables) with desirable properties, given the data requirements of the enterprise. - includes the minimal number of attributes to support the requirements - attributes that are closely related or functionally dependent should be grouped together into their own distinct table - redundancy should be minimized by ensuring that particular attributes occur only once across tables (except FK)

Cardinality

- describes maximum number of possible relationship occurrences for an entity participating in a given relationship type - the number of tuples in a relation

Constraints on Specialization / Generalization

- mandatory and disjoint - optional and disjoint - mandatory and nondisjoint - optional and nondisjoint.

Composite Key

A candidate key that consists of two or more attributes.

Subclass

A distinct subgrouping of occurrences of an entity type

candidate key

A superkey such that there is no proper subset of attributes that is also a superkey.

single-valued attribute

An attribute that can have only one value.

Superkey

An attribute, or set of attributes, that uniquely identifies a tuple within a relation.

Attribute Inheritance

An entity in a subclass represents same 'real world' object as in superclass, and may possess subclass-specific attributes, as well as those associated with the superclass.

Superclass

An entity type that includes one or more distinct subgroupings of its occurrences.

simple attribute

Attribute composed of a single component with an independent existence.

composite attribute

Attribute composed of multiple components, each with an independent existence.

derived attribute

Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type.

Primary Key

Candidate key selected to uniquely identify each occurrence of an entity type

Alternate keys

Candidate keys that are not selected to be primary key.

Weak entity type

Entity type that is existence-dependent on some other entity type - doesn't have a PK or part of composite PK is a FK to a PK in another table

Strong Entity Type

Entity type that is not existence-dependent on some other entity type. - always has a PK

Entity Types

Group of objects with same properties, identified by enterprise as having an independent existence.

Partial functional dependency

If there is an attribute of A that can be removed while still maintaining the dependency.

Full functional dependency

Indicates that if A and B are attributes of a relation, B is fully functionally dependent on A if B is functionally dependent on A, but not on any proper subset of A.

Relation Schema

- A named relation defined by a set of attribute and domain name pairs.

Foreign Key

- An attribute that references a candidate key in another table. - Usually the referenced candidate key is a primary key.

Disjoint constraint

- Describes relationship between members of the subclasses and indicates whether member of a superclass can be a member of one, or more than one, subclass. - May be disjoint or nondisjoint.

Participation constraint

- Determines whether every member in superclass must participate as a member of a subclass. - May be mandatory or optional.

Attribute

- Property of an entity or a relationship type. - Attributes describe entities and have values, they correspond to fields

Relational type

- Set of meaningful associations among entity types. - represented by lines, they correspond to foreign keys

Candidate Key

Minimal set of attributes that uniquely identifies each occurrence of an entity type.

Degree of a Relationship

Number of participating entities in a relationship. - two is binary - three is ternary - four is quaternary

Specialization

Process of maximizing differences between members of an entity by identifying their distinguishing characteristics.

Generalization

Process of minimizing differences between entities by identifying their common characteristics

Recursive Relationship

Relationship type where same entity type participates more than once in different roles.

Attribute Domain

Set of allowable values for one or more attributes.

Relational database schema

Set of relation schemas, each with a distinct name

Insertion anomaly

Tuple being inserted may be inconsistent with data in other tuples in the table

Fan Trap

Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous

Chasm Trap

Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences

Tuple

a row in a table that contains data about a specific item in a database table

Modification Anomaly

changing data in a row forces changes to other rows because of duplication

Deletion Anomaly

deleting rows may cause a loss of data that would be needed for other future rows

Functional Dependency

describes a relationship between attributes within a relation / table. Ex: A and B are sets of one or more attributes. B is functionally dependent on A (denoted A → B) if each value of A is associated with exactly one value of B.

Participation

determines whether all or only some entity occurrences participate in a relationship

Multi-valued attribute

having the potential to contain more than one value for an attribute

table schema

is defined by the name of the table, the names of all of the columns, and their datatypes.

database schema

is the set of table schema that are part of the database.

multiplicity

number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship - one-to-one (1:1) - zero to one (0:1) - one-to-many (1:*) - zero tp many (0:*) - many-to-many (*:*)

degree

the number of attributes in a relation

Second Normal Form (2NF)

• Applies only to tables in 1NF that have a composite key • A relation with a single-attribute primary key is automatically in at least 2NF • 1NF → 2NF: remove partial key dependencies by table decomposition

Third Normal Form (3NF)

• No dependencies between 2 non-key attributes • No non-key attribute is transitively dependent on the primary key • Solution: decompose the table so that the offending attribute is in a separate table

Unnormalized Form (UNF)

• No primary key or there are NULL values in the "primary key" fields • Duplicate rows • A table that contains an attribute with one or more repeating groups

First Normal Form (1NF)

• Tuples in a relation must contain the same number of fields • The domain of each attribute must be the same • The value of each attribute contains only a single value.