INSS 651



INSS 651 Final EXAM

Fall 2007 Data Base Management System

MUST BE DONE INDIVIDUALLY

READ THE INSTRUCTIONS CAREFULLY!!!!!!!!

NO LATE SUBMISSION!!!!!!!

DUE DATE: May 14, 2007

Note:

• This is an EXAM; I can NOT answer any questions. Please do not ask for any help.

• email me for any general clarifications

• Exam is to be done individually

• Honesty and integrity is expected

• Do not submit anything else.

Before submitting delete up to this point

EVIDENCE FORM

I have read UB’s policy on cheating and plagiarism (see UB’s website for more information). I have done all work myself with no HELP from anybody. Please put your name in the line below to certify the above statement.

___________________

Last First

Total points 50

Ques #1. You are given following enrollment information for June 2006- Oct 2006 for education_r_, an online university. (10 points)

|campus/Month |June |July |Aug |Sep |Oct |

|Campus 1 |4590 |3500 |3400 |4990 |5250 |

|Campus 2 |332 |311 |321 |340 |398 |

|Campus 3 |612 |666 |664 |620 |554 |

|Campus 4 |445 |411 |434 |480 |500 |

Sample enrollment information about each campus for the month of June is shown below:

|Campus/Department |Law |Business |Engineering |Arts |

|Campus 1 |1000 |2590 |1000 |0 |

|Campus 2 |0 |332 |0 |0 |

|Campus 3 |0 |100 |0 |512 |

|Campus 4 |200 |200 |0 |45 |

a. Education_r_us would like to develop a data warehouse. Draw the star schema for Education_r_us. Clearly identify fact table, dimension tables, PK and FK.

b. given the following cube, identify the slice (color it red or any other color) that will give you total enrollment for July 2006 for campus 2 (there is an extra cube on the last page also) [pic]

Ques#2.The following table structure contains many unsatisfactory components and characteristics. (For example, there are several multi-valued attributes, some naming conventions are violated, some attributes are not atomic, and so on.)

(6 points)

Attribute name Sample value

EMP_CODE 1003

LAST_NAME Willaker

EDUCATION HS, BBA, MBA

DEPT_CODE MKTG

DEPARTMENT Marketing

DEPT_MANAGER Jill H. Martin

JOB_CLASS 23

TITLE Sales agent

DEPENDENTS Gerald (spouse), Mary (daughter)

BIRTH_DATE 12/23/65

HIRE_DATE 10/14/94

TRAINING Level 1

BASE_SALARY $32,255

NOTE: shaded highlights imply repeating groups, also

Job_CLASS ---( BASE_SALARY

Given this structure:

Convert it into 3Nf

1stNF

2nd NF

3rd NF

Draw an ERD

Ques#3

Given the following table:

Assign_HRS (Emp_name, Project_ID, Task_ID, Phone, Total-Hours)

An employee can work on many projects and a project can have many tasks

(10 points)

Emp_Name Project_ID Task_ID Phone Total-hours

Don 100A B-1 12345 12

Don 100A P-1 12345 12

Don 200B B-1 12345 12

Don 200B P-1 12345 12

Pam 100A C-1 67890 26

Pam 200A C-1 67890 26

Pam 200D C-1 67890 26

Answer the following: (each part is independent)

a. What is the normal form of the ASSIGN_HRS table? Justify your answer

b. assuming total-hours are total hours an employee works, i.e. Don works a total of 12 hours irrespective of the project or task

Based on above assumption, convert the above table in 3NF

c. Assuming that total_hours are the hours worked by an employee on a given PROJECT, i.e. Don worked 12 hours on PROJECT 100A and 12 hours on Project 200B

Based on assumption in part c, convert the original ASSIGN_HRS table in 3NF

d. Assuming total-hours are the number of hours worked by an employee on a given task for a given project, i.e. Don worked 12 hours on task B-1 Project 100A, worked 12 hours on task P-1 on Project 100A etc..

Based on assumption in part d, convert the original ASSIGN_HRS table in 3NF

Ques#4.

Go to the following Conference web site, an international organization for IS people



Answer the following. (stay focused on the web site ONLY, do not go to any hyperlinks.

Identify objects and draw OODM

===================================================

(8 points)

Ques#5. The following question is based on the DDBMS scenario shown below

(10 points)

Note: both sites A & C have replicated CUSTOMER data; PRODUCT data is partitioned between two sites A, B.

Specify the minimum type(s) of operation(s) the database must support (local request, remote request, remote transaction, distributed transaction, or distributed request) in order to perform the following operations:

At Site C:

a. SELECT CUS_NUM, CUS_NAME, INV_TOTAL

FROM CUSTOMER, INVOICE

WHERE CUSTOMER.CUS_NUM = INVOICE.CUS_NUM;

b. SELECT * FROM

CUSTOMER;

At Site B:

c. BEGIN WORK;

UPDATE CUSTOMER

SET CUS_BALANCE = CUS_BALANCE + 100

WHERE CUS_NUM='10936';

INSERT INTO INVOICE(INV_NUM, CUS_NUM, INV_DATE, INV_TOTAL)

VALUES ('986391', '10936', ‘15-FEB-2002’, 100);

INSERT INTO INVLINE(INV_NUM, PROD_CODE, LINE_PRICE)

VALUES ('986391', '1023', 100);

COMMIT WORK;

d. SELECT * FROM PRODUCT WHERE PROD_QOH ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download