INFO 340 Study Guide - Quiz Guide: Conceptual Schema, Unique Key, Operand
INFO 340 Quiz4 Notes
Explain what is meant by 'data independence' and how relational database design addresses
it.
Schemas
- External schema – different views of the data
- Conceptual schema – describes entities, attributes, relationships, constraints
- Internal schema – internal model, including indexes, storage structures, data fields
- Only one conceptual + internal schema per database
Data independence – upper levels of data unaffected by changes to lower levels
- Abstraction between disk storage + application activity
o Not dependent on file path
o Expand disk storage without breaking application
- Types
o Logical data independence – change conceptual schema without affecting
external schemas or application programs
▪ Example: add or remove entities, attributes, or relationships
o Physical data independence – change internal schema without affecting
conceptual or external schemas
▪ Example: change file organizations, storage structures, modifying indexes
or hash algorithms
- Why? Improve database performance
- How: database organized into three-level architecture in order to provide data
independence:
o External -> conceptual -> internal
Explain the use of a sub-query; which clauses/statements can a sub-query be used?
Subqueries
- Use when: query same attribute multiple times in same query
- Eligible statements/clauses
o SELECT
o WHERE & HAVING – use after an operator (e.g., =)
o INSERT / UPDATE / DELETE
o ANY / ALL / SOME – for subqueries producing single column of numbers
- Types
o Scalar subquery – returns a single column & single row -> single value
Document Summary
Explain what is meant by "data independence" and how relational database design addresses it. External schema different views of the data. Conceptual schema describes entities, attributes, relationships, constraints. Internal schema internal model, including indexes, storage structures, data fields. Only one conceptual + internal schema per database. Data independence upper levels of data unaffected by changes to lower levels. Abstraction between disk storage + application activity: not dependent on file path, expand disk storage without breaking application. How: database organized into three-level architecture in order to provide data independence: external -> conceptual -> internal. Use when: query same attribute multiple times in same query. Eligible statements/clauses: select, where & having use after an operator (e. g. , =) Insert / update / delete: any / all / some for subqueries producing single column of numbers. Describe the purpose, structure and benefits of implementing the enhanced erd mechanism of super type/subtype. Goal reduce blanks + null values.
