IS 300 Exam 1

A small retailer can coordinate overseas suppliers at lower cost using shared systems. What opportunity and challenge occur together?

Access to wider markets with increased competitive pressure

An inventory system reduces the time required to reorder supplies. Which objective is most directly supported?

Operational excellence

A publisher replaces printed-issue sales with a digital subscription service. Which objective best fits?

New products, services, and business models

A hotel remembers room preferences to serve returning guests. Which objective best fits?

Customer intimacy

A manager uses current sales and inventory data to set reorder quantities. What is the main benefit?

Improved decision making

A firm installs a system because it cannot reliably meet a required filing obligation by hand. Which driver fits?

Survival

How can a system tracking facility energy use support ESG goals?

Measure progress toward environmental targets

Which combination best represents IT infrastructure?

Hardware, software, data management, networks, and technology services

What defines the server role in client/server computing?

Providing services or resources requested by clients

Which workload best matches a supercomputer?

Highly intensive scientific simulations

What is the idea behind grid computing?

Coordinate resources from multiple computers for a task

Why distinguish application-server and database-server roles?

Business logic and data management have different responsibilities

A browser calls an application service, which requests records from a database service. Which interpretation best fits?

A multi-tier design separating interface, business rules, and data services

Which item is secondary storage?

An SSD retaining saved files

A scanner reads a barcode and a display shows the price. How are these classified?

Scanner: input; display: output

Employees use personal phones for work applications. Which trend fits?

BYOD and consumerization of IT

One physical computer hosts several isolated virtual machines. What does this illustrate?

Virtualization

A company rents virtual machines and storage and manages the OS itself. Which model fits?

IaaS

A team deploys its own code to a managed platform without maintaining the underlying OS. Which model fits?

PaaS

Employees subscribe to a provider-hosted expense application. Which model fits?

SaaS

A company combines private cloud resources with public cloud resources. Which model fits?

Hybrid cloud

Which is a balanced way to evaluate moving an app to the cloud?

Consider flexibility, recurring cost, connectivity, and responsibilities

Which action supports green computing?

Reduce unnecessary energy use and manage equipment disposal

Which task belongs primarily to an operating system?

Manage resources and support application execution

Which pairing correctly distinguishes software roles?

An OS manages resources; a spreadsheet performs user calculations

Which pairing is accurate?

Spreadsheet: application software; HTML: web-page markup

A travel website combines reservations with an external map service. Which idea fits?

Services exchange information through defined interfaces

Which statement about open-source software is accurate?

Its license permits specified uses and modifications of available source code

A registrar estimates peak registration users; the system can add resources as demand grows. Which pairing fits

Estimation: capacity planning; growth capability: scalability

Over three years, option A costs $4,000 to buy plus $2,000 each year to support. Option B costs $7,000 plus $500 each year. Other costs are equal. Which has lower TCO?

B, at $8,500 compared with $10,000

Which approach best uses total cost of ownership?

Count purchase, support, maintenance, training, and downtime costs

A company evaluates an external provider for several countries. Which review is most complete?

Service commitments, switching effort, and language/process adaptation

What is the purpose of a service level agreement with a provider?

Define agreed service expectations

A company issues phones that may be lost or stolen. Which planning item best addresses this risk?

Device-management and security procedures

A museum stores an artifact ID, acquisition date, and photograph. Which statement is accurate?

ID/date are structured fields; the photograph is unstructured content

A catalog defines acquisition_date as a date and explains its meaning. What is this description?

Metadata

Departments keep separate supplier addresses and disagree about the current address. What is illustrated?

Duplication leading to inconsistency

Programs must be rewritten whenever a file layout changes. Which issue fits?

Program-data dependence

How do enterprise and project data models differ?

Enterprise models provide a broad view; project models add detail for a particular effort

Which statement balances database benefits and costs?

Shared consistent data can help, but staffing, migration, and recovery need resources

What does one row in a MuseumArtifact table normally represent?

One artifact record

Which key best identifies individual equipment assets?

A unique required asset_id

Repair.asset_id references Asset.asset_id. What is its role?

Link a repair to the asset being repaired

An author writes many articles; each article has exactly one author in this model. What is the maximum relationship?

One-to-many from Author to Article

