Data Management Foundations

D426

Which type of theory is the basis for relational data structure?

• Set theory

• Graph theory

• Complex design theory

• Entity management theory

Set theory

What is a difference between relational databases and NoSQL databases?

• Unlike relational databases, NoSQL databases are optimized for big data.

• Unlike relational databases, NoSQL databases need a predefined schema to store data.

• Relational databases are open-source software, whereas NoSQL databases are commercial products.

• Relational databases are used to store sets, whereas NoSQL databases are used to store tuples.

Unlike relational databases, NoSQL databases are optimized for big data.

Which statement correctly adds the values 'Strawberry' and 'Red' for the type and color columns, respectively, in the 'fruits' table?

• INSERT INTO fruits (type, color) VALUES ('Strawberry', 'Red');

• INSERT INTO fruits (type, color) VALUES ('Strawberry', 'Red', 'Seedless');

• INSERT INTO fruits (type, color) VALUES ('Red', 'Strawberry');

• INSERT INTO fruits (type, 'Strawberry'), (color, 'Red');

INSERT INTO fruits (type, color) VALUES ('Strawberry', 'Red');

Which DROP statement deletes a column from an existing table?

• DROP COLUMN column_name FROM table_name;

• DROP table_name COLUMN column_name;

• ALTER TABLE DROP COLUMN column_name FROM table_name;

• ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name DROP COLUMN column_name;

What is an example of an expression in SQL?

• City = ‘Bejing’

• 134

• INSERT

• ‘Welcome’

City = ‘Bejing’

What does the % operator return when using numeric values?

• The remainder when dividing one value by another

• The quotient when dividing one value by another

• The absolute value of a value

• The additive inverse (i.e., opposite) of a value

The remainder when dividing one value by another

You are inserting a new record into a table. The primary key column is using an auto-incrementing integer. 
 
What value should you specify for the primary key column in your INSERT statement?

• Omit the primary key column from the INSERT statement

• Include the primary key column and specify a value of 0

• Include the primary key column and specify a negative value

• Omit any column with an integer data type

Omit the primary key column from the INSERT statement

Which language element includes a term with a special meaning for the language processor?

• Literal

• Keyword

• Identifier

• Expression

Keyword

Which SQL command uses the correct syntax to add a new employee John Doe to the Employee table?

• INSERT INTO Employee (Name) Values ( 'John Doe' );

• INSERT Values INTO Employee ( 'John Doe' );I

• INSERT Values INTO Employee ( John Doe );

• INSERT INTO Employee (Name) Values ( John Doe );

INSERT INTO Employee (Name) Values ( 'John Doe' );

Which data type should be used to store the coordinates of a point on a map?

• Binary

• Decimal

• Document

• Spatial

Spatial

Which property specifies an expression on one or more columns of a table?

• CHECK

• RESTRICT

• CASCADE

• UNIQUE

CHECK

What is the function of the − (unary) operator?

• To invert the sign of a numeric value

• To subtract one numeric value from another

• To match exactly one character in pattern matching

• To compare two values for inequality

To invert the sign of a numeric value

Which SQL operator is used to combine two conditions in a query, where both conditions must be true for a row to be included in the result?

• SYNC

• AND

• MERGE

• JOIN

AND

Which SQL keyword is used to find string values that match a specified pattern?

• LIKE

• EQUAL

• SIMILAR

• BETWEEN

LIKE

Which expression can be used to create a temporary name for a table?

• SET

• UPDATE

• AS

• CREATE

AS

Which wildcard character is used to represent zero or more characters when searching for a specified pattern using a LIKE operator?

• %

• -

• *

• ^

%

Which condition must be true for two columns to be compared in a join?

• One column must be a foreign key and the other must be a primary key.

• The two columns must have types that can be compared with each other.

• The two columns must have the same name.

• One column must be a foreign key or primary key and the other must be a primary key.

The two columns must have types that can be compared with each other.

Which SQL query returns a list of all flights along with their corresponding airport names, including flights that do not have a matching airport?

• SELECT FlightNo, Airport.AirportID, AirportName
  FROM Flight
  LEFT JOIN Airport
  ON Flight.AirportID = Airport.AirportID;

• SELECT FlightNo, Airport.AirportID, AirportName
  FROM Flight
  JOIN Airport
  ON Flight.AirportID = Airport.AirportID;

• SELECT FlightNo, Airport.AirportID, AirportName
  FROM Flight
  RIGHT JOIN Airport
  ON Flight.AirportID = Airport.AirportID;

