CS403: Introduction to Modern Database Systems Certification Exam Answers

Clear My Certification March 15, 2024 Computer Science Leave a comment 235 Views

In the modern era, database systems play a pivotal role in managing and organizing vast amounts of data efficiently. These systems serve as the backbone for various applications ranging from e-commerce platforms to social media networks, scientific research, and beyond. Understanding the fundamentals of modern database systems is crucial for developers, data analysts, and businesses aiming to harness the power of data effectively.

Key Components of Modern Database Systems:

Data Model: A data model defines the structure of the data, including how data is organized, stored, and manipulated within the database. Modern database systems often support various data models, including relational, document-oriented, graph, and key-value stores, among others.
Query Language: Query languages allow users to interact with the database to retrieve, insert, update, and delete data. SQL (Structured Query Language) is the most common language for relational databases, while NoSQL databases may use different query languages tailored to their respective data models.
Storage Management: Modern databases employ sophisticated techniques for storing and retrieving data efficiently. This includes disk-based storage, in-memory databases for faster access, and techniques like indexing and compression to optimize performance.
Concurrency Control: Database systems must handle multiple concurrent users and transactions while ensuring data consistency and integrity. Concurrency control mechanisms, such as locking and multi-version concurrency control (MVCC), are essential components of modern database systems.
Transaction Management: Transactions represent a sequence of database operations that must be executed atomically (all or nothing), consistently, and isolated from other transactions. Modern database systems provide transaction management features to ensure data integrity and reliability.
Scalability and Distribution: With the exponential growth of data, modern database systems must be scalable and able to handle large volumes of data and high transaction rates. Distributed database systems distribute data across multiple nodes to achieve scalability, fault tolerance, and improved performance.
Data Security: Securing sensitive data is paramount in modern database systems. This includes authentication mechanisms, access control, encryption, and compliance with regulatory requirements such as GDPR and HIPAA.
Data Analytics and Business Intelligence: Many modern database systems offer built-in support for data analytics and business intelligence, allowing organizations to derive insights from their data through tools like reporting, data visualization, and predictive analytics.
Cloud Integration: With the widespread adoption of cloud computing, modern database systems often integrate seamlessly with cloud platforms, offering benefits such as scalability, flexibility, and cost-effectiveness.
Machine Learning Integration: Some advanced database systems incorporate machine learning capabilities for tasks such as query optimization, anomaly detection, and predictive analytics, enabling more intelligent data processing and analysis.

In conclusion, modern database systems are dynamic and versatile tools that form the foundation of data-driven applications across various industries. Understanding their key components and capabilities is essential for leveraging data effectively in today’s digital age.

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:

Modern Database Systems Saylor Academy 2

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:

Modern Database Systems Saylor Academy 3

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:

Modern Database Systems Saylor Academy 4

Primary key
Composite key
Multivalued attribute
Single-valued attribute

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

Modern Database Systems Saylor Academy 5

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?

Modern Database Systems Saylor Academy 6

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?

Modern Database Systems Saylor Academy 7

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: The diagram contains a unary entity with a one to many relationship. Which of the following best demonstrates a mapping of this relationship?

Modern Database Systems Saylor Academy 8

Select one:

Modern Database Systems Saylor Academy 9

Modern Database Systems Saylor Academy 10

Modern Database Systems Saylor Academy 11

Modern Database Systems Saylor Academy 12

Question 75: The diagram shows a supertype/subtype relationship. Which of the following best demonstrates a mapping of this relationship?

Modern Database Systems Saylor Academy 13

Select one:

Modern Database Systems Saylor Academy 14

Modern Database Systems Saylor Academy 15

Modern Database Systems Saylor Academy 16

Modern Database Systems Saylor Academy 17

Question 76: 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 77: 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 78: 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 79: 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 80: 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 81: 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 82: 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 83: 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 84: 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 85: 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 86: You are given the following tables: