Midterms - Handout 3, 4, and 5

• The relational model, introduced by E. F. Codd in 1970, is based on predicate logic and set theory.
• Predicate logic is used extensively in mathematics to provide a framework in which an assertion (statement of fact) can be verified as either true or false.
• Set theory is a part of mathematical science that deals with sets, or groups of things, and is used as the basis for data manipulation in the relational model.
• The relational model has three (3) components:

  1. A logical data structure represented by relations
  2. A set of integrity rules to enforce that the data is consistent and remains consistent over time
  3. A set of operations that defines how data is manipulated
• A table (relation) is as a two-dimensional structure composed of rows and columns.
• Each table row (tuple) represents data about an entity
• 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
• Each column has a specific range of values known as the attribute domain.
• The order of the rows and columns is not important in a DBMS.
• A key is an attribute or group of attributes that determines the values of other attributes.
• Determination is the state in which knowing the value of an attribute makes it possible to determine the value of another.
• Determination - It is based on the relationships among the attributes.
• Functional dependence means that the value of one or more attributes determines the value of one or more other attributes.
• The attribute whose value determines another is called the determinant or the key.
• The attribute whose value is determined by the other attribute is called the dependent.
• The standard notation for representing the relationship between attributes is: ATT_A → ATT_B
• STU_NUM → STU_LNAME: STU_NUM is the determinant and STU_LNAME is the dependent. When given a value for STU_NUM, you can determine the value for STU_LNAME because only one (1) value of STU_LNAME is associated with any given value of STU_NUM.
• Functional dependence can involve a determinant that comprises multiple attributes.
• A composite key is a key that is composed of more than one attribute.
• An attribute that is a part of a key is called a key attribute.
• The types of keys are: Superkey, Candidate Key, Primary Key, Foreign Key, and Secondary Key
• Superkey - An attribute or combination of attributes that uniquely identifies any row in the table
• Candidate Key - A superkey without any unnecessary attributes
• Primary Key - A candidate key selected to uniquely identify all other attribute values in any given row; cannot contain null entries
• 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.
• Secondary Key - An attribute or combination of attributes used strictly for data retrieval purposes.
• Integrity Rules

  • An entity integrity is the condition in which each row in a table has its own unique identity.

  • A referential integrity is the conditional in which every reference to an entity instance by another entity instance is valid.

  • Integrity rules are followed to maintain a good database design.
• Entity Integrity - Requirement of this is that all primary key entries are unique, and no part of a primary key may be null.
• Entity Integrity - Purpose of this is that each row will have a unique identity, and foreign key values can properly reference primary key values.
• Entity Integrity - Example of this is no invoice can have a duplicate number, nor can it be null; in short, all invoices are uniquely identified by their invoice number.
• Referential Integrity - Requirement of this it that 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.
• Referential Integrity - Requirement of this is that every non-null foreign key value must reference an existing primary key value
• Referential Integrity - Purpose of this is that it is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry
• Referential Integrity - Purpose of this is that 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.
• Referential Integrity - Example of this is a customer might not yet have an assigned sales representative (number), but it will be impossible to have an invalid sales representative (number)
• To avoid nulls, special codes called flags are used to indicate the absence of some value.
• If such a flag is used, the TABLE_NAME table must contain a dummy row with an ATTRIBUTE value of N
• Integrity rules are followed to maintain a good database design.