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.