database multiple questions

profileedions

this is about databases questions , maybe i miss copy some option D, if ABC there are all incorrecct please type D after that question thank you 

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a INTERSECT query based on these two tables?

[removed]

a. The query output will be: 125,124,123,126

 

[removed]

b. The query output will be: 123

 

[removed]

c. The query output will be: 125,124,124,123,123,124,125,126

 

[removed]

d. The query output will be: 123,124,125

 

 

What is the difference between UNION and UNION ALL?

[removed]

a. A UNION ALL operator will yield all rows of both relations, including duplicates

 

[removed]

b. UNION yields unique rows

 

[removed]

c. UNION eliminates duplicates rows

 

[removed]

d. All of these choices are correct.

A(n) ______________ is a block of PL/SQL code that is automatically invoked by the DBMS upon the occurrence of a data manipulation event (INSERT, UPDATE or DELETE.)

[removed]

a. stored procedure

 

[removed]

b. trigger

 

[removed]

c. view

 

[removed]

d. function

 

__________________ means that the relations yield attributes with identical names and compatible data types.

[removed]

a. duplicated

 

[removed]

b. Set comparable

 

[removed]

c. Union compatible

 

[removed]

d. compatible-oriented



Which of the following a parts of the definition of a trigger?

[removed]

a. The triggering level

 

[removed]

b. The triggering action

 

[removed]

c. The triggering timing

 

[removed]

d. All of these choices are correct.

Which of the following relational set operators does NOT require that the relations are union-compatible?

[removed]

a. INTERSECT

 

[removed]

b. PROJECT

 

[removed]

c. MINUS

 

[removed]

d. UNION

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the INTERSECT query?

[removed]

a. The query output will be: John Cretchakov and Mary Chen

 

[removed]

b. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen

 

[removed]

c. The query output will be: John Cretchakov

 

[removed]

d. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen

 

A _____________________ is a join that performs a relational product (or Cartesian product) of two tables.

[removed]

a. CROSS JOIN

 

[removed]

b. DUPLICATE JOIN

 

[removed]

c. OUTER JOIN

 

[removed]

d. INNER JOIN

What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?

[removed]

a. SELECT SYSDATE()-#25-JAN-1999#;

 

[removed]

b. SELECT SYSDATE – TO_DATE('25-JAN-1999', 'DD-MON-YYYY') FROM DUAL;

 

[removed]

c. SELECT SYSDATE FROM DUAL;

 

[removed]

d. SELECT SYSDATE()-JAN-#25-1999#;

Using tables named T1 and T2, write a query example for a LEFT OUTER JOIN, assuming that T1 and T2 share a common column named C1.

[removed]

a. SELECT * FROM T1 OUTER JOIN T2 ON T1.C1 = T2.C2;

 

[removed]

b. SELECT * FROM T1 JOIN T2 ON T2.C1 = T2.C1;

 

[removed]

c. SELECT * FROM T1 LEFT JOIN T2 ON T1.C1 = T2.C1;

 

[removed]

d. SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1;

 

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the MINUS query (specifically, SELECT * FROM EMPLOYEE MINUS SELECT * FROM EMPLOYEE_1)?

[removed]

a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen

 

[removed]

b. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen

 

[removed]

c. The query output will be: Mary Chen

 

[removed]

d. The query output will be: Alice Cordoza and Anne McDonald

What Oracle function should you use to return the current date?

[removed]

a. TODAY

 

[removed]

b. SYSDATE

 

[removed]

c. NOW

 
 

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a UNION ALL query based on these two tables?

[removed]

a. The query output will be: 125,124,124,123,123,124,125,126

 

[removed]

b. The query output will be: 125,124,124,123

 

[removed]

c. The query output will be: 125,124,123,126

 

[removed]

d. The query output will be: 123

_________________is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion.

[removed]

a. NoSQL

 

[removed]

b. Embedded SQL

 

[removed]

c. Dynamic SQL

 
 

The order of the operands (tables) matter in a _______ query.

[removed]

a. PROJECT

 

[removed]

b. MINUS

 

[removed]

c. UNION

 

[removed]

d. INTERSECT

Which of the following is true of Oracle sequences?

[removed]

a. You can have as many sequences as you want and they are not tied to any particular table.

 

[removed]

b. A sequence is a completely independent object.

 

[removed]

c. The sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.

 
 

Which of the following is true of Oracle sequences?

[removed]

a. You can have as many sequences as you want and they are not tied to any particular table.

 

