INMT 342 Exam Set
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 - answeridentify 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? - answerdata volume
how much storage needed? - answerdisk storage requirement
,how large is each table? - answersize estimate
access frequencies - answer- access paths/security
- file organization
- memory/processor requirement
which tables are accessed? - answeraccess paths/security
how are they accessed? - answerfile organization
how often accessed? - answermemory/processor requirement
designing physical files - answer- physical file
- tablespace
- file organization
- access method
physical file - answera named portion of secondary memory allocated for the purpose of
storing physical records
tablespace - answerdisk storage in which database tables are stored
file organization - answerhow the files are arranged on the disk
access method - answerhow 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 - answerthe 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) - answergood functional dependency example
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 - answeridentify 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? - answerdata volume
how much storage needed? - answerdisk storage requirement
,how large is each table? - answersize estimate
access frequencies - answer- access paths/security
- file organization
- memory/processor requirement
which tables are accessed? - answeraccess paths/security
how are they accessed? - answerfile organization
how often accessed? - answermemory/processor requirement
designing physical files - answer- physical file
- tablespace
- file organization
- access method
physical file - answera named portion of secondary memory allocated for the purpose of
storing physical records
tablespace - answerdisk storage in which database tables are stored
file organization - answerhow the files are arranged on the disk
access method - answerhow 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 - answerthe 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) - answergood functional dependency example
