Skip to document
Welcome to StudocuSign in to access the best study resources
Guest user
0followers
Upload
HomeMy LibraryAsk AI
  • You don't have any recent items yet.
My Library
  • You don't have any courses yet.
  • You don't have any books yet.
  • You don't have any Studylists yet.

07 - Homework Week 7

Homework Week 7
Course

Managing Distributed Data Systems (ITM 304)

7 Documents
Students shared 7 documents in this course
Academic year: 2019/2020
Uploaded by:
Student
Cherine Haddadian
Golden Gate University
0followers
6Uploads
7upvotes

Recommended for you

Comments

Please sign in or register to post comments.

Students also viewed

Related documents

Preview text

Database Processing, 13e (Kroenke/Auer) Chapter 7: SQL for Database Construction and Application Processing

  1. The SQL CREATE TABLE statement is used to name a new table and describe the table's columns. Answer: TRUE Diff: 1 Page Ref: 281

  2. The SQL keyword CONSTRAINT is used to define one of five types of constraints. Answer: TRUE Diff: 1 Page Ref: 283

  3. The SQL keyword PRIMARY KEY is used to designate the column(s) that are the primary key for the table. Answer: TRUE Diff: 1 Page Ref: 283, 289

  4. The SQL keyword CONSTRAINT is used to limit column values to specific values. Answer: TRUE Diff: 2 Page Ref: 283, 289

  5. The SQL keyword CONSTRAINT is used in conjunction with the SQL keywords PRIMARY KEY and FOREIGN KEY. Answer: TRUE Diff: 1 Page Ref: 289-

  6. One advantage of using the CONSTRAINT command to define a primary key is that the database designer controls the name of the constraint. Answer: TRUE Diff: 3 Page Ref: 289-

  7. The SQL keyword UNIQUE is used to define alternative keys. Answer: TRUE Diff: 2 Page Ref: 290

  8. If the table PRODUCT has a column PRICE, and PRICE has the data type Numeric (8,2), the value 98765 stored in that field will be displayed by the DBMS as 98765. Answer: FALSE Diff: 3 Page Ref: 289 Fig 7-

  9. If the table ITEM has a column WEIGHT, and WEIGHT has the data type Numeric (7,2), the value 4321 with be displayed by the DBMS as 43. Answer: TRUE Diff: 2 Page Ref: 289 Fig 7-

1

  1. The SQL keyword CHECK is used to limit column values to specific values. Answer: TRUE Diff: 2 Page Ref: 289, 292

  2. The SQL keyword MODIFY is used to change the structure, properties or constraints of a table. Answer: FALSE Diff: 1 Page Ref: 298

  3. Data values to be added to a table are specified by using the SQL VALUES clause. Answer: TRUE Diff: 2 Page Ref: 300-

  4. The SQL keyword DELETE is used to delete a table's structure. Answer: FALSE Diff: 1 Page Ref: 298-

  5. When the correct SQL command is used to delete a table's structure, the command can only be used with a table that has already had its data removed. Answer: FALSE Diff: 2 Page Ref: 298-

  6. One or more rows can be added to a table by using the SQL INSERT statement. Answer: TRUE Diff: 2 Page Ref: 300-

  7. Unless it is being used to copy data from one table to another, the SQL INSERT statement can be used to insert only a single row into a table. Answer: TRUE Diff: 3 Page Ref: 300-

  8. Rows in a table can be changed by using the SQL UPDATE statement. Answer: TRUE Diff: 2 Page Ref: 307-

  9. The SQL SET keyword is used to specify a new value when changing a column value. Answer: TRUE Diff: 3 Page Ref: 307-

  10. The SQL keyword MODIFY is used to change a column value. Answer: FALSE Diff: 3 Page Ref: 307-

  11. Rows can be removed from a table by using the SQL DELETE statement. Answer: TRUE Diff: 2 Page Ref: 309

