INMT 342 Exam 2
normalization - answer the process of grouping attributes together into tables
it helps to validate and improve logical database design to satisfy constraints and avoid
duplication of data
what do well structured tables avoid? - answer insertion anomalies
update anomalies
deletion anomalies
redundancy
functional dependency - answer the value of one attributes determines the value of
other attributes
a well-structured table has a single functional dependency; an individual's SSN can tell
about every attribute in the table
normalization: 0 normal form - answer data is not in table format
identifier is not defined or bad
table has multi-valued attributes
normalization: 1 normal form - answergood identifier
no multi-valued attributes (fill in blanks or separate lists)
partial functional dependency - answerthe value of non-key fields are functionally
dependent on only part of the primary key field
multiple primary keys that each tell different things about the table; need to be split up
normalization: 2 normal form - answeralready in 1st normal form
has no partial functional dependencies
go from 1NF to 2NF by breaking the original table into two related tables (functional
dependency)
every non-key field must be dependent on the entire primary key
transitive dependency - answerthe value of non-key fields are functionally dependent on
other non-key fields
ex: salary class field determines what goes in salary field
, normalization: 3 normal form - answeralready in 2nd normal form
no transitive dependencies
go from 2NF to 3NF by breaking the original table into two related tables (functional
dependency)
every non-key field must be dependent on only the primary key
denormalization - answerthe process of transforming normalized tables into non-
normalized physical record specifications
trade-off between higher efficiency vs risk of anomalies
advantages of denormalization - answerreduced disk access and greater performance
makes writing SQL statements simpler
disadvantages of denormalization - answerintroduction of anomalies (redundancies)
which will necessitate the need for more data maintenance
structured query language (SQL) - answerthe standard language used in relational
databases for creating databases and manipulation database contents
syntax of SQL statements - answernot case sensitive
can be on more than one line
phrases usually placed on separate lines
keywords cannot be split across lines
each SQL statement should end with a ;
data definition language (DDL) - answercommands used to define a database, including
creating, altering, and dropping tables and establishing constraints
associated with the STRUCTURE of the database
CREATE, ALTER, DROP
data control language (DCL) - answercommands used to control a database, including
administering privileges and the committing of data
associated with ACCESS, CONTROL, AND SECURITY of the database
GRANT, REVOKE
data manipulation language (DML) - answercommands used to maintain and query a
database, including updating, inserting, modifying, and querying data
associated with managing and viewing the data inside the database
normalization - answer the process of grouping attributes together into tables
it helps to validate and improve logical database design to satisfy constraints and avoid
duplication of data
what do well structured tables avoid? - answer insertion anomalies
update anomalies
deletion anomalies
redundancy
functional dependency - answer the value of one attributes determines the value of
other attributes
a well-structured table has a single functional dependency; an individual's SSN can tell
about every attribute in the table
normalization: 0 normal form - answer data is not in table format
identifier is not defined or bad
table has multi-valued attributes
normalization: 1 normal form - answergood identifier
no multi-valued attributes (fill in blanks or separate lists)
partial functional dependency - answerthe value of non-key fields are functionally
dependent on only part of the primary key field
multiple primary keys that each tell different things about the table; need to be split up
normalization: 2 normal form - answeralready in 1st normal form
has no partial functional dependencies
go from 1NF to 2NF by breaking the original table into two related tables (functional
dependency)
every non-key field must be dependent on the entire primary key
transitive dependency - answerthe value of non-key fields are functionally dependent on
other non-key fields
ex: salary class field determines what goes in salary field
, normalization: 3 normal form - answeralready in 2nd normal form
no transitive dependencies
go from 2NF to 3NF by breaking the original table into two related tables (functional
dependency)
every non-key field must be dependent on only the primary key
denormalization - answerthe process of transforming normalized tables into non-
normalized physical record specifications
trade-off between higher efficiency vs risk of anomalies
advantages of denormalization - answerreduced disk access and greater performance
makes writing SQL statements simpler
disadvantages of denormalization - answerintroduction of anomalies (redundancies)
which will necessitate the need for more data maintenance
structured query language (SQL) - answerthe standard language used in relational
databases for creating databases and manipulation database contents
syntax of SQL statements - answernot case sensitive
can be on more than one line
phrases usually placed on separate lines
keywords cannot be split across lines
each SQL statement should end with a ;
data definition language (DDL) - answercommands used to define a database, including
creating, altering, and dropping tables and establishing constraints
associated with the STRUCTURE of the database
CREATE, ALTER, DROP
data control language (DCL) - answercommands used to control a database, including
administering privileges and the committing of data
associated with ACCESS, CONTROL, AND SECURITY of the database
GRANT, REVOKE
data manipulation language (DML) - answercommands used to maintain and query a
database, including updating, inserting, modifying, and querying data
associated with managing and viewing the data inside the database
