MIT Database Management

Relationship Instance

(1) association between entity instances (2) specific relationship between 2 tables in a database

Data Model

(1) model of users' data requirements, usually in ER model (2) language for describing structure and processing of a database

attribute domain

(1) set of all possible values an atribute can have (2) description of the format (data type, length) and the semantics (meaning) of an attribute

What is a Well Formed Relation?

*Every determinant must be a candidate key* and any relation that is not ____ should be broken into two or more relations that are _____ .

Where is seed located (know how to identify) in Relational Model?

...?

file

A collection of data stored in one unit, identified by a filename. It can be a document, picture, audio or video stream, data library, application, or other collection of data;a set of related records (either written or electronic) kept together

Entity Type

A collection of entities that share common properties or characteristics.

Transitive Dependency

A condition in which an attribute is dependent on another attribute that is not part of the primary key.

Functional Dependency

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute. ex: PROJ_Num--> PROJ_Name

Associative Entity

A data entity that represents a many-to-many relationship between two other data entities. (commonly used in contracting and assignment applications)

Relationship Type

A meaningful association between (or among) entity types.

Enterprise Key

A primary key whose value is unique across all relations.(unique throughout entire database)

Ternary Relationship

A simultaneous relationship among the instances of three entity types.

Entity Instance

A term used in ER modeling to refer to a specific table row. Also known as an entity occurrence.

entity instance

A term used in ER modeling to refer to a specific table row. Also known as an entity occurrence.

Binary Relationship

An ER term used to describe an association (relationship) between two entities. Example: PROFESSOR teaches COURSE.

Unary Relationship

An ER term used to describe an association within an entity. Example: A COURSE is a prerequisite to another COURSE.

Composite Attribute

An attribute that can be further (broken down) subdivided to yield additional attributes. For example, a phone number (615-898-2368) may be divided into an area code (615), an exchange number (898), and a four-digit code (2368). Compare to simple attribute.

Multivalued Attribute

An attribute that can have many values for a single entity occurrence. For example, an EMP_DEGREE attribute might store the string "BBA, MBA, PHD" to indicate three different degrees held.

Simple Attribute

An attribute that cannot be broken down into smaller components.(contrast to composite attribute)

Stored Attribute

An attribute that supplies a value to the related attribute. Ex: Date of Birth (age can be derived from this)

Existence-independent

An entity that can exist apart from one or more related entities. It must be created first when referencing an existence-dependent table to it.

weak entity

An entity that displays existence dependence and inherits the primary key of its parent entity. Example: A DEPENDENT requires the existence of an EMPLOYEE.

Associative Entity 2

An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances.

modification action

Any insert, delete, or update action.

Multivalued Dependency

Condition in a relation with three or more attributes in which independent attributes appear to have relationships they do not have.EX: STUDENT linked to A=ID B=Contact Info C=Address...STUDENT can have multiple values of A, B, and C, but A,B ,C have no relationship

Partial Functional Dependency

Dependence of a non-key attribute (column) on a "portion" of the primary key in it's table.

Entity Integrity (vs Referential Integrity)

Every entity (record, row) must have a primary key. This should be a unique value and can not be a null value; it cannot be left blank.

Conceptual Data Model

Ex: ER Diagram

Logical Data Model

Ex: FD Diagram

strong entity

Exists independent of other entities, An entity whose data is meaningful without having to reference another entity in the data model

Child Entity

In a database one-to-many relationship, the entity on the many side, A row, record, or node on the many side of a one-to-many relationship.

Recursive Relationship

Occurs when an entity has a relationship to itself; A relationship that is found within a single entity type. For example, an EMPLOYEE is married to an EMPLOYEE or a PART is a component of another PART

Parent Entity

On the one side of a one-to-many relationship

Iterative Process

Process based on repetition of processes and procedures. Database design is ____

Granularity

Refers to the level of DETAIL represented by the values stored in a table's row. Data stored at their lowest level of granularity are said to be atomic data.

1NF

Remove multi-valued attributes. A relation that has a primary key, no repeating groups, and all attributes are atomic

2NF

Remove partial dependencies. A relation in 1st normal form, non-key attributes are dependent on and determined by the entire primary key.

3NF

Remove transitive dependencies. A relation in 2nd normal form that has no transitive dependencies(ex:functional dependency between 2 or more nonkey attributes); Violated when a nonkey column is a fact about another nonkey column.

Referential Integrity Constraint

Rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null

database schema

Something that shows the structure of a database as supported by the DBMS, A complete logical view of a database.

Identity Increment

The amount by which each auto-generated surrogate key value is increased for each new row....?

Denormalization

The rejoining of relations that were decomposed during normalization.