2

  1. Because SQL statements are table-oriented, whereas programs are variable-oriented, the results of SQL statements used in programs are treated as pseudofiles. Answer: TRUE Diff: 2 Page Ref: 319

  2. A set of SQL statements stored in an application written in a standard programming language is called embedded SQL. Answer: TRUE Diff: 1 Page Ref: 319

  3. Because SQL statements are table-oriented, whereas programs are variable-oriented, the results of SQL statements used in programs are accessed using an SQL cursor. Answer: TRUE Diff: 2 Page Ref: 319-

  4. A stored program that is attached to a table or view is called a stored procedure. Answer: FALSE Diff: 2 Page Ref: 323

  5. SQL triggers use the ANSI SQL keywords BEFORE, INSTEAD OF, and AFTER. Answer: TRUE Diff: 2 Page Ref: 323-325 Fig 7-

  6. SQL triggers can be used with SQL operations INSERT, UPDATE, and DELETE. Answer: TRUE Diff: 2 Page Ref: 323-325 Fig 7-

  7. SQL triggers can be used when the DBMS receives an INSERT request. Answer: TRUE Diff: 1 Page Ref: 323-325 Fig 7-

  8. SQL triggers are used for providing default values, validity checking, updating views, and performing referential integrity actions. Answer: TRUE Diff: 1 Page Ref: 323-

  9. The Oracle DBMS supports the SQL BEFORE trigger. Answer: TRUE Diff: 2 Page Ref: 323-325 Fig 7-

  10. The SQL Server DBMS supports the SQL BEFORE trigger. Answer: FALSE Diff: 2 Page Ref: 323-325 Fig 7-

4

  1. SQL triggers can be used when the DBMS receives an insert request. Answer: TRUE Diff: 1 Page Ref: 323-325 Fig 7-

  2. To set a column value to an initial value that is selected according to some business logic, you would use the SQL DEFAULT constraint with the CREATE TABLE command. Answer: FALSE Diff: 3 Page Ref: 325 Fig 7-

  3. SQL triggers are created using the SQL ADD TRIGGER statement. Answer: FALSE Diff: 3 Page Ref: 326 in Fig 7-

  4. If the values in an SQL view are not changeable through the view itself, you may still be able to update the view by using unique application logic. In this case, the specific logic is placed in an INSTEAD OF trigger. Answer: TRUE Diff: 3 Page Ref: 327-

  5. If a trigger is being written to enforce referential integrity actions, you cannot use an INSTEAD OF trigger. Answer: FALSE Diff: 3 Page Ref: 328-

  6. When a trigger is fired, the DBMS makes the appropriate data available to the trigger code. Answer: TRUE Diff: 3 Page Ref: 289-

  7. A stored program that is stored within the database and compiled when used is called a trigger. Answer: FALSE Diff: 2 Page Ref: 330 Fig 7-

  8. Stored procedures have the advantage of greater security, decreased network traffic, SQL optimized by the DBMS compiler, and code sharing. Answer: TRUE Diff: 2 Page Ref: 330-331 Fig 7-

  9. Unlike application code, stored procedures are never distributed to the client computers. Answer: TRUE Diff: 2 Page Ref: 330-331 Fig 7-

  10. Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers the advantages of less work, standardized processing, and specialization among developers. Answer: TRUE Diff: 2 Page Ref: 330-331 Fig 7-

5

  1. Which of the following illustrates the authors' preferred style of defining a primary key? A) CREATE TABLE CUSTOMER ( CustomerID Integer Primary Key LastName Char(35) Not Null First Name Char(25) Null ); B) CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(25) Null CONSTRAINT CustomerPK PRIMARY KEY (CustomerID) ); C) CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(25) Null );

ALTER TABLE CUSTOMER ADD CONSTRAINT CustomerPK PRIMARY KEY (CustomerID); D) either B or C E) The authors do not demonstrate a preference for how to define a primary key. Answer: B Diff: 3 Page Ref: 281-

  1. Given the SQL statement

CREATE TABLE SALESREP ( SalesRepNo int NOT NULL, RepName char(35) NOT NULL, HireDate date NOT NULL,

CONSTRAINT SalesRepPK PRIMARY KEY (SalesRepNo), CONSTRAINT SalesRepAK1 UNIQUE (RepName) );

we know that ________. A) RepName is the primary key B) RepName is a foreign key C) RepName is a candidate key D) RepName is a surrogate key E) None of the above is true Answer: C Diff: 3 Page Ref: 281-290 Fig 7-

