CS403: Introduction to Modern Database Systems Certification Exam Answers

CS403: Introduction to Modern Database Systems Exam Quiz Answers

Question 1: Which of the following is information? Select one:

• 32
• It is 32.
• It is 32 degrees.
• It is 32 degrees Fahrenheit.

Question 2: Which of the following is data? Select one:

• 32
• I am 32
• It is 32 degrees.
• It is 32 outside.

Question 3: Which of the following best describes a database? Select one:

• A system for managing data
• A set of data for a particular application
• A major subsystem of an operating system
• A reusable, organized collection of related data

Question 4: What are databases generally used for today? Select one:

• Backing up hard drives or other storage disks
• Recording the behavior of real time embedded systems
• Storing and retrieving enterprise data for business systems
• Performing computations for scientific and engineering application systems

Question 5: Which kind of database corresponds to ‘graph’? Select one:

• Network
• Relational
• Hierarchical
• Object-oriented

Question 6: What kind of database corresponds to ‘table’? Select one:

• Network
• Relational
• Hierarchical
• Object-oriented

Question 7: Which of the following describes the capability to change the physical schema of a database without having to rewrite application programs? Select one:

• Logical data independence
• Physical data dependence
• Physical data independence
• Physical schema refinement

Question 8: Logical data independence allows modifications of what without rewriting application programs? Select one:

• User view
• Table contents
• Logical schema
• Physical schema

Question 9: What is a database view? Select one:

• a meta table
• a virtual table
• a copy of a table
• a pointer to a table

Question 10: Indices are useful for searching a database because they decrease which of the following? Select one:

• query complexity
• search time for certain types of queries
• the amount of storage used in searching a database
• the design effort needed for developing the database search algorithms

Question 11: A link can be established between two tables by the use of what kind of key? Select one:

• foreign
• hash
• national
• primary

Question 12: IMS is an example of a database system that implemented which of the following models? Select one:

• Hierarchical
• Network
• Relational
• Theoretical

Question 13: “User requirements” corresponds to which of the following database terms? Select one:

• Internal view
• External view
• Network schema
• Data management language

Question 14: Which of the following statements describes the role of an E-R diagram in the development of a relational database? Select one:

• An E-R diagram is transformed into the database design.
• An E-R diagram is transformed into the internal database.
• An E-R diagram is transformed into the physical database.
• An E-R diagram is used to represent the database requirements.

Question 15: Which of the following can we use to indicate whether an entity instance is mandatory? Select one:

• Cardinality
• Optionality
• Disjointedness
• Referential integrity

Question 16: You have an application domain that consists of Cities, Towns, and Neighborhoods. A City has a name, zip code, state, and a country. A Town has a name, zip code, state, and country. A Neighborhood has a name, coordinate, and population. Using a relational model, what are Students, Courses, and Sections called? Select one:

• Attributes
• Constraints
• Entities
• Relations

Question 17: Which is the correct expression to find all students registered for CS403? Select one:

• σcourse_id=”CS403″ (course)
• σdescription=”CS403″ (course)
• σcourse_id = “CS403” (registration)
• σdescription=”CS403″ (registration)

Question 18: What is relational algebra? Select one:

• The mathematical basis for SQL
• A language used to store data in a database
• The translation of a query by an SQL compiler
• An informal way to describe how a database works

Question 19: Which of the following is this operation an example of?

Student ⋈ _{student.sid = course.sid} Course Select one:

• An equijoin
• A hash join
• A theta join
• Aa natural join

Question 20: Which of the following is Student ⨯ Courses an example of? Select one:

• A theta join
• An inner join
• A natural join
• A cartesian product

Question 21: In what normal form is the relation in the figure? Select one:

• First normal form
• Second normal form
• Third normal form
• Boyce-Codd normal form

Question 22: Which of the following describes a relation is in first normal form (1NF)? Select one:

• It has no identical rows
• It has no repeating groups
• It has no partial functional dependencies
• It has no transitive functional dependencies

