Database Normalization Quiz
45 Questions
1 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

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?

<p>Each word starts with an uppercase letter. (A)</p> Signup and view all the answers

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

<p>To link primary keys of two tables. (C)</p> Signup and view all the answers

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

<p>NOT NULL (C)</p> Signup and view all the answers

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

<p>To ensure a column has a unique value for each row (B)</p> Signup and view all the answers

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

<p>Attach the DEFAULT keyword with the desired value (B)</p> Signup and view all the answers

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

<p>They are not supported for #tables (B)</p> Signup and view all the answers

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

<p>INSERT INTO (C)</p> Signup and view all the answers

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

<p>INTEGER and DECIMAL (C)</p> Signup and view all the answers

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

<p>Only one identity column can be defined (D)</p> Signup and view all the answers

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

<p>The columns of the result set must be type-compatible with the target table's columns (A)</p> Signup and view all the answers

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

<p>To enforce a rule as a boolean expression (D)</p> Signup and view all the answers

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

<p>PRIMARY KEY does not allow NULL values, whereas UNIQUE can (B)</p> Signup and view all the answers

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

<p>Database will have potentially expensive default settings (C)</p> Signup and view all the answers

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

<p>The name of the referenced table and its column names (A)</p> Signup and view all the answers

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

<p>To define a code block for executing multiple statements (D)</p> Signup and view all the answers

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

<p>It ends the current loop execution (D)</p> Signup and view all the answers

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

<p>Software is installed and run on a company's own servers (B)</p> Signup and view all the answers

What is a cursor in a database context?

<p>An object that facilitates sequential processing of rows in a result set (A)</p> Signup and view all the answers

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

<p>To extract entire nodes from the XML (D)</p> Signup and view all the answers

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

<p>Incorrect case can result in no data being retrieved (A)</p> Signup and view all the answers

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

<p>A single scalar value or NULL (D)</p> Signup and view all the answers

Which statement best describes recursion in computer science?

<p>It involves a function that calls itself (D)</p> Signup and view all the answers

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

<p>CTEs are declared using the WITH clause (D)</p> Signup and view all the answers

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

<p>The result is NULL (D)</p> Signup and view all the answers

What is the nature of subselects in the FROM clause?

<p>They require aliases to function (C)</p> Signup and view all the answers

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

<p>Merely serves as a syntax requirement in recursion (A)</p> Signup and view all the answers

Which of the following statements is true regarding XML elements?

<p>An element is defined with an opening and a closing tag. (A)</p> Signup and view all the answers

What is the primary purpose of JSON?

<p>To represent data as a collection of key-value pairs. (C)</p> Signup and view all the answers

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

<p>It allows custom naming of elements while maintaining hierarchy. (A)</p> Signup and view all the answers

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

<p>It represents an attribute instead of an element. (D)</p> Signup and view all the answers

What is a key difference between JSON and XML?

<p>XML can define attributes for elements while JSON does not have a concept of attributes. (C)</p> Signup and view all the answers

How does FOR JSON AUTO differ from FOR JSON PATH?

<p>There is no difference; they produce the same results without parameters. (D)</p> Signup and view all the answers

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

<p>Boolean (D)</p> Signup and view all the answers

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

<p>It instructs the interpreter to produce a hierarchical structure. (C)</p> Signup and view all the answers

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

<p>NULL values are ignored unless specified otherwise. (D)</p> Signup and view all the answers

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

<p>They may return a value or NULL if no rows exist. (A)</p> Signup and view all the answers

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

<p>A unique name (C)</p> Signup and view all the answers

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

<p>CTEs improve readability and reusability. (C)</p> Signup and view all the answers

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

<p>Use the CAST function or the 'TYPE' keyword. (A)</p> Signup and view all the answers

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

<p>There will be no root element in the XML. (A)</p> Signup and view all the answers

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

<p>Either as character data or using the XML data type. (B)</p> Signup and view all the answers

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

<p>Both SET and SELECT can assign constant values to variables. (C)</p> Signup and view all the answers

Flashcards

JSON Data Types

JSON data can use string, number (integer or decimal), boolean (true/false), NULL (null) and complex data types such as arrays and objects.

JSON Object

A collection of key-value pairs, enclosed in curly braces { }

JSON Array

A collection of similar items, enclosed in square brackets [ ].

XML Structure

XML uses a tree-like hierarchy with elements and attributes, having a single root element.

Signup and view all the flashcards

XML Element

The building blocks of XML, starting with a start tag and ending with an end tag. They can hold values or other elements

Signup and view all the flashcards

XML Attribute

Extra information about an element, stored as key-value pairs within the start tag.

Signup and view all the flashcards

FOR XML AUTO/PATH

SQL commands to output data as XML, with AUTO creating elements from rows and columns as attributes, and PATH creating child elements.

Signup and view all the flashcards

SQL JSON output

SQL can output data in JSON format using FOR JSON AUTO or FOR JSON PATH. Both result in a JSON array of objects.

Signup and view all the flashcards

Subquery in SELECT

A subquery used as a scalar expression in a SELECT statement. It returns a single value, a single column from a single row

Signup and view all the flashcards

Subquery in FROM

