Database Management

  1. In modeling terms, an (        ) is a generic type that is related to one or more entity subtypes
    Entity Supertype
  2. The (       ) contains common characteristics, and the entity subtypes contain the unique characteristics of each entity subtype
    Entity Supertype
  3. Each supertypes and subtypes are organized in a ( ), which depicts the arrangement of the higher-level entity supertypes (parent entities) and lower-level entity subtypes
    Specialization Hierachy
  4. A specialization hierarchy provides the means to:
    • - Support attribute inheritance
    • - Define a special supertype attribute known as the subtype discriminator
    • - Define disjoint/overlapping constraints and complete/partial constraints
  5. A ( ) is the attribute in the supertype entity that determines to which subtype the supertype occurence is related
    Subtype Discriminator
  6. ( ) are subtypes that contain nonunique subsets of the supertype entity set; that is each entity instance of the supertype may appear in more than one subtype
    Overlapping subtypes
  7. The ( ) specifies whether each entity supertype occurrence must also be a member of at least one subtype.  The ( ) can be partial or total
    Completeness Constraint
  8. (Symbolized by a circle over a single line) means that not every supertype occurrence is a member of a subtype
    Partial Completeness
  9. ( ) (Symbolized by a circle over a double line) means that every supertype occurrence must be a member of at least one subtype
    Total Completeness
  10. ( ) is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype.  ( ) is based on grouping unique characteristics and relationships of the subtypes
  11. ( ) is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity
  12. An ( ) is a "virtual" entity type used to represent multiple entities and relationships in the ERD.  An () is formed by combining multiple interrelated entities into a single abstract entity object.
    Entity Cluster
  13. A ( ) or ( ) is a real-world, generally accepted identifier used to distinguish--that is uniquely identify -- real-world objects.  As its name implies, a () is familiar to end users and forms part of their day-to-day business vocabulary
    Natural Key or Natural Identiier
  14. A ( ) is a primary key created by the database designer to simplify the identification of entity instances. The () has no meaning in the user's environment--it exists only to distinguish one entity instane from another
    Surrogate Key
  15. () refer to data whose values change over time and for which you must keep a history of the data changes
    Time-Variant Data
  16. A ( ) occurs when a relationship is improperly or incompletely identified and is there therefore represented in a way that is not consistent with the real world.
    Design Trap
  17. A ( ) occurs when you have one entity in to 1:M relationships to other entities that is not expressed in the model
    Fan Trap
  18. ( ) is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. The ( ) process involves assigning attributes to tables based on the concept of determination
  19. ( ) produces a lower normal form; that is, a 3NF will be converted to a 2NF
  20. ( ) exists when there is a functional dependence in which the determinant is only part of the primary key. For example, if (A,B) -> (C,D), B -> C, and (A,B) is the primary key, then the functional dependence B->C is a ( ) because only part of the primary key (B) is needed to determine the value of C.
    Partial Dependency
  21. A ( ) exists when there are functional dependencies such as X -> Y, Y -> Z, and X is the primary key
    Transitive Dependency
  22. A ( ) derives its name from the fact that a group of multiple entries of the same type can exist
    Repeating Group
  23. A ( ) diagram that depicts all dependencies found within a given table structure
  24. The term ( ) describes the tabular format in which:
    • - All of the key attributes are defines
    • - There are no repeating groups in the table
    • - Each row/column intersection contains one and only one value, not a set of values
    • - All atributes are dependent
  25. A ( ) is any attribute whose value dependency, write a copy of its determinant other values within a row
  26. A table is in ( ) when: 
    - It is 2NF
    - It contains no transitive dependencies
    Third Normal Form (3NF)
  27. An ( ) is one that cannot be further subdivided
    Atomic Attribute
  28. ( ) refers to the level of detail represented by the values stored in a table's row.
  29. A table is in ( ) when every determinate in the table is a candidate key.
    Boyce-Codd normal form (BCNF)
  30. ( ) are a brief, precise and unambiguous description of a policy, procedure, or principle within a specific organization.
    - Sometimes misnamed
    Business Rules
  31. Anything about which data are to be collected and stored
    - Person, Place, Thing, or Event
  32. Association among entities
    - ( ) between customer and agent
    - Short hand notation 1:1, 1:M, M:N
  33. Data Model/Database Model
    • - Iterative progressive process
    • - Level of detailed increases as understanding of problem domain increases
    • - When done properly data model is "blueprint" containing all instructions to build database to meet end-user needs
  34. Data models can facilitate interaction among the following:
    • - Designer
    • - Application programmer
    • - End User
  35. Characteristics of an entity
    - Customer entity has customer last name, customer first name
  36. - Restrictions placed on data
    - Help to ensure data entities
    - Expressed in form of rules
  37. Business rules set the stage for proper identification
    • - Identification 
    • - Attributes 
    • - Relationship
    • - Constraints
  38. Naming Conventions
    • - Identification process
    • - Attributes
    • - Relationships
    • - Constraints
  39. First Data Model
    • - 1960 - 1970
    • - VMS/VSAM
    • - Used mainly on an IBM mainframe
  40. Second Data Model
    • - 1970s
    • - Hierarchal and network
    •        - IMS, ADABAS, IDS-II
    •        - Early database system navigational           access
  41. Third Data Model
    • - Mid 1970s to Present
    • - Relational
    •     a. DB2, Oracle, MS SQL-Server, MySQL
    •     b. Conceptual simplicity
    •     c. ER modeling and support for relational    data modeling
  42. Fourth Data Model
    • Mid-1080s to present
    • - Object-oriented/relational (O/R)
    • - Star Schema support for data warehousing
    • - Web databases become common
  43. Next Generation Hierarchal
    • Present to Future
    • 1. Developed in 1960s to manage large amounts of data for manufacturing projects
    • 2. Structure represented by an upside down tree
    • 3. Contains levels or segments
    • 4. Segment equivalent to file system's record type
    • 5. 1:M type relationship in this model (typically)
  44. Next Generation Network
    • 1. Created to represent complex data relationships more effectively than hierarchal 
    • a. Improves database performance
    • b. Imposes a database standard
    • c. Record can have more than one parent
  45. Schema
    Conceptual organizational of entire database
  46. SubSchema
    Defines portion of the database seen by application programs that actually produce the desired information from data contained within database
  47. DML
    Defines the environment in which data can be managed and to work with data in database
  48. DDL
    Enables database administrator to define the schema components
  49. Relational Model
    • - Matrix composed of intersecting rows and columns
    • 1. Row - tuple
    • 2. Column - attribute
  50. Relational Diagram
    • 1. Representation of
    • a. relational database's entities
    • b. attributes within those entities
    • c. relationship between entities
  51. Relational Table
    • 1. Stores collection of related entities
    • 2. Resemble a file
    • 3. Difference between table and file
    •       a. table yields complete data and structural independence
    •            i. purely logical structure
  52. Reasons for Relational data models
    • a. Powerful and flexible query language
    •     i. SQL - allows user to specify what needs to be done without specifying how it must be done
  53. Three parts do SQL-based relational database application
    • i. End-user interface
    •    1. Allows user to interact with data
    • ii. Collection of tables stored
    •    1. Data perceived to be stored in tables
    • iii. SQL engine
    •    1. Executes all the queries or data requests
  54. Entity Relationship Model (ERM0
    • i. Graphical tool in which entities and their relationships are pictured
    • ii. Widely accepted standard for data modeling
    • iii. Peter Chen (1976) introduces Entity Relationship Diagram (ERD)
  55. Entity Relationship Diagram
    • - Peter Chen (1976)
    • 1. Connectivities written next to each entity box
    • 2. Relationships represented by a diamond connected to the related entities through the relationship line
    • 3. Complemented the relational data model concepts
    • 4. Row is known as entity instance or entity occurrence
    • 5. ER models represented by entity relationship diagrams (ERD)
    • 6. Entity represented by rectangle
    • 7. Connectivity labels relationship type
    • 8. Relationships describe association among data
    • 9. Connectivity labels relationship type
    • 10. Name of relationship is usually an active or passive verb
  56. Crow's Foot Notation
    • 1. Derived from three-pronged symbol used to represent "many" side of relationship
    • 2. Connectivity represented by symbols
  57. Object-Oriented Model
    • 1. Data and their relationships contained in single structure known as object
    • 2. Reflects different way to define and use entities
    • 3. Object contains all operations that can be performed on it
    • 4. Object is self-contained
  58. OO Data model based on following components
    • 1. Object is abstraction of real-world entity
    • 2. Attributes describe the properties of object
    • 3. Objects that share similar characteristics are grouped in classes
  59. UML - Unified Modeling Language
    - Unified Modeling Language
  60. Overlapping subtypes are subtypes that contain a unique subset of the supertype entity set
  61. Specialization is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype
  62. To model time-variant data, you must create a new entity in a M:N relationship with the original entity
    False.  It is a 1:M relationship
  63. A design trap occurs when a relationship is improperly or incompletely identified and is therefore represented in a way that is not consistent with the real world
  64. Some designs use redundant relationships as a way to simplify the design
  65. blank is a generic entity type that is related to one or more entity subtypes
    Entity Supertype
  66. The ( ) depicts the arrangement of higher-level entity supertypes (parent entities) and lower level entity subtypes (child entities).
    Specialization Hierarchies
  67. Within a specialization hierarchy, every subtype can have blank supertype(s) to which it is directly related.
    One or More
  68. The property of blank enables an entity subtype to inherit the attributes and relationships of the supertype
  69. The default comparison condition for the subtype discriminator attribute is the blank comparison
  70. Overlapping subtypes are subtypes that contain ( ) subsets of the supertype entity set
  71. ( ) is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes.
  72. An entity cluster is formed by combining multiple interrelated entities into ( )
    Single Abstract Entity Object
  73. The ( ) characteristic of a primary key states that: The PK should not have embedded semantic meaning.  An attribute with embedded semantic meaning is probably better used as a descriptive characteristic of the entity rather than as an identifier
  74. Composite primary keys are particularly useful as identifiers of composite entities, where each primary key is allowed only once in the ( ) relationship.
  75. Normalization works through a series of stages called normal forms
  76. Normalization is a very important database design ingredient and the highest level is always the most desirable
  77. All relational tables satisfy the 1NF requirements
  78. Converting a database format from 1NF to 2NF is a complex process
  79. It is possible for a table in 2NF to exhbit transitive dependency, where one or more nonprime attributes functionally determine other nonprime attributes
  80. The combination of normalization and ER modeling yields a useful ERD, whose entities my now be translated into appropriate relationship structures
  81. The advantage of higher processing speed must be carefully weighed against the disadvantage of data anomalies
  82. Normalization purity is easy to sustain in the modern database enviroment
  83. Unnormalized database tables often lead to various data redundancy disasters in production databases.
  84. 1NF, 2NF, and 3NF are ( )
  85. Some very specialized applications may require normalization beyond the ( )
  86. A relational table must not contain a()
  87. If you have three different transitive dependencies, ( ) different determinant(s) exist
  88. Before converting a table into 3NF, it is imperative that the table already be in ( )
  89. An Atomic attribute ( )
  90. The most likely data type for a surrogate key is ( )
  91. From a strictly database point of view, ( ) attribute values can be calculated when they are needed to write reports or invoices
  92. A table where all attributes are dependent on the primary key and are independent on the primary key and are independent of each other, an no row contains two or more multivalued facts about an entity, is said to be in ( )
  93. When designing a database, you should ( )
  94. Systems analysis is used to determine the need for an information system and to establish to limits
  95. The primary objective in database design is to create complete, denormalized, redundant, and fully integrated conceptual, logical, and physical database models
  96. The SDLC's planning phase yields a general overview of the company and its objectives.
  97. Problems defined during the planning phase are examined in greater detail during the analysis phase
  98. During the testing phase, the system is subjected to exhaustive testing until it is ready for use
  99. Because every request for structural changes requires retracing the SDLC steps, the system is always at some stage of the SDLC
  100. To analyze the company situation, the database designer must discover what the company's operational components are, how they interact
  101. The testing and evaluation phase occurs after applications programming
  102. After the initial declarations in a study, the database designer must carefully probe in order to generate additional information that will help define the problem within the larger framework of company operations
  103. The testing and evaluation phase occurs after applications programming
  104. Performance evaluation is rendered more difficult by the fact that there are standard measurements for database performance
  105. Coding, testing, and debugging are part of the  ( ) phase of the SDLC
  106. Installation and fine tuning are part of the ( ) phase of the SDLC
  107. Evaluation, maintenance, and enhancement are part of the ( ) phase of the SDLC
  108. The SDLC is most important to the ( )
  109. What are the requirements of the current system's end user? is a question asked during the ( ) phase of the SDLC
  110. Producing the required information flow is part of the ( ) phase of the DBLC
  111. The implementation and loading phase of the DBLC involves ( )
  112. Once the data has been loaded into the database, the ( ) tests and fine-tunes the database for performance, integrity, concurrent access, and security constraints.
  113. The first step in developing the conceptual model using ER diagrams is to ( )
  114. The ( ) design is the process of selecting the data storage data access characteristics of the database.
Card Set
Database Management
Database Midterm