D426 Definitions

What is a materialized view?

A view that cannot be queried directly

A view that is automatically deleted after one use

A view where the result set is physically stored and periodically refreshed from the base tables

A view that can only be accessed by the database administrator

A view where the result set is physically stored and periodically refreshed from the base tables

Which of the following is a fundamental rule for relational tables?

Rows must be stored in a specific, permanent order

A cell can contain multiple values, such as a list of phone numbers

Each column in a table must have a unique name

Duplicate rows are required for data backup

Each column in a table must have a unique name

What is the main purpose of a primary key in a table?

To provide a descriptive name for each row

To link the table to another table

To uniquely identify each row in the table

To store the most important piece of data for each row

To uniquely identify each row in the table

A primary key is a column (or set of columns) that ensures every row in a table is unique and identifiable. No two rows can have the same primary key value, and it cannot be NULL.

The rule that ensures that a foreign key value must match an existing primary key value (or be NULL) is called:

Data independence

Referential integrity

A transaction

A foreign key constraint

Referential integrity

A composite primary key must satisfy which property?

A. Allow NULLs in at least one component.

B. Be minimal — no subset of the columns is itself a primary key.

C. Contain exactly two columns.

D. Always be numeric.

B. Be minimal — no subset of the columns is itself a primary key. Composite keys must be minimal and non-null; allowing NULLs or being required to be two columns are incorrect.

Which guideline about choosing primary keys is recommended?

A. Use descriptive text (like email) as the primary key.

B. Choose a stable, simple, and preferably meaningless attribute (or artificial key).

C. Always use composite keys.

D. Primary keys should change when business rules change.

B. Choose a stable, simple, and preferably meaningless attribute (or artificial key). Stable, simple surrogate keys are recommended; descriptive values change and composite keys are not always preferred.

A table can be logically connected to another table by defining a ____.

A. hyperlink
B. common attribute
C. primary key
D. logic key

B. Common attributes are properties or characteristics (i.e. fields) shared by two or more tables.

Normalization works through a series of normal ____.

A. schemas
B. entities
C. databases
D. forms

D. Normal forms are rules for designing tables with less redundancy.

DROP is used to ____.

A. remove a table
B. insert data
C. update data
D. select data

A. DROP is the Data Definition Language statement to remove a table from the database.

A ____ join returns unmatched rows from both tables.

A. natural
B. cross
C. full outer
D. left outer

C. FULL JOIN is the only join which returns unmatched data from both tables.

Which index type is most suitable for equality searches on many distinct values?

A. Bitmap index

B. Hash index

C. No index

D. Function index

B. Hash index. Hash indexes excel at equality lookups for many distinct values (subject to DBMS support); bitmap indexes suit low-cardinality columns.

What is a base table?

A base table is a table that physically stores data in the database.

What is an artificial key?

An artificial key (also called a surrogate key) is a key that is created solely to uniquely identify rows and has no business meaning.

If you see a column like:

CustomerID
OrderID
StudentID
Person

that is simply an auto-generated identifier with no business meaning, it is probably an artificial (surrogate)

What is a natural key?

A natural key comes from real-world data and already has meaning.

Examples:

• Social Security Number

• Email address

• ISBN for a book

• Vehicle VIN

What is subject area?

Think of it as a group of related entities. Example: University Database

Possible subject areas: Students, Courses, Instructors, Departments, Registration

Within the Students subject area, you might have entities such as: Student, StudentAddress, StudentPhone

What is UML?

UML stands for Unified Modeling Language.

It is a standardized graphical language used to model, visualize, design, and document systems, especially software systems and databases.

What is cardinality notation?

Used to show how many instances of one entity can be related to instances of another entity.

One-to-One (1:1) → A person has one passport, and a passport belongs to one person.

One-to-Many (1:M) → A department has many employees, but each employee belongs to one department.

Many-to-Many (M:N) → A student can enroll in many courses, and a course can have many students.

What is crows foot notation?

The most common cardinality notation in database design is Crow's Foot Notation.

One to Many: ||—-< Many to Many: |>—-<| One to One: ||-—||

Optional 1:M: ||—-0< Optional M:M: ||—-0< Optional 1:1: ||-—0|

What is a transitive dependency?

A non-key attribute depends on another non-key attribute, instead of depending directly on the primary key.

• A → B (B depends on A)

• B → C (C depends on B)

Then:

• A → C (C depends on A through B)

That indirect relationship (A → C) is a transitive dependency.

C depends on B, and because B depends on A, C also depends on A because B depends on A.

What are function (expression) indexes?

Creates a separate data structure that makes queries more efficient. A table might have “Smith” stored many different ways depending on human input and database design.

WHERE UPPERCASE(lastname)=’SMITH’ would parse through every row in the table and convert it to uppercase and then compare.

INDEX(UPPER(lastname)) would create an index with the last name already uppercase so future queries could get search results faster.

What are block splits?

Blocks contain data from tables. When updating tables, the blocks are also being updated. If the respective block is full, it splits to hold that new data.

What is an atomic attribute?