Question 23: Consider the following database table:

Part No.	Description	Cust ID	Name	Quantity Ordered
2361	Pens	7810	J. Smith	22
2371	Paper Clips	7810	J. Smith	1000
2914	Pens	7914	K. Jones	900

If we were to delete the third row, all information about customer 7914 would be lost. Which of the following is this an example of? Select one:

• A deletion anomaly
• An update anomaly
• An insertion anomaly
• A modification anomaly

Question 24: Consider the following database table:

Part No.	Description	Cust ID	Name	Quantity Ordered
2361	Pens	7810	J. Smith	22
2371	Paper Clips	7810	J. Smith	1000
2914	Pens	7914	K. Jones	900

If J. Smith changes her last name to Klein, we would have to update multiple rows in the table. Which of the following is this an example of? Select one:

• A hash anomaly
• A deletion anomaly
• An update anomaly
• An insertion anomaly

Question 25: Which of the following is a data definition command? Select one:

• Insert
• Select
• Update
• Create table

Question 26: If we have the following table:

customer (custNo, name, street, city, state, zip, telephone, creditLimit)

What will this update command do?

update customer
set creditLimit = creditLimit * 1.05
where state = ‘MA’

Select one:

• Insert a new record into the table for each MA customer
• Set the credit limit of all MA customers to credit limit * 1.05
• Set the credit limit of all MA customers to credit limit / 1.05
• Change state to MA where credit limit was increased by 5%

Question 27: Which of the following is true about a select statement? Select one:

• It can only be used by itself.
• It can be included as part of an insert only.
• It can be included as part of an update but not as part of a delete.
• It can be included as part of an update, delete, or insert statement.

Question 28: What is the select statement used to do? Select one:

• Query a table
• Create a new table
• Insert data into a table
• Delete rows from a table

Question 29: What would the following query produce for results?

select count (*)
from customer
where credit_limit > 500

Select one:

• A listing of all customers
• A listing of all customers with a credit limit greater than 500
• A count of all customers
• A count of all customers with a credit limit greater than 500

Question 30: Which of the following describes the order by clause? Select one:

• It is always the first part of an SQL select statement
• It sorts the entire table by ordering the values of a column(s)
• It sorts the result set from a query by ordering column values
• It uses descending when the order for sorting is not specified

Question 31: You are given this table:

Registration
SID	Course ID	Sem ID	Instructor	Grade
282712	ENGL210	201701	H. Zacny	B+
362112	CS101	201701	K. Ross	C
652123	CS403	201701	K. Ross	A
362112	CS403	201701	K. Smith	C+
282712	BIO101	201601	I. Olsen	C

What will the result of the following query be?

select sid, count (*)
from registration
group by sid

Select one:

• The entire table sorted by sid
• No results due to an error
• 282712 2

362112 2

652123 1

• 282712 1

362112 1

652123 1

Question 32: Which type of join returns values from either table regardless of whether there is a matching value in the other table? Select one:

• Full join
• Left join
• Half join
• Empty join

Question 33: You are given the following tables:

Student
SID	Lname	Fname	Major
986223	Smith	Janet	Chemistry
362112	Williams	Henry	Computer Science
282712	Jones	John	English Literature

Registration
SID	Course ID	Sem ID	Instructor	Grade
282712	ENGL210	201701	H. Zacny	B+
362112	CS101	201701	K. Ross	C

What would the following SQL Select statement produce for output?

select Lname, Course ID, grade
from student 
left join registration
on student.sid = registration.sid

Select one:

• Williams CS101
• Williams CS101 C

Jones ENGL210 B+

• Smith null null

Williams CS101 C

Jones ENGL210

• No output due to a syntax error

Question 34: Which of the following code segments best describes information? Select one:

• Cost = 10
• Int X;

X = 10

• int Dollars = 10;

Print Dollars

• Int Dollars;

Dollars = 10;

Cost = Dollars

Question 35: Which of the following are generic database architectures? Select one:

