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