The Relational Database Model Flashcards
What does the Relational Database Model do?
It enables logical representation of data and its relationshipsW
What does logical simplicity yield?
It yields simple and effective database design methodologies
What is the logical view facilitated by?
It is facilitated by the creation of data relationships based on a logical construct called a relation
What are the 8 characteristics of a relational table?
Table is perceived as a two-dimensional structure composed of rows and columns
Each table row (tuple) represents a single entity occurence within the entity set
Each table column represents an attribute, and each column has a distinct name
Each intersection of a row and column represents a single data value
All values in a column must conform to the same data format
The order of the rows and columns is immaterial to the DBMS
Each table must have an attribute or combination of attributes that uniquely identifies each row
What does a key consist of?
A key consists of one or more attributes that determine other attributes
What does a key ensure?
It ensures that each row in a table is uniquely identifiable
What does a key establish?
It establishes relationships among tables and to ensure the integrity of the data
What is a primary key?
It is an attribute or combination of attributes that uniquely identifies a row
What is a determination?
It is a state in which knowing the value of one attribute makes it possible to determine the value of another
What does a determination establish?
It establishes the role of a key
What is a determination based on?
It is based on the relationships among the attributes
What is functional dependence?
It is a value of one or more attributes that determines the value of one or more other attributes
What is a determinant?
It is an attribute whose value determines another
What is a dependent?
It is an attribute whose value is determined by the other attribute
What is a composite key?
It is a key that is composed of more than one attribute
What is a key attribute?
It is an attribute that is a part of a key
What is a superkey?
It is a key that can uniquely identify any row in the table
What is a candidate key?
It is a minimal superkey
What is a foreign key?
It is the primary key of one table that has been placed into another table to create a common attribute
What is a secondary key?
It is a key that is used strictly for data retrieval purposes
What is entity integrity?
It is when all primary key entries are unique, and no part of a primary key may be null
What is the purpose of entity integrity?
Each row will have a unique identity, and foreign key values can properly reference primary key values
What is referential integrity?
It is when a foreign key may have either a null entry, as long as it is not a part of its table’s primary key, or an entry that matches the primary key value in a table to which it is related (every non-null foreign key value must reference an existing primary key value)
What is the purpose of referential integrity?
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 could a null represent?
An unknown attribute value
A known, but missing, attribute value
A “not applicable” condition
What are some ways to handle nulls?
Flags
Constraints
What is a flag?
It is a special code used to indicate the absence of some value
What is a constraint? (2)
NOT NULL constraint: placed on a column to ensure that every row in the table has a value for that column
UNIQUE constraint: restriction placed on a column to ensure that no duplicate values exist for that column
What property do relational operators have?
They have the property of closure
What is closure?
It is a property of relational operators that permits the use of relational algebra operations on existing tables (relations) to produce new relations
What is select?
It is a unary operator that yields a horizontal subset of a table (slide 17)
What is project?
It is a unary operator that yields a vertical subset of a table (slide 18)
What is union?
It combines all rows from two tables, excluding duplicate rows (slide 19)
What is union compatible?
It is when tables share the same number of columns, and their corresponding columns share compatible domains
What is intersect?
It is a relational set operator that yields only the rows that appear in both tables
Tables must be union-compatible to yield valid results (slide 20)
What is difference?
It is a relational set operator that yields all rows in one table that are not found in the other table
Tables must be union compatible to yield valid results (table 21)
What is product?
It is a relational operator that yields all possible pairs of rows from two tables
What do joins do?
They allow information to be intelligently combined from two or more tables
What is a natural join?
It links tables by selecting only the rows with common values in their common attribute
What is an inner join?
It only returns matched records from the tables that are being joined
What is an outer join?
It is when matched pairs are retained and unmatched values in the other table are left null
What is a left outer join?
It is a join yields all of the rows in the first table, including those that do not have a matching value in the second table
What is a right outer join?
It is a join that yields all of the rows in the second table, including those that do not have matching values in the first table
What is a data dictionary?
It is a description of all tables in the database created by the user and designer
What is a system catalogue?
It is a system data dictionary that describes all objects within the database
What is one-to-many (1:M)?
It is the norm for relational databases
What is a one-to-one (1:1)?
It is one entity that can be related to only one other entity and vice versa
What is a many-to-many (M:N)?
It is implemented by creating a new entity in 1:M relationships with the original entities
What is a composite entity?
It is an entity that helps avoid problems inherent to M:N relationships