• Distributed and leased
• Centralized and leased
• Centralized and distributed
• Centralized and networked

Question 36: What is data that describes data called? Select one:

• Metadata
• Abstract data
• Semantic data
• Referential data

Question 37: Which level of the three-level database architecture deals with how the user sees data? Select one:

• Conceptual
• External
• Individual
• Internal

Question 38: What are rules that are valid for information in a database called? Select one:

• constructs
• constraints
• foreign keys
• primary keys

Question 39: Which of the following statements best describes object-relational databases? Select one:

• They have little vendor support
• They integrate multiple paradigms
• They cannot be easily implemented
• They integrate incompatible paradigms

Question 40: Which of the following is an example of a use of an E-R diagram? Select one:

• A model of a database
• A design of a database
• A diagram of a database
• A specification of database requirements

Question 41: Which of the following is true of the ER diagram in the figure below? Select one:

• An employee can work for many departments
• An employee can work for one department and a department can only have one employee
• An employee can work for one department and a department can have one or more employees
• An employee can work for one department only and a department does not have to have employees

Question 42: Which of the following does this figure represent? Select one:

• Imaging entity
• Associative entity
• Binary relationship
• Ternary relationship

Question 43: In relational algebra, how would one select just the name from the student relation? Select one:

• Π (Student)
• Π (Course)
• ΠName (Student)
• σname (Student)

Question 44: A relation is considered to be in Boyce-Codd normal form if and only if which of the following applies? Select one:

• It is in fourth normal form
• There are transitive dependencies
• Every determinant is a candidate key
• There are some partial functional dependencies

Question 45: What would this delete operation do?

delete from course
where credits = 3

Select one:

• Add a new row to the course table
• Delete all records in the course table
• Delete three credit courses from the course table
• Delete all records except those with three credits from the course table

Question 46: What would this statement produce for output?

select lname, fname
from customer
where state like ‘%M’

Select one:

• Customers’ last and first names who live in a state that ends with an M
• Customers’ last and first names who live in a state that begins with an M
• a list of all customers in the table who live in a state that ends with an M
• A list of all customers in the table who live in a state that begins with an M

Question 47: Which of the following is the where clause most useful for? Select one:

• Only joining tables
• Choosing the correct table
• Choosing the correct expression to display
• Choosing only rows that fit a certain criterion

Question 48: What will the following Select statement produce for output?

select cid, lname, fname
from customer
where credit_limit between 500 and 1000

Select one:

• A list of all customer records with credit limit > 500 and < 1000
• A list of all customer records with credit limit > =500 and < =1000
• A list of (cid, lname, fname) where credit limit >500 and <1000
• A list of (cid, lname, fname) where credit limit > =500 and < = 1000

Question 49: What does an inner join do? Select one:

• Return all rows in all tables
• Return only those rows with a matching row in the corresponding table
• Return all rows in the right table regardless of whether there is a match in the left table
• Return all rows in the left or right table regardless of whether there is a match in the corresponding table

Question 50: You are given the following tables:

Course
Course ID	Description	Credits
CS101	Computer Science I	3
CS201	Elementary Data Structures	3
ENGL210	Technical Writing	3

Registration
SID	Course ID	Sem ID	Instructor	Grade
282712	ENGL210	201701	H. Zacny	B+
362112	CS101	201701	K. Ross	C
652123	CS403	201603	K. Ross	A

What would the following SQL Select statement produce for output?

select course. Description, registration.SID, registration. grade
from registration
full join course
on registration. courseID = course. courseID

Select one:

• Technical Writing 28212 B+

• Technical Writing 28212 B+

Computer Science I 362112 C

Null 652123 A

• Technical Writing 28212 B+

Computer Science I 362112 C

Elementary Data Structures null null

• Technical Writing 28212 B+

Computer Science I 362112 C

Null 652123 A

Elementary Data Structures null null

Question 51: Which of the following selections best describes information? Select one:

• Letters
• Data plus semantics
• Letters that spell a word
• A number and its data type

