D426 Test Question and Correct Answers
What's an entity?
a person, place, product, concept, or activity
What's a relationship?
a statement about two entities
What's an attribute?
a descriptive property of an entity
What does cardinality define in an entity-relationship model?
MAX number of instances one entity can relate to another.
one-to-one
one-to-many
many-to-many
What does modality define in a relationship?
MIN number of times an entity participates in a relationship
What does a short bar across the relationship line in an entity-relationship (ER) diagram
mean?
the entity must participate in the relationship
Complex databases are developed in 3 phases which are...
CLP
Concept: develop entity-relationship model
Logical: converts the entity-relationship model into tables, columns, keys
Physical: adds indexes and specifies how tables are organized on storage media
What is the point of indexes in databases?
,to quickly locate the data
What SQL function returns the current date in YYYY-MM-DD format?
CURDATE()
What SQL function deletes leading and trailing white space from text values?
TRIM
What wildcard character is used to represent zero or more characters when searching for a
specific pattern using a LIKE operator?
%
What does data represent in data management?
Numeric, textual, visual, or audio info
What type of theory is the basis for relational data structure?
Set Theory
What is the difference between relational databases and NoSQL databases?
Unlike relational databases, NoSQL databases are optimized for big data
How to determine how many clauses are in a statement?
The number of keywords (ie. INSERT INTO, VALUES, FROM)
All SQL statements require a terminating symbol, which is what?
semicolon ;
What statement must precede a SHOW (tables) command from a specific database?
USE <DATABASE>
What is a primary key?
a column, or set of columns, used to identify a row. Must be Unique and not Null
What's a simple primary key?
, primary key consists of a single column
What's a composite primary key?
primary key consists of multiple columns
What's a foreign key?
a column, or group of columns, that refer to a primary key.
_________ is a relational rule that requires foreign key values are either NULL or match
primary key value.
Referential integrity
What are aggregate functions?
processes values from a set of rows and returns a summary value (ie. COUNT, SUM,
MIN/MAX, AVG, GROUP BY, HAVING)
A ______ relationship relates an entity to itself.
reflexive
A ______ entity is a subset of another entity type, called the ______ entity.
subtype, supertype
(ie. gasVehicle and electricVehicles are subtypes of Vehicle, which is the supertype)
A _______ key is a single-column primary key created by the database designer when no
suitable single-column or composite primary key exists.
artificial key (they're stable, simple and meaningless)
What constraint propagates any modifications made to primary keys to foreign keys?
(e.g. if a primary key is deleted, rows containing matching foreign keys are deleted)
CASCADE
What relationship type exists between a supertype and its subtype entities?
What's an entity?
a person, place, product, concept, or activity
What's a relationship?
a statement about two entities
What's an attribute?
a descriptive property of an entity
What does cardinality define in an entity-relationship model?
MAX number of instances one entity can relate to another.
one-to-one
one-to-many
many-to-many
What does modality define in a relationship?
MIN number of times an entity participates in a relationship
What does a short bar across the relationship line in an entity-relationship (ER) diagram
mean?
the entity must participate in the relationship
Complex databases are developed in 3 phases which are...
CLP
Concept: develop entity-relationship model
Logical: converts the entity-relationship model into tables, columns, keys
Physical: adds indexes and specifies how tables are organized on storage media
What is the point of indexes in databases?
,to quickly locate the data
What SQL function returns the current date in YYYY-MM-DD format?
CURDATE()
What SQL function deletes leading and trailing white space from text values?
TRIM
What wildcard character is used to represent zero or more characters when searching for a
specific pattern using a LIKE operator?
%
What does data represent in data management?
Numeric, textual, visual, or audio info
What type of theory is the basis for relational data structure?
Set Theory
What is the difference between relational databases and NoSQL databases?
Unlike relational databases, NoSQL databases are optimized for big data
How to determine how many clauses are in a statement?
The number of keywords (ie. INSERT INTO, VALUES, FROM)
All SQL statements require a terminating symbol, which is what?
semicolon ;
What statement must precede a SHOW (tables) command from a specific database?
USE <DATABASE>
What is a primary key?
a column, or set of columns, used to identify a row. Must be Unique and not Null
What's a simple primary key?
, primary key consists of a single column
What's a composite primary key?
primary key consists of multiple columns
What's a foreign key?
a column, or group of columns, that refer to a primary key.
_________ is a relational rule that requires foreign key values are either NULL or match
primary key value.
Referential integrity
What are aggregate functions?
processes values from a set of rows and returns a summary value (ie. COUNT, SUM,
MIN/MAX, AVG, GROUP BY, HAVING)
A ______ relationship relates an entity to itself.
reflexive
A ______ entity is a subset of another entity type, called the ______ entity.
subtype, supertype
(ie. gasVehicle and electricVehicles are subtypes of Vehicle, which is the supertype)
A _______ key is a single-column primary key created by the database designer when no
suitable single-column or composite primary key exists.
artificial key (they're stable, simple and meaningless)
What constraint propagates any modifications made to primary keys to foreign keys?
(e.g. if a primary key is deleted, rows containing matching foreign keys are deleted)
CASCADE
What relationship type exists between a supertype and its subtype entities?
