BUAN 6320 - Exam 2 Review

What are other differences between RDBMS and relational databases?

- Flexibility of the schema - Scaling technique - Support for transactions - Reliance on data to object mapping

What are NoSQL database features?

- Flexible schemas - Horizontal scaling - Fast queries due to the data model - Ease of use for developers

What is the characteristic of the second normal form (2NF)?

1NF and no partial dependencies

What is the characteristic of the third normal form (3NF)?

2NF and no transitive dependencies

What does a natural join do?

A natural join returns all rows with matching values in the matching columns and eliminates duplicate columns

What special functions that perform arithmetic computations over a set of rows?

Aggregate functions (COUNT, MIN, MAX, and AVG) are special functions that perform arithmetic computations over a set of rows

In laymen's terms, what is 2NF?

All Non Key attributes should depend on full Primary key

Do all relational tables satisfy 1NF requirements?

All relational tables satisfy 1NF requirements

in No SQL database features, what is horizontal scaling?

Also known as "scale-out," it involves adding more nodes or servers to a system to share the processing load. In the context of databases, it means distributing data across multiple servers. MongoDB Atlas allows for horizontal scaling by adding nodes to handle increased loads.

What is transitive dependency?

Attribute is dependent on another attribute that is not part of the primary key

In RDBMS we use tables, in MongoDB we use?

Collections

At the heart of SQL is the query and it covers

Covers both questions and actions

What allows you to add, modify, and delete rows from tables?

Data manipulation commands allow you to add, modify, and delete rows from tables

What is dependency diagram?

Depicts all dependencies found within given table structure • Helps to get an overview of all relationships among table's attributes • Makes it less likely that an important dependency will be overlooked

What four major types of NoSQL databases emerged?

Document databases, key-value databases, wide-column stores, and graph databases.

In RDBMS we use rows, in MongoDB we use?

Documents

What is normalization?

Evaluating and correcting table structures to minimize data redundancies

In RDBMS we use columns, in MongoDB we use?

Fields

What is partial dependency?

Functional dependence in which the determinant is only part of the primary key

What is repeating group?

Group of multiple entries of same type can exist for any single key attribute occurrence • Reduces data redundancies• Reduces data redundancies

When all tables are in at least 3NF, higher forms are not likely what?

Higher forms are not likely to be encountered in business environment

What is fully functional dependence (composite key)?

If attribute B is functionally dependent on a composite key A but not on any subset of that composite key, the attribute B is fully functionally dependent on A.

What can be used to generate values to be assigned to a record?

In Oracle and SQL Server, sequences may be used to generate values to be assigned to a record

What keywords may joins use?

Joins may use keywords such as USING and ON

in No SQL database features, what are flexible schemas?

MongoDB's collections, by default, do not require their documents to have the same schema. That is: - The documents in a single collection do not need to have the same set of fields and the data type for a field can differ across documents within a collection. - To change the structure of the documents in a collection, such as add new fields, remove existing fields, or change the field values to a new type, update the documents to the new structure.

In laymen's terms, what is 1NF?

No repeating columns

What is the objective of normalization?

Objective is to ensure that each table conforms to the concept of well-formed relations

What can be classified as inner joins and outer joins?

Operations that join tables can be classified as inner joins and outer joins

What can be used to create triggers, stored procedures, and PL/SQL functions?

Procedural Language SQL (PL/SQL) can be used to create triggers, stored procedures, and PL/SQL functions

What is denormalization?

Produces a lower normal form

The SQL commands are divided into what two overall categories?

SQL commands can be divided into two overall categories: data definition language (DDL) commands and data manipulation language (DML) commands

What generates a new relation in SQL?

SQL provides relational set operators to combine the output of two queries to generate a new relation

What does NoSQl stand for?

Some say the term "NoSQL" stands for "non SQL" while others say it stands for "not only SQL." Either way, most agree that NoSQL databases are databases that store data in a format other than relational tables.

What is data type?

Specification about the kinds of data that can be stored in an attribute • Influence queries that retrieve data

What queries are used when it is necessary to process data based on other processed data?

Subqueries and correlated queries are used when it is necessary to process data based on other processed data

What is the characteristic of the first normal form (1NF)?

Table format, no repeating groups, and PK identified

What is the ORDER BY clause used for?

The ORDER BY clause is used to sort the output of a SELECT statement

What is the main data retrieval command in SQL?

The SELECT statement is the main data retrieval command in SQL

What clause restricts the rows affected by the DDL command?

The WHERE clause can be used with the SELECT, UPDATE, and DELETE statements to restrict the rows affected by the DDL command

What is functional dependence?

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B. Example: PROJ_NUM S PROJ_NAME (read as PROJ_NUM functionally determines PROJ_NAME) In this case, the attribute PROJ_NUM is known as the determinant attribute, and the attribute PROJ_NAME is known as the dependent attribute.

What allows you to create tables and indexes?

The basic data definition commands allow you to create tables and indexes

What are the basic data types?

The basic data types are NUMBER, NUMERIC, INTEGER, CHAR, VARCHAR, and DATE

What list represents one or more column names separated by commas?

The column list represents one or more column names separated by commas

What can be created to expose subsets of data?

Views can be created to expose subsets of data to end users primarily for security and privacy reasons

When people use the term "NoSQL database", they refer to it?

When people use the term "NoSQL database," they typically use it to refer to any non -relational database.

In laymen's terms, what is 3NF?

all Non Key attributes non-transitively ( Directly ) dependent on Primary key

1NF describes tabular format in which

• All key attributes are defined • There are no repeating groups in the table • All attributes are dependent on the primary key

While designing a new database structure, normalization does what?

• Analyzes the relationship among the attributes within each entity • Determines if the structure can be improved through normalization • Improves the existing data structure and creates an appropriate database design

What makes up partial dependency?

• Assumption: one candidate key • Straight forward • Easy to identify

What are fundamental types of data?

• Character data • Numeric data • Date data

What are the categories of SQL functions?

• Data definition language (DDL) • Data manipulation language (DML) • Transaction control language (TCL) • Data control language (DCL)

What makes up the normalization objective?

• Each table represents a single subject • Each row/column intersection contains only one value and not a group of values • No data item will be unnecessarily stored in more than one table • All nonprime attributes in a table are dependent on the primary key • Each table has no insertion, update, or deletion anomalies

What is three step procedure?

• Eliminate the repeating groups • Identify the primary key • Identify all dependencies

When should NoSQL be used?

• Fast-paced Agile development • Storage of structured and semi-structured data • Huge volumes of data • Requirements for scale-out architecture • Modern application paradigms like microservices and real-time streaming

What are the normal forms?

• First normal form (1NF) • Second normal form (2NF) • Third normal form (3NF)

What makes up the structural point of view of normal forms?

• Higher normal forms are better than lower normal forms • Properly designed 3NF structures meet the requirement of fourth normal form (4NF)

When normalization works one relation at a time, it does what?

• Identifies the dependencies of a relation (table) • Progressively breaks the relation up into a new set of relations

What makes up transitive dependency?

• More difficult to identify among a set of data • Occur only when a functional dependence exists among nonprime attributes

Why NoSQL? Why do firms use NoSQL?

• NoSQL databases are used in nearly every industry. • Use cases range from the highly critical to the more fun and frivolous. • financial data • healthcare records • storing IoT readings from a smart kitty litter box.

What makes SQL relatively easy to learn?

• Nonprocedural language with basic command vocabulary set of less than 100 words • Differences in SQL dialects are minor

What are the benefits of normalization?

• Reduces data anomalies • Assigns attributes to tables based on determination

What does denormalization result in?

• Results in increased performance and greater data redundancy