A subquery used to create a temporary table within a FROM clause, effectively acting as a virtual table for the main query.

Signup and view all the flashcards

Common Table Expression (CTE)

A temporary, named result set that can be referenced multiple times within a single query. Improves readability and reusability.

Signup and view all the flashcards

XML query return

Returns a single row with a single column containing XML data. Essential for querying & transforming XML data into relational format.

Signup and view all the flashcards

XML Conversion

Converts a subquery's nvarchar(max) output to an XML data type. Necessary for full XML functionality within SQL queries.

Signup and view all the flashcards

Column alias (for XML)

Specifies the name of the root element for XML output. Crucial for structure and interpretation of XML data.

Signup and view all the flashcards

Variable Assignment (SQL)

Assigning values to variables using SET, SELECT statements, or calculated result from queries.

Signup and view all the flashcards

Storing XML Data

Storing XML in database with MSSQL data type. Crucial to utilize XML's structure and avoid just storing as character data.

Signup and view all the flashcards

XML Query (.query)

Used to extract data from XML documents by defining a path to the desired elements.

Signup and view all the flashcards

XML Query Value (.value)

Retrieves a specific value at a given path in an XML document. Includes data type for the expected result.

Signup and view all the flashcards

XML Nodes (.nodes)

Returns a table from XML data, allowing for further processing. Used in FROM clause; results suitable for XML functions only.

Signup and view all the flashcards

Recursion

A programming technique where a function calls itself to solve a problem by breaking it down into smaller, similar subproblems.

Signup and view all the flashcards

Recursive CTE

A Common Table Expression used for recursive queries. Requires UNION ALL to handle different recursive steps.

Signup and view all the flashcards

Subquery (SELECT Clause - Scalar)

A query nested within another query, returning a single value.

Signup and view all the flashcards

Subquery (FROM Clause)

A subquery that returns multiple columns and rows, acting as a temporary table within the main query.

Signup and view all the flashcards

CREATE TABLE (column level)

Defines a new table with columns. Each column definition includes data type and optional constraints.

Signup and view all the flashcards

CREATE TABLE (table level)

Defines a new table with constraints that apply to the entire table, not specific columns.

Signup and view all the flashcards

NULL/NOT NULL

Specifies whether a column can accept empty values (NULL) or requires a value (NOT NULL).

Signup and view all the flashcards

UNIQUE

Ensures that a column has only unique values, no duplicates.

Signup and view all the flashcards

DEFAULT

Sets a default value for a column if no value is provided during data insertion.

Signup and view all the flashcards

CHECK

Defines a rule or condition that data in a column must satisfy.

Signup and view all the flashcards

PRIMARY KEY

Identifies a unique row within a table, ensuring each row has a distinct identifier.

Signup and view all the flashcards

FOREIGN KEY

Creates a link between tables by referencing a column in another table. Ensures data consistency between related tables.

Signup and view all the flashcards

On-Premise Software

Software installed and run on a company's own servers or computers located within its facilities.

Signup and view all the flashcards

Cloud-Based Solutions

Software hosted and accessed over the internet, not locally installed.

Signup and view all the flashcards

CREATE DATABASE Command

Used to create a new database in both on-premise and AzureSQL environments.

Signup and view all the flashcards

T-SQL Code Block

A section of code defined by the BEGIN and END keywords in T-SQL, used for multiple operations.

Signup and view all the flashcards

T-SQL Loop

A block of code that executes repeatedly until a condition is met.

Signup and view all the flashcards

T-SQL BREAK Statement

Used to exit a loop prematurely.

Signup and view all the flashcards

T-SQL CONTINUE Statement

Used to skip the current iteration of a loop and continue with the next.

Signup and view all the flashcards

Cursor

A database object that allows for sequential processing of rows in a result set.

Signup and view all the flashcards

3NF (Third Normal Form)

A database design where dependencies are non-transitive. This means that a column depends directly on the primary key, not indirectly through other columns.

Signup and view all the flashcards

1:N (One-to-Many) Relation

A relationship between two tables where one record in the first table can be associated with multiple records in the second table.

Signup and view all the flashcards

M:N (Many-to-Many) Relation

A relationship where multiple records in one table can be associated with multiple records in another table.

Signup and view all the flashcards

Normalizing M:N Relations

Converting a many-to-many relationship into a one-to-many relationship by creating a junction table.

Signup and view all the flashcards

PascalCase

A naming convention where each word in a name starts with a capital letter, for example: StreamingProviderId.

Signup and view all the flashcards

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.

Quiz Team

Related Documents

Seminar 7 - Database Design

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.

More Like This

Database Normalization Quiz
5 questions

Database Normalization Quiz: Test Your Knowledge

WellRunPrairieDog avatar
WellRunPrairieDog
Database Normalization
5 questions

Database Normalization

LegendaryKnowledge avatar
LegendaryKnowledge
A-1 Database Tables and Normalization
10 questions

A-1 Database Tables and Normalization

HealthfulHyperbola avatar
HealthfulHyperbola
Database Management System - Relational Databases
10 questions

Database Management System - Relational Databases

GloriousOpal6990 avatar
GloriousOpal6990
Use Quizgecko on...
Browser
Open
Browser
Browser