DBST651 9040 Midterm exam part 1 and 2 (fall 2014)

MIDTERM PART I

1. Which of the following is a characteristic of databases?

A) Many users can use the same database at the same time

B) Different database entities can be connected to provide more information

C) Relevant business data are stored in a database

D) All of the above

2. A(n) ____ enables a database administrator to define schema components.

A) extensible markup language (XML)

B) data definition language (DDL)

C) unified modeling language (UML)

D) query language

Use the following to answer questions 4 - 5:

A database table consists of the following columns: employee's name, SS#, job-title, hiring-date, years-in-job, base-salary. A bonus calculation program, a personnel promotion application program, and a payroll application program all use this table. To implement a policy change in personnel promotion, the following column is added to the table: highest-college-degree. This new column is not used by the payroll program or by the bonus calculation program.

3. The following application program will be modified to accommodate the change to the database table:

A) The bonus calculation program

B) The personnel promotion application program

C) The payroll application program

D) All of the above

4. The ability to add the new column without requiring changes to all of the application programs strongly relates to the following feature of modern database systems:

A) Nonprocedural programming

B) Transaction processing

C) Data independence

D) Data sharing

5. Entity Relationship Diagrams are input to the following phase of Database Development:

A) Logical Database Design

B) Conceptual Data Modeling

C) Physical Database Design

D) Distributed Database Design

6. Advantages of a DBMS are:

A) Improved data sharing and data security

B) Better data integration

C) Minimized data inconsistency and increase end-user productivity

D) Improved data access and decision making

E) All of the above

7. In a relational table the order of the rows and columns are immaterial to the DBMS

True False

Use the following to answer Questions 8-11

The following data are in the tables Employee (primary key: Empno) and Department (primary key: Deptno). The questions refer to row numbers of this table. The first row of each table is row #1. Empdeptno in table Employee is a foreign key that refers to Deptno in the table Department. Deptmgr in the table Department is a foreign key that refers to Empno in the table Employee.

8. Which of the following statements is true?

A. Row #7 of table Employee violates neither the entity integrity rule nor the referential integrity rule.
B. Row #7 of table Employee violates the entity integrity rule.
C. Row #7 of table Employee violates the referential integrity rule.
D. Row #7 of table Employee violates both the entity integrity rule and the referential integrity rule.

9. An inner join only returns matched records from the tables that are being joined.

True False

10. Which of the following columns of the table Department is a foreign key?

A. Deptno
B. Deptname
C. Deptcity
D. Deptmgr

11. The values shown in the table Department:

A. violate neither the entity integrity rule nor the referential integrity rule
B. violate the entity integrity rule
C. violate the referential integrity rule
D. violates both the entity integrity rule and the referential integrity rule

12. In the relational model, ____ are important because they are used to ensure that each row in a table is uniquely identifiable.

A) Relations

B) Indexes

C) Keys

D) Logical structures

13. Entity supertype Contains unique characteristics of each entity subtype

True False

14. The ____ is not supported directly in the relational environment.

A) M:N

B) 1:M

C) M:1

D) 1:1

15. A ____ occurs when you have one entity in two 1:M relationships to other entities, thus producing an association among the other entities that is not expressed in the model.

A) Surrogate primary keys

B) time-variant data

C) Design trap

D) Fan trap

16. The following step occurs first in the process of building an ERD: ____.

A) Develop the initial ERD.

B) Identify the business rules based on the description of operations.

C) Identify the attributes and primary keys that adequately describe the entities.

D) Create a detailed narrative of the organization’s description of operations.

17. When the PK of one entity does not contain the PK of a related entity, the relationship is ____.

A) Weak

B) Strong

C) Neutral

D) Missing

18. In Crow's Foot notation, the symbol

Denotes one or many; the “many” side is optional

True False

19. Entities corresponds to table and not to row in relational environment

True False

20. The ________________ key is an attribute or a combination of attributes in one table whose values must either match the primary key in another table or be be null

A) Super Key

B) Candidate Key

C) Primary Key

D) Foreign Key

E) Secondary Key

21. The three stage process in natural joins are:

A) Product, Union, Project

B) Product, Select, Intersect

C) Product, Select, Project

22. Composite attribute can be subdivided

True False

23. In a supertype subtype relationship

A) Subtype entities inherit attributes and relationships of supertype

B) All entity subtypes inherit their primary key attribute from their supertype

C) Entity subtypes inherit all relationships in which supertype entity participates

D) All of the above.

24. Characteristics of Primary Key include:

a. Must have unique values and should be intelligent

b. Should be nonintelligent and not change over time

c. Must not change over time and composed of several attribute

25. Third Normal Form is often as high as needed in normalization

True False

MIDTERM PART II

Name: Date:

Question 1:

The table shown below lists dentist-patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a specific surgery for that day.

StaffNo	Dentist Name	PatNo	PatName	Appointment Date & Time	SurgeryNo
S1011 S1011 S1024 S1024 S1032 S1032	James Bond James Bond Andrew Joe Andrew Joe Kevin King Kevin King	P100 P105 P108 P108 P107 P110	Kim Su Jenny Gold Ian Chappell Ian Chappell John Spooner Frank Holey	1/9/05 10.00 1/9/05 12.00 1/9/05 10.00 2/7/05 14.00 2/7/05 16.30 2/7/05 18.00	S10 S15 S10 S10 S15 S13

Evaluate if the table shown above is normalized, if not describe and illustrate the process of normalizing the table shown above to third normal form. Show your work in table format along with step by step explanations of how you got from to 1NF, 2NF, and 3NF.State any assumptions you make about the data shown in this table. (20 Points)

Question 2:

Please provide answers to problems 10 through 16 in Chapter 3 of Coronel book after reading the problem. (Page 99-100) (15 Points)

Question 3:

Please provide answers to Questions (located under the Review Question in our textbook) 7-9 in chapter 5 of Coronel book after reading the problem. (Page 172) (15 Points)