Databases Final Study

The LEN() function returns the length of a string. SYNTAX: LEN(string)

*

The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. SYNTAX: REPLACE(string, old_string, new_string)

*

A relation includes columns that are not fully atomic and a transitive dependency, but no partial functional dependencies. What normal form is it in?

0NF

Happy Snarl Pet Products company typically sells 15 ten-pound bags of Swellkibble Dog Food each day. It takes about three weeks from the time an order is placed for ten-pound bags of Swellkibble Dog Food until those bags arrive at the store and are available for sale. Given this, which of the following would be the most reasonable ReorderPoint value for Happy Snarl to enter into the Product table instance for ten-pound bags of Swellkibble Dog Food, given that the company would prefer to never be out of stock of this item?

350

A relation has only one value in its primary key, is fully atomic, and no non-key values predict other non-key values. What normal form is this relation in?

3NF

Which of the following statements is true regarding normal forms?

3NF is more normalized than 2NF, since 3NF should have fewer data duplication issues.

Which of the following describes a case where there is an "atomicity problem" that could be solved through normalization?

A column in the table can be further broken down into meaningful parts.

To which of the following companies would the production cycle almost certainly apply? (Mark any/all that apply.)

A company that uses logs, ink, and resin to manufacture baseball bats. A charity that cuts hand-made "snowflakes" out of paper, then sells them at auction.

Which of the following best describes the difference between a purchase requisition and a purchase order?

A purchase requisition is an initial request placed by the business unit, while a purchase order reflects an actual order placed by purchasing.

According to the syllabus, each homework assignment...

Accounts for 2% of the student's overall grade.

type of accounts receivable system that permits a customer payment on an account balance is called a(n) __________ system.

Balance-forward

An attribute for which an instance's value can only be one or zero is called a(n)...

Binary Attribute

What data type category would you use to store a photo for each record in a table called Customer?

Binary Object

A ________ specifies the number of instances of one entity that can be associated with each instance of another entity

Cardinality constraint

Choose the best answer. The bill of materials...

Choose the best answer. The bill of materials...

An attribute that can be broken down into smaller parts is called a(n) ________ attribute

Composite

Which of the following are included in the receiving report? (Mark any/all that apply.)

Date of Receipt PO Number Referenced Identity of the Supplier

The number of entities that participate in a relationship is called the _____.

Degree

Considering only the FROM clause of a query, which of the following would successfully assign aliases to tables?

FROM Article A, Category C, Writer W

What is the order of execution of the SELECT statement?

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

Let's assume that you wrote a query that has the following four clauses: SELECT, FROM, WHERE, ORDER BY. The order in which the four SQL query clauses are executed is _____ _____ _____ _____. (separate your answers by commas.)

FROM, WHERE, SELECT, ORDER BY

The camera policy in the syllabus does not require students to have a working camera on their computer.

False

Once data within a database are normalized...

Fields are atomic and there are no data duplication issues.

A data dictionary...

Includes information about each field in a database.

UPS, FedEx, and the US Postal Service would most reasonably be...

Instances in the Carrier entity.

When joining tables...

It is necessary to match the foreign key in one table to the primary key in another within the query.

If a student has an emergency and therefore can't complete a quiz on time and receives a 0 on the quiz, ...

It won't necessarily affect the student's grade, since the two lowest quiz scores are dropped.

* in an SQL SELECT statement means

List all columns

Which of the following is/are true with regard to exams? (Mark all that apply.)

More than half of a student's grade is dependent on exam scores.

Question: You determined that you will need a RIGH JOIN to correctly run a query. Will it make a difference if you write RIGH OUTER JOIN instead of RIGH JOIN?

No, we can leave out OUTER and and INNER from a JOIN statement

Given the above ERD, what should go to the GROUP BY clause for the following query to run properly? What is the total sales for NIKE products? SELECT sum(price) AS Total_Revenue FROM SimplifiedSales WHERE ManufacturerName = 'Nike'

Nothing. We do not need the GROUP BY clause in this case

To override precedence of AND, OR, and NOT, use:

Parentheses

An attribute (or attributes) that uniquely identifies each row in a relation is called a:

Primary key

Which of the following is not an objective when selecting a field's data type?