[removed]

b. A sequence is a completely independent object.

 

[removed]

c. The sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.

 

[removed]

d. All of these choices are correct.

A(n) ______________ is a named collection of procedural and SQL statements that are stored in the database and that can be used to encapsulate and represent business transactions.

[removed]

a. trigger

 

[removed]

b. stored procedure

 

[removed]

c. view

 
 

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query?

[removed]

a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen

 

[removed]

b. The query output will be: John Cretchakov and Mary Chen

 

[removed]

c. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen

 

[removed]

d. The query output will be: John Cretchakov



A subquery can appear in which of the following places in a SQL statement?

[removed]

a. as part of a FROM clause,

 

[removed]

b. to the right of a HAVING clause conditional operator,

 

[removed]

c. in a EXISTS operator,

 

[removed]

d. All of these choices are correct.

A __________ is a query (expressed as a SELECT statement) that is located inside another query and is normally executed first.

[removed]

a. multi-query

 

[removed]

b. subquery

 

[removed]

c. outside query

 

[removed]

d. special query

What string function (in Oracle) should you use to list the first three characters of a company's EMP_LNAME values using a table named EMPLOYEE?

[removed]

a. SELECT STR(EMP_LNAME, 1, 3) FROM EMPLOYEE;

 

[removed]

b. SELECT STRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;

 

[removed]

c. SELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;

 

[removed]

d. SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE;

 

_____________ is a term used to describe an environment in which the SQL statement is not known in advance; instead, the SQL statement is generated at run time.

[removed]

a. PL/SQL

 

[removed]

b. Embedded SQL

 

[removed]

c. Dynamic SQL

 

[removed]

d. NoSQL

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a MINUS query (VENDOR MINUS PRODUCT) based on these two tables?

[removed]

a. The query output will be: 124

 

[removed]

b. The query output will be: 125,124,123,126

 

[removed]

c. The query output will be: NULL

 

[removed]

d. The query output will be: 126



In the relational model, SQL operators are ________________ because they operate over entire sets of rows and columns (or tables) at once.

[removed]

a. set-oriented

 

[removed]

b. table-oriented

 

[removed]

c. completeness-oriented

 
 

Which of the followings explains the difference between a regular subquery and a correlated subquery?

[removed]

a. A correlated subquery will execute once for each row evaluated by the outer query

 

[removed]

b. A regular subquery executes only once and the result is held for use by the outer query

 

[removed]

c. A regular subquery executes before the outer query

 

[removed]

d. All of these choices are correct.

If you do not specify a join condition when joining tables, the result will be a ______________ or PRODUCT operation.

[removed]

a. CROSS JOIN

 

[removed]

b. DUPLICATE JOIN

 

[removed]

c. INNER JOIN

 

[removed]

d. OUTER JOIN

A ______________ is a subquery that executes once for each row in the outer query; it will run the outer query first, and then it will run the inner subquery once for each row returned in the outer subquery.

[removed]

a. outside subquery

 

[removed]

b. uncorrelated subquery

 

[removed]

c. correlated subquery

 
 

A relational view has which of the following characteristics?

[removed]

a. views are dynamically updated

 

[removed]

b. views may be used as the basis for reports or queries, just like tables

 

[removed]

c. views provide a level of security in the database

 
 

A(n) ______________ is a special type of object that generates unique numeric values in ascending or descending order; it can be used to assign values to a primary key field in a table and it provides functionality similar to the Autonumber data type in MS Access.

[removed]

a. sequence

 

[removed]

b. key

 

[removed]

c. instance

 

[removed]

d. view



which of the following is NOT considered an advantage of a stored procedure?

[removed]

a. Increase performance

 

[removed]

b. Help reduce code duplication through code isolation and code sharing

 

[removed]

c. Reduce network traffic

 

[removed]

d. None of these choices are correct.

What are the types of results a subquery can return?

[removed]

a. a list of values only

 

[removed]

b. a single value, a list of values, or a virtual table

 

[removed]

c. a single value only

 

[removed]

d. a virtual table only



The SQL standard prescribes three different types of __________ operations: LEFT, RIGHT, and FULL.

[removed]

a. OUTER join

 

[removed]

b. INNER join

 

[removed]

c. DUPLICATE join

 
 

A(n) __________________ is a type of JOIN operation that yields all rows with matching values in the join columns as well as all unmatched rows ( those without matching values in the join columns).

[removed]