Question 52: What is one of the disadvantages of file processing systems? Select one:

• Low data quality
• Limited data sharing
• Short development time
• Program-data independence

Question 53: Which of the following sets of terms describes the data in a database? Select one:

• integrated and shared
• redundant and accurate
• functional and redundant
• transactional and unconstrained

Question 54: One function of a DBMS is provision of which of the following? Select one:

• Data security
• Data accuracy
• Data redundancy
• Data dependence

Question 55: What is used in DBMSes to make sure that data are accurate and consistent? Select one:

• Primary keying
• Check constraints
• Referential integrity
• Field-level validations

Question 56: In this figure, what is TextBook an example of? Select one:

• Primary key
• Composite key
• Multivalued attribute
• Single-valued attribute

Question 57: What kind of relationship is this figure an example of? Select one:

• Unary
• One to all
• One to one
• One to many

Question 58: What constraint type does the following constraint belong to?

“Every table must have a primary key, of which no part can be null.”

Select one:

• Entity integrity
• Domain integrity
• Semantic integrity
• Referential integrity

Question 59: If we had another relation, staff, which was union compatible with faculty, what would the following expression return for a result?

Π_empno (Staff ⋃ Instructor)

Select one:

• The employee number of every staff employee and every instructor
• Every employee who is either a member of the staff or an instructor
• All tuples of the instructor relation along with all tuples of the staff relation
• The employee number of all rows in the staff and in the instructor relations

Question 60: What is the highest normal form that all relations in the diagram are in?

Select one:

• First normal form
• Third normal form
• Fourth normal form
• Boyce-Codd normal form

Question 61: What is the delete statement used to do? Select one:

• Delete table rows that match a condition
• Truncate parts of a table that are too long
• Remove table columns that match a condition
• Insert a mark to indicate that a table is deleted

Question 62: Which of the following is a SQL aggregate function? Select one:

• average
• avg
• mean
• sqrt

Question 63: What is the SQL ‘group by’ statement used to do? Select one:

• Sort the results in group order
• List the rows ordered by group
• List a single row for each group
• Insert new records into the table

Question 64: Which of the following describes a DBMS? Select one:

• A part of a database
• A database application
• An interface between applications
• An interface between a database and users of the database

Question 65: Which of the following are the most widely-used types of databases today? Select one:

• Network
• Relational
• Hierarchical
• Object-oriented

Question 66: Which view in the three-level database architecture offers a formal description of the data with no concern for how it will be stored? Select one:

• Conceptual
• Data
• External
• Internal

Question 67: What is commonly used to represent a relation? Select one:

• an index
• a schema
• a table
• a view

Question 68: If you were a database consultant tasked with recommending a data model for a company that gathers data from the internet to identify information on trends in consumer behavior, which type of database would you recommend? Select one:

• Hierarchical
• NoSQL
• Object
• Relational

Question 69: “Design” corresponds to which of the following database terms? Select one:

• Internal view
• External view
• Conceptual schema
• Data management language

Question 70: Which of the following is one of the problems with non-relational databases? Select one:

• Data dependency
• Limited vendor support
• Low speed of operation
• Structural independence

Question 71: In this figure, which of the following is a candidate key?

Select one:

• Name
• License
• SocSec#
• StudentId

Question 72: On an ER diagram, which shape is used to represent an entity? Select one:

• Circle
• Diamond
• Oval
• Rectangle

Question 73: What kind of relationship is this figure an example of? Select one:

• Binary
• Ternary
• Tributary
• Unary

Question 74: Which of the following should we always do when preparing to map an ER diagram to relations? Select one:

• Normalize the ERD until no more normalization can be done
• Check that the DBMS can interpret the constraints in the ERD
• Make sure that we have simplified the ERD as much as possible
• Make sure that our ERD has only one entity and one relationship

Question 75: Which of the following statements about domain constraints is true? Select one:

• They cannot be used in most modern database systems
• They ensure values entered into a table satisfy conditions
• They can only check numeric values for arithmetic relations
• They cannot always be implemented in some SQL implementations