A vehicle may have zero or many inspections; each inspection belongs to exactly one vehicle. May a new vehicle have no inspection?

Yes, the minimum number of inspections is zero

Students take many courses and courses have many students. Where does the grade for one enrollment belong?

On an Enrollment association

A repair references an asset ID that does not exist. Which rule does an enforced foreign key reject?

A violation of referential integrity

Departments see different views of the same employee database. Which capability does this illustrate?

Logical views separated from physical storage

A report keeps only repairs completed in September. Which relational operation filters these rows?

Selection

A report displays only asset_name and purchase_date from Asset. Which operation chooses the fields?

Projection

A report pairs shipments with carrier names by matching carrier IDs. Which operation is needed?

Join

Which task is data definition?

Create a table and specify column types

Why consider a document database for records with varying optional fields?

Its flexible document model may fit the workload

A company adopts a managed cloud database. What still needs its attention?

Access rules and data accuracy

Which feature distinguishes a blockchain-style transaction ledger in the slides?

Linked blocks replicated across participants

A service receives different formats of rapidly arriving records. Which two big-data dimensions are described?

Variety and velocity

A firm integrates historical data from several operational systems for enterprise reporting. What repository fits?

Data warehouse

Which comparison of data marts and lakes is accurate?

A mart focuses on a subject/group; a lake can retain raw data in varied formats

What is the basic role of Hadoop-style distributed processing?

Divide large data work across computing nodes

Why can in-memory computing shorten analytical query response times?

It reduces repeated retrieval from slower storage

What is an analytical platform designed to support?

Query processing and analysis of large datasets

An analyst examines revenue by year, region, and product line. Which tool concept fits?

OLAP

A manager asks for sales by product and quarter; another analyst searches for previously unknown purchase patterns. Which pairing fits?

First is OLAP; second is data mining

Customers often buy batteries and flashlights in the same purchase. What mining result is this?

Association

An analyst groups unlabeled customers by similar shopping behavior. Which task is this?

Clustering

A model assigns applications to established low-, medium-, or high-risk categories. Which task is illustrated?

Classification

An analyst uses monthly sales history to estimate next month's sales. Which task is illustrated?

Forecasting

Customers often buy a camera and then buy a lens in a later month. Which pattern specifically emphasizes this ordering?

Sequence

A business analyzes review text to estimate positive and negative opinions. Which task fits?

Sentiment analysis through text mining

A team analyzes visitors' click paths through its website. Which mining type fits?

Web usage mining

An analyst examines which pages link to a product page, without using visitor logs. Which type of web mining fits?

Web structure mining

Which component ordinarily hosts the DBMS for a web application?

Database server

A firm defines product-record owners, editing permissions, and code-approval procedures. What is this?

Data governance

How do a data-quality audit and cleansing differ?

An audit assesses quality; cleansing corrects identified problems

A CUSTOMER table has the columns customer_id, name, and email, and it contains 350 rows. What does one row represent?

One specific customer, described by that row's values for customer_id, name, and email.

Two customers share the name "Alex Rivera", and customers sometimes change their email address and phone number. Which attribute is the best primary key for CUSTOMER?

 

customer_id, an assigned number that is unique and never changes

A seller may create many listings, and every listing has exactly one seller. In the relational design, where does the foreign key go?

On LISTING: each listing row stores the seller_id of its seller.

A student may join zero or many clubs, and a club may have zero or many students. You must record the date each student joined each club. Which design is correct?

Add an associative entity MEMBERSHIP with primary key (student_id, club_id) and the attribute joined_on.

A model says: "For one Podcast: Episode (1,N). For one Episode: Podcast (1,1)." Which statement is correct?

Every podcast has at least one episode and may have many; every episode belongs to exactly one podcast.

Current assignments only: a student may have no locker or one locker, and a locker may be empty or assigned to one student. Which symbols belong at the LOCKER end of the line between STUDENT and LOCKER?

A circle and a bar (zero or one).

Each creator numbers their own portfolio projects 1, 2, 3, ... so two creators can both have a project 1. Which primary key works for PROJECT?

(creator_id, project_number) together.

PRODUCT stores current_price. Why does ORDER_LINE also store unit_sale_price for each product sold?