7

  1. The SQL keyword used to limit column values to specific values is ________. A) CONSTRAINT B) CHECK C) NOT NULL D) UNIQUE E) UPDATE Answer: B Diff: 2 Page Ref: 289,292 Fig 7-

  2. Which SQL keyword is used to change the structure, properties or constraints of a table? A) SET B) CREATE C) SELECT D) ALTER E) CONSTRAINT Answer: D Diff: 1 Page Ref: 298

  3. Which SQL keyword is used to delete a table's structure? A) DELETE B) DROP C) DISPOSE D) ALTER E) MODIFY Answer: B Diff: 1 Page Ref: 298-

  4. When the correct SQL command is used to delete a table's structure, what happens to the data in the table? A) If the deleted table was a parent table, the data is added to the appropriate rows of the child table. B) If the deleted table was a child table, the data is added to the appropriate rows of the parent table. C) The data in the table is also deleted. D) Nothing because there was no data in the table since only an empty table can be deleted. E) A and B Answer: C Diff: 2 Page Ref: 298-

  5. Which SQL keyword is used to add one or more rows of data to a table? A) DELETE B) INSERT C) SELECT D) SET E) UPDATE Answer: B Diff: 1 Page Ref: 300-

8

  1. Based on the tables below, which of the following SQL statements would increase the balance of the Gonzales account by $100 to a total of $450?

GENERAL SALES DATABASE:

SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/ 734 Smith 02/03/ 345 Chen 01/25/ 434 Johnson 11/23/

CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345

A) SELECT Gonzales FROM CUSTOMER INSERT VALUES PLUS (100) INTO Balance; B) SELECT Gonzales FROM CUSTOMER INSERT VALUES (450) INTO Balance; C) INSERT INTO CUSTOMER VALUES PLUS (100) SELECT Balance WHERE CustName = 'Gonzales'; D) INSERT INTO CUSTOMER VALUES (450) SELECT Balance WHERE CustName = 'Gonzales'; E) UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales'; Answer: E Diff: 3 Page Ref: 300-

  1. An SQL virtual table is called ________. A) a CHECK constraint B) a view C) embedded SQL D) a trigger E) a stored procedure Answer: B Diff: 1 Page Ref: 309

10

  1. The SQL command used to create a virtual table is ________. A) CREATE VTABLE B) CREATE VIEW C) VTABLE D) VIEW E) NEWLOOK Answer: B Diff: 2 Page Ref: 309

  2. SQL views are constructed from ________. A) CREATE statements B) INSERT statements C) UPDATE statements D) SELECT statements E) VIEW statements Answer: D Diff: 2 Page Ref: 309-

  3. According to the SQL-92, statements used to construct views cannot contain ________. A) the SELECT clause B) the FROM clause C) the WHERE clause D) the ORDER BY clause E) SQL view statements can use all of the listed clauses. Answer: D Diff: 2 Page Ref: 309

  4. Which SQL statement is used to retrieve view instances? A) CREATE B) DELETE C) INSERT D) SELECT E) UPDATE Answer: D Diff: 1 Page Ref: 309

  5. SQL views are used ________. A) to hide columns B) to show results of computed columns C) to hide complicated SQL statements D) to provide a level of indirection between data processed by applications and the data actually stored in the database tables E) SQL views are used for all of the above. Answer: E Diff: 1 Page Ref: 311-319 Fig 7-

11

  1. Because SQL statements are table-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are accessed using ________. A) standard programming tools B) custom written programming tools C) an SQL cursor D) an SQL trigger E) an SQL stored procedure Answer: C Diff: 2 Page Ref: 319-

  2. A stored program that is attached to a table or view is called ________. A) a CHECK constraint B) a view C) embedded SQL D) a trigger E) a stored procedure Answer: D Diff: 1 Page Ref: 323-

  3. Which of the following is not an ANSI SQL trigger? A) BEFORE UPDATE B) INSTEAD OF UPDATE C) BEFORE INSERT D) INSTEAD OF CONSTRAINT E) AFTER DELETE Answer: D Diff: 3 Page Ref: 323-325 Fig 7-

  4. Which of the following is an SQL trigger Oracle supports? A) BEFORE B) INSTEAD OF C) AFTER D) B and C only E) A, B, and C Answer: E Diff: 2 Page Ref: 323-325 Fig 7-

  5. Which of the following is an SQL trigger Microsoft SQL Server supports? A) BEFORE B) INSTEAD OF C) AFTER D) B and C only E) A, B, and C Answer: D Diff: 2 Page Ref: 323-325 Fig 7-