Provide compatibility between database management systems.

This exercise focuses on aggregate functions and JOINs. From the list below, select all the aggregate functions.

SUM (); AVG (); MIN (); MAX (); COUNT ()

The best aggregate function clause to use to determine the total amount each customer spent on shoes would be the following:

SUM(ListPrice*Quantity)

Comparing data types between SQL Server and MySQL...

TINYINT in MySQL can serve the same purpose as bit in SQL Server

In which case would you identify a field as an index field when creating a data dictionary?

The field is often part of the WHERE statement in SQL queries.

It turns out that the marketing department wants to spend $100,000 buying local radio advertising. Who would most likely complete the purchase req for this?

The marketing manager in charge of advertising.

In the default revenue cycle ERD, why are there three different relationships between Employee and ShipmentOut?

The three lines enables the "segregation of duties" among the picking, packing, and shipping activities.

We will start with subqueries in the WHERE clause. What is the purpose of the WHERE clause?

To evaluate a logical test

All of the following are noted in the syllabus as subjects to be covered in this class except...

Use of columnar databases.

Suppose that a given raw material can be supplied by many vendors and that a single vendor can supply many raw materials. Also suppose that each vendor that supplies a raw material does so at a unique price (i.e., not all vendors charge the same price for the same raw material). Given this, in what default production cycle ERD entity would you expect to find a specific vendor's price charged for a specific raw material?

VendorRM

What are the 3 V's of Big Data? Select all that apply

Volume Variety Velocity

Which of the following WHERE clauses would include only those records in the output where the value in the Destination field is something OTHER than Norman?

WHERE Destination <> 'Norman'

Which of the following WHERE clauses would include only those records from the Customer table where the customer's last name (the LastName field) starts with Y and the customer's birthday (DOB field) falls some time after 1994?

WHERE LastName LIKE 'Y%' AND DOB > '1994-12-31'

Which of the following WHERE clauses would include only those records in the output for which the value of UniformNumber is 1, 3, 5, or 7?

WHERE UniformNumber IN (1, 3, 5, 7)

Using a database that includes information about every opera ever written, I want to write a query that displays information only about those operas that were written prior to the year 1825. To do this, I would use which of the following?

WHERE clause

An entity type whose existence depends on another entity type is called a ________ entity

Weak

When would it make sense to use an unsigned data type?

When all possible values are positive numbers.

When would you index a field?

When it often appears in the WHERE clause.

Under the _____ _____ method of invoicing, all orders delivered within a given month are invoiced on a single invoice at the end of the month.

balance forward

Which of the following SQL Server data types would probably be the best choice for a field containing two-letter postal codes that refer to US states?

char(2)

Which of the following SQL Server data types would probably be the best choice for a field containing data on each student's score (out of 100) on a mid-term?

decimal(3,1)

Which of the following SQL Server data types would probably be the best choice for a field containing the number of credits required to complete a master's degree?

tinyint

It would make sense to consider creating a super-type/sub-type relationship when...

An entity's instances share some, but not all, attributes.

Choose the best answer. A report that details, in 30-day increments, the outstanding payments owed to a company is...

An output.

In the conceptual design context, the common types of entities are... (Select any/all that apply.)

Associative entities Weak entities Strong entities

An entity that contains attributes specific to the relationship between instances within one or more entity(-ies) is called a(n)... (Select any/all that apply.)

Associative entity

A property or characteristic of an entity type that is of interest to the organization is called a(n):

Attribute

The attribute within the super-type entity that is used to specify the sub-type(s) to which an instance belongs is called what?

Sub-Type Discriminator

A subquery is a query that is written inside another query Also called a nested query In addition, a subquery can have a subquery A subquery contains the following clauses SELECT, FROM (optional WHERE, GROUP BY, HAVING)

*

INNER - returns data present in both tables OUTERLEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN CROSS - joins everything to everything (multiplies); same effect as when we don't join just list the tables in the FROM clause

*

