Chapter 4 Flashcards
Data Structure
Data are organized in two-dimensional tables (also called relations) with columns and rows
Data Manipulation
Data stored in the tables may be manipulated through the use of a command language (Structured Query Language – SQL – was developed expressly for this purpose)
Data Integrity
Business rules may be defined that maintain the integrity of the data that is manipulated
Relational Database Model Constraints–Domain Integrity
constrains allowable values for columns(e.g., data type, column size, maximum value, etc.)
Relational Database Model Constraints–Policy Integrity
constrains data operations to business rules (e.g., only managers may place vendor orders)
Relational Database Model Constraints–Entity Integrity
prohibits null values for primary key column
Relational Database Model Constraints–Referential Integrity
constrains a foreign key value to match a primary key value in a related table
Example: For every value of CustomerID in the Order table there must be a matching value of CustomerID in the Customer table
Properties of Relational Tables–table (relation)
Each table (relation) in a given database has a unique name
Properties of Relational Tables-column (attribute)
within a given table has a unique name
- -Every column (attribute) is single-valued
- -Thus, multivalued attributes require special teatment when designing relational tables
Properties of Relational Tables–row (tuple)
Every row (tuple) in a table is unique
Primary Key (PK) (analogous to entity identifier)
A column (or columns) whose value uniquely identifies or differentiates each row in a table(e.g., EmployeeID)
Composite Key
a primary key made up of more than one column
e.g., FirstName + MiddleName + LastName
Foreign Key (FK)
A column in one table that serves as the primary key of another table in the same database (thus serving as a link between the two tables)
Candidate Key
If a table has more than one column that provides a way of uniquely identifying the rows of the table, then they are each called a candidate key
When there is more than one candidate key, one of them must be chosen to be the primary key of the table
alternate key
A candidate key that is not chosen to be the primary key of a table
Foreign Keys– additional rules
The foreign key column in one table must have the same domain of values as the primary key column in the linked table
–Two columns have the same domain of values if the columns have values of the same type (e.g., integer numbers; see previous slide)
Logical Database Design
The process of arranging the entities and attributes of the conceptual data model of the business environment into the tables and columns of a relational database structure to serve that business in an information system
The goal is to create well-structured tables (i.e., free of anomalies) that properly reflect the organization’s business environment
Anomalies
are problems that are experienced when attempting to manipulate stored data
Well-Structured Tables
Tables that contains minimal redundancy and that allow users to insert, delete, and modify table rows without errors or inconsistencies are considered to be well-structured
When designing relational database tables, we seek to eliminate anomalies through the use of normalization
Insertion Anomalies
are experienced when we attempt to store a value for a column but cannot because the value of another column is unknown
–e.g., cannot add a new customer’s information until an order number is ready to be entered (because OrderID column serves as the primary key for the table and cannot have null values)
Deletion Anomalies
are experienced when a value for a column we wish to keep is unexpectedly removed when a value for another column is deleted
e.g., cannot delete the sole order for a customer without deleting the only copy of the customer’s information also
Update Anomalies
are experienced when changes to multiple rows of a table are needed to effect an update to a single value of a column
e.g., cannot completely update a customer’s address without changing it for every order placed by that customer
Map Binary Relationships/schemas–One-to-Many
Primary key on the one side becomes a foreign key on the many side
Map Binary Relationships/schemas–Many-to-Many
- Create a new table; the primary key of the new table is typically a CPK comprised of (at least) the primary keys of the two entities involved in the relationship
Map Binary Relationships/schemas–One-to-One
Primary key on the mandatory side becomes a foreign key on the optional side (if optionalities are asymmetric)
Unary (Recursive) Relationships–One-to-Many
Recursive foreign key in the same table (also true for unary One-to-One)
Unary (Recursive) Relationships–Many-to-Many
(e. g., bill of materials): Two tables result:
- -One for the entity type
- -One for an associative relation in which the primary key has two fields, both taken from the identifier of the original entity
Unary (Recursive) Relationships–Ternary (and n-ary) Relationships
- -One table for each original entity and one for the common relationship (associative entity) (i.e., a ternary relationship maps to a total of four tables)
- -Table representing the associative entity has foreign keys to each entity in the relationship
- -PK of the table formed for the associative entity is typically a composite PK composed of (at least) the primary keys of the three entities
Map Supertype/Subtype Structures
- -Create a separate table for the supertype and each of the subtypes
- -Assign common attributes, including subtype discriminator, to the supertype table
- -Assign to the subtype tables those attributes unique to each subtype
- -Assign to the subtype tables the primary key of the supertype table (which also functions as a FK referencing the supertype)
Data Normalization
- -A formal process for grouping attributes into tables
- -A tool to validate and improve logical designs so that they satisfy certain constraints to avoid unnecessary duplication of data
- -The process of decomposing a table with anomalies into two or more, smaller, well-structured tables
Functional Dependency
The value of one attribute (the determinant) determines the value of another attribute
A—>B
We say here that “A determines B” or
“B is functionally dependent on A”
First Normal Form
Table has no multivalued attributes
- -a table that has multivalued attributes is unnormalized
- -in this context, a multivalued attribute is sometimes referred to as a repeating group
Identify the functional dependencies in the table and draw the arrows
Second Normal Form
Table is in 1NF and has no partial functional dependencies (that is, every nonkey attribute is fully functionally dependent on the entire primary key)
what is the Solution to get to 2NF
The solution to the problem with the previous table is to break it into two related tables to achieve 2NF (at least)
Third Normal Form
Table is in 2NF and no transitive dependencies (functional dependencies between nonkey attributes)
–This means that no nonkey attribute should be able to determine another nonkey attribute
how to remove transitive dependancies?
–For each nonkey attribute that is a determinant in a table, create a new table; that attribute becomes the primary key of the new table
–Move all of the attributes that are functionally dependent on that determinant attribute from the old table to the new table
–Leave the attribute that serves as the primary key in the new table in the old table to serve as a foreign key to allow the tables to be related