So a later change to current_price does not alter what past orders show was paid.

In the PacoDogShop model, what do the symbols on the Contains line say about orders and order lines?

Model description: PacoDogShop order model, crow's-foot notation. CUSTOMER (customer_id PK, name, email). ORDER (order_id PK, ordered_at, customer_id FK). ORDER_LINE (order_id PK/FK, product_id PK/FK, quantity, unit_sale_price_cents). PRODUCT (product_id PK, name, current_price_cents). Relationship Places between CUSTOMER and ORDER: at the ORDER end a circle and a crow's foot labeled 0..N; at the CUSTOMER end two bars labeled 1..1. Relationship Contains between ORDER and ORDER_LINE: at the ORDER_LINE end a bar and a crow's foot labeled 1..N; at the ORDER end two bars labeled 1..1. Relationship Refers to between PRODUCT and ORDER_LINE: at the ORDER_LINE end a circle and a crow's foot labeled 0..N; at the PRODUCT end two bars labeled 1..1.

Every order has at least one order line and may have many; every order line belongs to exactly one order.

In the PacoDogShop model, which statement about the keys of ORDER_LINE is correct?

Model description: PacoDogShop order model, crow's-foot notation. CUSTOMER (customer_id PK, name, email). ORDER (order_id PK, ordered_at, customer_id FK). ORDER_LINE (order_id PK/FK, product_id PK/FK, quantity, unit_sale_price_cents). PRODUCT (product_id PK, name, current_price_cents). Relationship Places between CUSTOMER and ORDER: at the ORDER end a circle and a crow's foot labeled 0..N; at the CUSTOMER end two bars labeled 1..1. Relationship Contains between ORDER and ORDER_LINE: at the ORDER_LINE end a bar and a crow's foot labeled 1..N; at the ORDER end two bars labeled 1..1. Relationship Refers to between PRODUCT and ORDER_LINE: at the ORDER_LINE end a circle and a crow's foot labeled 0..N; at the PRODUCT end two bars labeled 1..1.

order_id and product_id are both foreign keys, and together they form the primary key.

Which of these situations VIOLATES the PacoDogShop model as drawn?

Model description: PacoDogShop order model, crow's-foot notation. CUSTOMER (customer_id PK, name, email). ORDER (order_id PK, ordered_at, customer_id FK). ORDER_LINE (order_id PK/FK, product_id PK/FK, quantity, unit_sale_price_cents). PRODUCT (product_id PK, name, current_price_cents). Relationship Places between CUSTOMER and ORDER: at the ORDER end a circle and a crow's foot labeled 0..N; at the CUSTOMER end two bars labeled 1..1. Relationship Contains between ORDER and ORDER_LINE: at the ORDER_LINE end a bar and a crow's foot labeled 1..N; at the ORDER end two bars labeled 1..1. Relationship Refers to between PRODUCT and ORDER_LINE: at the ORDER_LINE end a circle and a crow's foot labeled 0..N; at the PRODUCT end two bars labeled 1..1.

Order 501 has two ORDER_LINE rows for product P7 (quantity 2 and quantity 1).

Which pair of entities in the PacoDogShop model has a many-to-many relationship, and what resolves it?

Model description: PacoDogShop order model, crow's-foot notation. CUSTOMER (customer_id PK, name, email). ORDER (order_id PK, ordered_at, customer_id FK). ORDER_LINE (order_id PK/FK, product_id PK/FK, quantity, unit_sale_price_cents). PRODUCT (product_id PK, name, current_price_cents). Relationship Places between CUSTOMER and ORDER: at the ORDER end a circle and a crow's foot labeled 0..N; at the CUSTOMER end two bars labeled 1..1. Relationship Contains between ORDER and ORDER_LINE: at the ORDER_LINE end a bar and a crow's foot labeled 1..N; at the ORDER end two bars labeled 1..1. Relationship Refers to between PRODUCT and ORDER_LINE: at the ORDER_LINE end a circle and a crow's foot labeled 0..N; at the PRODUCT end two bars labeled 1..1.

ORDER and PRODUCT; ORDER_LINE resolves it.

In the Classic Models fragment, CUSTOMERS.salesRepEmployeeNumber is a foreign key and the EMPLOYEES end of that line shows 0..1. What does the 0 permit?