An atomic attribute is an attribute (column) whose values are indivisible — meaning each value holds one single piece of information, not multiple.

ID2301 → “Alice” = Atomic

ID2301 → “Alice White” = not atomic, if firstname and lastname are different values

ID2301 → 222-2222, 333-3333 = not atomic, more than one value.

What is modality?

The minimum number of times an entity must participate in a relationship.

“A customer may or may not place an order” so modality = 0 (optional)

“Every employee must belong to a department” so modality = 1 (mandatory)

What is functional dependence/dependency?

Where one attribute uniquely determines another attribute.

“StudentID → Name” Each StudentID corresponds to one Name. Functional dependency

“Name → Course” ❌ One name can map to multiple courses. Not functionally dependent.

What is a derived attribute?

An attribute whose value is calculated from other stored attributes instead of being stored directly as a column in a table.

Age = CurrentDate - BirthDate → Age is a derived attribute. It’s not stored as a column in a table because it would constantly have to be updated.

What is an associative entity?

An associative entity is an entity created to represent a many-to-many (M:N) relationship between two other entities. It is sometimes called a bridge entity, junction entity, or intersection entity.

A student can borrow many books, and a book can be borrowed by many students over time.

• Associative entity: Borrowing → Students 1:M Borrowing M:1 Books

A student can enroll in many courses, and a course can have many enrolled students.

• Associative entity: Enrollment → Student 1:M Enrollment M:1 Courses

A can have many B, B can have many A → Create associative entity.

What is a derived entity?

What is a heap table?

ETL transformation step involves ____.

A. extraction
B. transformation
C. loading
D. denormalization

A. During the transformation step, a series of rules or functions is applied to the extracted data and can involve transformations such as data summations, encoding, merging, splitting, calculations, and creation of surrogate keys.

What is the fundamental difference between the WHERE clause and the HAVING clause?

WHERE is used for filtering rows, while HAVING is used for sorting them.

WHERE can only be used with SELECT, while HAVING can be used with UPDATE.

WHERE filters individual rows, while HAVING filters the groups after aggregation.

HAVING is a newer, more efficient version of the WHERE clause.

WHERE filters individual rows, while HAVING filters the groups after aggregation.

What is the difference between INNER JOIN and FULL JOIN?

INNER JOIN selects only matching left and right table rows.

FULL JOIN selects all left and right table rows, regardless of match.

In an entity-relationship model, what is a "relationship"?

A statement about two entities.

Which of the following is an example of a reflexive relationship?

A customer places an order.

A book has a title.

An employee manages employees.

A student is enrolled in a course.

An employee manages employees.

Reflexive/Recursive Relationship: Where one row in a table can be associated with another row in the same table.

What is a reflexive relationship?

AKA recursive relationship, where one row in a table can be associated with another row in the same table.

The definition of a table stipulates that columns are _____ and rows are _____.

A. not ordered, not ordered

B. ordered, not ordered

C. ordered, ordered

B. Think of it like this, if you change the order of the columns, the data becomes inconsistent. If you change the order of the rows, the data stays consistent.

Which of the following best describes a tuple in the relational model?

A. A named collection of tables

B. An unordered set of values representing a row

C. An ordered collection of elements representing a row

D. A data type for date/time values

C. An ordered collection of elements representing a row.

A tuple is an ordered list of column values (a single row); sets and relations treat rows as unordered collections but a tuple itself is ordered. Options A and D are incorrect; B uses “unordered” which misdescribes a tuple.

Which statement about NULL is correct?

A. NULL equals zero for numeric columns.

B. NULL is identical to an empty string.

C. NULL represents unknown or inapplicable data.

D. NULL always causes a WHERE condition to evaluate TRUE

C. NULL represents unknown or inapplicable data. NULL is a special marker meaning unknown/inapplicable; it is not equal to zero or empty string and does not evaluate TRUE in boolean comparisons.

Which of the following is structured data?

A. images
B. emails
C. database records
D. audio/video

C. Structured data is data that has a standardized format and can be processed into information.

Dependencies based on only a part of a composite primary key are called ____ dependencies.

A. primary
B. partial
C. incomplete
D. composite

B. Partial dependency in a relational database occurs when a non-prime attribute is functionally dependent on only part of a composite key rather than the whole key.

The set of possible values for an attribute is a ____.

A. domain
B. range
C. set
D. key

A. A domain is a collection of rules that describe available values for a field type.

A(n) ____ attribute is one that cannot be subdivided.

A. composite
B. atomic
C. binary-valued
D. multivalued

B. An Atomic Attribute is another term for Simple Attribute, which refers to a single value and cannot be broken down further.

A ____ key is a key that consists of more than one attribute (made of 2+ columns).

A. primary
B. foreign
C. composite
D. domain

C. In a relational model, attribute is the formal term for a column, and a composite consists of multiple columns.

A relationship is an association between ____.

A. objects
B. entities
C. databases
D. fields

B

A derived attribute ____.

A. must be stored physically within the database
B. need not be physically stored within the database
C. has many values
D. must be based on the value of three or more attributes