Question 76: When is a relation considered to be in first normal form (1NF)? Select one:

• When there are no dependencies of an attribute on another attribute in the table
• When there are no compound primary keys or partial key that can have a null value
• When there are no partial dependencies or transitive dependencies on a primary key
• When there are no repeating groups of values of an attribute in one or more columns

Question 77: Consider the following database table:

Part No.	Description	Cust ID	Name	Quantity Ordered
2361	Pens	7810	J. Smith	22
2371	Paper Clips	7810	J. Smith	1000
2914	Pens	7914	K. Jones	900

Since the primary key is made up of PartNo and CustID, we cannot insert a new part without adding an associated customer. Which of the following is this an example of? Select one:

• A non-normal form
• A deletion anomaly
• An update anomaly
• An insertion anomaly

Question 78: Which of the following terms describes one attribute (or field) that determines the value of another attribute? Select one:

• Foreign key
• Primary key
• Independent key
• Functional dependency

Question 79: What is the difference between data definition language and data manipulation language? Select one:

• It is used to create tables, views, etc., and data manipulation language is only used for inserts
• It is used for queries and data manipulation language is used for updating and deleting tables only
• It is used to create the files that store tables whereas data manipulation language is used to update data
• It is used to create tables, indexes, etc., whereas data manipulation language is used to query the database

Question 80: Assume that we have a table called student with the following columns:

(sid, fname, lname, major)

What is wrong with this insert command?

insert into student (‘s2234′,’John’,’Smith’,’Chemistry’)

Select one:

• It is missing a left parenthesis
• It is missing a right parenthesis
• It is missing a value before the parenthesis
• There are not enough fields in the statement

Question 81: Which of the following are constraints that can be used with the create table command? Select one:

• references, alias, check
• null, primary key, view of
• primary key, not null, unique
• primary key, default, between

Question 82: What will this SQL statement produce for output?

select distinct lname, fname, dob

from customer

Select one:

• Customer data rows so that there are no duplicates
• Customer rows so that there are no duplicate records
• Customer rows so that there are no duplicate customers
• Customer data rows so that there are no duplicate names

Question 83: What will the following query return for results?

select lname

from customer

where credit_limit in (1000,2000,3000)

Select one:

• All last names and credit limits from 1000 to 2000, and 2000 to 3000
• The last name of all customers with a credit limit of 1000, 2000, or 3000
• All last names and credit limits where the credit limit is 1000, 2000, 3000
• The last name of all customers with a credit from 1000 to 2000, and 2000 to 3000

Question 84: You are given the following tables:

Course
Course ID	Description	Credits
CS101	Computer Science I	3
CS201	Elementary Data Structures	3
ENGL210	Technical Writing	3

Registration
SID	Course ID	Sem ID	Instructor	Grade
282712	ENGL210	201701	H. Zacny	B+
362112	CS101	201701	K. Ross	C

What would the following SQL Select statement produce for output?

select course. description, registration.SID, registration. grade

from registration

right join course

on registration. courseID = course. courseID

Select one:

• Computer Science I 362112 C
• Computer Science I 362112 C

Technical Writing 28212 B+

• Computer Science I 362112 C

Elementary Data Structures null null

Technical Writing 28212 B+

• No results due to syntax error

Question 85: Which kind of database corresponds to ‘class’? Select one:

• Network
• Relational
• Hierarchical
• Object-oriented

Question 86: “User interface” corresponds to which of the following database terms? Select one:

• Subschema
• External view
• Network schema
• Data management language

Question 87: A non-relational data model based on binary trees is which of the following models? Select one:

• Entity
• Hierarchical
• Network
• Object

Question 88: Which of the following does referential integrity ensure? Select one:

• A field is not duplicated in the table
• Two fields are related to one another
• Field values are consistent in the table
• There are no repeating groups in the table

Question 89: What would the following operation produce for output?