Recall that an output of a query looks like a table: it has columns (list of items you put to the SELECT clause it has rows - data that met your criteria in the FROM, WHERE, GROUP BY, and HAVING clauses Additional hints: A subquery in the FROM clause is a temporary table. It must have a field that acts as a unique identified if you need to join this subquery/temporary table with a physical table. The subquery in the FROM clause must be given a name - alias - as an identifier for the primary/outer query.

*

The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. Note: This function performs a case-insensitive search. SYNTAX: CHARINDEX(substring, string, start) ParameterDescriptionsubstringRequired. The substring to search forstringRequired. The string to be searchedstartOptional. The position where the search will start (if you do not want to start at the beginning of string). The first position in string is 1

*

The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

*

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character

*

The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. SYNTAX: REPLACE(string, old_string, new_string) The LEFT() function extracts a number of characters from a string (starting from left). SYNTAX: LEFT(string, number_of_chars)

*

The UPPER() function converts a string to upper-case. SYNTAX: UPPER(text) The LOWER() function converts a string to lower-case. SYNTAX: LOWER(text)

*

Which of the following SQL Server data types would probably be the best choice for a field containing last names of famous economists?

varchar(30)

The SELECT statement has 6 caluses. WHich clauses are required for every SELECT statement? · SELECT · FROM · ORDER BY · WHERE

· SELECT · FROM

According to the default expenditure cycle ERD, an instance of POLine can relate to many instances of PReqLine. This means...

An instance of purchase order line can include lines from several different purchase requisitions.

Which of the following terms represents a customer receiving a 5% discount if paying within 12 days of receiving an invoice, while invoices are considered due within 80 days of receipt?

5/12Net80

According to the traditional view of denormalization, which of the following would contribute to the argument in favor of denormalizing a given field?

95% of records have the same value for the field.

Which of the following most clearly describes an example of a transitive dependency?

A foreign key in a table predicts the value of another foreign key in the same table.

Which of the following is true regarding inputs, processes, and outputs?

A vendor's invoice is an input, a SQL query is a process, and a sales invoice is an output.

Which aggregate function would you use (in the SELECT clause) if you wanted to know the total number of magicians in the Magician table who are from Albuquerque?

COUNT

A primary key that consists of more than one attribute is called a:

Composite key

The total quiz points for a student for an entire semester would most likely be an example of a(n) ________ attribute

Derived

The objective of good physical database design is...

Efficient data processing.

Which of the following attributes, if included in an ERD entity, would least likely need to be normalized when transitioning from conceptual design (ERD) to logical design (relations)? (None are primary or foreign keys.)

EnrollmentDate

Customers, cars, and parts would most likely be examples of which one of the following?

Entities

Which one of the following statements is true?

Final grades are based on weighted categories (as opposed to total points).

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:

Foreign key

Given the ERD above, what should go to the GROUP BY clause for the following query to run properly? What is the average price of shoes per manufacturer? SELECT ManufacturerName AS Manufacturer, AVG(Price) AS Average_Price FROM SimplifiedSales

GROUP BY ManufacturerName

Which of the following clauses is NOT an example of a query-based data manipulation that setting a correct data type enables?

GROUP BY WizardType

What answer best reflects what it means to have a blended class?

Half of the class will attend class one day, while the other half attends class online. The next class day, the half of the class that previously attended online will come to the classroom.

The relationship between a weak entity type and its owner is a form of which one of the following?

Identifying relationship

Which of the following contributes to a student's final grade?

Implementing a functional database.

Which of the following describes a case of a partial functional dependency?

In an associative entity with a composite primary key, one or more attributes can be predicted by just one part of the primary key.

Which documents are matched in the three-way matching process?

Invoice, Receiving Report, Purchase Order

Recording each customer's assigned credit limit in the database...

Is a form of control.

An attribute included in the super-type...

Is inherited by each of the sub-types.

A student can attend five classes, each with a different professor. Each professor has 30 students. The relationship of students to professors is a ________ relationship.

Many-to-many

Which of the following negative outcomes can result from a lack of atomicity?

May not be able to SORT BY a meaningful data point (e.g., a last name).

Which of the following would be considered a "threat" to an organization? (Mark any/all that apply.)

Meteor striking the production facility Paying the same invoice multiple times Inconsistent quality of manufactured products

According to the emergent view of denormalization, why is denormalization of primary data stores no longer as useful as it once was?

Moore's Law

The default expenditure cycle ERD shows an optional-one cardinality in the relationship between Employee and PReqLine. This is important, because...

Otherwise a PReqLine instance could not be stored in the database before having been approved by purchasing.

The default expenditure cycle ERD shows an optional-one cardinality in the relationship between DeliveryIn and PaymentOut. Why is this important?

Otherwise there could be no record of a delivery prior to the company having paid for the delivery.

If I... Had a super-type of Personnel with sub-types Volunteer, Donor, and Employee; Was okay with having people in my Personnel table about whom I wasn't sure their role in the organization; and Was okay with, employees volunteering for events, volunteers making donations, and donors also being employees. What constraints would I want to place on my super-type/sub-type relationship?

Partial Specialization Rule, Overlap Rule

Which of the following documents is created by a manager who recognizes the need to stock up on inventory of a certain raw material?\

Purchase Requisition

Who is ultimately responsible for vetting and approving vendors?

Purchasing

The disjoint rule and the overlap rule...

Refer to whether an instance can belong to multiple sub-types or not.

Which of the following is an example of denormalization?

Reference table B is referenced only by table A, so B is collapsed into A.

A rule that states that each foreign key value must match a primary key value in the other relation is called the:

Referential integrity constraint

Threat exposure...

Reflects a combination of both the magnitude and probability of a threat.

A two-dimensional table of data is called a:

Relation

If you were writing a query and wanted to show data for all columns returned in the query, which of the following could you use as your SELECT clause?

SELECT *

Based on the syllabus, which of the following is/are true regarding group work? (Mark any/Theall that apply.)

Students will complete peer evaluations for other group members. The average score of peer evaluation of will influence the total points a member receives for the project. The group project requires a class presentation.

Assuming no fields/keys are misnamed, which of the following multiple-table queries would you expect to run without errors?

SELECT Mountain.GPSLat, Mountain.GPSLon, Location.StateNameFROM Mountain, LocationWHERE Mountain.LocID = Location.LocIDORDER BY Location.LocID;

Assuming that the table and fields referenced are valid, which of the following queries have appropriate syntax and would work without error?

SELECT PlayerName, Position, HeightFROM RosterWHERE Height > 63ORDER BY Position;

Question: A subquery can reside in which of the following clauses of the outside (outer) query?

SELECT; FROM; WHERE; HAVING (must be an aggregate function)

Which of the following is not a clause in a SELECT query?

SORT BY

To receive participation points, you must (select all that apply):

Students viewing class online must have their camera turned on. Must indicate on the Attendance for they whether you attended in class or online (within 48 hours after the class ended). Must do all the in class exercises and submit them. Must wear mask to class.

As a part of their regular manufacturing process, a company frequently pre-assembles parts, then stores these pre-made assemblies in inventory until they are used, later, to make finished products. Which of the following is true of the ERD that is needed to model and track this? (Mark any/all that apply.)

There should be a raw materials bill of materials entity with two relationships between itself and the raw materials entity.

In the relationship between <SalesOrderLine> and ShipmentOut, why is the one-side cardinality optional-one rather than mandatory-one?

This allows a sales order to exist without it having been shipped yet.

How will office hours and team communication be handled?

Through Microsoft Teams

What is the purpose of the FROM clause?

To list all the tables where the data will be coming from

What is the purpose of the HAVING clause?

To test a logical test on an aggregate function

Regarding completeness constraints...

Total specialization means each instance of the super-type must belong to at least one sub-type.

When would you want to use an outer JOIN?

When you want to output all records from one table and records from a second table that have matching keys with records from the first.

When joining three tables within the same query, which of the following is true?

You create a "virtual table" that includes matching records from all three tables.

Fred's Slow Mail Order Company sells stuff through the mail. When customers want to buy something, they call a salesperson and place the order. That sales person records the order. At the end of each week, a sales supervisor reviews all the week's sales and approves them (or rejects them). Following approval, all orders are shipped out the next Tuesday. Assuming that all employees involved in the process are represented by records in the Employee table and that sales orders are recorded (and approval/rejection noted) in the SalesOrder entity, what would the cardinality be on the Employee side of the relationship with SalesOrder that represents order approval?

Zero to one

A data warehouse stores which type of data? Select all that apply.

periodic data status data event data