Chapter 3

weak entity (conditions)

-existence-dependence -it has a primary key that is partially or totally derived from parent entity in the relationship.

column

-has a distinct name -all values must conform to the same data format

one-to-many (1:M)

-norm for relational databases (relationships within the relational database)

composite entity

Helps avoid problems inherent to M:N relationships, includes the primary keys of tables to be linked

null

absence of any data value -unknown attribute value, known but missing attribute value or inappropriate condition.

joins

allow information to be intelligently combined from two or more tables.

integrity rules

are very important to good database design.

unary relationship

association is maintained within a single entity

primary key

attribute or combination of attributes that uniquely identifies a row.

composite attribute

attribute that can be subdivided to yield additional attributes (name, fname, lname, mi)

simple attribute

attribute that cannot be subdivided

single-valued attribute

attribute that has only a single value.

key attribute

attribute that is part of a key (types of keys)

determinant

attribute whose value determines another.

derived attribute

attribute whose value is calculated from other attributes. derived using an alogorithm

dependent

attribute whose value is determined by the other attribute.

multivalued attributes

attributes that have many values.

association

between entites that always operate in both direction.

union

combines all rows from two tables, excluding duplicate rows.

entity integrity

condition in which each row in the table as its own unique identity. -all of the values in the primary key must be unique -no key attribute in the primary key can contain a null. requirement: all primary key entries are unique, and no part of a primary key may be null purpose: each row will have unique identity, and foreign key values can properly reference primary key values.

keys

consists of one or more attributes that determine other attributes. ensure that each row in a table is uniquely identifiable. -establish relationships among tables and to ensure the integrity of the data.

connectivity

describes the relationship classification -include 1:1, 1:M, and M:N.

data dictionary

description of all tables in the database created b the user and designer.

database designer

determines whether an entity is weak. -based on business rules (weak entity)

synonym

different names are use to describe the same attribute

Optional attribute

does not require a value and can be left empty

table column

each represents an attribute

Relational Database Model

enables logical representation of the data and its relationships.

relational database managament systems

enforce integrity rules automatically -much safer to make sure the application, design conforms to entity and referential integrity rules.

full functional dependence

entire collection of attributes in the determinant is necessary for the relationship.

participants

entities that participate in a relationship

existence independence

entity exists aprt from all of its related entities. -referred to as a strong entity or regular entity.

existence dependence

entity exists in the database only when it is associated with another related entity occurrence.

referential integrity

every reference to an entity instance is void. requirement: a foereign key may either have 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 to which it is related (every non-null foreign key value must reference an existing primary key value.) purpose: it is possible for an attribute not to havea corresponding value but it will be impossible to have an invalid entry; the enforcement of the referential integrity makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table.

cardinality

expresses the minimum and manximum number of entity occurrences associated with one occurrence of related entity. -in ERD, it is indicated by placing the appropriate numbers beside the enities, using the format (x,y).

entity relationship model

forms the basis of an entity relationship diagram (ERD) -conceptual database as viewed by the end user

many-to-many (M:N)

implemented by creating a new entity in 1:M relationships with the original entities.(relationships within the relational database)

relationship degree

indicates the number of entities or partcipants associated with a relationship

output

is a single columns that contians all values from the second column of the dividend that are associated with every row in the divisor.

the logical view

is facilitated by the creation of data relationships based on a logical construct called a relation

table

is perceived as a two-diemnsional structure composed of rows and columns. -must have an attribute or combination of attributes that uniquely identifies each row.

superkey

key that can uniquely identify any row in the table

composite key

key that is composed of ore than one attribute. (types of keys)

secondary key

key used strictly for data retrieval purposes.

equijoin

liks tables on the basis of an equality condition that compares specified columns of each table.

natural join

links tables by selecting only the rows with coomon values in their common attribute

thetajoin

links tables using an inequality comparison operator

outer join

matched pairs are retained and unmatched values in the outer table are left null.

relation

mathematical construct

candidate key

minimal superkey

Required attribute

must have a value and cannot be left empty.

database designers

must often make design compromises tjat are triggered by conflicting goals. -database design must conform to design standards -high processing speed may limit the number and complexity of logically desireable relationships -maximum information generation may lead to loss of clean design structure and high transaction speed.

entities

object of interest ot the end user. -refers to the entity set and not to a single entity occurrence. -ERm corressponds to a table-not a row-in the relational environment. -ERM refers to a table row as an entitity instance or entity occurrence. -In Chen, Crow's foot and UML notations, an entity is represented by a rectangle that contains the entity's name. -the name -noun-isusually wirtten in all capital letters (database main components)

one-to-one (1:1)

one enitity can be created to only one other entity and vice versa (relationships within the relational database)

mandatory participation

one entity occurence requires a corresponding entity ocurrence in a particular relationship.

optional participation

one entity occurrence does not require a corresponding entity occurence in a prticular relationship

inner join

only returns matched reasons from the tables that are being joined

composite identifier

primary key composed of more than one attribute

foreign key

primary key of one table that had been placed into another table to create a common attribute.

strong (identifying) relationships

primary key of the related entity contains a primary key component of the parent entity.

weak (non-identifying)relationship

primary key of the related entity does not contain a primary key componenet of the parent entity.

recursive relationship

relationship exists within a single entity type -common in manufacturing industries

intersection of a row and column

represents a single data value

table row

represents a single entity occurence within the entity set.

homonym

same name is used to label different attributes

domain

set a possible values (date and int) for a given attribute.

determination

state in which knowing the value of one attribute makes it possible to determine the value of another. -establishes the role of the key -based on the relationships among the attributes. (dependences)

system catalog

system data dictionary that describes all objects within the database

union-comapatible

tables share the same number of columns, and their corresponding columns share compatible domains.

attribute domain

this is known as the specific range of values in each column.

ternary relationship

three entities are associated

binary relationship

two entities are associated

select (restrict)

unary operator that yeilds a horizontal subset of a table.

project

unary operator that yeilds a veritical subset of a table

associative (composite) entities

used to represent an M:N relationship between two or more entities. -has a 1:M relationship with the parent entities. -composed of the primary key attributes of each parent entity. -may also contain additional attributes that play no role in connective process

Divide

uses on double-column tabke as the divident and one -single-column table as the divisor

Functional Dependence

value of one or more attributes determines the value of one or more other attributes.

left outer join

yields all of the rows in the first table, including those that do not have a matching value in the second table.

right outer join

yields all of the rows in the secind table, including those that do not have matching values in the first table

product

yields all possible pairs of rows from two tables

difference

yields all rows in one table that are not found on the other table. -tables must be union-comaptible to yield valid results.

intersect

yields only the rows that appear in both tables. tables must be union-compatible to yield valid results.

logical simplicity

yields simple and effective database design methodologies.