Database Normalization Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does 3NF indicate regarding dependencies?

All dependencies are considered irrelevant.

Each dependency must include multiple relationships.

Dependencies must be transitive.

Dependencies are non-transitive. (correct)

What is the benefit of having a 'Roles' table in database normalization?

It combines all roles into a single column.

It restricts the number of roles each person can have.

It allows for easier updates and prevents inconsistencies. (correct)

It eliminates the need for a 'People' table.

What does not satisfy the First Normal Form (1NF) in a database?

Storing multiple values in a single column. (correct)

Eliminating duplicate rows.

Having a unique primary key.

Using a junction table to link two tables.

In the context of changing column names for a table, what does 'PascalCase' require?

Each word starts with an uppercase letter. Signup and view all the answers

What is the intended purpose of creating a junction table in M:N relationships?

To link primary keys of two tables. Signup and view all the answers

Which keyword must be attached to column datatype for defining if a column can accept NULL values?

NOT NULL Signup and view all the answers

What is the purpose of the PRIMARY KEY constraint in a table?

To ensure a column has a unique value for each row Signup and view all the answers

How can you define a DEFAULT value for a column in a table?

Attach the DEFAULT keyword with the desired value Signup and view all the answers

What limitation exists for FOREIGN KEY constraints on certain types of tables?

They are not supported for #tables Signup and view all the answers

What command is used to insert data into a specific column of a table?

INSERT INTO Signup and view all the answers

Which data types can be used for defining an identity column?

INTEGER and DECIMAL Signup and view all the answers

When creating a table, how many identity columns can be defined?

Only one identity column can be defined Signup and view all the answers

What is a requirement when using the INSERT INTO statement with a SELECT statement?

The columns of the result set must be type-compatible with the target table's columns Signup and view all the answers

What is the primary purpose of a CHECK constraint in a database?

To enforce a rule as a boolean expression Signup and view all the answers

In the context of constraints, what distinguishes a PRIMARY KEY constraint from a UNIQUE constraint?

PRIMARY KEY does not allow NULL values, whereas UNIQUE can Signup and view all the answers

What happens when you execute the CREATE DATABASE command in Azure SQL if defaults are not modified?

Database will have potentially expensive default settings Signup and view all the answers

When defining a FOREIGN KEY constraint, what is required in the command?

The name of the referenced table and its column names Signup and view all the answers

What is the correct usage of the BEGIN and END keywords in T-SQL?

To define a code block for executing multiple statements Signup and view all the answers

What does the BREAK statement do in a WHILE loop in T-SQL?

It ends the current loop execution Signup and view all the answers

Which of the following best describes an on-premise software deployment?

Software is installed and run on a company's own servers Signup and view all the answers

What is a cursor in a database context?

An object that facilitates sequential processing of rows in a result set Signup and view all the answers

What is the primary function of the .query method in XML data handling?

To extract entire nodes from the XML Signup and view all the answers

What is the significance of the path being case-sensitive in XML functions?

Incorrect case can result in no data being retrieved Signup and view all the answers

What does the .value method return when used correctly with a specified path?

A single scalar value or NULL Signup and view all the answers

Which statement best describes recursion in computer science?

It involves a function that calls itself Signup and view all the answers

What is one important rule regarding common table expressions (CTEs)?

CTEs are declared using the WITH clause Signup and view all the answers

When using subqueries in the SELECT clause, what happens if no rows are returned?

The result is NULL Signup and view all the answers

What is the nature of subselects in the FROM clause?

They require aliases to function Signup and view all the answers

What does the UNION ALL keyword do within recursive common table expressions?

Merely serves as a syntax requirement in recursion Signup and view all the answers

Which of the following statements is true regarding XML elements?

An element is defined with an opening and a closing tag. Signup and view all the answers

What is the primary purpose of JSON?

To represent data as a collection of key-value pairs. Signup and view all the answers

Which of the following correctly describes the FOR XML PATH option?

It allows custom naming of elements while maintaining hierarchy. Signup and view all the answers

In JSON, what does a column name starting with '@' indicate?

It represents an attribute instead of an element. Signup and view all the answers

What is a key difference between JSON and XML?

XML can define attributes for elements while JSON does not have a concept of attributes. Signup and view all the answers

How does FOR JSON AUTO differ from FOR JSON PATH?

There is no difference; they produce the same results without parameters. Signup and view all the answers

Which of the following is not a complex data type in JSON?

Boolean Signup and view all the answers

What is the significance of a column name including '/' when using FOR XML?

It instructs the interpreter to produce a hierarchical structure. Signup and view all the answers

What happens by default to NULL values in the output of a query?

NULL values are ignored unless specified otherwise. Signup and view all the answers

