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.