a. INNER JOIN

 

[removed]

b. OUTER JOIN

 

[removed]

c. CROSS JOIN

 

[removed]

d. DUPLICATE JOIN

Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for a UNION query based on these two tables?

[removed]

a. The query output will be: 123,124,125,126

 

[removed]

b. The query output will be: 125,124,124,123

 

[removed]

c. The query output will be: 125,124,123,126

 

[removed]

d. The query output will be: 123

Triggers are critical to proper database operation and management in which of the following ways?

[removed]

a. add functionality by automating critical actions and providing appropriate warnings for remedial action.

 

[removed]

b. used to enforce constraints that cannot be enforced at the DBMS design and implementation levels.

 

[removed]

c. can be used to enforce referential integrity, to update table values, insert records in tables, and call other stored procedures.

 

[removed]

d. All of these choices are correct.



A(n) ________ is a virtual table based on a SELECT query.

[removed]

a. view

 

[removed]

b. copy

 

[removed]

c. schema

 
 

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION ALL query?

[removed]

a. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald and Mary Chen

 

[removed]

b. The query output will be: John Cretchakov and Mary Chen

 

[removed]

c. The query output will be: John Cretchakov

 

[removed]

d. The query output will be: Alice Cordoza, John Cretchakov, Anne McDonald, John Cretchakov and Mary Chen

 

The ______________________ will yield all rows with matching values in the join columns, plus all of the unmatched rows from the right table.

[removed]

a. CENTER OUTER JOIN

 

[removed]

b. RIGHT OUTER JOIN

 

[removed]

c. LEFT OUTER JOIN

 

[removed]

d. FULL OUTER JOIN

 

Use the following scenario for questions from this chapter:

You have been given a database for a small charity used to track donations made to it. It has the following structure:

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/activities/CH07_Donations_erd.png

and the following sample data in the tables:

Donor

Donor_Id

Donor_
FirstName

Donor_
LastName

Donor_Address

Donor_City

Donor_State

Donor_ZipCode

Donor_Phone

Donor_Email

101

James

James

123 Mockingbird Place

Peoria

IL

55556

555-555-2342

[email protected]

175

Joseph

Mays

54321 7th St

Atlantic City

NJ

15678

555-555-9877

[email protected]

207

Susan

Ames

777 Main St

Burlington

KY

41098

555-555-3478

[email protected]

303

Nancy

Zornes

P.O. Box 88776

Peoria

IL

55578-8776

555-555-1255

[email protected]

 

ReceiptType

ReceiptType_Id

ReceiptType_Description

C

Cash

CK

Check/Money Order

CC

Credit Card

PD

Payroll Deduction

A

Art / Collectible

I

In-kind

 

Fund

Fund_Id

Fund_Name

G

General Operation

S

Scholarship

B

Building Maintenance

C

Capital Campaign

 

Receipt

Receipt_Id

Donor_Id

Receipt_Date

ReceiptType_Id

Fund_Id

Receipt_Amount

Receipt_Description

1001

101

2015-01-05

CK

G

100

 

1002

207

2015-01-05

C

S

250

For: Virginia Wolfe Wilde

1003

207

2015-01-05

C

B

100

 

1004

175

2015-01-06

CC

G

137.5

In Memory of Bob

1005

101

2015-02-14

CK

G

100

 

1006

175

2015-02-20

A

C

15000000

Picasso Painting

 

For reporting purposes the client would like you to create a temporary table called “FundSummary” that contains the fund id, donor id, the number of receipts (donations) made to the fund by a donor, and the total of the receipts and the donor. The new table from the sample data would look like:

    

B

207

1

100

C

175

1

15000000

G

101

2

200

G

175

1

137.5

S

207

1

250

 

Fill in the blanks of the SQL Statements:

                                            
Fund_Id VARCHAR(10)                   ,
Donor_id VARCHAR(10)                   ,
Count_Of_Receipts INTEGER,
Total_Receipts DECIMAL(10,2),
                  (Fund_Id, Donor_Id),
FOREIGN KEY (Donor_Id) REFERENCES          (         ),
FOREIGN KEY (         ) REFERENCES          (Fund_Id) );

INSERT INTO FundSummary
         Fund_Id, Donor_Id,          (Receipt_Id),                  )
FROM Receipt
                  Fund_Id,          ;

 

Fill in the blanks with words that would best complete the passage.