Which of the following is true about scalar-valued subqueries?

They may return a value or NULL if no rows exist. Signup and view all the answers

What must every column in a table-valued subquery have?

A unique name Signup and view all the answers

What distinguishes a Common Table Expression (CTE) from a standard subquery?

CTEs improve readability and reusability. Signup and view all the answers

How can you convert the return value of a subquery from nvarchar(max) to XML?

Use the CAST function or the 'TYPE' keyword. Signup and view all the answers

What happens if you omit the column alias in a subquery that produces XML data?

There will be no root element in the XML. Signup and view all the answers

In what format can XML data be stored in SQL Server?

Either as character data or using the XML data type. Signup and view all the answers

Which statement is correct regarding the assignment of values to variables in SQL?

Both SET and SELECT can assign constant values to variables. Signup and view all the answers

Study Notes

Hierarchical Data Structures

Hierarchical data structures are common in real life (e.g., file systems, menus, organizations).
Hierarchical data can be represented as an adjacency list.
An adjacency list is a hierarchical data representation where each element (node) is stored in a table, and relationships between elements are indicated using references.
This structure is often used in relational databases to represent hierarchical structures, with each element linking to its parent's identifier.
XML (eXtensible Markup Language) is a markup language designed for storing and transporting data in a human- and machine-readable format.
XML documents have a well-defined hierarchical structure, organized in a tree-like fashion with a single root element at the top.

XML Structure

Each element is enclosed within opening and closing tags.
Elements can contain other elements, establishing a parent-child relationship.
This hierarchical nature is a key feature of XML.
An XML document has exactly one root element, with other elements nested beneath.
Elements define the structure and content within an XML document.
Elements are nested within opening and closing tags.
Attributes provide additional information about an element and are located within the opening tag.
Attributes are written as name-value pairs, e.g. content.

JSON (JavaScript Object Notation)

JSON is a lightweight data interchange format, easy for humans and machines to read and write.
JSON data is represented as a collection of key-value pairs where keys are strings and values can be various datatypes (strings, numbers, objects, arrays, booleans, or null.)
Key-value pairs are enclosed in curly braces for objects and square brackets for arrays.
This hierarchical structure allows for complex data representations.

Seminar 7 Recap

XML stands for eXtensible Markup Language
Defines and stores data in a shareable manner, used for information exchange between systems.
Follows a tree-like hierarchy structure:
- Has exactly one root.
- Every item has exactly one parent.
- Every element has either child elements or a value.
- Elements are the building blocks of the XML, starting with an opening tag and ending with a closing tag.
- Elements can contain values or other elements.
- Attributes provide extra information about an element via key-value pairs within the opening tag.

Seminar 8 Recap

Subqueries can be used in select statements as scalar-valued expressions or with a table alias in the FROM clause.
Subqueries can be self-contained or correlated.
Subqueries can also be table-valued subqueries.
A Common Table Expression (CTE) is a table-valued subquery, useful for readability, reusability, and defining recursive queries.

Seminar 9 Recap

Temporary tables (# or ## prefixed names): Session-scoped or global tables.
TempDB: A system database used for temporary tables, indexes, and intermediate result sets during processing.
NULL or NOT NULL to define columns that can or cannot store NULL values.
ALTER TABLE used for modifying existing tables; add or alter columns, or drop columns.
DROP TABLE used to drop a table.
Naming rules for tables and columns are mentioned: uniqueness

Seminar 10 Recap

Conditionals (IF...ELSE) are used to create conditional statements.
Loops (WHILE) are used to repeatedly execute blocks of statements.
GO statements are used to separate batches of commands.

Seminar 11 Recap

Database design steps: Determine purpose, organize information, split into tables, define columns, specify primary keys, set up relationships, apply normalization rules.
Normalization: a process to organize data in a database to avoid anomalies.
Anomalies: problems that can occur during data manipulation, preventing flexibility and data consistency.

Seminar 12 Recap

Naming conventions for primary and foreign keys, and junction tables.
GO commands to separate batches of SQL statements.

Seminar 11 & 12 Recap

Normalization tasks: Import data, examine input table datatypes, find key candidates, extract tables, handle 1:N relationships, create junction tables, insert data, set relationships.
Column naming conventions (PascalCase, camelCase, snake_case).

XML Data handling Considerations

XML data can be stored as character data, but using XML data type provides advantages for structured data.

Other Considerations

Multiple considerations related to different SQL statements (e.g., SQL Server) are mentioned in the scanned document.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge on database normalization concepts such as 3NF, 1NF, and the purpose of junction tables. This quiz will cover key aspects of dependency management and naming conventions in database design. Perfect for students or professionals looking to reinforce their understanding of relational database principles.