INMT 342 EXAM QUESTIONS AND
ANSWERS
physical database design purpose - ANSWER-translate the logical description into
technical specifications for storing & retrieving data
physical database design goal - ANSWER-create a design for storing data that will
provide:
- performance
- data integrity
- data security
- data recoverability
- simplify application development
physical design process inputs - ANSWER-- normalized relations
- volume estimates
- attribute definitions
- response time expectations
- data security needs
- backup/recovery needs
- integrity expectations
- DBMS technology used
physical design process decisions - ANSWER-- attribute data types
- physical record designs (doesn't always match logical design)
- file organizationsterm-5
- indexes & database architectures
- hardware configuration options
data volume & usage analysis - ANSWER-identify likely areas requiring attention for
performance
composite usage maps - ANSWER-- use of tables to capture table sizes & access
rates/expectations
- requirements may also be recorded & displayed using an annotated ERD
data volumes - ANSWER-- volume of data
- size estimate
- disk storage requirement
how many records in entity? - ANSWER-data volume
how much storage needed? - ANSWER-disk storage requirement
how large is each table? - ANSWER-size estimate
access frequencies - ANSWER-- access paths/security
,- file organization
- memory/processor requirement
which tables are accessed? - ANSWER-access paths/security
how are they accessed? - ANSWER-file organization
how often accessed? - ANSWER-memory/processor requirement
designing physical files - ANSWER-- physical file
- tablespace
- file organization
- access method
physical file - ANSWER-a named portion of secondary memory allocated for the
purpose of storing physical records
tablespace - ANSWER-disk storage in which database tables are stored
file organization - ANSWER-how the files are arranged on the disk
access method - ANSWER-how the data can be retrieved based on the file
organization
database design decisions - ANSWER-- normalization
- denormalization
- partitioning
- file organization/indexing
normalization - ANSWER-- the process of GROUPING attributes together into tables
- validates & improves logical database design to SATISFY certain constraints &
avoid duplication of data
- decomposition of tables to remove anomalies & create 2 or more well-structured
tables
creating well structured tables - ANSWER-- contain minimal redundancy
- allows users to insert, modify, & delete the rows in the table without errors or
inconsistencies
well structured tables avoid: - ANSWER-- insertion anomalies
- update anomalies
- deletion anomalies
insertion anomaly example (from normalization problems) - ANSWER-- built a new
Warehouse (900)
- want to add Warehouse 900 into database
- know the Location of Warehouse 900
- doesn't have inventory yet because it's brand new
- record MUST have PartNumber and Warehouse to be - able to insert the new
Warehouse
, - *restricts what can be inserted*
update anomaly example (from normalization problems) - ANSWER-- need to
update Warehouse 500's Location from 123 NW ST to 100 Haslam ST
- problem because it only updates one of the Warehouse 500 records
- if there was a Warehouse table there would only be one record for Warehouse 500
and you would only have to update that one record
deletion anomaly example (from normalization problems) - ANSWER-- stopped
making PartNumber 02
- problem because if 02 is deleted Warehouse 700 is completely lost because it
solely makes PartNumber 02
- whereas Warehouse 500 will be deleted for making PartNumber 02, but will still
exist because it makes other parts
functional dependencies - ANSWER-the value of one attribute determines the other
attribute
functional dependencies example - ANSWER-- what can be determined from a
person's social security number
- SSN# determines name, address, birth date & phone
steps to normalization - ANSWER-- table with multivalued attributed
* (remove multivalued attributes)
- first normal form
* (remove partial dependencies)
- second normal form
* (remove transitive dependencies)
- third normal form
0 normal form - ANSWER-- multi-valued attributes
- bad identifier
1st normal form - ANSWER-- no multi-valued attributes
- one value per "cell"
(SS#, Fname, Lname, Bdate) - ANSWER-good functional dependency example
partial dependency - ANSWER-when the attributes in a tables are dependent on
different key fields or values
(Part#, Fact#, PartName, PartSize, FactoryName) - ANSWER-partial functional
dependency example
2nd normal form - ANSWER-- already in 1term-33NF & every non-key attribute is
fully functionally dependent on the primary key
- this means that every non-key attribute must be determined by the entire primary
key, not by only part of the primary key
- can be created by breaking the original table into two related tables on the basis of
functional dependency
ANSWERS
physical database design purpose - ANSWER-translate the logical description into
technical specifications for storing & retrieving data
physical database design goal - ANSWER-create a design for storing data that will
provide:
- performance
- data integrity
- data security
- data recoverability
- simplify application development
physical design process inputs - ANSWER-- normalized relations
- volume estimates
- attribute definitions
- response time expectations
- data security needs
- backup/recovery needs
- integrity expectations
- DBMS technology used
physical design process decisions - ANSWER-- attribute data types
- physical record designs (doesn't always match logical design)
- file organizationsterm-5
- indexes & database architectures
- hardware configuration options
data volume & usage analysis - ANSWER-identify likely areas requiring attention for
performance
composite usage maps - ANSWER-- use of tables to capture table sizes & access
rates/expectations
- requirements may also be recorded & displayed using an annotated ERD
data volumes - ANSWER-- volume of data
- size estimate
- disk storage requirement
how many records in entity? - ANSWER-data volume
how much storage needed? - ANSWER-disk storage requirement
how large is each table? - ANSWER-size estimate
access frequencies - ANSWER-- access paths/security
,- file organization
- memory/processor requirement
which tables are accessed? - ANSWER-access paths/security
how are they accessed? - ANSWER-file organization
how often accessed? - ANSWER-memory/processor requirement
designing physical files - ANSWER-- physical file
- tablespace
- file organization
- access method
physical file - ANSWER-a named portion of secondary memory allocated for the
purpose of storing physical records
tablespace - ANSWER-disk storage in which database tables are stored
file organization - ANSWER-how the files are arranged on the disk
access method - ANSWER-how the data can be retrieved based on the file
organization
database design decisions - ANSWER-- normalization
- denormalization
- partitioning
- file organization/indexing
normalization - ANSWER-- the process of GROUPING attributes together into tables
- validates & improves logical database design to SATISFY certain constraints &
avoid duplication of data
- decomposition of tables to remove anomalies & create 2 or more well-structured
tables
creating well structured tables - ANSWER-- contain minimal redundancy
- allows users to insert, modify, & delete the rows in the table without errors or
inconsistencies
well structured tables avoid: - ANSWER-- insertion anomalies
- update anomalies
- deletion anomalies
insertion anomaly example (from normalization problems) - ANSWER-- built a new
Warehouse (900)
- want to add Warehouse 900 into database
- know the Location of Warehouse 900
- doesn't have inventory yet because it's brand new
- record MUST have PartNumber and Warehouse to be - able to insert the new
Warehouse
, - *restricts what can be inserted*
update anomaly example (from normalization problems) - ANSWER-- need to
update Warehouse 500's Location from 123 NW ST to 100 Haslam ST
- problem because it only updates one of the Warehouse 500 records
- if there was a Warehouse table there would only be one record for Warehouse 500
and you would only have to update that one record
deletion anomaly example (from normalization problems) - ANSWER-- stopped
making PartNumber 02
- problem because if 02 is deleted Warehouse 700 is completely lost because it
solely makes PartNumber 02
- whereas Warehouse 500 will be deleted for making PartNumber 02, but will still
exist because it makes other parts
functional dependencies - ANSWER-the value of one attribute determines the other
attribute
functional dependencies example - ANSWER-- what can be determined from a
person's social security number
- SSN# determines name, address, birth date & phone
steps to normalization - ANSWER-- table with multivalued attributed
* (remove multivalued attributes)
- first normal form
* (remove partial dependencies)
- second normal form
* (remove transitive dependencies)
- third normal form
0 normal form - ANSWER-- multi-valued attributes
- bad identifier
1st normal form - ANSWER-- no multi-valued attributes
- one value per "cell"
(SS#, Fname, Lname, Bdate) - ANSWER-good functional dependency example
partial dependency - ANSWER-when the attributes in a tables are dependent on
different key fields or values
(Part#, Fact#, PartName, PartSize, FactoryName) - ANSWER-partial functional
dependency example
2nd normal form - ANSWER-- already in 1term-33NF & every non-key attribute is
fully functionally dependent on the primary key
- this means that every non-key attribute must be determined by the entire primary
key, not by only part of the primary key
- can be created by breaking the original table into two related tables on the basis of
functional dependency