• SELECT FlightNo, Airport.AirportID, AirportName
  FROM Flight
  FULL JOIN Airport
  ON Flight.AirportID = Airport.AirportID;

• SELECT FlightNo, Airport.AirportID, AirportName
  FROM Flight
  LEFT JOIN Airport
  ON Flight.AirportID = Airport.AirportID;

What happens when a column in the SELECT clause is neither included in GROUP BY nor used in an aggregate function, such as in the following query?
 
SELECT Author, Title, COUNT(*) 
FROM Book 
GROUP BY Author;

• Only the first book title for each author will appear in query results.

• The query will return a list of every author with a random title from their books.

• The query will result in an error in standard SQL databases.

• Every book will be listed with its author and a count of one in query results.

The query will result in an error in standard SQL databases.

When would a database designer need to create an artificial key?

• When there is no single column key available in the table

• When the primary key is identical to the supertype primary key

• When no simple or composite primary key exists in the table

• When there is an "Is-A" relationship in the table

When no simple or composite primary key exists in the table

Which type of key can be simple or composite while also being correctly classified as both unique and minimal?

• Encryption key

• Candidate key

• Foreign key

• Master key

Candidate key

Consider the following tables:
 
Table: patient
 

patientId	patientFirstName	patientLastName
100	Maya	Patel
101	Ren	Yoshida
102	Sara	Johnson

 
Table: patientMedication
 

patientMedicationId	patientId	medicationId	startDate	endDate
200	100	91	03-03-2022	NULL
201	100	92	15-03-2022	31-12-2022
202	101	91	03-03-2022	02-01-2023
203	101	94	31-12-2020	14-06-2021
204	101	95	02-02-2023	NULL

 
Table: medication
 

medicationId	medication	ndcCode
91	Aminta	37808-249-01
92	Belcamus	00002-7510-01
93	Sircus	55566-8501-00
94	Elekix	59011-0255-00
95	Lexiplus	63629-5321-01
96	Cadallia	69337-849-01

 
Which columns identify the foreign keys used by the given tables?

• patient.patientId, patientMedication.patientMedicationId, medication.medicationId

• patientMedication.patientId, patientMedication.medicationId

• Medication.ndcCode

• patientMedication.startDate, patientMedication.endDate

patientMedication.patientId, patientMedication.medicationId

How is an attribute defined in an Entity-Relationship (ER) model?

• A descriptive characteristic of an entity

• An entire table within a database schema

• A single record of data stored in a database

• A specific value assigned to a field in a table

A descriptive characteristic of an entity

What is the role of a data dictionary in entity-relationship models?

• To document normalization rules and key constraints

• To document entities, attributes, and their relationships

• To store SQL queries for database transactions

• To define indexing strategies for optimized query performance

To document entities, attributes, and their relationships

How is a one-to-one relationship represented between two entities in an entity-relationship diagram?

• A crow's foot on both ends of the relationship line connecting the two entities

• A less-than sign and a greater-than sign on each end of the relationship line connecting the two entities

• A bar on both ends of the relationship line connecting the two entities

• A bar on one end and a crow's foot on the other end of the relationship line connecting the two entities

A bar on both ends of the relationship line connecting the two entities

Which relationship exists between a supertype and its subtype entities? 

• Binary relationship

• Has-A Relationship

• Unary Relationship

• Is-A relationship

Is-A relationship

What does cardinality define in an entity-relationship model?

• The maximum number of instances one entity can relate to another

• The minimum number of relationships an entity must have

• The uniqueness of each individual record within a database table

• The total number of records stored in a database system

The maximum number of instances one entity can relate to another

Refer to the following relationship diagram:
 

Account                               Processes                           Transaction

AccountNumber R U                                             TransactionCode R

AccountHolder R                                                   TransactionType R

AccountBalance R                                                 TransactionAmount R
                                                                                 TransactionDate R 

Which kind of entity is a transaction in this case?

• Weak

• Strong

• Subtype

• Identifying

Weak

Which relationship is an example of a unary relationship?

• A doctor treating patients

• Employees managing other employees

• A product belonging to multiple categories

• A customer placing multiple orders

Employees managing other employees

Which term is used interchangeably with secondary index in database indexing?

• Sparse index

• Nonclustering index

• Sorted index

• Binary index

Nonclustering index

Which type of index contains an entry for each record in a database table?

• Dense index

• Row index

• Column index

• Sparse index

Dense index

How will the database optimize query execution if the filter factor is high?

• It will scan the index sequentially. 

• It will look for an indexed column in the WHERE clause.

• It will create a new table index.

• It will perform a table scan.

It will perform a table scan.