πdescription, name ((course ⟖course. courseid = registration. courseid registration) ⋈sid = student.sid student)

Select one:

• All course descriptions and names of all students
• Registered students and all the course descriptions
• All student names and all courses that are registered
• Registered students and descriptions of their courses

Question 90: In SQL, the join statement is most commonly used to do which of the following? Select one:

• Join two or more tables based upon related fields
• Join two or more tables based on related column fields
• Join two or more columns together based on common names
• Join two or more rows together based on related column fields

Introduction to Introduction to Modern Database Systems

Modern database systems are essential for managing and organizing data efficiently in today’s data-driven world. These systems have evolved significantly from early file-based storage methods to sophisticated, scalable solutions that support complex applications and large-scale data management. Here’s an introduction to key concepts and types of modern database systems:

1. Database Models

• Relational Databases (RDBMS):
  • Structure: Data is organized into tables (relations) with rows and columns. Each table represents an entity, and relationships between tables are defined using foreign keys.
  • Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server.
  • Query Language: SQL (Structured Query Language) is used for querying and managing data.
  • Advantages: Strong consistency, data integrity, and support for complex queries.
• NoSQL Databases:
  • Types:
    • Document Stores: Store data as documents (e.g., JSON or BSON). Examples: MongoDB, CouchDB.
    • Key-Value Stores: Store data as key-value pairs. Examples: Redis, DynamoDB.
    • Column-Family Stores: Store data in columns rather than rows, optimized for read and write operations. Examples: Apache Cassandra, HBase.
    • Graph Databases: Focus on relationships and connections between data. Examples: Neo4j, ArangoDB.
  • Advantages: Flexibility in schema design, scalability, and performance for specific use cases like real-time data and unstructured data.

2. Database Architecture

• Single-Tier Architecture:
  • All database components (e.g., data storage, application logic) are in one layer. Suitable for small applications but not scalable for large systems.
• Two-Tier Architecture:
  • The database and the application are separated into client and server layers. The client interacts directly with the database server.
• Three-Tier Architecture:
  • Involves three layers: presentation (user interface), application logic (business rules), and data storage (database). This architecture enhances scalability and maintainability.

3. Database Management System (DBMS) Functions

• Data Storage: Efficiently storing data on disk or in-memory.
• Data Retrieval: Providing mechanisms for querying and accessing data quickly.
• Transaction Management: Ensuring data integrity and consistency through transactions (ACID properties: Atomicity, Consistency, Isolation, Durability).
• Concurrency Control: Managing simultaneous access to data by multiple users.
• Backup and Recovery: Protecting data against loss and enabling recovery in case of failures.
• Security: Managing access control, authentication, and authorization to protect data from unauthorized access.

4. Scalability and Performance

• Vertical Scaling: Adding more resources (CPU, RAM) to a single server to handle increased load.
• Horizontal Scaling: Adding more servers to distribute the load and handle larger volumes of data. Often used in NoSQL databases and cloud-based systems.

5. Cloud Databases

• Managed Databases: Cloud providers offer managed database services (e.g., Amazon RDS, Google Cloud SQL) that handle maintenance tasks such as backups, updates, and scaling.
• Serverless Databases: Provide automatic scaling and resource management without requiring explicit server management (e.g., Amazon Aurora Serverless).

6. Big Data and Analytics

• Data Warehouses: Specialized databases designed for analytical processing and large-scale querying (e.g., Amazon Redshift, Google BigQuery).
• Data Lakes: Storage repositories that hold vast amounts of raw data in its native format until it is needed (e.g., AWS S3, Azure Data Lake).

7. Emerging Trends

• Machine Learning Integration: Databases incorporating machine learning for predictive analytics and automation.
• Multi-Model Databases: Support multiple data models (e.g., relational, document, graph) within the same database system (e.g., ArangoDB, OrientDB).

Modern database systems are integral to the operation of many applications, from transactional systems in businesses to real-time analytics platforms. Understanding their architecture, capabilities, and use cases is crucial for selecting the right database system for specific needs.