donor_id

 FundSummary

 receipt_amount

 ( 

not 

null

select 

sum

donor_id 

primary

by

group 

CREATE 

key

donor

not

fund_id

fund

count

null

TABLE

Use the following scenario for questions from this chapter:

You have been given a database for a small charity used to track donations made to it. It has the following structure:

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/activities/CH07_Donations_erd.png

and the following sample data in the tables:

Donor

Donor_Id

Donor_
FirstName

Donor_
LastName

Donor_Address

Donor_City

Donor_State

Donor_ZipCode

Donor_Phone

Donor_Email

101

James

James

123 Mockingbird Place

Peoria

IL

55556

555-555-2342

[email protected]

175

Joseph

Mays

54321 7th St

Atlantic City

NJ

15678

555-555-9877

[email protected]

207

Susan

Ames

777 Main St

Burlington

KY

41098

555-555-3478

[email protected]

303

Nancy

Zornes

P.O. Box 88776

Peoria

IL

55578-8776

555-555-1255

[email protected]



ReceiptType

ReceiptType_Id

ReceiptType_Description

C

Cash

CK

Check/Money Order

CC

Credit Card

PD

Payroll Deduction

A

Art / Collectible

I

In-kind



Fund

Fund_Id

Fund_Name

G

General Operation

S

Scholarship

B

Building Maintenance

C

Capital Campaign



Receipt_Id

Donor_Id

Receipt_Date

ReceiptType_Id

Fund_Id

Receipt_Amount

Receipt_Description

1001

101

2015-01-05

CK

G

100

 

1002

207

2015-01-05

C

S

250

For: Virginia Wolfe Wilde

1003

207

2015-01-05

C

B

100

 

1004

175

2015-01-06

CC

G

137.5

In Memory of Bob

1005

101

2015-02-14

CK

G

100

 

1006

175

2015-02-20

A

C

15000000

Picasso Painting

Receipt



The client wants a listing of donor id, last name, first name, receipt date, type, and amount for all receipts greater than $100.00. The client wants the result sorted by donor last name, first name, and the donation date. The query result from the sample data would look like:

207

Ames

Susan

2015-01-05

C

250

175

Mays

Joseph

2015-01-06

CC

137.5

175

Mays

Joseph

2015-02-20

A

15000000



Fill in the blanks of the SQL Statement:

         Donor.          , Donor_LastName, Donor_FirstName, Receipt_Date,
Receipt.ReceiptType_Id, Receipt_Amount
         Receipt                   
        
 Receipt.Donor_Id =          
         Receipt_Amount          100.00
                     Donor_LastName, Donor_FirstName, Receipt_Date;



Fill in the blanks with words that would best complete the passage.

from

donor.donor_id 

select

donor

where

and

order

by 

donor_id

,

 

The problems for this chapter use a database for a simple department store that sells items to customers and wants to keep track of the invoices, the selling price (if an item is on sale), and the sales tax (7%) to be collected on some items. Every customer and invoice are assigned unique numbers. All items have a Universal Product Code (UPC) number and bar-code assigned to each unique item. Food and non-carbonated beverages are not taxed, but clothing, home goods, and most other items are.

The structure of the tables are described in the following crows foot ERD:

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/activities/CH08_Invoice.png

Sample Data for the tables follow:

Customer

Customer_Id

Customer_
FirstName

Customer_
LastName

Customer_
Address

Customer_
City

Customer_
State

Customer_
ZipCode

Customer_
Phone

Customer_Email

342

Linda

Spangler

2323 Roanoke Pk

Floyd

VA

24987

555-555-5646

[email protected]

505

Rodney

Ray

12399 27th Ave

New York

NY

10097

555-555-0909

[email protected]

776

Nancy

Reno

P.O.Box 98

Carter City

KY

41155

555-555-2342

[email protected]

987

Gustov

Jones

333 East Main St

Jamestown

VA

23099

555-555-9876

[email protected]



ItemType

ItemType_Id

ItemType_Description

W

Woman's Clothing

M

Men's Clothing

WA

Woman's Accessories

MA

Men's Accessories

A

General Accessories

O

Other



ItemSize

ItemSize_Id

ItemSize_Description

XS

Extra Small

S

Small

M

Medium

L

Large

XL

Extra Large



Item

UPC

Item_Description

ItemType_Id

ItemSize_Id

Item_Price

Item_Taxable

012345234569

Cream Blouse

W

S

29.95

1

012345234576

Cream Blouse

W

M

29.95

1

012345234588

Cream Blouse

W

L

29.95

1

012345234590

Cream Blouse

W

XL

29.95

1

012345234468

Blue Blouse

W

S

29.95

1

012345234475

Blue Blouse

W

M

29.95

1

012345234491

Blue Blouse

W

XL

29.95

1

012345224889

12 Inch Pearl Necklace

WA

 

345.95

1

012345224126

10 Inch Pearl Necklace

WA

 

298.95

1

012345334678

Explorer Cargo Shorts

M

S

33.45

1

012345334734

Explorer Cargo Shorts

M

M

33.45

1

012345334795

Explorer Cargo Shorts

M

L

33.45

1

012345334889

Explorer Cargo Shorts

M

XL

33.45

1

012345335101

Pink Silk Tie

MA

 

67.55

1

012345335303

Pink and Green Silk Tie

MA

 

67.55

1

012345999001

Yummy Bottled Water

O

 

1.29

0



Invoice

Invoice_Number

Customer_Id

Invoice_Date

Invoice_Taxable

Invoice_NonTaxable

Invoice_SalesTax

Invoice_Total

10101

987

2015-07-27

29.95

2.58

2.1

34.63

10102

505

2015-07-27

33.45

0

2.34

35.79

10107

505

2015-07-28

59.99

1.29

4.2

65.48

10111

342

2015-07-28

262.89

0

18.4

281.29



InvoiceDetail

Invoice_Number

UPC

Detail_Quantity

Detail_RegularPrice

Detail_SellingPrice

10101

012345334795

1

33.45

29.95

10101

012345999001

2

1.29

1.29

10102

012345334889

1

33.45

33.45

10107

012345335303

1

67.55

59.99

10107

012345999001

1

1.29

1.29

10111

012345234576

1

29.95

29.95

10111

012345234475

1

29.95

29.95

10111

012345224126

1

298.95

202.99

Suppose that we have a second table with vendor information (sample is below) in it and that we want to create a single telephone directory with both vendor and customer information in it.

Vendor_Id

Vendor_CompanyName

Vendor_Address

Vendor_City

Vendor_State

Vendor_ZipCode

Vendor_Phone

Vendor_Email

101

QRS Importers

12345 Dock St

San Fransisco

CA

97654

555-544-4444

[email protected]

505

ABC Supply

505 Euclid Ave

Lexington

KY

40505

555-505-0505

[email protected]



The phone directory should contain the state, a name column with either the customer last name and first name concatenated with a comma or the vendor company name, city, and phone number. Output should be sorted by state then by name. Your results should look like:

State

Name

City

Phone

CA

QRS Importers

San Fransisco

555-544-4444

KY

ABC Supply

Lexington

555-505-0505

KY

Reno, Nancy

Carter City

555-555-2342

NY

Ray, Rodney

New York

555-555-0909

VA

Jones, Gustov

Jamestown

555-555-9876

VA

Spangler, Linda

Floyd

555-555-5646

Fill in the blanks (remember a blank, drop "blank" as answer)

SELECT          ,          ,          ,          
FROM ( SELECT          || ', ' || Customer_FirstName AS Name,
Customer_City                    , Customer_State AS State, Customer_Phone AS Phone
FROM Customer          
         
SELECT Vendor_CompanyName AS Name,
Vendor_City AS City, Vendor_State AS State, Vendor_Phone AS Phone
FROM Vendor          )
                  State, Name;



Fill in the blanks with words that would best complete the passage.

blank

 

Phone

 

city

 

City

 

Customer_LastName

 

blank

 

Name

 

AS

 

BY

 

UNION

 

State

 

ORDER

 

The problems for this chapter use a database for a simple department store that sells items to customers and wants to keep track of the invoices, the selling price (if an item is on sale), and the sales tax (7%) to be collected on some items. Every customer and invoice are assigned unique numbers. All items have a Universal Product Code (UPC) number and bar-code assigned to each unique item. Food and non-carbonated beverages are not taxed, but clothing, home goods, and most other items are.

The structure of the tables are described in the following crows foot ERD:

http://cnow.apps.ng.cengage.com/ilrn/books/cmdsin12h/images/activities/CH08_Invoice.png

Sample Data for the tables follow:

Customer

Customer_Id

Customer_
FirstName

Customer_
LastName

Customer_
Address

Customer_
City

Customer_
State

Customer_
ZipCode

Customer_
Phone

Customer_Email

342

Linda

Spangler

2323 Roanoke Pk

Floyd

VA

24987

555-555-5646

[email protected]

505

Rodney

Ray

12399 27th Ave

New York

NY

10097

555-555-0909

[email protected]

776

Nancy

Reno

P.O.Box 98

Carter City

KY

41155

555-555-2342

[email protected]

987

Gustov

Jones

333 East Main St

Jamestown

VA

23099

555-555-9876

[email protected]



ItemType

ItemType_Id

ItemType_Description

W

Woman's Clothing

M

Men's Clothing

WA

Woman's Accessories

MA

Men's Accessories

A

General Accessories

O

Other



ItemSize

ItemSize_Id

ItemSize_Description

XS

Extra Small

S

Small

M

Medium

L

Large

XL

Extra Large



Item

UPC

Item_Description

ItemType_Id

ItemSize_Id

Item_Price

Item_Taxable

012345234569

Cream Blouse

W

S

29.95

1

012345234576

Cream Blouse

W

M

29.95

1

012345234588

Cream Blouse

W

L

29.95

1

012345234590

Cream Blouse

W

XL

29.95

1

012345234468

Blue Blouse

W

S

29.95

1

012345234475

Blue Blouse

W

M

29.95

1

012345234491

Blue Blouse

W

XL

29.95

1

012345224889

12 Inch Pearl Necklace

WA

 

345.95

1

012345224126

10 Inch Pearl Necklace

WA

 

298.95

1

012345334678

Explorer Cargo Shorts

M

S

33.45

1

012345334734

Explorer Cargo Shorts

M

M

33.45

1

012345334795

Explorer Cargo Shorts

M

L

33.45

1

012345334889

Explorer Cargo Shorts

M

XL

33.45

1

012345335101

Pink Silk Tie

MA

 

67.55

1

012345335303

Pink and Green Silk Tie

MA

 

67.55

1

012345999001

Yummy Bottled Water

O

 

1.29

0



Invoice

Invoice_Number

Customer_Id

Invoice_Date

Invoice_Taxable

Invoice_NonTaxable

Invoice_SalesTax

Invoice_Total

10101

987

2015-07-27

29.95

2.58

2.1

34.63

10102

505

2015-07-27

33.45

0

2.34

35.79

10107

505

2015-07-28

59.99

1.29

4.2

65.48

10111

342

2015-07-28

262.89

0

18.4

281.29



InvoiceDetail

Invoice_Number

UPC

Detail_Quantity

Detail_RegularPrice

Detail_SellingPrice

10101

012345334795

1

33.45

29.95

10101

012345999001

2

1.29

1.29

10102

012345334889

1

33.45

33.45

10107

012345335303

1

67.55

59.99

10107

012345999001

1

1.29

1.29

10111

012345234576

1

29.95

29.95

10111

012345234475

1

29.95

29.95

10111

012345224126

1

298.95

202.99

You have been tasked to generate a report from the database using a single SQL statement to do the following:

List all items with the number of invoices that the items have been on and the total of the sales of that item. Display in order by type, description, and size.

Your generated output should look like:

UPC

Item_Description

ItemType_Id

ItemSize_Id

Invoice_Count

Total_Sales

012345334795

Explorer Cargo Shorts

M

L

1

1

012345334734

Explorer Cargo Shorts

M

M

0

 

012345334678

Explorer Cargo Shorts

M

S

0

 

012345334889

Explorer Cargo Shorts

M

XL

1

1

012345335101

Pink Silk Tie

MA

 

0

 

012345335303

Pink and Green Silk Tie

MA

 

1

1

012345999001

Yummy Bottled Water

O

 

2

3

012345234475

Blue Blouse

W

M

1

1

012345234468

Blue Blouse

W

S

0

 

012345234491

Blue Blouse

W

XL

0

 

012345234588

Cream Blouse

W

L

0

 

012345234576

Cream Blouse

W

M

1

1

012345234569

Cream Blouse

W

S

0

 

012345234590

Cream Blouse

W

XL

0

 

012345224126

10 Inch Pearl Necklace

WA

 

1

1

012345224889

12 Inch Pearl Necklace

WA

 

0

 



Fill in the blanks (remember a blank, drop "blank" as answer)

SELECT Item.UPC, Item.Item_Description, Item.ItemType_Id,
Item.ItemSize_Id, COUNT(Invoice.Invoice_Number) AS Invoice_Count,
SUM(InvoiceDetail.Detail_Quantity) AS Total_Sales
         FROM item
                 InvoiceDetail ON item.                  .UPC
                  Invoice ON          .Invoice_Number = InvoiceDetail.         
GROUP BY Item.UPC
ORDER BY Item.ItemType_id, Item.Item_Description, Item.ItemSize_Id;



Fill in the blanks with words that would best complete the passage.

InvoiceDetail

 

LEFT

 

JOIN

 

blank

 

UPC

 

LEFT

 

Invoice

 

Invoice_Number

 

JOIN

 

Which of the following is the data dictionary's function in database design?

[removed]

a. It makes it easier to check for the existence of synonyms and homonyms, to check whether all attributes exist to support required reports, to verify appropriate relationship representations, and so on.

 

[removed]

b. Its contents are both developed and used during the six DBLC phases.

 

[removed]

c. It provides a precise description of the characteristics of all the entities and attributes found within the database.

 

[removed]

d. All of these choices are correct.

In the decentralized conceptual database design approach, the aggregation process requires the lead designer to assemble a single model where which of the following aggregation problems must be addressed?

[removed]

a. synonyms

 

[removed]

b. entity subclasses

 

[removed]

c. conflicting object definitions

 

[removed]

d. All of these choices are correct.

______________ design begins by identifying the different entity types and the definition of each entity's attributes.

[removed]

a. Bottom up

 

[removed]

b. Over the top

 

[removed]

c. Top down

 

[removed]

d. None of these choices are correct.



_______________ is the last stage in the database design process.

[removed]

a. Conceptual design

 

[removed]

b. Database design

 

[removed]

c. Physical design

 

[removed]

d. Logical design

Which conceptual database design is best suited to relatively small and simple databases that lend themselves well to a bird's eye view of the entire database and may be designed by a single person or by a small and informally constituted design team?

[removed]

a. Centralized database design

 

[removed]

b. Distributed database design

 

[removed]

c. Decentralized database design

 

[removed]

d. Top-down database design

A(n) ________________________ is a system that provides for data collection, storage, and retrieval; facilitates the transformation of data into information; and manages both data and information. It is composed of hardware, the DBMS and other software, database(s), people, and procedures.

[removed]

a. information system

 

[removed]

b. storage system

 

[removed]

c. software system

 

[removed]

d. database system

____________are narrative descriptions of the business policies, procedures, or principles that are derived from a detailed description of operations.

[removed]

a. Entity rules

 

[removed]

b. Database rules

 

[removed]

c. Attribute rules

 

[removed]

d. Business rules

The DBLC is composed of _____ phases.

[removed]

a. four

 

[removed]

b. six

 

[removed]

c. three

 

[removed]

d. five

Business rules are particularly valuable to database designers, because they help define which of the following?

[removed]

a. attributes

 

[removed]

b. relationships

 

[removed]

c. entities

 

[removed]

d. All of these choices are correct.

The _________________ goal is to design an enterprise-wide database that is based on a specific data model but independent of physical-level details.

[removed]

a. conceptual design

 

[removed]

b. database design

 

[removed]

c. physical design

 

[removed]

d. logical design



Which of the following is NOT a step performed in the physical design stage in the database design process?

[removed]

a. Validate the model integrity constraints

 

[removed]

b. Determine performance measurements

 

[removed]

c. Define integrity and security measures

 

[removed]

d. Define data storage organization

 

 

 

 

Which of the following is established during the systems design phase, in which the designer completes the design of all required system processes?

[removed]

a. The database schema

 

[removed]

b. The detailed system specifications

 

[removed]

c. The user interface

 

[removed]

d. None of these choices are correct.

Which of the following is established during the systems design phase, in which the designer completes the design of all required system processes?

[removed]

a. The database schema

 

[removed]

b. The detailed system specifications

 

[removed]

c. The user interface

 

[removed]

d. None of these choices are correct.

The conceptual design is composed of _____ steps.

[removed]

a. seven

 

[removed]

b. five

 

[removed]

c. four

 

[removed]

d. three

Ultimately, the purpose of an _____________________ is to facilitate good decision making by making relevant and timely information available to the decision makers.

[removed]

a. information system

 

[removed]

b. database system

 

[removed]

c. software system

 

[removed]

d. storage system

Which conceptual database design is best when company operations are spread across multiple operational sites or when the database has multiple entities that are subject to complex relations?

[removed]

a. Distributed database design

 

[removed]

b. Top-down database design

 

[removed]

c. Centralized database design

 

[removed]

d. Decentralized database design

SDLC is the acronym that is used to label the _______________________________.

[removed]

a. System Design Life Cycle

 

[removed]

b. System Development Life Cycle

 

[removed]

c. Software Design Life Cycle

 

[removed]

d. Software Development Life Cycle



A(n) ______________________ backup of the database creates a backup of only those database objects that have changed since the last full backup.

[removed]

a. differential

 

[removed]

b. partial

 

[removed]

c. complete

 

[removed]

d. transaction

Which of the following shows how systems analysis fits into a discussion about information systems?

[removed]

a. By evaluating the existing system

 

[removed]

b. By developing a logical systems design

 

[removed]

c. By establishing end-user requirements

 

[removed]

d. All of these choices are correct.

DBLC is the acronym that is used to label the _______________________________.

[removed]

a. Database Life Cycle

 

[removed]

b. Development Logical Cycle

 

[removed]

c. Database Logical Cycle

 

[removed]

d. Development Life Cycle

The SDLC is composed of _____ phases.

[removed]

a. four

 

[removed]

b. three

 

[removed]

c. seven

 

[removed]

d. five



Which of the following is NOT a step performed in the logical design stage in the database design process?

[removed]

a. Validate the model using normalization

 

[removed]

b. Validate the model using user requirements

 

[removed]

c. Validate the model integrity constraints

 

[removed]

d. None of these choices are correct.

A(n) _____________ is the use of different names to identify the same object, such as an entity, an attribute, or a relationship.

[removed]

a. synonym

 

[removed]

b. pseudonym

 

[removed]

c. homonym

 

[removed]

d. antonym



Database design must yield a database that does which of the following?

[removed]

a. does not fall prey to uncontrolled data duplication, thus preventing data anomalies and the attendant lack of data integrity.

 

[removed]

b. is efficient in its provision of data access.

 

[removed]

c. serves the needs of the information system.

 

[removed]

d. All of these choices are correct.

__________ design first defines the required attributes and then groups the attributes to form entities.

[removed]

a. Bottom up

 

[removed]

b. Top down

 

[removed]

c. Over the top

 

[removed]

d. None of these choices are correct.

The ___________________ specifies that all the data defined in the data model are actually required to fit present and expected future data requirements.

[removed]

a. completeness data rule

 

[removed]

b. maximum data rule

 

[removed]

c. multiple data rule

 

[removed]

d. minimal data rule

Which of the following is NOT one of the six (6) steps identified as part of the ER model verification process?

[removed]

a. Verify all processes against the system requirements

 

[removed]

b. Identify each module and its components

 

[removed]

c. Identify the ER model's central entity

 

[removed]

d. Map the entity names to proper process names

How many steps are required in the development of the conceptual model using an ER diagram?

[removed]

a. six

 

[removed]

b. seven

 

[removed]

c. eight

 

[removed]

d. two

Which of the following is NOT an important factor in the selection of a DBMS software product?

[removed]

a. DBMS features and tools

 

[removed]

b. Cost

 

[removed]

c. Portability

 

[removed]

d. Number of employees



 

2.Each of the following activities are commonly performed during the implementation of the Database Life Cycle (DBLC). Fill in the blank, before each activity, with the phase number of the DBLS that this activity would normally be performed.

DBLC Task Numbers:
1. Database initial study
2. Database design
3. Implementation and loading
4. Testing and evaluation
5. Operation
6. Maintenance and evolution

[removed] Load the initial values into the tables
[removed] Finish user documentation
[removed] Adding additional tables, attributes, and indexes
[removed] Attempt to gain unauthorized access to the data
[removed] Interview management
[removed] Convert existing data
[removed] Study the competition's database
[removed] Plan how to grant different levels of access to different user groups
[removed] Install the database
[removed] Train users
[removed] Changing constraints to match changes in business rules
[removed] Define budget and scope
[removed] Select a DBMS software solution
[removed] Draw a logical ERD
[removed] Performing software patches to the DBMS
[removed] Create the database
[removed] Understand how this database will connect to other databases in the organization
[removed] Develop a Conceptual Model
[removed] Make sure application software updates the database
[removed] Regular security audits
[removed] Define objectives
[removed] Create a detailed model that can be physically implemented

Top of Form

Bottom of Form

 

 

  • Posted: 2 years ago
  • Due: 
  • Budget: $15
Answers 1
  • answer
    Answer rating:5Stars out of2ratings

Purchase the answer to view it

blurred-text