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. Note these are NOT simple T/F answers
Circle the appropriate answer. GIVE REASONS or EXPLANATIONS for correct answers and if the answer is false explain why and then indicate the correct answer: It is NOT a simple T/F question each question requires T/F explanation
(10 points)
T F 1 A data warehouse is a snapshot of an organization at a particular time
T F 2. Update CUSTOMER
Set cust_name=”SMITH”
Where cust_order IN (select cust_ord from ORDER
where order_name = 20);
will require a shared lock on both Customer and Order tables.
T F 3. Derived data will always create dependency and the relation will not be in the
third normal form.
T F 4. Given the following Relation
R ( B, A, C, D) (B) is the PK and following dependency exist (B,A) ----(D
Is the relation in 2nd NF?
T F 5. Cardinality of following would be
Assuming a club must have at least one student and a student must belong to a club
STUDENT CLUB
(0,1) (1,N)
Ques#2. You have just accepted a job as a CIO (Chief Information Officer) of WHOrWE, a distribution company. On your first week on the job you discover that database users are entering one common password to enter to log on to the database each morning when they arrive for work. You also learned employees leave their workstations connected to the database all day, even when they are away from their machines for an extended period of time.
(4 points)
Answer each part separately
a. Should you be concerned? WHY?
b. What would you do in this situation?
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 (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.