Database Modeling Chapter 1-4
disjointedness constraint
A ________ addresses whether an instance of a supertype may simultaneously be a member of two or more subtypes. A) disjointedness constraint B) disjoint rule C) partial specialization D) total specialization
the key must indicate the row's position in the table
A candidate key must satisfy all of the following conditions EXCEPT: A) the key must uniquely identify the row. B) the key must indicate the row's position in the table. C) the key must be nonredundant. D) each nonkey attribute is functionally dependent upon it.
File
A collection of related records. For example, may contain data about students at a university.
True
Data structures include data organized in the form of tables with rows and columns. T / F
Structured query language
De facto query language and data access standard supported by the majority of DBMS vendors.
Attributes
Describe the properties of an object.
Relationship
Describes an association among entities. One-to-many, many-to-many, one-to-one.
Unified Modeling Language UML
Describes sets of diagrams and symbols to graphically model a system.
Operational database
Designed to support a company's day-to-day operations.
Data anomaly
Develops when not all of the required changes in the redundant data are made successfully.
Data inconsistency
Different versions of the same data appear in different places.
Online analytical processing OLAP
Enables retrieving, processing, and modeling data from the data warehouse.
Schema data definition language DDL
Enables the database administrator to define the schema components.
External model
End user's view of the data environment. ER diagrams are used to represent the external views.
Performance tuning
Ensures efficient performance of the database in terms of storage and access speed.
Data manipulation language DML
Environment in which data can be managed and is used to work with the data in the database.
Segments
Equivalent of a file system's record type.
SQL engine
Executes all queries
Well designed databases:
Facilitate data management, generate accurate and valuable information.
Strong entity/weak entity
In the figure below, what type of relationship do the relations depict? A) Strong entity/weak entity B) Multivalued C) Composite foreign key D) One-to-many
one-to-Many
In the figure below, what type of relationship do the relations depict? A) Strong entity/weak entity B) One-to-many C) Ternary D) Many-to-many
Patient_Name
In the figure below, which of the following apply to both OUTPATIENTs and RESIDENT_PATIENTs? A) Checkback_Date B) Date_Discharged C) Patient_Name D) XML
Outpatient
In the figure below, which of the following is a subtype of patient? A)Outpatient B) Physician C) Bed D) Date_Hired
NoSQL databases
Not based on relational model, support distributed database architectures. Provide high scalability, high availability, and fault tolerance.
Raw data
Not yet been processed to reveal the meaning.
Translating business rules into data model components
Nouns translate into entities. Verbs translate into relationships among entities. Relationships are bidirectional.
Inheritance
Objects inherits methods and attributes of parent class.
require customization
Packaged data models: A) are ready to use right out of the box. B) require customization. C) allow partial specialization. D) cannot be used for most applications.
Relational model database system RDBMS
Performs basic functions provided by the hierarchal and network DBMS systems. Makes relational data model easier to understand and implement. Hides the complexities of the relational model from the user.
Desktop database
Runs on PC.
overlap
The ________ rule states that an entity instance can simultaneously be a member of two (or more) subtypes. A) disjoint B) overlap C) partial specialization D) total specialization
entity integrity rule
The ________ states that no primary key attribute may be null. A) referential integrity constraint B) entity integrity rule C) partial specialization rule D) range domain rule
determinant.
The attribute on the left-hand side of the arrow in a functional dependency is the: A) candidate key. B) determinant. C) foreign key. D) primary key.
Ternary
The figure below is an example of mapping which type of relationship? A) First B) Second C) Unary D) Ternary
determining the business rules that will be established through the data model.
The most important challenge of customizing a purchased data model is: A) getting user buy-in. B) determining the business rules that will be established through the data model. C) implementation. D) user training.
merge
The need to ________ relations commonly occurs when different views need to be integrated. A) metadata B) system C) drop D) merge
fourth normal form.
The normal form which deals with multivalued dependencies is called: A) fifth normal form. B) fourth normal form. C) Boyce-Codd normal form. D) sixth normal form.
functional dependency
A constraint between two attributes is called a(n): A) functional relation. B) attribute dependency. C) functional dependency. D) functional relation constraint
integrity constraints.
A domain definition consists of all of the following components EXCEPT: A) domain name. B) data type. C) integrity constraints. D) size.
security specifications
A form of database specification which maps conceptual requirements is called: A) logical specifications. B) response specifications. C) security specifications. D) physical specifications
transitive dependency.
A functional dependency between two or more nonkey attributes is called a: A) partial functional dependency. B) partial nonkey dependency. C) transitive dependency. D) partial transitive dependency.
partial functional
A functional dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key is called a ________ dependency. A) partial key-based B) partial functional C) cross key D) merged relation
universal data model
A generic or template data model that can be reused as a starting point for a data modeling project is called a(n): A) packaged data model. B) universal data model. C) enterprise data model. D) collection data model
data profiling
A good method for identifying inconsistencies and finding hidden meaning in the customized purchased data model is: A) data analysis. B) data volume usage analysis. C) user interviews. D) data profiling
Field
A group of characters, alphabetic or numeric, that has a specific meaning. Defines and stores data.
descriptor
A nonkey attribute is also called a(n): A) column. B) unimportant datum. C) descriptor. D) address.
Composite Key
A primary key that consists of MORE THAN ONE ATTRIBUTE IS CALLED A: A) foreign key. B) composite key. C) multivalued key. D) cardinal key.
Enterprise Key
A primary key whose value is unique across all relations is called a(n): A) global primary key. B) inter-table primary key. C) enterprise key. D) foreign global key
well-structured
A relation that contains minimal redundancy and allows easy use is considered to be: A) clean. B) simple. C) complex. D) well-structured
Second
A relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies is in which normal form? A) First B) Second C) Third D) Fourth
referential integrity constraint.
A rule that states that each foreign key value must match a primary key value in the other relation is called the: A) referential integrity constraint. B) key match rule. C) entity key group rule. D) foreign/primary match rule.
Relation
A two-dimensional table of data sometimes is called a: A) group. B) set. C) declaration. D) relation.
Completeness
A(n) ________ constraint is a type of constraint that addresses whether an instance of a supertype must also be an instance of at least one subtype. A) disjoint B) overlap C) completeness D) weak
Model
Abstraction of a real-world object or event.
Database communication interfaces
Accept end-user requests via multiple, different network environments.
Structural dependence
Access to a file is dependent on its own structure. All file system programs are modified to conform to a new file structure.
Manual file systems
Accomplished through a system of file folders and filing.
more one-to-one relationships give the data model more flexibility.
All of the following are advantages of packaged data models EXCEPT: A) packaged data models can be built using proven components evolved from cumulative experiences. B) projects take less time and cost less. C) the data model is easier to evolve. D) more one-to-one relationships give the data model more flexibility.
utilize all business rules that come with the packaged data model.
All of the following are steps to using a packaged data model EXCEPT: A) identify the parts of the data model that apply to your data modeling situation. B) utilize all business rules that come with the packaged data model. C) rename the identified data elements. D) map data to be used in packages with existing data in the current databases
Maximize Storage Pace
All of the following are the main goals of normalization EXCEPT: A) minimize data redundancy. B) simplify the enforcement of referential integrity. C) maximize storage space. D) make it easier to maintain data
End-user interface
Allows end user to interact with the data.
alias.
An alternative name for an attribute is called a(n): A) synonym. B) alias. C) alternate attribute. D) related characteristic
primary key
An attribute (or attributes) that uniquely identifies each row in a relation is called a: A) column. B) foreign field. C) primary key. D) duplicate key.
Foreign Key
An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a: A) link attribute. B) link key. C) foreign key. D) foreign attribute.
subtype discriminator
An attribute of the supertype that determines the target subtype(s) is called the: A) determinant. B) subtype decision. C) disjoint indicator. D) subtype discriminator.
homonym
An attribute that may have more than one meaning is called a(n): A) homonym. B) alias. C) double defined attribute. D) synonym.
combining a strong entity and its weak entities
An entity cluster can be formed by: A) deleting a supertype and its subtype. B) combining metadata. C) combining a strong entity and its weak entities. D) deleting metadata.
a set of one or more entity types and associated relationships grouped into a single abstract entity type.
An entity cluster is: A) a formal method for specifying attributes of related entities. B) a set of one or more entity types and associated relationships grouped into a single abstract entity type. C) a useful way to present data for a small and fairly simple organization. D) a way of developing more granular views of the data model.
Importance of data models
Are a communication tool, give overall view of the database, organize data for various users, are an abstraction for the creation of good database.
Conceptual schema
Basis for the identification and high-level description of the main data objects. Has micro-level view of data environment. Software and hardware independent.
Entity names- required to:
Be descriptive of the objects in the business environment. Use terminology that is familiar to the users.
Business rules
Brief, precise, and unambiguous description of a policy, procedure, or principle. Enable defining the basic building blocks. Describe main and distinguishing characteristics of the data.
Business intelligence
Captures and processes business data to generate information that supports decision making.
Physical independence
Changes in physical model do not affect internal model.
Logical independence
Changing internal model without affecting the conceptual model.
Attribute
Characteristics of an entity.
Database management system DBMS
Collection of programs; manages the database structure; controls access to data stored in the database.
Class
Collection of similar objects with shared structure and behavior organized in a class hierarchy.
Sources of business rules
Company managers, policy makers, department managers, written documentation, direct interviews with end users.
Generalization is the process of defining a more general entity type from a set of more specialized entity types. For example, if we had undergraduate and graduate students with similar attributes, then we might create a student supertype. The student supertype would then contain the attributes that all subtypes have in common. Specialization, on the other hand, would occur when we discover that we have a student type but there are different attributes for different types of students. In this case, we would create subtypes which would contain unique attributes for that subtype.
Compare and contrast generalization and specialization.
NoSQL disadvantages
Complex programming is required. There is no relationship support. There is no transaction integrity support.
Computerized file systems
Computer-based system that track data and produce required reports.
Schema
Conceptual organization of the entire database as viewed by the database administrator.
Network advantages
Conceptual simplicity, handles more relationship types. Data access is flexible. Data owner/member relationship promotes data integrity, conformance to standards. Includes data definition language and data manipulation language.
General-purpose database
Contains a wide variety of data used in multiple disciplines.
Objects
Contains data and their relationships with operations that are performed on it. Basic building block for autonomous structures. Abstraction of real-world entity.
Discipline-specific database
Contains data focused on specific subject areas.
The disjoint rule is used when you wish to specify that an entity instance can only be one type of subtype. For example, if a student could only be a graduate or undergraduate student, but not both. The overlap rule specifies that an entity instance could be one or more subtypes. For example, if an instance of a person supertype could be a faculty member as well as a student subtype, then we would use the overlap rule.
Contrast the overlap rule to the disjoint rule
Cloud database
Created and maintained using cloud data services that provide defined performance measures for the database.
Metadata
Data about data, which the end-user data are integrated and managed.
Data dependence
Data access changes when data storage characteristics change. Significant for difference between logical and physical format.
Distributed database
Data is distributed across different sites.
Centralized database
Data is located at a single site.
Tables
Data is represented in the form of: A) data trees. B) tables. C) data notes. D) chairs.
Data independence
Data storage characteristics are changed without affecting the program's ability to access the data.
Subtypes that are lower in the hierarchy inherit attributes not only from their immediate supertype but also from all supertypes higher in the hierarchy all the way to the root
Discuss how attribute inheritance works in a supertype/subtype hierarchy
Attributes are assigned at the highest logical level that is possible in the hierarchy. For example, in the following diagram, one would assign the name to the person supertype so that it could be shared by as many subtypes as possible
Discuss how attributes are assigned in a supertype/subtype hierarchy.
Two relations are created. One represents the entity type in the relationship, and the other is an associative relation to represent the M:N relationship. The primary key of the associative relation consists of two attributes which take their value from the primary key of the other relation.
Discuss how to map a unary many-to-many relationship.
The entity type in the unary relationship is mapped to a relation in the same way as we did for a relation. We next add a foreign key attribute, which maps back to the primary key. This is called a recursive foreign key. So, if we had a relation such as a person supervises one or more persons, the person_id would be a primary key and then there would be a supervisor id which points back to the person_id
Discuss how to map a unary one-to-many relationship.
Each regular entity is transformed into a relation. There are a couple of things that need to be done with some special types of attributes. Composite attributes are broken down into their individual components. Multivalued attributes are broken down into separate relations. For example, if there was a multivalued skill attribute, this would become a skill relation. Also, we ignore derived attributes.
Discuss how you would map a regular entity to a relation.
Each relation (or table) has a unique name. An entry at the intersection of each row and column is atomic and each row is unique. Each attribute (or column) within a table has a unique name. The sequence of rows and columns is insignificant
Discuss the properties of relations
It is best to use a supertype/subtype hierarchy when there are attributes that apply to some but not all instances of an entity type. For example, if we have an employee entity type and there is an hourly wage attribute that only applies to hourly employees, it may be best to use a supertype/subtype relationship. Another reason for using supertype/subtype relationships is when you have instances of a subtype that participate in a relationship unique to that subtype. For example, if we have a contractor subtype of employee that has a relationship with staffing agency, then it would be best to use supertype/subtype relationship
Discuss the reasoning behind using supertype/subtype relationships
During logical database design, normalization is used as a check and balance to make sure that your E-R diagram is correct as well as your relational schema before doing physical design. Another occasion when you benefit is reverse-engineering an older system, since many of the tables and user views are redundant.
Discuss the two major occasions when you benefit from using normalization
There are two possible cases for associative entities. In one case, you can have an identifier assigned. You would then use this as the primary key. The other case is where the identifier is not assigned. In this case, you would use the primary keys from the other two (or more) relations that are related to the associative entity.
Discuss the two possible scenarios when mapping an associative entity
The three major types of integrity constraints are domain constraints, entity integrity and referential integrity. A domain is a set of attributes assigned to an attribute and consists of the domain name, data type, size and allowable values. The entity integrity rule is designed to ensure that every relation has a primary key and that the data values for the primary key are valid (in particular, not null). The referential integrity constraint is a rule that maintains consistency among the rows of two relations. If there is a foreign key in one relation, there must be a matching primary key in the other relation or the foreign key must be null
Discuss the types of integrity constraints.
A primary key uniquely identifies each row of a relation (or table). It can be either a single column or a composite of two or more columns, which is called a composite key. A foreign key allows us to represent the relationship between two tables. A foreign key in one table is generally a reference to a primary key in another table
Discuss the various relational keys.
A surrogate key should be created when any of the following conditions hold: there is a composite primary key, the natural primary key is inefficient (such as a long name) or the natural primary key is recycled. When you have an associative entity, it is often best to use a surrogate key when you have more than two relations that the associative entity is related to, since the primary key would be a composite of the primary keys of all entities related to the associative entity.
Discuss when it is best to create a surrogate key for an associative entity
Total specialization would be used when you know that there are no other subtypes of a supertype other than those defined. For example, if we only had undergraduate and graduate students but no other types of students, then we would use total specialization. However, if there were students other than graduate and undergraduate, such as just a general student, then we would want to use partial specialization.
Discuss when one would use total specialization and when one would use partial specialization
A multivalued attribute is one that can take on more than one value. According to the definition of a relation, there can be no multivalued attributes. The reason for this will be seen later when the schema is normalized. A multivalued attribute can be resolved by creating a separate instance for each value and repeating the data from the non-multivalued attributes
Discuss why it is important to remove multivalued attributes from a relation
Collection of tables stored in the database:
Each table is independent from another. Rows in different tables are related based on common values in common attributes.
DBMS benefits
Eliminates most of the file system's problems. Stores data structures, relationships, and access paths. Defines all components.
When we have a supertype/subtype relationship, we need to assign one entity for the supertype and one for each subtype. The supertype has all attributes common to all subtypes as well as a primary key. Each subtype relation has the primary key of the supertype as well as any attributes that are specific to that subtype. Finally, we assign one or more attributes to the supertype to function as subtype discriminators
Explain how subtype/supertype relationships are converted to relations
A subtype is an entity which represents data meaningful to the organization. For example, an undergraduate student and a graduate student might be a subtype. A supertype is a generalization of various subtypes and contains attributes which are common to both. Supertypes and subtypes may have relationships with other entities. Also, a subtype inherits the attributes of its supertype.
Explain the terms subtype and supertype. Discuss the differences between them
A candidate key is an attribute or a combination of attributes that uniquely identifies a row in a relation. A candidate key must be nonredundant and must also uniquely identify each row. When we look at functional dependencies and candidate keys, we can always say that a determinant is always a candidate key.
Explain what a candidate key is and how it might be used
An entity cluster is a group of entities and relationships which act as one entity. Entity clustering can be thought of as a way to decompose a data model hierarchically, gaining finer and finer views. Entity clusters can interact with other entity clusters just like entities can interact with each other.
Explain what entity clustering is.
The business environment has drastically changed since the relational model was first introduced in the 1970s. Business relationships are more complex, and organizations must have ways to represent data to represent the complexity. Organizations must be prepared to segment their markets as well as customize their products. The enhanced E-R model has evolved to represent these changes and also is similar to the object-oriented data model.
Explain why the E-R model needed to be expanded into the enhanced E-R model.
Proper naming
Facilitates communication between parties. Promotes self-documentation.
Structural independence
File structure is changed without affecting the application's ability to access the data.
Big Data aims to:
Find new and better ways to manage large amounts of web and sensor-generated data. Provide high performance and scalability at a reasonable cost. Volume, velocity, and variety.
Data management
Generation, storage, and retrieval of data.
Define one vehicle entity type to hold all entities.
Given the following entities, which of the choices below would be the most complicated? Automobile: VIN, EngineSize, NumberOfDoors, NumberOfPassengers, FuelType, Transmission SUV: VIN, EngineSize, NumberOfPassengers, NoWheelDrive, FuelType, Transmission Truck: VIN, EngineSize, NoWheelDrive, FuelType, Transmission, Payload A) Define one vehicle entity type to hold all entities. B) Define a separate entity type for each entity. C) Define a supertype called vehicle and make each of the entities subtypes. D) Keep only the Truck entity type.
Entity relationship diagram
Graphical representation of entities and their relationships in a database structure. Uses graphics representations to model database components
Big Data new technologies
Hadoop, MapReduce, Hadoop Distributed File System HDFS, NoSQL.
Reasons for identifying and documenting business rules
Help standardize company's view of data. Communications tool between users and designs.
NoSQL advantages
High scalability, availability, and fault tolerance are provided. Uses low-cost commodity hardware. Support Big Data. Key-value model improves storage efficiency.
A weak entity becomes a relation. However, since a weak entity instance does not exist by itself, we must create a relation with the primary key from the strong relation as the primary key, as well as the identifying attribute.
How do you convert weak entities to relations?
A packaged data model is quite extensive, so you would begin with the part of the data model that you will use for your situation. You would start first with entities, then attributes and then relationships. The next step is to rename the data elements to terms that are meaningful to your organization. Since the packaged data model may be used to replace an existing system, the next step is to map the packaged model to the current database. This mapping will be used later for data migration. Perhaps the most challenging step is to determine the business rules for the new system.
How is the data modeling process different when starting with a purchased solution? Answer:
Questions to identify the relationship type
How many instances of B are related to one instance of A? How many instances of A are related to one instance of B?
only one supertype.
In a supertype/subtype hierarchy, each subtype has: A) only one supertype. B) many supertypes. C) at most two supertypes. D) at least one subtype.
supertypes
In a supertype/subtype hierarchy, subtypes that are lower in the hierarchy inherit attributes from not only their immediate supertype but from all ________ in the hierarchy. A) subtypes B) supertypes C) constraints D) dimensions
total specialization; overlap
In packaged data models, all subtype/supertype relationships follow the ________ and ________ rules. A) partial specialization; disjoint B) total specialization; disjoint C) total specialization; overlap D) partial specialization; overlap
M:N relationships - many-to-many
In packaged data models, strong entities always have ________ between them. A) weak entities B) 1:1 relationships C) 1:M relationships D) M:N relationships
create table
In the SQL language, the ________ statement is used to make table definitions. A) create session B) create table C) create index D) select
Candidate
In the figure below, Customer_ID in the CUSTOMER Table is which type of key? A) Composite B) Candidate C) Standard D) Grouped
must be a graduate student, an undergraduate, a special student or some other type of student
In the figure below, a student: A) must be a graduate student, an undergraduate, a special student or some other type of student. B) must be a graduate student or an undergraduate student. C) must be at least a special student. D) must be a doctoral student.
TRUE.
In the figure below, each employee has exactly one manager. Answer : T / F
disjoint
In the figure below, the patient must be either an outpatient or a resident patient. This is an example of the ________ rule. A) disjoint B) specialization C) generalization D) overlap
COMPOSITE
In the figure below, the primary key for "Order Line" is which type of key? A) Composite B) Foreign C) Standard D) Grouped
Vehicle
In the figure below, to which of the following entities are the entities "CAR" and "TRUCK" generalized? A) Make B) Vehicle C) Model D) Price
An associative entity
In the figure below, what is depicted? A) A one-to-one relationship B) A unary relationship C) A one-to-many relationship D) An associative entity
Recursive foreign
In the figure below, what type of key is depicted? A) Primary B) Recursive primary C) Composite D) Recursive foreign
Advantages of DBMS
Increased end-user productivity; data sharing, security, access, and decision making.
Role of the DBMS
Intermediary between user and database; enables data sharing; present end-user integrated view of data; receives and translates application requests into operations; hides integral complexity from programs and users.
Data modeling
Iterative and progressive process of creating a specific data model for a determined problem domain.
Query language
Lets the user specify what must be done without specifying how.
Entity relationship model disadvantages
Limited constraint representation and relationship representation. No data manipulation language. Loss of information content occurs when attributes are removed from entities to avoid crowded displays.
There are six different normal forms, all of which build upon each other. When a relation is in first normal form, all repeating groups have been removed. Second normal form means that any partial functional dependencies have been removed. A partial functional dependency occurs when a primary key is a composite key and a nonkey attribute is dependent upon part of the key. A relation is in third normal form when all transitive dependencies have been resolved. A transitive dependency occurs when one nonkey attribute is dependent upon another nonkey attribute. Boyce-Codd normal form removes any further dependencies that may result from anomalies. A relation is in fourth normal form when any dependencies from multivalued attributes have been removed. Fifth normal form removes any further anomalies. LO: 4.8: Use normalization to decompose a relation with anomalies into well-structured
List and explain 1st, 2nd, 3rd, BCNF, 4th, and 5th normal forms
Record
Logically connected set of one or more fields that describe a person, place, or thing. E.g. Customer name, address, phone number, date of birth.
Physical model
Lowest level of abstraction. The way data is saved on storage media. Requires definition of physical storage. Aimed at logical level, physical-level details not required.
Hierarchal models
Manages large amounts of data for complex manufacturing projects. Represented by an upside-down tree which contains segments. Depicts one-to-many relationships.
Extensible Markup Language XML
Manages unstructured data for efficient and effective exchange of all data types.
Relation or table
Matrix composed of intersecting tuple/rows and attribute/columns.
DBMS traits
Multiuser access control, backup and recovery management, and integrity management.
Data redundancy implications
Poor data security, data inconsistency, and increased likelihood of data-entry errors when complex entries are made in different files.
Subschema
Portion of the database seen by the application programs that produce the desired information from the data within the database.
Semi structured data
Processed to some extent.
Relational model
Produced an automatic transmission database that replaced standard transmission databases. Describes a precise set of data manipulation constructs.
Information
Produced by processing data. Reveals meaning of data; should be accurate, relevant, and timely.
Hierarchal advantages
Promotes data sharing, parent/child relationship promotes conceptual simplicity and data integrity. Database security is provided and enforced by DBMS. Efficient with one-to-many relationships.
Data quality
Promoting accuracy, validity, and timeliness of data.
End-user data
Raw facts of interest to end user.
Data
Raw facts, such as a telephone number, birth date, customer name, and year-to-date sales value. Little meaning unless organized in some logical manner.
A component is always used in only one item.
Referring to the figure below, which of the following is NOT true? A) A component is part of an item. B) A component is always used in only one item. C) A component can be part of an item. D) A component may be used in many items
Network models
Represent complex data relationships. Improve database performance and impose a database standard. Depicts one-to-many and many-to-many relationships.
Extensible markup language XML
Represents data elements in textual format.
Attribute name
Required to be descriptive of the data represented by the attribute.
Hierarchal disadvantages
Requires knowledge of physical data storage characteristics. Navigational system requires knowledge of hierarchical path. Changes in structure require changes in all application programs. Implementation limitations. No data definition. Lack of standards.
Relational model disadvantages
Requires substantial hardware and system software overhead. Conceptual simplicity gives untrained people the tools to use a good system poorly. May promote information problems.
Class hierarchy
Resembles an upside-down tree in which each class has only one parent.
Entity instance or entity occurence
Rows in a relational table
Islands of information
Scattered data locations. Increases the probability of having different versions of the same data.
Object-oriented model advantages
Semantic content is added, visual representation includes semantic content, inheritance promotes data integrity.
Constraint
Set of rules to ensure data integrity.
Data models
Simple representations of complex real-world data structures. Useful for supporting a specific problem domain.
Object-oriented model disadvantages
Slow development of standards caused vendors to supply their own enhancements. Complex navigational system, learning curve is steep, high system overhead slows transactions.
External schema
Specific representation of an external view.
Internal schema
Specific representation of an internal model. Uses database constructs supported by the chosen database. Software and hardware independent.
Data warehouse
Stores data in a format optimized for decision support.
Data dictionary
Stores definitions of the data elements and their relationships.
Analytical database
Stores historical data and business metrics used exclusively for tactical or strategic decision making.
Relation model advantages
Structural independence is promoted using independent tables. Tabular view improves conceptual simplicity. Ad hoc query capability is based on SQL. Isolates the end user from physical-level details. Improves implementation and management simplicity.
there are attributes that apply to some but not all instances of an entity type
Subtypes should be used when: A) there are attributes that apply to some but not all instances of an entity type. B) supertypes relate to objects outside the business. C) the instances of a subtype do not participate in a relationship that is unique to that subtype. D) a recursive relationship is needed.
Extended relational data model
Supports OO features and complex data representation. Focuses on better data management.
Workgroup database
Supports a small number of users or a specific department.
Enterprise database
Supports many users across many departments.
Multi-user database
Supports multiple users at the same time.
Single-user database
Supports one user at a time.
Network model disadvantages
System complexity limits efficiency. Navigational system yields complex implementation, application development, and management. Structural changes require changes in all application programs.
Connectivity
Term used to label the relationship types.
partial specialization
The ________ rule specifies that an entity instance of a supertype is allowed not to belong to any subtype. A) semi-specialization B) total specialization C) partial specialization D) disjointedness
total specialization
The ________ rule specifies that each entity instance of the supertype must be a member of some subtype in the relationship. A) semi-specialization B) total specialization C) partial specialization D) total convergence
no primary key attribute can be null.
The entity integrity rule states that: A) no primary key attribute can be null. B) referential integrity must be maintained across all entities. C) each entity must have a primary key. D) a primary key must have only one attribute
entity cluster. An entity cluster is a set of one or more entity types and associated relationships
The figure below is an example of a(n): A) supertype/subtype hierarchy. B) hierarchical data model. C) entity cluster. D) column cluster.
subtype discriminator
The following diagram shows: A) total specialization. B) partial specialization. C) the overlap rule. D) subtype discriminator
FALSE
The following figure is an example of the overlap rule
FALSE
The following figure is an example of total specialization.
total specialization.
The following figure is an example of: A) partial specialization. B) completeness. C) total specialization. D) disjointness
partial specialization.
The following figure is an example of: A) partial specialization. B) disjoint completeness. C) total specialization. D) transunion constraint.
supertype/subtype hierarchy.
The following figure shows a: A) disjoint constraint. B) completeness constraint. C) supertype/subtype hierarchy. D) spindle constraint
the overlap rule.
The following figure shows an example of: A) the disjoint rule. B) the completeness rule. C) the underdog rule. D) the overlap rule
Boyce-Codd normal form.
The normal form which removes any remaining functional dependencies because there was more than one primary key for the same nonkeys is called: A) fifth normal form. B) fourth normal form. C) Boyce-Codd normal form. D) sixth normal form.
generalization
The process of defining a more general entity type from a set of more specialized entity types is called: A) generalization B) specialization. C) normalization. D) extrapulation.
specialization
The process of defining one or more subtypes of a supertype and forming relationships is called: A) specialization. B) generalization. C) creating discord. D) selecting classes
attribute inheritance.
The property by which subtype entities possess the values of all attributes of a supertype is called: A) hierarchy reception. B) class management. C) attribute inheritance. D) generalization.
Part type - A Subtype Discriminator is an attribute of the Supertype that is used to "code" or denote which Subtype an entity is
The subtype discriminator in the figure below is: A) Part_Type. B) Part_No. C) Manufactured Part. D) Location.
overlap rule
The subtype discriminator is a composite attribute when there is a(n): A) overlap rule. B) disjoint rule. C) partial specialization. D) full specialization.
map data to be used from package to data in current databases
The third step in the data modeling process with a packaged data model is: A) rename identified data elements. B) rename relationships. C) map data to be used from package to data in current databases. D) interview users
TRUE
There are three separate discriminators in the following diagram because of the overlap rule.
synonyms
Two or more attributes having different names but the same meaning are called: A) homonyms. B) aliases. C) synonyms. D) alternate attributes.
Structured data
Type of database resulting from formatting. Based on type of processing to be performed.
Unstructured data
Type of database that exists in its original state.
Business rules allow designer to:
Understand nature, role, scope of data, and business processes. Develop appropriate relationship participation rules and constraints. Create an accurate data model.
one must be able to check the output of a CASE tool.
Understanding the steps involved in transforming EER diagrams into relations is important because: A) one must be able to check the output of a CASE tool. B) there are rarely legitimate alternatives from which to choose. C) CASE tools can model any situation. D) CASE tools model hypothetical business problems
Entity
Unique and distinct object used to collect and store data.
essential components and structures are already defined.
Using a packaged data model, projects take less time and cost because: A) less personnel are required. B) essential components and structures are already defined. C) there is more time taken to model the enterprise. D) packaged data models only run in Oracle.
Entity relationship model advantages
Visual modeling yields conceptual simplicity. Visual representation makes it an effective communication tool. Is integrated with the dominant relational model.
Big data challenges
Volumes does not allow the usage of conventional structures, expensive, and OLAP tools proved inconsistent dealing with unstructured data.
An anomaly is an error or inconsistency that may result when a user attempts to update a table that contains redundant data. There are three types of anomalies. An insert anomaly occurs when a user attempts to enter new information for part of a table. For example, if we stored customer address with an order. A deletion anomaly occurs when the user wants to delete part of a record but also has to delete related data. A modification anomaly occurs when we have to change the same data in multiple places
What is an anomaly, and what are the three types of anomalies?
create two new relations, one containing the multivalued attribute.
When a regular entity type contains a multivalued attribute, one must: A) create a single relation with multiple lines for each instance of the multivalued attribute. B) create two new relations, one containing the multivalued attribute. C) create two new relations, both containing the multivalued attribute. D) delete the relation and start over.
First normal form
When all multivalued attributes have been removed from a relation, it is said to be in: A) first normal form. B) second normal form. C) Boyce-Codd normal form. D) third normal form
entities
When identifying the parts of the packaged data model that apply to your organization, one should first start with: A) entities. B) attributes. C) primary keys. D) relationships
No two rows in a relation are identical.
Which of the following are properties of relations? A) Each attribute has the same name. B) No two rows in a relation are identical. C) There are multivalued attributes in a relation. D) All columns are numeric
Total specialization
Which of the following is a completeness constraint? A) Total specialization B) Partial generalization C) Total recall D) Partial hybridization
Supertype
Which of the following is a generic entity type that has a relationship with one or more subtypes? A) Megatype B) Supertype C) Subgroup D) Class
Relationships are connected to the highest-level entity type in an order that makes sense.
Which of the following is true of packaged data models? A) Relationships are connected to the highest-level entity type in an order that makes sense. B) All subtype/supertype relationships follow the total specialization and disjoint rules. C) No entities on the many sides of a relationship can be weak. D) All weak entities are considered strong.
A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time.
Which of the following statements is true about the figure shown below? A) A rental unit must be either an apartment or a house, and cannot be both at the same time. B) A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time. C) A rental unit must be either an apartment or a house, and could be both. D) A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time
A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time.
Which of the following statements is true about the figure shown below? A) A rental unit must be either an apartment or a house, and cannot be both at the same time. B) A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time. C) A rental unit must be either an apartment or a house, and could be both. D) A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time
A rental unit must be either an apartment or a house, and could be both.
Which of the following statements is true about the figure shown below? A) A rental unit must be either an apartment or a house, and cannot be both at the same time. B) A rental unit can be an apartment, house or just a rental unit; it may not be more than one at the same time. C) A rental unit must be either an apartment or a house, and could be both. D) A rental unit can be an apartment, a house or just a rental unit. It could be both an apartment and a house at the same time.
Sam Hinz
Which of the following violates the atomic property of relations? A) Sam B) Hinz C) Sam Hinz D) Atomic
A person can only be a faculty, student or staff
Which statement is true about the following diagram? A) A person can only be a faculty, student or staff. B) A student can be both an undergraduate and a graduate student at the same time. C) All attributes of person and student are inherited by undergraduate. D) All attributes of graduate are inherited by person.
Most organizations can no longer afford to have data models developed in house, both in terms of labor costs as well as time. As such, the role of data modeler is evolving from artisan to that of engineer. A packaged data model allows one to customize an organization's database needs using a superset of the model needed by the organization
Why are packaged data models gaining popularity?
Modification
________ are anomalies that can be caused by editing data in tables. A) Insertion B) Deletion C) Modification D) Creation
Physical
________ database specification indicates all the parameters for data storage that are then input to database implementation. A) Logical B) Physical C) Schematic D) Conceptual
Data integrity
________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated. A) Business rule constraint B) Data integrity C) Business integrity D) Data structure
Deletion
________ problems are encountered when removing data with transitive dependencies. A) Insertion B) Modification C) Deletion D) Merging