Referential Integrity (vs Entity Integrity)

This is a property of a relationship in Access which tells Access to take the relationship seriously by enforcing the foreign-key constraint. Entering a value in the foreign-key column of one table will require that that value already exist in the primary-key column of the other.

Cascade Update

When the primary key field is updated in a relationship, the corresponding foreign key value in the child table's related records automatically updates.

table

a database structure of rows and columns to create cells that hold data values, aka relation in relational database

unique key

a key that identifies a unique row

non-unique key

a key that potentially identifies more than 1 row

column

a logical group of bytes in a row of a relation or a table. The meaning of a ____ is the same for every row of the relation

Normalization

a method for analyzing and reducing a relational database to its most streamlined form for minimum redundancy, maximum data integrity, and best processing performance; The process of following the guidelines for properly designing a relational database that is free from delete, insert, and update anomalies

Non-identifying Relationship

a relationship in which each participating entity has its own independent primary key

tuple

a row

database

a self-describing collection of related records or for relational tables, of related tables

Functional Dependency

a set of attributes x which uniquely determines a set of Y attributes

Relational DBMS

a type of logical database model that treats data as if they were stored in two-dimensional tables. It can relate data stored in one table to data in another as long as the two tables share a common data element

surrogate key

a unique, system-supplied identifier used as the primary key of a relation. The values have no meaning to the users and usually are hidden on forms and reports

self describing

adh, means that a database contains, within itself, a description of its contents

null values

an attribute value that has never been supplied. Ambiguous can mean value is unknown, inappropriate, , or is known to be blank.

Derived Attribute

an attribute who's value is CALCULATED from related attribute(s); does not need to be physically stored in a database, it can be derived via algorithm Ex: Age = current date - birth date.

Relationship Class

associations among 2 entities, objects, or rows of relations

Required Attribute

attribute that MUST have a value for each entity instance.(optional may not have)

Discriminator

attribute that cannot be represented in relational designs.

foreign key

attribute that is a key of one or more relations other than the one in which it appears

primary key

candidate key selected to be the key of a relation. Uniquely identifies the records (rows) in a table (relation).

Cardinality

constraint on the # of instances of 1 entity that can (or must) be associated w/each instance of another entity

metadata

data concerning the structure of data in a database stored in the data dictionary. used to describe tables, columns, constraints, indexes, etc.

data vs. information

data= values of qualitative or quantitative variables, belonging to a set of items info= sequence of symbols that can be interpreted as a message

Structure Query Language (SQL)

defines structure and processing of a relational database, national requirements/standard/model for relational database

Degree of a Relationship

degree of relationship: # of entity types that participate in that relationship

Functional Dependency Diagram (FDD)

depicts relationship between attributes where 1 attribute or group of attributes determines the value of another (given the value of X, we can determine the value of Y)

anomaly

deviation from the normal or common order or form or rule; abnormality

Supertype Entity

entity or object containing subtypes. Ex: EMPLOYEE is a supertype of ENGINEER, ACCOUNTANT, and MANAGER

Subtype Entity

entity or object that is a lower category of a higher-level types; i.e. ENGINEER is a ..... of EMPLOYEE

modification problem

exists when storing one row in a table records facts about 2 themes or deletion of a row removes facts about 2 themes, or when data change must be made in multiple rows for consistency

row

group of columns in a table. All the columns in a ____ pertain to the same entity. Also known as tuple or record.

record

group of field pertaining to the same entity, used in file-processing systems; in the relational model, a synonym for row and tuple

composite key

key of a relation that consists of 2 or more columns

field

logical group of bytes in a record used with file processing ; in the context of the relational model, a synonym for attribute

Entity Class

often called a table, is a collection of similar entities. Ex. customer, order, order line, product, distributor

determinant

one or more attributes that functionally determineanother attribute or attributes.The attribute on the left-hand side of the arrow in a functional dependency: (A,B) --> D,C ...attributes (A,B) are the ______

BCNF

relation in 3rd normal form where every determinant is a candidate key

Identifying Relationship

relationship between a strong entity and its child. child has parent's PK in its composite PK weak; "strong relationship"

Cascade Delete

searches the database and deletes all of the related records; ex. Delete a customer and all his orders will be deleted, too.

DBMS

set of programs used to define, administer, and process a database and its applications

entity

something of importance to a user that needs to be represented in a database, restricted to things that can be represented by a single table

Business Rule

statement that defines or constrains some aspect of the business

relation

two-dimensional array containing single-value entries and no duplicate rows. meaning of the columns are the same in each row. order of the rows arnd columns is immaterial

attribute

value that represents a characteristic of an entity; column of a relation

concurrency (transactions)

when two or more transactions are processed against a database at the same time