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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.