IS640 - Chapter 1

11. Which of the following has no repeating groups but can contain partial or transitive dependencies? A. unnormalized data B. 1NF C. 2NF D. 3NF

B. 1NF

15. Which of the following symbols represents an optional relationship in an E-R model? a. a straight line b. a dashed line c. a straight line with a crow's foot at both ends d. a straight line with a crow's foot at one end.

B. A dashed line

2. Which of the following defines a relationship in which each occurence of data in one entity is represented by multiple occurrences of the data in the other entity? A. one to one B. one to many C. Many to many D. none of the above

B. one to many

22. The unique identifier for a record is called the a. foreign key b. primary key c. turn key d. common field

B. the primary key

10. Which of the following has no partial dependencies but can contain transitive dependencies? A. unnormalized data B. INF C. 2NF D. 3NF

C. 2NF

18. Which of the following defines a relationship in which data can have multiple occurrences in each entity? A. One to one B. One to many C. Many to Many D. None of the above

C. Many to Many

5. Which of the following symbols represents a many to many relationship in an E-R model? A. a straight line B. a dashed line C. a straight line with a crow's foot at both ends D. A straight line with a crow's foot at one end.

C. a straight line with a crow's foot at both ends

Syntax 4 Transaction control (TC) Save or undo data value modifications

COMMIT, ROLLBACK

Syntax 3 Data definition language (DDL) Define data structures

CREATE, ALTER, DROP

Hands on Assignment 1. Which tables and field would you access to determine which book titles have been purchased by a customer and when the order shipped.

CUSTOMERS: Customer#; ORDERS: Order#, Shipdate, Customer#; ORDERITEMS: Order#, ISBN; BOOKS: ISBN, Title

Discuss the problems that can be caused by data redundancy.

Can create data anomalies or inconsistencies in the data, making it unreliable.

8. Which of the following has no partial or transitive dependencies A. unnormalized data B. 1NF C. 2NF D. 3NF

D. 3NF

1. Which of the following represents a row in a table? A. an attribute B. a characteristic C. a field D. a record

D. A Record

9. Which of the following symbols represents a one to many relationship in an E-R model? A. a straight line B. a dashed line C. a straight line with a crow's foot at both ends D. a straight line with a crow's foot at one end.

D. A straight line with a crow's foot at one end

16. Which of the following, when used in an E-R model, indicates the need for an additional table? a. sometimes to always relationship b. one to one relationship c. one to many relationship d. many to many relationship

D. Many to Many relationship

3. An entity is represented in an E-R model as a(n): A. arrow B. crow's foot C. dashed line D. none of the above.

D. None of the above

Hands on assignment 4. Explain how you would determine how much profit was generated from orders placed in April 2009

Determine the amount of profit generated by each book on an order item (Paideach-Cost), multiply the profit for each book by the quantity purchased and then total the amount of profit generated by all orders placed in April.

Give an example of three entities that might exist in a database for a medical office and list some attributes that would be stored in a table for each entitity.

Doctor: name, address, Social Security Number, medical ID number; Patient: name, address, Social Security Number, insurance policy information, medical history; Appointment: date, time, patient, doctor.

List the steps in the normalization process

First, a primary key is identified and any repeating groups are identified (1NF). Second, any partial dependencies are eliminated (2NF). Third, any transitive dependencies are eliminated (3NF).

syntax 5 Data control language (DCL)

GRANT, REVOKE

Syntax 2 Data manipulation language (DML) Create or modify data values

INSERT, UPDATE, DELETE

Hands on assignments 2. How would you determine which orders have not yet been shipped to the customer?

Identify all orders that don't have an entry for the date shipped.

Describe how a foreign key is different from a primary key.

A foreign key is used to reference or join data in different tables. In most cases, the foreign key references a primary key in another table. In a one-to-many relationship, the foreign key is stored in the "many" entity.

What type of relationship can't be stored in a relational database? Why?

A many-to-many relationship can't be stored in a database because there would be no way to restructure or rejoin the data correctly.

7. Which of the following defines a relationship in which each occurrence of data in one entity is represented by only one occurrence of data in the other entity? A. one to one B. one to many C. many to many D. none of the above

A. One to one

6. Which of the following can contain repeating groups? A. Unnormalized data B. 1NF C. 2NF D. 3NF

A. Unnormalized data

13. Which of the following fields also serves as a primary key in another table when two tables are joined together on that value? A. foreign key B. primary key c. turn key D. repeating group key

A. foreign key

4. Which of the following is not an E-R model relationship? A. some to many B. one to one C. one to many D. many to many

A. some to many

Hands on assignment 5. If a customer inquired about a book written in 2003 by an author name Thompson, which access path ( tables and fields) would you need to follow to find the list of books meeting the customer's request?

AUTHOR: Lname, AuthorID; BOOKAUTHOR: AuthorID, ISBN; BOOKS: ISBN, Pubdata

What is an entity?

An entity is any person, place, or thing having attributes, or characteristics, of interest to the organization.

Define a one to many relationship.

An occurrence of data in one entity can result in zero, one, or many occurrences of the data in the other entity. Zero (or no) related records can occur only in optional relationships.

Syntax 1 Query Retrieve data values

SELECT

Explain the role of a primary key

The primary key is used to uniquely identify each row in a table.

What is the purpose of an E-R model?

To identify the entities about which the database should store data and the relationships among those entities.

19.. When part of the data in a table depends on a field in the table that isn't the table primary key, it's known as: A. transitive dependency B. partial dependency C. psychological dependency D. a foreign key

a. transitive property

14. A unique identifitier for a data row that consists of more than one field is commonly called a A. Primary plus key B. Composite primary key c. Foreign key d. None of the above

b. composiste primary key

17. Which of the following represents a field in a table? A. a record B. a row C. a column D. an entity

c. a column

20. Which of the following is used to join data contained in two or more tables? a. primary key b. unique identifier c. common field d. foreign key

c. common field

Hands on Assignment 3. If management needed to determine which book category generated the most sales in April 2009, which tables and field would they consult to derive this information.

ORDERS: Orderdate, Order#; ORDERITEMS: Order#, ISBN, Quantity, Paideach; BOOKS: ISBN, Category

Identify at least three reasons an organization might analyze historical sales stored in its database.

. Answers will vary. To determine the necessary inventory levels to support sales fluctuations, to project employee-scheduling requirements, to determine appropriate marketing campaigns based on historic purchasing patterns, and so forth.