Database Fundamentals 3 Flashcards
Attribute
Named column in relational table.
It draws values from the domain
Domain
The set of possible values for the attribute
Degree
Number of attributes
Cardinality
Number of rows
Key
A key is one or more attributes that determine other attributes.
Composite key
Composed of more than one attribute
Types of relational database keys
- Superkey
- Candidate key
- Primary key
- Secondary key
- Foreign key
Superkey
An attribute (or combination of attributes) that uniquely identifies each row in a table.
Candidate key
A minimal (irreducible) superkey. A superkey that does not contain a subset of attributes that is itself a superkey.
Primary key
A candidate key selected to uniquely identify all other attribute values in any given row. Cannot contain null values.
Secondary key
An attribute (or combination of attributes) used strictly for data retrieval purposes.
Foreign key
An attribute (or combination of attributes) in one table whose values must either match the primary key in another table or be null.
What can Nulls represent?
- An unknown attribute value.
- A known, but missing, attribute value.
- A “not applicable” condition.
What do flags do?
Flags indicate the absence of some value
What is controlled redundancy?
Control data redundancy by using common attributes shared by tables.
Foreign keys make the relational database work, and enables tables to be linked together. This is crucial to exercising data redundancy control.
What are Integrity Rules?
- Entity Integrity:
- Requirement: All primary key entries are unique, and no part of a primary key may be null.
- Purpose: Each row will have a unique identity, and foreign key values can properly reference primary key values. - Referential Integrity:
- Requirement: A foreign key may have either a null entry, as long as it is not a part of the table’s primary key, or an entry that matches the primary key value in a table to which it is related.
- Purpose: It is possible for an attribute NOT to have a corresponding value, but it will be impossible to have an invalid entry. The enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table.
What is The Data Dictionary?
It provides detailed accounting of all tables found within the database.
It contains (at least) all the attribute names and characteristics for each table in the system.
It also contains metadata, also known as the metadata repository.
1:M Relationship
Should be the norm in any relational database design
1:1 Relationship
Should be rare in any relational database design.
One entity relates to only one other entity and vice versa.
Sometimes this means that entity components were not defined properly.
This could indicate that two entities actually belong in the same table.
M:N Relationships
Cannot be implemented as such in the relational model.
You can avoid problems inherent to M:N relationships by creating a composite/bridge entity. It is implemented by breaking it up into a set of 1:M relationships. It includes as foreign keys the primary keys of tables to be linked.
What are indexes?
Orderly arrangement to logically access rows in a table.
Each index is associated with only ONE table.
Main purpose: To optimize query speed.
Index key
Index’s reference point.
Points to data location identified by the key.
Unique index
Index in which the index key can have only one pointer value (row) associated with it.