B. Derived attributes are calculated using stored values rather than stored themselves.

The Entity Relationship Diagram (ERD) is used to graphically represent the ____ database model.

A. condensed
B. physical
C. logical
D. conceptual

D. The conceptual model is developed during the Analysis Phase of database design.

A foreign key must ____.

A. be numeric
B. be unique
C. be defined in all tables within the database
D. match the value of a primary key in a related table

D. Foreign keys must include a reference to another table’s primary key.

An attribute (or combination of attributes) that uniquely identifies each entity in a table is called a ____.

A. superkey
B. foreign key
C. master key
D. secondary key

A

If an entity can exist apart from one or more related entities, it is said to be ____-independent.

A. existence
B. relationship
C. business
D. weak

A

A primary key ____.

A. is a minimal superkey
B. is always the first field in each table
C. must be numeric
D. must be unique

D. Rules for a primary key are that they are Unique and Not Null.

Another word for the term “relation” is ____.

A. Datafile
B. Data index
C. Table Name
D. Data query

C

A table is perceived as a ____.

A. flat structure
B. two-dimensional structure
C. linked structure
D. graph

B. The two-dimensions we are referring to in this context are the columns and rows (or X and Y axis if you are thinking mathematically).

What does the ‘refer’ in referential integrity mean?

A. The relationships between entities and attributes, also called referrals.
B. Reference points that databases place in each record during backups.
C. A foreign key in a table must refer to a valid primary key in another table.
D. You delete a row in one table whose primary key does not have a matching foreign key value in another table.

C. The reference between a primary and foreign key is how tables in a database are connected and can ensure that changes in one area of the database can be propagated to others.

The entity integrity rule requires that ____.

A. All primary key entries are unique
B. A part of the key may be null
C. Foreign key values do not reference primary key values
D. Duplicate object values are allowed

A. Entity Integrity: This rule proclaims that each row in a table must contain some unique data.

A Database Model is ____.

A. a conceptual framework for database systems
B. a leading relational database system sponsored by Oracle
C. s suitable for non-commercial applications such as education
D. is a specification of database objects such as tables

A

Data is/are ______.

A. Information
B. Raw facts
C. Processed information
D. A DBMS

B. Data by itself cannot tell us much, but data connected in a logical way can produce information which can be acted on.

A record consists of a ____.

A. Character
B. Collection of related characters
C. Set of one or more fields
D. Group of files

C. A set of connected fields in a table make up a row, and that complete row is referred to as a record.

Which of the following best summarizes referential integrity?

A. Foreign keys must always be NOT NULL.

B. Foreign key values must either be fully NULL or match an existing primary key value in the referenced table.

C. Primary keys may be NULL if referenced by foreign keys.

D. Referential integrity is optional and rarely enforced.

B. Foreign key values must either be fully NULL or match an existing primary key value in the referenced table. This is the referential integrity rule.

In ER modeling, what does a “weak entity” mean?

A. It has no attributes.

B. It lacks an identifying (unique) attribute and depends on another entity for identity.

C. It cannot have foreign keys.

D. It is stored outside the database.

B. It lacks an identifying (unique) attribute and depends on another entity for identity. Weak entities depend on identifying relationships for their identity; they are not stored outside the database.

Which of the following best describes a dense index?

A. Contains an entry for every table block only.

B. Contains an entry for every table row.

C. Contains an entry for every distinct value only.

D. Contains only pointers, no values.

B. Contains an entry for every table row. A dense index includes an entry per row; a sparse index has entries per block or subset.

What is a bitmap index?

What is a hash index?

What are B+tree index characteristics?

What is an identifying entity?

What is a subtype entity?

What are clustered (primary) and nonclustered (secondary) indexes?

What is a key characteristic of a NATURAL JOIN?

It is the only type of join that can be used to join a table to itself.

It produces a CROSS JOIN by default.

It automatically joins tables based on all columns that have the same name in both tables.

It requires the developer to explicitly state the join columns using an ON clause.

It automatically joins tables based on all columns that have the same name in both tables.

In an identifying relationship (IsA / subtype), how is the subtype primary key typically defined?

A. Independent random UUID.

B. Same as supertype primary key and also a foreign key referencing the supertype.

C. A composite of all subtype attributes.

D. Always an auto-increment integer unrelated to supertype.

B. Same as supertype primary key and also a foreign key referencing the supertype.

Subtype tables often reuse the supertype primary key as their PK and FK to implement IsA relationships.

Which is a transitive dependency example?

A. A → B and B → C, thus A → C

B. A → B only

C. Composite key split into parts

D. Two tables with one-to-many relationship

A. A → B and B → C, thus A → C This chain is the definition of transitive dependency (A determines C via B); other options do not show transitivity.

A table must have a least __#__ column(s) and __#__ row(s).

One, Zero. A table can have no rows. Ex: A new table is empty until rows are inserted.

DDL stands for

Data Definition Language

DQL stands for

Data Query Language

DML stands for

Data Manipulation Language

DTL stands for

Data Transaction Language

DCL stands for

Data Control Language