Model description: Classic Models database fragment, crow's-foot notation, six tables. OFFICES (officeCode PK, city, country, territory). EMPLOYEES (employeeNumber PK, lastName, firstName, jobTitle, officeCode FK). CUSTOMERS (customerNumber PK, customerName, city, country, salesRepEmployeeNumber FK). ORDERS (orderNumber PK, orderDate, status, customerNumber FK). ORDERDETAILS (orderNumber PK/FK, productCode PK/FK, quantityOrdered, priceEach). PRODUCTS (productCode PK, productName, productLine, buyPrice, MSRP). Lines: OFFICES to EMPLOYEES 1..1 at OFFICES, 0..N at EMPLOYEES. EMPLOYEES to CUSTOMERS 0..1 at EMPLOYEES, 0..N at CUSTOMERS. CUSTOMERS to ORDERS 1..1 at CUSTOMERS, 0..N at ORDERS. ORDERS to ORDERDETAILS 1..1 at ORDERS, 1..N at ORDERDETAILS. PRODUCTS to ORDERDETAILS 1..1 at PRODUCTS, 0..N at ORDERDETAILS.

A customer with no sales rep assigned (the FK is NULL).

Every employee works in exactly one office and an office may have zero or many employees. According to the fragment, which table stores the officeCode foreign key?

Model description: Classic Models database fragment, crow's-foot notation, six tables. OFFICES (officeCode PK, city, country, territory). EMPLOYEES (employeeNumber PK, lastName, firstName, jobTitle, officeCode FK). CUSTOMERS (customerNumber PK, customerName, city, country, salesRepEmployeeNumber FK). ORDERS (orderNumber PK, orderDate, status, customerNumber FK). ORDERDETAILS (orderNumber PK/FK, productCode PK/FK, quantityOrdered, priceEach). PRODUCTS (productCode PK, productName, productLine, buyPrice, MSRP). Lines: OFFICES to EMPLOYEES 1..1 at OFFICES, 0..N at EMPLOYEES. EMPLOYEES to CUSTOMERS 0..1 at EMPLOYEES, 0..N at CUSTOMERS. CUSTOMERS to ORDERS 1..1 at CUSTOMERS, 0..N at ORDERS. ORDERS to ORDERDETAILS 1..1 at ORDERS, 1..N at ORDERDETAILS. PRODUCTS to ORDERDETAILS 1..1 at PRODUCTS, 0..N at ORDERDETAILS.

EMPLOYEES — each employee row records the officeCode of its office.

Which column connects CUSTOMERS and ORDERS in the fragment, and what is its role in each table?

Model description: Classic Models database fragment, crow's-foot notation, six tables. OFFICES (officeCode PK, city, country, territory). EMPLOYEES (employeeNumber PK, lastName, firstName, jobTitle, officeCode FK). CUSTOMERS (customerNumber PK, customerName, city, country, salesRepEmployeeNumber FK). ORDERS (orderNumber PK, orderDate, status, customerNumber FK). ORDERDETAILS (orderNumber PK/FK, productCode PK/FK, quantityOrdered, priceEach). PRODUCTS (productCode PK, productName, productLine, buyPrice, MSRP). Lines: OFFICES to EMPLOYEES 1..1 at OFFICES, 0..N at EMPLOYEES. EMPLOYEES to CUSTOMERS 0..1 at EMPLOYEES, 0..N at CUSTOMERS. CUSTOMERS to ORDERS 1..1 at CUSTOMERS, 0..N at ORDERS. ORDERS to ORDERDETAILS 1..1 at ORDERS, 1..N at ORDERDETAILS. PRODUCTS to ORDERDETAILS 1..1 at PRODUCTS, 0..N at ORDERDETAILS.

customerNumber — primary key in CUSTOMERS, foreign key in ORDERS.

On the Has line between ORDERS and ORDERDETAILS, the ORDERDETAILS end shows a bar and a crow's foot (1..N). Which situation do those two symbols forbid?

