DATABASE EXAM

Which form of normalization removes transitive dependencies?

3NF

What is the output of a CROSS JOIN?

A Cartesian product of all rows from both tables.

What is a candidate key?

A column or set of columns that can uniquely identify a row.

What is the difference between a composite key and a composite determinant?

A composite key uniquely identifies rows; a composite determinant determines other attributes.

What distinguishes a database from a spreadsheet?

A database supports multiple related tables and enforces relationships.

What is a foreign key?

A field that references the primary key of another table.

What is a supertype in an E-R model?

A generic entity related to one or more subtypes.

What is a composite key?

A key made of two or more attributes that together uniquely identify a row.

What type of relationship is modeled using a line-item pattern?

A many-to-many relationship with attributes.

What does a vertical bar in Crow's Foot notation indicate?

A minimum cardinality of one (mandatory participation).

What is a transitive dependency?

A non-key attribute depends on another non-key attribute, which depends on the primary key.

What is a transitive dependency?

A non-key attribute is determined by another non-key attribute.

What is a subquery?

A query nested inside another SQL query.

What is a recursive relationship in SQL?

A relationship where a table relates to itself.

What is a stored procedure (SPROC)?

A saved SQL code block that can be executed with parameters

What is an identifying relationship?

A solid line relationship where the child's key includes the parent's key.

What is the difference between a strong entity and a weak entity?

A strong entity can exist independently; a weak entity depends on a strong entity for identification.

What symbol indicates a mandatory relationship in Crow's Foot?

A vertical bar (|)

What is the domain integrity constraint?

All values in a column must be of the same data type or domain.

What is a surrogate key?

An artificial primary key often created by the DBMS.

What is a composite identifier?

An identifier made up of two or more attributes.

What does a trigger do in SQL?

Automatically executes code in response to an event like INSERT or DELETE.

What SQL keyword is used to find values within a range?

BETWEEN

What data type should be used for fixed-length strings?

CHAR

What SQL clause is used to create a table?

CREATE TABLE

What does Crow's Foot notation show?

Cardinality and relationship direction

Give an example of metadata.

Column name, data type, or length

What keyword prevents duplicate rows in query results?

DISTINCT

What are the two main components stored in databases?

Data and relationships

What causes an update anomaly?

Data redundancy where a change in one place is not reflected elsewhere.

What are the basic elements of an E-R diagram?

Entities, attributes, relationships

What is the difference between exclusive and inclusive subtypes?

Exclusive allows one subtype; inclusive allows multiple.

What is the difference between hard delete and soft delete?

Hard delete removes the record; soft delete marks it as deleted.

The ______________ operator allows you to compare a column to a list of values.

IN

What type of relationship exists between subtypes and supertypes?

IS-A relationship

What does a discriminator attribute do?

Identifies the subtype of a supertype.

Why is a null value problematic?

It can mean unknown, not applicable, or missing, which leads to ambiguity.

What does the DISTINCT keyword do in a SQL query?

It eliminates duplicate rows from the result.

In a recursive relationship, what does the entity relate to?

Itself

What does the TOP 5 clause do?

Limits the query result to the first 5 rows.

What aggregate function would you use to find the highest order total?

MAX()

What is the result of a RIGHT OUTER JOIN if no match is found in the left table?

NULLs are returned for the left table's columns.

What does a null value mean in a foreign key column?

No relationship or unknown reference.

Which clause would you use to sort query results by department?

ORDER BY

To find non-matching values across tables, use a(n) ______________ JOIN.

OUTER

What does a 1:N relationship indicate in a data model?

One instance of an entity is related to many instances of another entity.

What does a circle in Crow's Foot notation indicate?

Optional participation (minimum cardinality 0)

What type of dependency exists when a non-key attribute is determined by part of a composite key?

Partial dependency

What does the NOT NULL constraint do?

Prevents a column from having null values.

What type of key is best for maintaining referential integrity?

Primary key

_____________ is the internationally recognized standard language used in all DBMS products.

SQL

What type of join uses the JOIN ON syntax explicitly?

