Exam Two Flashcards
data are organized in the form of tables/relations, rows, columns
data structure
powerful SQL operations for retrieving and modifying data stored in the relation
data manipulation
mechanisms for implementing business rules that maintain integrity of manipulated data
data integrity
named, 2D table of data, rows (records), columns (fields)
relation
to qualify a table as a relation what six things must it have?
unique table name
every attribute value must be atomic (not multivalued or composite)
every row must be unique
attributes/columns must have unique names
order of columns must be irrelevant
order of rows must be irrelevant
used as indexes to speed up response to user queries
key fields
unique identifiers of relations, guarantees all rows are unique
primary keys
identifiers that enable a dependent relation (on many side of relationship) to refer to its parent relation (on the one side)
foreign keys
first step of relational model
mapping regular entities to relations
what to do with composite attributes in relational model?
use only in their simple component attribute form
what to do with multivalued attributes in relational model?
become separate relation with foreign key taken from the superior entity
second step of relational model
mapping weak entities
what to do with weak entities in relational model?
becomes separate relation with foreign key taken from the superior entity
what is the primary key composed of for a weak entity?
partial identifier of weak entity and primary key of identifying relation
third step of relational model
mapping binary relationships
what to do with a one to many relationship in relational model?
primary key on one side becomes foreign key on the many side
what to do with a many to many relationship in relational model?
create a new relation with the primary keys of the two entities as its primary key, composite primary key
what to do with a one or one relationship in relational model?
primary key on mandatory side becomes foreign key on optional side
fourth step of relational model
mapping associative entities
what to do with associative entity if identifier is not assigned in relational model?
default primary key is composed of primary keys of two entities
what to do with associative entity if identifier is assigned in relational model?
make primary keys into foreign keys
fifth step of relational model
mapping unary relationships
what to do with unary relationships that are one to many in relational model?
recursive foreign key in the same relation, make new field and make it the foreign key
what to do with unary relationships that are many to many in relational model?
make two relations, one for associative relation where primary key has two attributes both from primary key
sixth step of relational model
mapping ternary relationships
what to do for ternary relationships in relational model?
one relation for each entity and one for associative, associative entity relation has foreign keys to each entity in the relationship
seventh step of relational model
mapping supertype/subtype relationship
what to do for supertype/subtype relationship in relational model?
one relation for supertype and one for each subtype, primary key of supertype becomes primary key of subtype discrimination with a different name
how to treat partial and overlapping for supertype/subtype relationship in relational model?
partial- can be unknown
overlapping- treat as composite attribute and take them seperately
allowable values for an attribute (includes data types and restrictions on values), all of the values that appear in a column of a relation must be from the same domain
domain constraints
no primary key attributes may be null, all primary key values must contain data values that must be unique, no two rows the same
entity integrity
rules that maintain consistency between the rows of two related tables, foreign key value on many side must match a primary key values on one side
referential integrity
what are the three parts of referential integrity?
restrict, cascade, set-to-null
don’t allow delete of parent side if related rows exist in dependent side
restrict
automatically delete dependent side rows that correspond with the parent side rows to be deleted
cascade
set foreign key in the dependent side to null if deleting from parent side (not allowed for weak entities)
set-to-null
process of decomposing relations with anomalies to produce smaller, well-structured relations, deciding which attributes should be grouped together in a relation so that all anomalies are removed
data normalization
primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data
purpose of data normalization
contain minimal data redundancy and allow users to insert, delete, and update rows without causing data inconsistencies, avoid anomalies
well-structured relations
adding new rows forces a user to create duplicate data, redundant
insertion anomaly
deleting rows may cause a loss of data that would be needed for other rows
deletion anomaly
changing data in a row forces changes to other rows because of duplication
modification anomaly
value of one attribute (the determinant) determines the value of another attribute, constraint between two attributes or two sets of attributes
functional dependency
unique identifiers, one will become the primary key
candidate key
first normal form
no multivalued attributes
second normal form
no partial dependencies
third normal form
no transitive dependencies
short text statements part of a graphical representation with each relation in a rectangle with attributes
structure of DB in schema
set of values that may be assigned to an attribute
domain
domain name, meaning, data type, size/length, allowable values/range
what is included in domain definition
value that may be assigned to an attribute when no other value applies or when the applicable value is unknown
null
errors or inconsistencies in a table when a use attempts to update data
anomalies
what are the types of anomalies?
insertion, deletion, modification
minimize data redundancy, simply enforcement of preferential integrity constraints, make it easier to maintain data, provide better design that is an improved representation of the real world and a stronger basis for future growth
goals of normalization
state of a relation that requires that certain rules regarding relationships between attributes (or functional dependencies) are satisfied
normal form
attribute on left side of functional dependency
determinant
when a nonkey attribute is functionally dependent on part (but not all) of the primary keys
partial functional dependency
functional dependency between primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute
transitive dependency
multiple attributes that have different names but the same meaning
synonyms
alternative names used for an attribute
alias
attribute name that may have more than one meaning
homonym
synonyms, homonyms, transitive dependencies, supertype/subtype relationships
redundancy problems that are removed by merging relations
hierarchy of SQL
cluster (computer)
database
schema (folder)
table
how to put composite key in SQL
PRIMARY KEY (PK1, PK2);
standard for relational database management systems, created 1974-1979 by IBM
structured query language (SQL)
DBMS that manages data as collection of tables, all relationships represented by common values in related tables
RDBMS
specify syntax/semantics for data definition and manipulation, definitely data structures and basic operations, enable portability of DB definition and application modules, specify minimal and complete standards, allow for later growth/enhancement to standard
original purpose of SQL standard
reduced training costs, productivity, application portability, application longevity, reduced dependence on a single vendor, cross-system communication
benefits of standardized relational language
set of schemas that constitute description of a DB whether or not a user created it
catalog
structure that contains descriptions of objects created by user (tables, views, constraints), collection of related objects associated with catalog
schemas
commands that define a DB, including creating, alternating, dropping tables, establishing constraints
data definition language (DDL)
commands that maintain and query a DB
data manipulation language (DML)
commands that control a DB, including admin privileges and committing data
data control language (DCL)
catalog, schema, DDL, DML, DCL
SQL environment
strings, binary, number, temporal, boolean
SQL data types
steps in table creation
identify data types
identify columns that can/can’t be null
identify columns that must be unique
identify primary key - foreign key mates
determine default values
identify constraints on columns (domains)
create table and associated indexes
choice of indexes, file organizations for base table, file organizations for indexes, data clustering, statistics maintenance
control processing/store efficiency
single value returned from SQL query with aggregate function
scalar aggregate
multiple values returned from SQL query with aggregate function
vector aggregate
relational Operation that causes two or more tables with a common domain to be combined into a single table or view
join
an equi-join where one of the duplicate columns is eliminated in the result table
inner join
what is inner used for?
alternative to where close to match primary and foreign keys
components of relational model
data structure
data manipulation
data integrity