Model description: Classic Models database fragment, crow's-foot notation, six tables. OFFICES (officeCode PK, city, country, territory). EMPLOYEES (employeeNumber PK, lastName, firstName, jobTitle, officeCode FK). CUSTOMERS (customerNumber PK, customerName, city, country, salesRepEmployeeNumber FK). ORDERS (orderNumber PK, orderDate, status, customerNumber FK). ORDERDETAILS (orderNumber PK/FK, productCode PK/FK, quantityOrdered, priceEach). PRODUCTS (productCode PK, productName, productLine, buyPrice, MSRP). Lines: OFFICES to EMPLOYEES 1..1 at OFFICES, 0..N at EMPLOYEES. EMPLOYEES to CUSTOMERS 0..1 at EMPLOYEES, 0..N at CUSTOMERS. CUSTOMERS to ORDERS 1..1 at CUSTOMERS, 0..N at ORDERS. ORDERS to ORDERDETAILS 1..1 at ORDERS, 1..N at ORDERDETAILS. PRODUCTS to ORDERDETAILS 1..1 at PRODUCTS, 0..N at ORDERDETAILS.

An order that has no order-detail lines at all.

What is the difference between the WHERE clause and the HAVING clause?

WHERE filters individual rows before grouping; HAVING filters the groups.

We write SELECT first, but in what order does the database actually process a query with all six clauses?

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

In the query SELECT * FROM products, what does the asterisk mean?

All columns of the table(s) named in FROM.

In MySQL, SELECT productName, MSRP - buyPrice AS Markup FROM products WHERE Markup > 50; stops with the error "Unknown column 'Markup' in 'where clause'". Why?

WHERE runs before SELECT, so the alias Markup does not exist yet.

Using the offices table, what does this query return?

SELECT city FROM offices WHERE country = 'USA';

Table: offices

officeCode	city	country	territory
1	San Francisco	USA	NA
2	Boston	USA	NA
3	NYC	USA	NA
4	Paris	France	EMEA
5	Tokyo	Japan	Japan
6	Sydney	Australia	APAC
7	London	UK	EMEA

Three rows: San Francisco, Boston, NYC.

Using the offices table, what does this query return?

SELECT COUNT(*) FROM offices WHERE territory = 'EMEA';

Table: offices

officeCode	city	country	territory
1	San Francisco	USA	NA
2	Boston	USA	NA
3	NYC	USA	NA
4	Paris	France	EMEA
5	Tokyo	Japan	Japan
6	Sydney	Australia	APAC
7	London	UK	EMEA

 

A single value: 2

Using the offices table, how many rows does this query return?

SELECT country, COUNT(*) AS office_count FROM offices GROUP BY country;

Table: offices

officeCode	city	country	territory
1	San Francisco	USA	NA
2	Boston	USA	NA
3	NYC	USA	NA
4	Paris	France	EMEA
5	Tokyo	Japan	Japan
6	Sydney	Australia	APAC
7	London	UK	EMEA

5 rows — one per distinct country.

Using the offices table, what does this query return?

SELECT country, COUNT(*) AS office_count FROM offices GROUP BY country HAVING COUNT(*) > 1;

Table: offices

officeCode	city	country	territory
1	San Francisco	USA	NA
2	Boston	USA	NA
3	NYC	USA	NA
4	Paris	France	EMEA
5	Tokyo	Japan	Japan
6	Sydney	Australia	APAC
7	London	UK	EMEA

One row: USA, 3

Using the offices table, which city appears in the FIRST row of this result?

SELECT city FROM offices ORDER BY city DESC;

Table: offices

officeCode	city	country	territory
1	San Francisco	USA	NA
2	Boston	USA	NA
3	NYC	USA	NA
4	Paris	France	EMEA
5	Tokyo	Japan	Japan
6	Sydney	Australia	APAC
7	London	UK	EMEA

Tokyo

Using the employees table, what does this query return?

SELECT lastName, firstName FROM employees WHERE officeCode = '1' AND jobTitle = 'Sales Rep';

Table: employees

employeeNumber	lastName	firstName	officeCode	jobTitle
1002	Murphy	Diane	1	President
1056	Patterson	Mary	1	VP Sales
1102	Bondur	Gerard	4	Sales Manager (EMEA)
1165	Jennings	Leslie	1	Sales Rep
1337	Bondur	Loui	4	Sales Rep
1621	Nishi	Mami	5	Sales Rep

One row: Jennings, Leslie