13

  1. SQL triggers can be used when the DBMS receives a(n) ________ request. A) INSERT B) UPDATE C) DELETE D) A and B E) A, B, and C Answer: E Diff: 1 Page Ref: 323-325 Fig 7-

  2. SQL triggers are used for ________. A) validity checking B) providing default values C) updating views D) A and B E) A, B, and C Answer: E Diff: 1 Page Ref: 327-328 Fig 7-

  3. When a trigger is fired, the DBMS makes the appropriate data available to ________. A) the SQL interpreter B) the application code C) the embedded SQL code D) the trigger code E) the stored procedure code Answer: D Diff: 1 Page Ref: 323-

  4. SQL triggers are created using ________. A) the SQL CREATE TRIGGER statement B) the SQL ADD TRIGGER statement C) the SQL TRIGGER statement D) the SQL ADD CONSTRAINT TRIGGER statement E) the SQL CONSTRAINT TRIGGER statement Answer: A Diff: 3 Page Ref: 326 in Fig 7-

  5. To set a column value to an initial value that is selected according to some business logic, you would use: A) the SQL DEFAULT constraint with the CREATE TABLE command. B) an SQL view. C) embedded SQL. D) an SQL trigger. E) an SQL stored procedure. Answer: D Diff: 2 Page Ref: 327-328 Fig 7-

14

  1. Explain the essential format of the CREATE TABLE statement. Answer: The essential format for the CREATE TABLE statement is: CREATE TABLE tablename ( column-description, column-description, column-description, ... optional table constraints ); "Tablename" is the name that will be given to the newly created table. "Column-description" is a three-part description of each column to appear in the table. This description includes the name of the column, the column's data type, and an optional column constraint (either Primary Key, Null, or Not Null), in that order. The CONSTRAINT phrase can be used to set optional primary key, foreign key and referential integrity constraints for the table. All SQL statements must end with a semi-colon (;). Diff: 1 Page Ref: 281-

  2. Explain how relationships are created using SQL. Answer: In SQL, relationships are created using a FOREIGN KEY constraint. This has the format:

CONSTRAINT ConstraintNameFK FOREIGN KEY({ForeignKeyColumnInCurrentTable} REFERENCES {ReferencedTableName}(PrimaryKeyColumnInReferencedTable})

The constraint thus names the foreign key column in the current table and its corresponding primary key in a referenced table. Diff: 1 Page Ref: 289-292 Fig 7-

  1. Discuss SQL data types. Answer: Common examples of standard SQL data types are Char, VarChar, Integer, and Numeric. The Char data type is for fixed-length character data. VarChar is for variable-length character data. Integer is for numeric data that are whole numbers only. Numeric is for numeric data that may include decimals. Char, VarChar, and Numeric must be qualified by a length specification to indicate the amount of storage space to be allocated for each data item. For example, Char(10) indicates fixed-length character data that is always stored as 10 characters. Diff: 1 Page Ref: 283 Fig 7-

  2. Discuss what is meant by a data type of "Numeric (10,3)." Answer: Numeric indicates a non-integer, decimal number in SQL Server. Oracle Database uses Number, and MySQL uses Decimal or Fixed. The (10,3) is in (n,d) format, where n is the total number of digits allowed, and d is the number of digits to the right of the decimal place. Thus, "10, 3" allows a maximum of ten digits, and the last three are considered to be to the right of the decimal place. For example, "1234567" would be read as "1234." Diff: 1 Page Ref: 283, 289 Fig 7-

16

  1. Distinguish between Char and VarChar data types. Answer: Char data type is fixed-length, so that no matter the actual length of the data entered it will always take exactly the same storage space. For example, Char(10) indicates that 10 characters will always be stored for each value of that column. If the actual data entered is less than the specified fixed- length, the data will be padded with blanks. VarChar data type is variable length so that only the amount of space actually needed to store the data is used. Although VarChar may be more efficient in its use of space, it is not always preferred. VarChar requires the storage of some extra data to indicate the length of the data values, plus it requires some extra processing by the DBMS to arrange the variable length data. Diff: 3 Page Ref: 283 Fig 7-

17

  1. The following database will be used in this question:

GENERAL SALES DATABASE:

SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/ 734 Smith 02/03/ 345 Chen 01/25/ 434 Johnson 11/23/

CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345

What is an SQL view, and what is it used for? Include an example based on the CUSTOMER table of the General Sales Database. Answer: An SQL view is a virtual table constructed from database tables or other views. It is based on the SQL CREATE VIEW command and uses the SQL SELECT statement to construct the view. However, the ORDER BY clause cannot be used when creating a view. For example:

CREATE VIEW CustomerNameOnly AS SELECT CustName FROM CUSTOMER;

A view may be used to (1) hide columns or rows, (2) show the results of computed columns, (3) hide complicated SQL statements such as joins, (4) layer built-in functions, (5) provide a level of indirection between the data processed by applications and the actual table data,(6) assign different processing permissions to different views of the same table, and (7) assign different triggers to different views of the same table. Diff: 3 Page Ref: 309-311 Fig 7-

  1. What is embedded SQL, and what considerations are necessary when using it in an application? Answer: Embedded SQL are SQL statements used, or embedded, in program code, triggers or stored procedures. Applications are typically written in program code, using a programming language. There are two problems that arise. First, the results of SQL statements must be assigned to programming language variables. DBMS products typically provide the means of doing this. Second, SQL is table or set-oriented and SQL results use tables or sets of rows, whereas application programming languages are variable or row-oriented. This is resolved by treating SQL results as pseudofiles. A cursor is then used to move through the pseudofile one row at a time. Diff: 2 Page Ref: 319-

19

  1. What are SQL triggers and how are they used? Answer: An SQL trigger is a stored program that is attached to a table or view. The trigger is invoked by the DBMS whenever an insert, update or delete request is made on the table or view with the trigger. There are three commonly used triggers: BEFORE, INSTEAD OF, and AFTER (MS SQL server does not support BEFORE). This creates a set of nine possible trigger types: BEFORE + [INSERT or UPDATE or DELETE], INSTEAD OF + [INSERT or UPDATE or DELETE], and AFTER + [INSERT or UPDATE or DELETE]. Triggers are used (among other things) for (1) providing default values, (2) validity checking, (3) updating views, and (4) enforcing referential integrity actions. Diff: 2 Page Ref: 323-325 Fig 7-

  2. What are SQL stored procedures and how are they used? Answer: An SQL stored procedure is a stored program that is attached to a database instead of just a table or view. Stored procedures can receive input parameters and return results. They can be executed by any process that has permission with the database to use stored procedures. They can issue INSERT, UPDATE and DELETE commands. They are typically used by (1) database administrators to do common administrative tasks, and (2) database applications. Diff: 1 Page Ref: 330-331 Fig 7-

20

Was this document helpful?

07 - Homework Week 7

Course: Managing Distributed Data Systems (ITM 304)

7 Documents
Students shared 7 documents in this course

University: Golden Gate University

Was this document helpful?
Database Processing, 13e (Kroenke/Auer)
Chapter 7: SQL for Database Construction and Application Processing
1) The SQL CREATE TABLE statement is used to name a new table and describe the table's
columns.
Answer: TRUE
Diff: 1 Page Ref: 281
2) The SQL keyword CONSTRAINT is used to define one of five types of constraints.
Answer: TRUE
Diff: 1 Page Ref: 283
3) The SQL keyword PRIMARY KEY is used to designate the column(s) that are the primary
key for the table.
Answer: TRUE
Diff: 1 Page Ref: 283, 289
4) The SQL keyword CONSTRAINT is used to limit column values to specific values.
Answer: TRUE
Diff: 2 Page Ref: 283, 289
5) The SQL keyword CONSTRAINT is used in conjunction with the SQL keywords PRIMARY
KEY and FOREIGN KEY.
Answer: TRUE
Diff: 1 Page Ref: 289-290
6) One advantage of using the CONSTRAINT command to define a primary key is that the
database designer controls the name of the constraint.
Answer: TRUE
Diff: 3 Page Ref: 289-290
7) The SQL keyword UNIQUE is used to define alternative keys.
Answer: TRUE
Diff: 2 Page Ref: 290
8) If the table PRODUCT has a column PRICE, and PRICE has the data type Numeric (8,2), the
value 98765 stored in that field will be displayed by the DBMS as 98765.00.
Answer: FALSE
Diff: 3 Page Ref: 289 Fig 7-4
9) If the table ITEM has a column WEIGHT, and WEIGHT has the data type Numeric (7,2), the
value 4321 with be displayed by the DBMS as 43.21.
Answer: TRUE
Diff: 2 Page Ref: 289 Fig 7-4
1
ScholarStock

Students also viewed

Related documents