In modeling terms, an ( ) is a generic type that is related to one or more entity subtypes
Entity Supertype
The ( ) contains common characteristics, and the entity subtypes contain the unique characteristics of each entity subtype
Entity Supertype
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
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
A ( ) is the attribute in the supertype entity that determines to which subtype the supertype occurence is related
Subtype Discriminator
( ) 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
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
(Symbolized by a circle over a single line) means that not every supertype occurrence is a member of a subtype
Partial Completeness
( ) (Symbolized by a circle over a double line) means that every supertype occurrence must be a member of at least one subtype
Total Completeness
( ) 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
Specialization
( ) is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity
Generalization
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
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
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
() refer to data whose values change over time and for which you must keep a history of the data changes
Time-Variant Data
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
A ( ) occurs when you have one entity in to 1:M relationships to other entities that is not expressed in the model
Fan Trap
( ) 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
Normalization
( ) produces a lower normal form; that is, a 3NF will be converted to a 2NF
Denormalization
( ) 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
A ( ) exists when there are functional dependencies such as X -> Y, Y -> Z, and X is the primary key
Transitive Dependency
A ( ) derives its name from the fact that a group of multiple entries of the same type can exist
Repeating Group
A ( ) diagram that depicts all dependencies found within a given table structure
Dependency
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
A ( ) is any attribute whose value dependency, write a copy of its determinant other values within a row
Determinant
A table is in ( ) when:
- It is 2NF
and
- It contains no transitive dependenciesThird Normal Form (3NF)
An ( ) is one that cannot be further subdivided
Atomic Attribute
( ) refers to the level of detail represented by the values stored in a table's row.
Granularity
A table is in ( ) when every determinate in the table is a candidate key.
Boyce-Codd normal form (BCNF)
( ) are a brief, precise and unambiguous description of a policy, procedure, or principle within a specific organization.
- Sometimes misnamedBusiness Rules
Anything about which data are to be collected and stored
- Person, Place, Thing, or EventEntities
Association among entities
- ( ) between customer and agent
- Short hand notation 1:1, 1:M, M:NRelationships
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
Data models can facilitate interaction among the following:
- - Designer
- - Application programmer
- - End User
Characteristics of an entity
- Customer entity has customer last name, customer first nameAttributes
- Restrictions placed on data
- Help to ensure data entities
- Expressed in form of rulesConstraints
Business rules set the stage for proper identification
- - Identification
- - Attributes
- - Relationship
- - Constraints
Naming Conventions
- - Identification process
- - Attributes
- - Relationships
- - Constraints
First Data Model
- - 1960 - 1970
- - VMS/VSAM
- - Used mainly on an IBM mainframe
Second Data Model
- - 1970s
- - Hierarchal and network
- - IMS, ADABAS, IDS-II
- - Early database system navigational access
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
Fourth Data Model
- Mid-1080s to present
- - Object-oriented/relational (O/R)
- - Star Schema support for data warehousing
- - Web databases become common
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)
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
Schema
Conceptual organizational of entire database
SubSchema
Defines portion of the database seen by application programs that actually produce the desired information from data contained within database
DML
Defines the environment in which data can be managed and to work with data in database
DDL
Enables database administrator to define the schema components
Relational Model
- - Matrix composed of intersecting rows and columns
- 1. Row - tuple
- 2. Column - attribute
Relational Diagram
- 1. Representation of
- a. relational database's entities
- b. attributes within those entities
- c. relationship between entities
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
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
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
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)
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
Crow's Foot Notation
- 1. Derived from three-pronged symbol used to represent "many" side of relationship
- 2. Connectivity represented by symbols
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
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
UML - Unified Modeling Language
- Unified Modeling Language
Overlapping subtypes are subtypes that contain a unique subset of the supertype entity set
False
Specialization is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype
True
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
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
True
Some designs use redundant relationships as a way to simplify the design
False
blank is a generic entity type that is related to one or more entity subtypes
Entity Supertype
The ( ) depicts the arrangement of higher-level entity supertypes (parent entities) and lower level entity subtypes (child entities).
Specialization Hierarchies
Within a specialization hierarchy, every subtype can have blank supertype(s) to which it is directly related.
One or More
The property of blank enables an entity subtype to inherit the attributes and relationships of the supertype
Inheritance
The default comparison condition for the subtype discriminator attribute is the blank comparison
Equality
Overlapping subtypes are subtypes that contain ( ) subsets of the supertype entity set
Nonunique
( ) is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes.
Generalization
An entity cluster is formed by combining multiple interrelated entities into ( )
Single Abstract Entity Object
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
Nonintelligent
Composite primary keys are particularly useful as identifiers of composite entities, where each primary key is allowed only once in the ( ) relationship.
M:N
Normalization works through a series of stages called normal forms
Normalization is a very important database design ingredient and the highest level is always the most desirable
All relational tables satisfy the 1NF requirements
-
Converting a database format from 1NF to 2NF is a complex process
It is possible for a table in 2NF to exhbit transitive dependency, where one or more nonprime attributes functionally determine other nonprime attributes
The combination of normalization and ER modeling yields a useful ERD, whose entities my now be translated into appropriate relationship structures
The advantage of higher processing speed must be carefully weighed against the disadvantage of data anomalies
Normalization purity is easy to sustain in the modern database enviroment
Unnormalized database tables often lead to various data redundancy disasters in production databases.
1NF, 2NF, and 3NF are ( )
Some very specialized applications may require normalization beyond the ( )
A relational table must not contain a()
If you have three different transitive dependencies, ( ) different determinant(s) exist
Before converting a table into 3NF, it is imperative that the table already be in ( )
The most likely data type for a surrogate key is ( )
From a strictly database point of view, ( ) attribute values can be calculated when they are needed to write reports or invoices
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 ( )
When designing a database, you should ( )
Systems analysis is used to determine the need for an information system and to establish to limits
The primary objective in database design is to create complete, denormalized, redundant, and fully integrated conceptual, logical, and physical database models
The SDLC's planning phase yields a general overview of the company and its objectives.
Problems defined during the planning phase are examined in greater detail during the analysis phase
During the testing phase, the system is subjected to exhaustive testing until it is ready for use
Because every request for structural changes requires retracing the SDLC steps, the system is always at some stage of the SDLC
To analyze the company situation, the database designer must discover what the company's operational components are, how they interact
The testing and evaluation phase occurs after applications programming
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
The testing and evaluation phase occurs after applications programming
Performance evaluation is rendered more difficult by the fact that there are standard measurements for database performance
Coding, testing, and debugging are part of the ( ) phase of the SDLC
Installation and fine tuning are part of the ( ) phase of the SDLC
Evaluation, maintenance, and enhancement are part of the ( ) phase of the SDLC
The SDLC is most important to the ( )
What are the requirements of the current system's end user? is a question asked during the ( ) phase of the SDLC
Producing the required information flow is part of the ( ) phase of the DBLC
The implementation and loading phase of the DBLC involves ( )
Once the data has been loaded into the database, the ( ) tests and fine-tunes the database for performance, integrity, concurrent access, and security constraints.
The first step in developing the conceptual model using ER diagrams is to ( )
The ( ) design is the process of selecting the data storage data access characteristics of the database.
Overlapping subtypes are subtypes that contain _____ subsets of the supertype entity set.
Disjoint subtypes are subtypes that contain nonunique subsets of the supertype entity set.
A supertype can have one or more subtypes, and a subtype can have one or more supertypes. A supertype can be a subtype of some other supertype, and a subtype can be a supertype of some other subtype.
An example of an overlapping subtype structure would be the classification of employees at a College into instructors and administrators. Since some instructors are likely to be administrators, this would be an overlapping subtype structure. Hence, we would have EMPLOYEE as a supertype of INSTRUCTOR and ADMINISTRATOR.
However, a subtype is (directly) a subtype of only one supertype. Thus, the use of supertypes and subtypes in ERDs resembles the use of ``single inheritance'' between classes, in object-oriented programming.