SQL INNER JOIN

Give an example of an IS-A relationship.

STUDENT is a PERSON.

What form of normalization removes partial dependencies?

Second Normal Form (2NF)

What kind of entity might be used to avoid inappropriate null values?

Subtype

Which clause limits the number of rows returned in SQL Server?

TOP

What does "self-describing" mean in the context of databases?

The database contains metadata that describes its own structure.

What does maximum cardinality represent in an E-R diagram?

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

What is the result of SELECT COUNT(*) FROM Orders;?

The total number of rows in the Orders table.

What is the purpose of relationships in a database?

To associate data in different tables.

What is a trigger used for in a database?

To automatically execute code in response to insert, update, or delete operations.

What is the main function of a DBMS?

To create, process, and administer databases.

What is the purpose of metadata?

To describe the structure of the database and its contents.

Why are stored procedures used?

To encapsulate reusable SQL code that can be executed with parameters.

What is the purpose of the CHECK constraint?

To ensure that a value meets a specific condition.

What is the purpose of the GROUP BY clause?

To group rows that have the same values in specified columns for aggregation.

What is the purpose of a VIEW?

To present a virtual table based on the result of a SELECT query

What is the main use of a VIEW in SQL?

To present a virtual table based on the result of a SELECT query.

When is a DEFAULT constraint used?

To provide a value when none is specified during insertion.

What is the purpose of normalization?

To reduce data redundancy and eliminate anomalies.

What is the goal of normalization?

To remove redundancy and improve data integrity.

In Crow's Foot notation, what does a double line next to an entity indicate?

Total participation (minimum cardinality of one)

What kind of dependency is removed in 3NF?

Transitive dependency

Which construct would you use to enforce custom logic during data modification?

Trigger

What is the main difference between UNION and UNION ALL?

UNION removes duplicates; UNION ALL keeps duplicates.

What is data redundancy?

Unnecessary repetition of data across tables or rows.

How do you test if a column has no value in SQL?

Use IS NULL

Write an SQL clause that would return all buyers whose name starts with "Ann".

WHERE Buyer LIKE 'Ann%'

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation.

What kind of entity requires another to exist?

Weak entity

When should the GROUP BY clause be used?

When aggregate functions are used and grouping by specific columns is needed.

What is a functional dependency?

When one attribute determines another.

When would you use NVARCHAR over VARCHAR?

When storing Unicode/multilingual text

In normalization, when do you stop splitting tables?

When the table is in 3NF and all anomalies are removed.

A _______________ is a self-describing collection of integrated tables.

database

A _______________ is a value that determines other attribute values in a relation.

determinant

A table is in BCNF if it is in 3NF and every ______________ is a candidate key.

determinant

A ______________ dependency occurs when one attribute uniquely determines another.

functional

An entity's attribute that uniquely identifies its instances is called a(n) ____________.

identifier (or primary key)

A row in a database table represents a(n) _______________.

instance

In a 1:N relationship, the "1" side is called the ______________ and the "N" side is the ______________.

parent; child

A table is in 2NF if it is in 1NF and there are no ______________ dependencies.

partial

The LIKE keyword is used to filter rows based on ____________.

pattern matching in text

A table is in 1NF if it has no ________________ values.

repeating groups or multivalued

The SQL function COUNT(*) returns the number of __________ in a table.

rows

The WHERE clause filters ______________, while the HAVING clause filters ______________.

rows; groups

A ________________ key is automatically generated and has no business meaning.

surrogate

A _______________ entity depends on another for its identification.

weak

Write an example of a JOIN ON clause.

...JOIN Orders ON Customers.CustomerID = Orders.CustomerID

List Codd's 8 rules for a table to qualify as a relation in the relational model.

1. Each table has a unique name. 2. Each row is distinct (no duplicate rows). 3. Each column has a unique name. 4. All values in a column are of the same data type. 5. The order of columns does not matter. 6. The order of rows does not matter. 7. Each cell contains a single atomic value (no multiple values or lists). 8. Each value in a cell is from the domain of that column (no invalid data types).