DATABASE MGT
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
A central, all-inclusive data repository is referred to as...
An Enterprise Data Warehouse
Choose the best answer. A report that details, in 30-day increments, the outstanding payments owed to a company is...
An output
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
Which of the following are included in the receiving report? (Mark any/all that apply.)
PO Number Referenced Identity of the Supplier Date of Receipt
The disjoint rule and the overlap rule...
Refer to whether an instance can belong to multiple sub-types or not.
What is a data warehouse? (Select the best answer.)
A central database repository that takes in data from operational databases throughout the organization.
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.
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 primary key that consists of more than one attribute is called a:
Composite key
Once data within a database are normalized...
Fields are atomic and there are no data duplication issues.
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
Which of the following is not an objective when selecting a field's data type?
Provide compatibility between database management systems.
A company includes information about its customers in a database table called Customer. A line within this table includes a customer's name, phone number, address, and date of birth. Each of these lines is called...
Record
Establishing a central, separate data warehouse...
Reduces the probability of large-scale data analyses interfering with day-to-day data-related operations.
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
When there is a relationship between records in two different tables...
The mandatory side donates its key to the many side.
All of the following are noted in the syllabus as subjects to be covered in this class except...
Use of columnar databases.
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
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.
A property or characteristic of an entity type that is of interest to the organization is called a(n):
attribute
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
An attribute that can be broken down into smaller parts is called a(n) ________ attribute
composite
what is the definition of periodic data?
data warehouse never altered or deleted once its added each record creates a time stamp for updated event
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)
What is the definition of Bill of Materials?
it is a part of product design, Part No., Description, Qty. of Ea. Component
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
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
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)
What is an example of unstructured data?
-data that doesn't follow a specific format -data that doesn't currently fit in a structured relational database table -EX) satellite images, photographs, video, radar images, text within word documents, social media content like tweets and FB posts, along content.. etc. -unstructured data files can be stored within a structured database, however, their content still remains unstructured -unstructured data a major source of the "variety" in big data
A relation includes columns that are not fully atomic, and a transitive dependency, but no partial functional dependencies. What normal form is it in?
0 NF
A relation includes columns that are not fully atomic, and a transitive dependency, but no partial functional dependencies. What normal form is it in?
3 NF
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
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 would be most likely to differentiate a table containing periodic data from one containing transient data?
A periodic data table would possibly contain additional columns to indicate when a value changed.
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.
Which of the following is true regarding sub-queries?
A sub-query can create a table that is then joined to other tables.
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.
According to the syllabus, each homework assignment...
Accounts for 2% of the student's overall grade.
An independent data mart...
Addresses the data analysis needs of a particular part of the organization.
An attribute for which an instance's value can only be one or zero is called a(n)...
Binary Attribute
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)?
EnrollmentDate
Which of the following requirements 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 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.
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.
Which documents are matched in the three-way matching process?
Invoice, Receiving Report, Purchase Order
Choose the best answer. The bill of materials...
Is a control for the mis-production of products.
Recording each customer's assigned credit limit in the database...
Is a form of control.
Figure 9-2, a graphic depicting independent data mart data warehousing architecture...
Is confusing as all get out, particularly at first glance.
An attribute included in the super-type...
Is inherited by each of the sub-types.
The MIDDLE JOIN command...
Is not a SQL command.
Enabling different data points to relate to one another...
Is one of the core functions of a database.
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.
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).
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
Which of the following is a factor driving the need for data warehousing?
Need to separate informational and operational data systems for the sake of performance.
Which of the following is a key difference between an operational system and an informational system?
Operational systems are used for day-to-day business activities, informational systems are for data analysis and high-level decision-making.
Which of the following would be considered a "threat" to an organization? (Mark any/all that apply.)
Paying the same invoice multiple times Meteor striking the production facility Inconsistent quality of manufactured products
An attribute (or attributes) that uniquely identifies each row in a relation is called a:
Primary key
When attempting to consolidate data from different operational databases into a central data warehouse, one issue that causes difficulty has to do with primary keys. What is that issue?
Primary key value used across databases might not be the same (e.g., a user id in one table, a social security number in another).
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 group project requires a class presentation.
Based on the syllabus, which of the following is/are true regarding group work? (Mark any/Theall that apply.)
The group project requires a class presentation. Students will complete peer evaluations for other group members.
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.
Which of the following is the best example of "event data" (as opposed to "status data")?
The visit date/time and amount of food eaten by a hummingbird visiting a feeder.
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.
The number of entities that participate in a relationship is called the _____.
degree
The total quiz points for a student for an entire semester would most likely be an example of a(n) ________ attribute
derived
Customers, cars, and parts would most likely be examples of which one of the following?
entities
An entity is always a weak entity if the following is true:
I. It has a unique primary key (rule #3) II. It is on the one side of a one to many relationship. (course/section relationship, weak entity is on the many side here- so throw it out)
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 is/are true with regard to exams? (Mark all that apply.)
More than half of a student's grade is dependent on exam scores.
Regarding completeness constraints...
Total specialization means each instance of the super-type must belong to at least one sub-type.
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
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 in the output for which the value of UniformNumber is 1, 3, 5, or 7?
WHERE UniformNumber IN (1, 3, 5, 7)
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?
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?
what is the definition of Transient Data?
operational systems changes to existing records are written over previous records records are deleted without preserving previous content
What is the purpose of the Expenditure cycle?
to minimize total cost of acquiring and maintaining : inventories, supplies, and other necessary services. you use this whoever you're spending money
In the conceptual design context, the common types of entities are... (Select any/all that apply.)
weak entities strong entities associative entities
An entity type whose existence depends on another entity type is called a ________ entity
weak entity
What is the index function and how is it used?
you tell the DBMS to index any column Don't index every field (it slows everything down). When you update anything, all the indexes have to be updated. That's why When choosing indexes, select fields that frequently appear in the WHERE clause.
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
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
In a company's database, a given table...
Can include millions of records.
A ________ specifies the number of instances of one entity that can be associated with each instance of another entity
Cardinality constraint
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.
Who is ultimately responsible for vetting and approving vendors?
Purchasing
A two-dimensional table of data is called a:
Relation
1. In which case would you identify a field as an index- indexing creates extra tables field when creating a data dictionary?
The field is often part of the WHERE statement in SQL queries
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.
Comparing data types between SQL Server and MySQL...
TINYINT in MySQL can serve the same purpose as bit in SQL Server
A relation includes columns that are not fully atomic and a transitive dependency, but no partial functional dependencies. What normal form is it in?
0 NF
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
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 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
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 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.
It would make sense to consider creating a super-type/sub-type relationship when...
An entity's instances share some, but not all, attributes.
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.
What data type category would you use to store a photo for each record in a table called Customer?
Binary Object
A STUDENT takes a CLASS is an example of what type of relationship?
Binary- two entities here
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
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 is true regarding SELECT query clauses?
If there's not a GROUP BY clause, there shouldn't be a HAVING clause.
Which of the following contributes to a student's final grade?
Implementing a functional database.
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.
A company includes information about its customers in a database table called Customer. Each line within this table includes a customer's name, phone number, address, and date of birth. These lines are called...
Records
Threat exposure...
Reflects a combination of both the magnitude and probability of a threat.
Databases... (Select all/any that apply.)
Rely on primary and foreign keys to relate records from different tables.
A "primary key" is...
Required for each record.
Which of the following SELECT clauses would you use if you wanted to show the average temperature (from the AvgTemp field) for each city in the City table, but with an additional 5% added to the average temperature to account for projected global warming? The correct answer should also rename the column of calculated outputs "AdjTemp".
SELECT AvgTemp * 1.05 AS [AdjTemp]
Which of the following is not a clause in a SELECT query?
SORT BY
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
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.
1. A type of accounts receivable system that permits a customer payment on an account balance is called a(n) __________ system.
a. Balance-forward - like ur credit card, you can pay later, you do not pay as you go
Which of the following is not a potential advantage of the database approach?
a. Minimal data redundancy. b. Reduced program maintenance. c. Improved sharing of data. d. Data independence. answer= e. None of the above (all ARE advantages).
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?
a. SELECT *
If you were writing a query and wanted to show data for all columns returned in the query, which would you use for your SELECT clause?
a. SELECT *
