69 terms

Database Theory Test 1

are raw facts
is the result of processing raw data to reveal a meaning
is used to define and store data
a logically connected set of one or more fields that describes a person, place, or thing.
A collection of related records
the intermediary between the user and the database
Data redundency
Same data stored unnecessarily in different places
Data Inconsistency
different and conflicting versions of same data occur at different places
Business Rules
is a brief description of a policy, procedure or principle within a specific organization
5 major parts of a database system
Hardware, Software, People, Procedures, Data
Data anomalies
problems when all changes in redundant data not made correctly
Update anomalies
Some records are updated, some are not; leads to inconsistent data
Deletion anomalies
Deleting a record also deletes information in the record that is only in that record.
Disadvantages of DBMS
Increased costs
Management complexity
Maintaining currency
Vendor dependence
Frequent upgrade/replacement cycles
Characteristics of a relational table
1. A table is perceived as a two-dimensional structure composed of rows and columns.
2. Each table row represents a single entity occurrence within the entity set.
3. Each table column represents an attribute, and each column has a distinct name.
4. Each row/column intersection represents a single data value.
5. All values in a column must conform to the same data format.
6. Each column has a specific range of values known as the attribute domain.
7. The order of the rows and columns is immaterial to the DBMS.
8. Each table must have an attribute or a combination of attributes that uniquely identifies each row
one or more attributes that determine other attributes.
an attribute determines another attribute if you know the value of the first attribute, you can lookup the value of the other attribute
Primary key
attribute or combination of attributes that uniquely identifies any given row
Functional Dependency
The 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
a key may be composed of more than one attribute
Key Attribute
any attribute that is part of a key
Super Key
any key that uniquely identifies each row
Candidate key
a super key without redundancies
Foreign key
a primary key of table A used in table B as an attribute to link to table A
Secondary key
an attribute or combination of attributes used as a sort key to organize the rows of a table and to speed up data retrieval.
is null if no value has been assigned to it. Not same as 0 or blank
Entity Integrity
All primary keys must be unique and no part of a primary key may be NULL. That is, if you give the primary key only one row will be returned.
Referential Integrity
a foreign key may have either a NULL entry as long as it is not part of its table's primary key or be an entry that matches the primary key value of the to which it is related
special codes used when data value is not known
Relational Operators
Select, Project and Join
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
M:N relationships
Cannot be implemented as such in the relational model can be changed into two 1:M relationships
Orderly arrangement to logically 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
Each index is associated with only one table
RDR Information
All information in a relational database must be logically represented as column values in rows within tables
RDR Guaranteed Access
Every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name.
RDR Systematic Treatment of Nulls
Nulls must be represented and treated in a systematic way, independent of data type.
RDR Dynamic On
Line Catalog Based on the Relational Model - The metadata must be stored and managed as ordinary data in tables within the database. Such data must be available to authorized users using the standard database relational language.
RDR Comprehensive Data Sublanguage
The relational database may support many languages, but it must at least support one well-defined declarative language with support for data definition, view definition, data manipulation, integrity constraints, authorization and transaction management
RDR View Updating
Any view that is theoretically updatable must be updatable through the system.
High-Level Insert, Update and Delete - The database must support set-level inserts, updates and deletes.
RDR Physical Data Independence
Application programs and ad hoc facilities are logically unaffected when physical access methods or storage structures are changed
RDR Logical Data Independence
Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of column or inserting columns).
RDR Integrity Independence
All relational integrity constraints must be definable in the relational language and stored in the system catalog, not at the application level.
RDR Distribution Independence
The end users and application programs are unaware and unaffected by the data location (distributed vs local databases)
RDR Nonsubversion
If the system supports low-level access to the data, there must not be a way to bypass the integrity rules of the database
RDR Rule Zero
All preceding rules are based on the notion that in order for a database to be considered relational, it must use its relational facilities exclusively to manage the database
Association between entities
Required attribute
must have a value
Optional attribute
may be left empty
set of possible values for an attribute
one or more attributes that uniquely identify each entity instance
Composite identifier
primary key composed of more than one attribute
Composite attribute
can be subdivided
Simple attribute
cannot be subdivided
Single-value attribute
can have only a single value
Multivalued attributes
can have many values
Derived attribute
value may be calculated from other attributes
Association between entities
are entities that participate in a relationship
Describes the relationship classification
Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity
Optional participation
One entity occurrence does not require corresponding entity occurrence in particular relationship
Mandatory participation
One entity occurrence requires corresponding entity occurrence in particular relationship
Weak (non-identifying) relationships
Exists if PK of related entity does not contain PK component of parent entity
Strong (identifying) relationships
Exists when PK of related entity contains PK component of parent entity
Process for evaluating and correcting table structures to minimize data redundancies
Reduces data anomalies
produces a lower normal form