Design of Database Systems Final Exam
Customers, cars, and parts are examples of: A) entities. B) attributes. C) cardinals. D) relationships
A) entities.
Relational DBMS (RDBMS)
A collection of programs and capabilities that enable IT teams and others to create, update, administer, and interact with a relational database.
Composite Key
A combination of two or more columns in a table that can be used to uniquely identify each row in the table.
A person's name, birthday, and social security number are all examples of: A) attributes. B) entities. C) relationships. D) descriptors.
A) attributes.
A ________ defines or constrains some aspect of the business. A) business constraint B) business structure C) business control D) business rule
D) business rule
A business rule should be internally consistent
T
________ is a tool even non-programmers can use to access information from a database. A) ODBC B) Structured query language C) ASP D) Data manipulation query language
B) Structured query language
Provide a brief overview of the various components of the database environment.
- Staff - Database Administrators - System Developers - End Users - Machine - User Interface -Application Programs - Database - Repository - Database Management System - Database
Legacy systems often contain data of ________ quality and are generally hosted on ________. A) poor; personal computers B) excellent; mainframes C) poor; mainframes D) excellent; workgroup computers
C) poor; mainframes
Relational databases establish the relationships between entities by means of common fields included in a file called a(n): A) entity. B) relationship. C) relation. D) association.
C) relation.
A person, place, object, event, or concept about which the organization wishes to maintain data is called a(n): A) relationship. B) object. C) attribute. D) entity.
D) entity.
In the figure below, Name would be an ideal identifier.
F
With the traditional file processing approach, each application shares data files, thus enabling much data sharing.
F
Information is processed data.
T
What are the three ways to handle missing field values?
- Substitute an approximation of the missing quality - Track missing data - Perform sensitivity testing
A database is an organized collection of ________ related data. A) logically B) physically C) loosely D) badly
A) logically
The most common types of entities are: A) strong entities. B) weak entities. C) associative entities. D) smush entities
A) strong entities.
In an E-R diagram, there are/is ________ business rule(s) for every relationship. A) two B) three C) one D) zero
A) two
Referential Integrity Constraint
An association between two entity types. Table relationships must always be consistent.
Which of the following is NOT a characteristic of a good business rule? A) Declarative B) Atomic C) Inconsistent D) Expressible
C) Inconsistent
Data Dictionary
Set of information used to control access to and manipulation of the database.
Schema
The structure that contains descriptions of objects created by a user. (base tables, views, constraints)
What is the role of data dictionary in management and maintenance of a database?
To help database administrators and their staff to manage databases.
A data model is a graphical system used to capture the nature and relationships among data.
T
A single occurrence of an entity type is called an entity instance.
T
While business rules are not redundant, a business rule can refer to another business rule.
T
What are the key decisions in physical database design?
- Choosing the storage format from the logical data model - Grouping attributes into physical records - Arranging similarly structured records in secondary memory - Selecting structures for storing and connecting files - Preparing strategies for storing and connecting files
Discuss some of the disadvantages of file processing systems.
- Data Redundancy (Duplicated Data) - Inconsistent Data (Because of Data Redundancy) - Difficult Access to Data - Limited Data Sharing - Lack of Data Integrity (Not Correct or Consistent Data) - Security Problems
What are six potential benefits of achieving a widely accepted SQL standard?
- Less training expenses - Productivity - Application Portability - Application Durability - Reduced trust on a particular vendor - Cross-System Communication
What are the Three major inputs to physical design?
- Logical database structures developed during logical design - User processing requirements identified during requirements definition - Characteristics for the DBMS and other components of the computer operating environment
What are five areas where threats to data security may occur?
- Lost data - Corrupted Data - Unauthorized Access - Natural Disasters, terrorism, war - Revenge attacks by employees
What are the primary data integrity constraints in SQL?
- Primary Key Constraints - Unique Constraints - Foreign Key Constraints - NOT NULL Constraints - Check Constraints - Dropping Constraints
Discuss some of the advantages to the database approach.
- Reduce Data Redundancy - Consistent Data Flow - Data Integrity - Security (User Privileges & Limited Access) - Improved Data Sharing - Better Access to Data - Increased User Productivity
Discuss some of the costs associated with the database approach.
- Specialized Database Personnel - Hardware - Installation of Hardware - Conversion Costs - Backup & Recovery Protocols
What are the seven criteria for selecting a file organization?
- Test data retrieval - High throughput for processing transaction - Efficient use of storage space - Protection from failures and data loss - Minimizing need for reorganization - Accommodating growth - Security from unauthorized use
Describe a relational DBMS (RDBM), its underlying data model, data storage structures, and manner of establishing data relationships.
A collection of programs and capabilities that enable IT teams and others to create, update, administer, and interact with a relational database. Has a row-based table structure that connects data elements, maintains security, integrity, and consistency of data. Ensures that data is reliable. Most common means of access is via SQL.
Data Type
A particular kind of data item, as defined by the values it can take, the programming language used, or the operations that can be performed on it.
In the figure shown below, which of the following is true? A) A person can marry at most one person. B) A person has to be married. C) A person can marry more than one person, but that person can only be married to one person. D) A person can marry more than one person.
A) A person can marry at most one person.
In the following diagram, which of the answers below is true? A) Each patient has one or more patient histories. B) Each patient has one and only one visit. C) Each patient history belongs to zero and one patient. D) Each patient history belongs to many patients.
A) Each patient has one or more patient histories.
Which of the following is NOT an advantage of database systems? A) Redundant data B) Program-data independence C) Better data quality D) Reduced program maintenance
A) Redundant data
In a file processing environment, descriptions for data and the logic for accessing the data are built into: A) application programs. B) database descriptors. C) fields. D) records.
A) application programs.
A property or characteristic of an entity type that is of interest to the organization is called a(n): A) attribute. B) coexisting entity. C) relationship. D) cross-function.
A) attribute.
Data modeling may be the most important part of the systems development process because: A) data characteristics are important in the design of programs and other systems components. B) the data in a system are generally less complex than processes and play a central role in development. C) data are less stable than processes. D) it is the easiest.
A) data characteristics are important in the design of programs and other systems components.
Program-data dependence is caused by: A) file descriptions being stored in each database application. B) data descriptions being stored on a server. C) data descriptions being written into programming code. D) data cohabiting with programs.
A) file descriptions being stored in each database application.
Business policies and rules govern all of the following EXCEPT: A) managing employees. B) creating data. C) updating data. D) removing data.
A) managing employees.
Discuss why the alternative IS development approaches have evolved, and provide an overview of a couple of these methodologies.
Alternative ID Approaches evolve to make the processes more efficient, cost-friendly, and higher quality. Some approaches are SDLC, Prototyping, and Outsourcing.
What is the difference between an entity type and an entity instance? Give an example
An entity type is a collection of entities, an entity instance is a single occurrence of an entity type. Student is an entity type while their name is an entity instance.
E. F. Codd developed the relational model in the: A) 1960s. B) 1970s. C) 1980s. D) 1990s.
B) 1970s.
________ are established between entities in a well-structured database so that the desired information can be retrieved. A) Entities B) Relationships C) Lines D) Ties
B) Relationships
The following figure shows an example of: A) a one-to-many relationship. B) a strong entity and its associated weak entity. C) a co-dependent relationship. D) a double-walled relationship
B) a strong entity and its associated weak entity.
A rule that CANNOT be violated by database users is called a: A) password. B) constraint. C) program. D) view.
B) constraint.
The logical representation of an organization's data is called a(n): A) database model. B) entity-relationship model. C) relationship systems design. D) database entity diagram.
B) entity-relationship model.
An attribute that must be present for every entity (or relationship) instance is a(n): A) composite attribute. B) required attribute. C) optional attribute. D) multivalued attribute.
B) required attribute.
Because applications are often developed independently in file processing systems: A) the data is always non-redundant. B) unplanned duplicate data files are the rule rather than the exception. C) data can always be shared with others. D) there is a large volume of file I/O.
B) unplanned duplicate data files are the rule rather than the exception.
An entity type whose existence depends on another entity type is called a ________ entity. A) strong B) weak C) codependent D) variant
B) weak
Why is SQL considered to be a set-oriented language?
Because SQL has its own tables and indices. It's better to do something with all rows in one step rather than to do something to one row, then the next, and so on.
For the relationship represented in the figure below, which of the following is true? A) An employee can work in more than one department but does not have to work for any department. B) A department must have at least one employee. C) A department can have more than one employee. D) An employee has to work for more than one department.
C) A department can have more than one employee.
Which of the following is an entity that exists independently of other entity types? A) Codependent B) Weak C) Strong D) Variant
C) Strong
Which of the following is NOT an objective that drove the development and evolution of database technology? A) The need to provide greater independence between programs and data. B) The desire to manage increasing complex data types and structures. C) The desire to require programmers to write all file handling functionality. D) The need to provide ever more powerful platforms for decision support applications.
C) The desire to require programmers to write all file handling functionality.
A user view is: A) what a user sees when he or she looks out the window. B) a table or set of tables. C) a logical description of some portion of the database. D) a procedure stored on the server.
C) a logical description of some portion of the database.
An entity type name should be all of the following EXCEPT: A) concise. B) specific to the organization. C) as short as possible. D) a singular noun.
C) as short as possible.
An attribute that can be broken down into smaller parts is called a(n) ________ attribute. A) associative B) simple C) composite D) complex
C) composite
Database management involves all of the following EXCEPT: A) collecting data. B) organizing data. C) design web pages. D) managing data.
C) design web pages.
A(n) ________ is often developed by identifying a form or report that a user needs on a regular basis. A) enterprise view B) reporting document C) user view D) user snapshot
C) user view
How do we develop field specifications?
Categorize our elements into general, physical, or logical elements.
Data Definition Language
Commands that define a database, including creating, altering, and dropping tables and establishing constraints.
Data Manipulation Language
Commands that maintain and query a database.
What are the components and structure of a typical SQL environment?
Components: - Data Definition Language - Data Manipulation Language - Data Control Language Structure: - Consists of SQL DBMS along with databases, users, programs - Catalog - Schema
Which organizational function should set database standards? A) Management B) Application development C) Technical services D) Database Administration
D) Database Administration
A major difference between data warehouses and transactional systems as compared to big data is: A) the data is unstructured in big data systems. B) the size of the CPU used. C) the programming required to access data. D) all of the above.
D) all of the above.
A graphical system used to capture the nature and relationships among data is called a(n): A) XML data model. B) hypertext graphic. C) relational database. D) data model.
D) data model.
The most common source of database failures in organizations is: A) lack of planning. B) inadequate budget. C) inadequate hardware. D) failure to implement a strong database administration function.
D) failure to implement a strong database administration function.
Metatdata typically describes all of the following EXCEPT: A) data definitions. B) length. C) allowable values. D) location on disk.
D) location on disk.
Data that describe the properties of other data are: A) relationships. B) logical. C) physical. D) metadata.
D) metadata.
According to a McKinsey Global Institute Report, in 2010 alone, global enterprises stored more than: A) 1 terabyte of data. B) 3 gigabytes of data. C) at least 100 petabytes of data. D) more than 7 billion exabytes of data.
D) more than 7 billion exabytes of data.
All of the following are primary purposes of a database management system (DBMS) EXCEPT: A) creating data. B) updating data. C) storing data. D) providing an integrated development environment.
D) providing an integrated development environment.
With the database approach, data descriptions are stored in a central location known as a: A) server. B) mainframe. C) PC. D) repository.
D) repository.
A good data definition will describe all of the characteristics of a data object EXCEPT: A) subtleties. B) examples. C) who determines the value of the data. D) who can delete the data.
D) who can delete the data.
A user view is how the user sees the data when it is produced.
F
A well-structured database establishes the entities between relationships in order to derive the desired information.
F
Data redundancy is used to establish relationships between data but is never used to improve database performance.
F
Databases were developed as the first application of computers to data processing.
F
Most systems developers believe that data modeling is the least important part of the systems development process
F
Organizations that utilize the file processing approach spend only 20 percent of development time on maintenance.
F
The data that you are interested in capturing about an entity is called an instance.
F
The intent of a business rule is to break down business structure.
F
Transaction Log
File that contains log records produced during the logging process in the SQL Server Database.
Sequential File Organization
Files are stored one after another in a sequential manner.
Hashed File Organization
Hashed functions are used to calculate the address of the block to store the records.
A book is identified by its ISBN, and it has a title, a price, and a date of publication. It is published by a publisher, each of which has its own ID number and a name. Each book has exactly one publisher, but one publisher typically publishes multiple books over time.
Image.
Secondary Key
Made on a field that you would like to be indexed for faster searches.
What are some of the guidelines for good data names of objects in general?
Names should relate to the business, be meaningful, be unique, be repeatable, follow a standard syntax, be composed of words from an approved list, and be readable.
Indexed File Organization
Similar to sequential file organization, but an index is used to locate individual records.
What is the difference between a simple attribute and a composite attribute?
Simple Attributes cannot be broken down any further, for example age or marriage status. Composite Attributes can be broken down further for example a name can be broken into first and last name.
Denormalization
Strategy used on a previously normalized database to increase performance.
A good data definition is always accompanied by diagrams, such as the entity-relationship diagram
T
A person is an example of an entity.
T
A relational database establishes the relationships between entities by means of a common field.
T
Data modeling is about documenting rules and policies of an organization that govern data.
T
Data names should always relate to business characteristics.
T
Development starts from scratch with the traditional file processing approach because new file formats, descriptions, and file access logic must be designed for each new program.
T
File processing systems have been replaced by database systems in most critical business applications today.
T
Many of the disadvantages of file processing systems can also be limitations of databases.
T
Metadata are data that describe the properties of other data.
T
One of the roles of a database analyst is to identify and understand rules that govern data.
T
One reason for improved application development productivity with the database approach is that file design and low-level implementation details do not need to be handled by the application programmer.
T
Redundancy increases the risk of inconsistent data.
T
Separation of metadata from application programs that use the data is called data independence.
T
The data repository assists database administrators in enforcing standards.
T
Unplanned duplicate data files are the rule rather than the exception in file processing systems.
T
Encryption
The process of encoding messages or information in such a way that only authorized parties can read it.
What is the purpose of indexing in database implementation?
To optimize performance of the database by minimizing the number of disk accesses required when a query is processed. Used to quickly locate and access data.
