Exam 1-Chapter 3 Flashcards
Relational Model
View data logically rather than physically
Table
- Two-dimensional structure composed of rows and columns
- Structural an data independence
- Resembles a file conceptually
Entity Set
Contains group of related entity occurrences
Key
- One or more attributes that determine other attributes
- A key’s role is based on determination (if you know the value of attribute A, you can look up or determine the value of attribute B)
Functional Dependence
Attribute B is functionally dependent on the attribute A if each value in column A determines one and only one value in column B
Composite Key
Composed of more than one attribute (Combination of LNAME, FNAME, INIT, and PHONE are likely to be unique)
Ful Functional Dependence
Attribute B is functionally dependent on a composite key A, but not on any subset of A
Key Attribute
Any attribute that is part of a key
Superkey
Any key that uniquely identifies each row. A superkey functionally determines all of the entity’s attributers (STU_NUM)
Candidate Key
A superkey without unnecessary attributes (a minimal superkey; STU_NUM, STU_LNAME is a superkey but not a candidate key, STU_NUM ins a candidate key)
Nulls
No data entry; not permitted in primary key; should be avoided in other attributes; can represent different meaning, such as an unknown attribute value, a known but missing attribute value, and a “not applicable” condition
Flags
Designers use flags to avoid nulls. Flags indicate absence of some value
Controlled Redundancy
Makes the relational database work. Tables within the database share common attribues
Rational Schema
A textual representation of the database tables
Foreign Key
An attribute whose values match primary key values in the related table
Referential Integrity
Foreign key contains a value that refers to an existing valid tuple (row) in another relation
Secondary Key
Key used strictly for data retrieval purposes
Entity Integrity Rules Requirement
All primary key entries are unique, and no part of a primary key may be null
Reference Integrity Rules Requirement
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
Relational Algebra
Defines theoretical way of manipulating table contents using relational operators. Use of relational algebra operators on existing relations produces new relations
SELECT
SELECT all rows in a table that satisfy a given condition. Results in a horizontal subset of the table
PROJECT
Yields all values for selected attributes. Results in a vertical subset of the table
UNION
Combines all rows from two tables, excluding duplicate rows. The tables must be union-compatible (have the same degree (# of columns); columns must be of the same type; column domains (range of permissible values) must be compatible)
INTERSECT
Yields only the rows that appear in both tables. The tables must be union-compatible.
DIFFERENCE
Yields all rows in one table that are not found in the other table. Subtracts one table from the other. The tables must be union-compatible
PRODUCT
Yields all possible rows from two tables. Also known as the Cartesian product
JOIN
JOIN combines data from two or more tables. It is the real power behind the relational database. Tables are linked by a common attribute
Natural Join
Links tables by selecting rows with common values in common attributes
A three stage process: 1) PRODUCT 2) SELECT 3) PROJECT
Inner Join
A join that returns matched records from the tables being joined
Outer Join
Inner join+ returns all the matched records from the tables being joined, plus it returns the unmatched records from one of the two tables
Left Outer Join
Yields all rows from CUSTOMER table, including those that do not have matching value in the AGENT table
Right Outer Join
Yields all rows from AGENT table, including those that do not have a matching value in the CUSTOMER table
Data Dictionary
Provides detailed accounting of all tables found within the database; contains (at least) all the attribute names and characteristics for each table in the system; contains metadata
System Catalog
Contains metadata; detailed system data dictionary that describes all objects within the database (data dictionary + other information, such as user authorizations and access privileges)
1:M Relationship
Relational modeling ideal; should be the norm in any relational database design
1:1 Relationship
Should be rare in any relational database design; one entity related to only one other entity, and vice versa
M:N Relationship
Cannot be implemented as such in the relational model; an M:N relationship can be changed into two 1:M relationship
Data Redundancy
Leads to data anomalies (sometimes, data redundancy is necessary
Index
Orderly arrangement used to logically (an quickly) access rows in a table
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
Composite Index
An index key can have multiple attributes
