Birla Institute of Technology (BIT Mesra) 2007 B.E DATA PROCESSING - Question Paper
BIRLA INSTITUTE OF TECHNOLOGY AND SCIENCE, PILANI
FIRST SEMESTER, 2007-2008
AAOC C311 DATA PROCESSING
COMPREHENSIVE exam
PART A(CLOSED BOOK)
MAXIMUM DURATION: 60 Mins
MAXIMUM MARKS: 45 06/12/2007 FN
NOTE:
1. Each ques. carries 3 marks
2. Write all answers in a separate ans sheet given.
3. Write the answers to the point. Verbose answers will not be credited.
4. You can collect PART B after submitting PART A ans sheet.
Q1) Consider the subsequent E-R diagram.
Composed_of
If an object is contained in several objects and if an object is composed of several objects
Write down the cardinality constraint for the Composed_of relation and convert the above E-R diagram to relational schema.
Q2) Suppose a table consists of only 3 columns that are not multivalued and 2 of the columns form a primary key. What is the highest possible normal form of the table (1st or second or 3rd). State the cause.
Q3) Consider the subsequent table ‘EMPLOYEE’. Using the coding technique in physical database design, replace the table design to save the storage apace.
EmpId EmpName Gender
1 Krishna Male
2 Malviya Male
3 Meera Female
4 Dina Tarini Female
Q4) Compare the sequential, indexed and hashed file organizations with respect to deleting a record in a file.
Q5) Describe the term location transparency and mention what is needed to ensure this in distributed databases.
Q6) Mention the differences ranging from homogenous and heterogeneous distributed databases.
Q7) List 3 common issues of ineffective data administration.
Q8) Mention any 3 data integrity controls that are supported by DBMS.
Q9) What is a stored procedure and mention any 2 advantages of using it?
Q10) Mention 3 differences ranging from file server and database server architectures?
Q11) Consider the relation R(A,B,C). The functional dependencies are A,B->C, C->B. Convert R into BCNF relations.
Q12) Mention 2 differences ranging from conceptual schema and logical schema.
Q13) What does the term data independence mean? Why is it an important goal?
Q14) Mention the 4 important properties of transactions that have to be ensured by DBMS to maintain data in the face of concurrent access and system failures and briefly discuss any two?
Q15) Identify and discuss the anomaly associated with the subsequent schedule with respect to concurrency control?
T1 T2
R(X)
X=X-N
W(X)
R(X)
X=X+X*M
W(X)
R(Y)
Y=Y+Y*M
W(Y)
R(Y)
Y=Y+N
W(Y)
Commit
Commit
End of PART A
BIRLA INSTITUTE OF TECHNOLOGY AND SCIENCE, PILANI
FIRST SEMESTER, 2007-2008
AAOC C311 DATA PROCESSING
COMPREHENSIVE exam
PART B(CLOSED BOOK)
MAXIMUM DURATION: 120 Mins
MAXIMUM MARKS: 75 06/12/2007 FN
NOTE:
5. Start every ques. on a fresh page
6. All parts of every ques. must be answered together in 1 place
#Question 1
The activities of a coach company need to be organized as a database. An initial analysis of the requirements produced the subsequent facts:
• The company has different branches, for every of which the branch name and address is maintained.
• For every employee of the company, name, town of birth, date of birth and national insurance number are maintained.
• If the employee is a driver, the number of driving license, and the number of life policy should be also maintained.
• For every vehicle, plate number and number of seats are maintained. Drivers are allocated 1 specific vehicle to be used for 1 specific route. 1 driver may work on several routes, and 1 vehicle may be used for several routes. every vehicle must have a route and driver allocated. For every route, there are several stops, for every of which a progressive number and the name of the town are recorded. Also for every route, distance, starting station, ending station, and route name is recorded
• Drivers must have permission to work on specific routes that cross across borders (e.g. going abroad).
• Some employees serve as conductors for particular routes. For every conductor, amount collected by him so far is stored. 1 conductor may be allocated many routes. A driver cannot be a conductor and vice-versa.
• Conductors and drivers are allocated to routes on a turn-taking basis. For every turn, date of beginning and date of end is kept.
• Employees may work for 1 branch only. Branch should have atleast 1 employee.
• Vehicles are also used by 1 branch only. Branch can have many vehicles.
ans the subsequent ques. for the above specification. (15+10 = 25 M)
(i) Draw an Entity-Relationship diagram for the above specification. Clearly indicate the cardinality constraints.
(ii) Convert your Entity-Relationship diagram to relational schema.
#Question 2
Consider the subsequent table “StockAnalysis”. It contains the history of the % of amount increase with respect to its face value for a particular month and year, calling it NAV and say the fund name is ‘JINDAL STEEL’ .(4+4+4+4+4 = 20 M)
StockAnalysis (year : Number(4), month : Number(2), NAV : Number(7,2))
Now ans the subsequent queries using SQL:
(i) List the year(s) for which the avg. NAV is more than the total avg. NAV found for all years.
(ii) List all the years and the month(s) of that year for which the NAV found was maximum. Use subquery only .
(iii) List all those years for which record for 1 or more months is missing.
(iv) Create a copy of the above table naming it ‘StockBackup’ using a suitable SQL.
(v) List all those years in which the percent increase of stock amount is at lowest 100% in atleast 2 months.
#Question 3
Consider the schema provided in Question#2. (12+ five + three = 20 M)
(i) NAV value for the next month is predicted by computing weighted avg. of the NAV of last n latest months. The most latest month is provided the largest weight. The weight for last n months is distributed using the formula Wi= i / ((n * n+1)/2) where i represents the month number when n months are numbered from lowest latest to most latest. NAV for next month is predicted as ?Wi * NAVi where i ranges from one to n. Write a function predict_NAV which takes n as input and provide predicted NAV for next month. Handle the case of not having n months in the table by returning -2 using exception no_sufficient_months. For any other error, function returns -1.
(ii) Assume that there is a different table ‘ALERTS’ which stores the year and month during which the NAV score was either the highest or the least up to that time. Write a trigger NAV_alert which upon inserting or updating a row in StockAnalaysis checks for the alert. If the record is suitable for alert, it inserts a record in the ALERTS table.
Alerts(year : Number(4), month : Number(2), High_Low Char(1))
(iii) Write a trigger validate_record which prevents the record to be entered into StockAnalysis table if the year is not the current year and month is not the current month. presume that functions get_current_year() and get_current_month() are available.
#Question 4
Consider the subsequent Schedule of the transactions: (3 + five + two = 10 M)
T1 T2 T3 T4
Read(A)
Read(A)
Write(A)
Write(A)
Read(B)
Read(B)
Write(A)
Read(A)
Write(C)
Commit
Commit
Commit
Commit
(i) Determine whether the provided schedule is serializable using precedence graph.
(ii) Add lock and unlock instructions, so that they observe the strict 2 phase locking protocol.
(iii) Whether the schedule resulted from (ii) is dead lock free? Justify.
End of PART B
BIRLA INSTITUTE OF TECHNOLOGY AND SCIENCE, PILANI
FIRST SEMESTER, 2007-2008
AAOC C311 DATA PROCESSING
COMPREHENSIVE EXAMINATION
PART A(CLOSED BOOK)
MAXIMUM DURATION: 60 Mins
MAXIMUM MARKS: 45 06/12/2007 FN
NOTE:
- Each question carries three marks
- Write all answers in a separate answer sheet provided.
- Write the answers to the point. Verbose answers will not be credited.
- You can collect PART B after submitting PART A answer sheet.
Q1) Consider the following E-R diagram.
Composed_of
If an object is contained in several objects and if an object is composed of several objects
Write down the cardinality constraint for the Composed_of relation and convert the above E-R diagram to relational schema.
Q2) Suppose a table consists of only three columns that are not multivalued and two of the columns form a primary key. What is the highest possible normal form of the table (1st or 2nd or 3rd). State the reason.
Q3) Consider the following table EMPLOYEE. Using the coding technique in physical database design, modify the table design to save the storage apace.
|
EmpId |
EmpName |
Gender |
|
1 |
Krishna |
Male |
|
2 |
Malviya |
Male |
|
3 |
Meera |
Female |
|
4 |
Dina Tarini |
Female |
Q4) Compare the sequential, indexed and hashed file organizations with respect to deleting a record in a file.
Q5) Describe the term location transparency and mention what is needed to ensure this in distributed databases.
Q6) Mention the differences between homogenous and heterogeneous distributed databases.
Q7) List three common problems of ineffective data administration.
Q8) Mention any three data integrity controls that are supported by DBMS.
Q9) What is a stored procedure and mention any two advantages of using it?
Q10) Mention three differences between file server and database server architectures?
Q11) Consider the relation R(A,B,C). The functional dependencies are A,B->C, C->B. Convert R into BCNF relations.
Q12) Mention two differences between conceptual schema and logical schema.
Q13) What does the term data independence mean? Why is it an important goal?
Q14) Mention the four important properties of transactions that have to be ensured by DBMS to maintain data in the face of concurrent access and system failures and briefly explain any two?
Q15) Identify and explain the anomaly associated with the following schedule with respect to concurrency control?
|
T1 |
T2 |
|
R(X) |
|
|
X=X-N |
|
|
W(X) |
|
|
|
R(X) |
|
|
X=X+X*M |
|
|
W(X) |
|
|
R(Y) |
|
|
Y=Y+Y*M |
|
|
W(Y) |
|
R(Y) |
|
|
Y=Y+N |
|
|
W(Y) |
|
|
Commit |
|
|
|
Commit |
End of PART A
BIRLA INSTITUTE OF TECHNOLOGY AND SCIENCE, PILANI
FIRST SEMESTER, 2007-2008
AAOC C311 DATA PROCESSING
COMPREHENSIVE EXAMINATION
PART B(CLOSED BOOK)
MAXIMUM DURATION: 120 Mins
MAXIMUM MARKS: 75 06/12/2007 FN
NOTE:
- Start each question on a fresh page
- All parts of each question must be answered together in one place
#Question 1
The activities of a coach company need to be organized as a database. An initial analysis of the requirements produced the following facts:
- The company has various branches, for each of which the branch name and address is maintained.
- For each employee of the company, name, town of birth, date of birth and national insurance number are maintained.
- If the employee is a driver, the number of driving license, and the number of life policy should be also maintained.
- For each vehicle, plate number and number of seats are maintained. Drivers are allocated one specific vehicle to be used for one specific route. One driver may work on several routes, and one vehicle may be used for several routes. Each vehicle must have a route and driver allocated. For each route, there are several stops, for each of which a progressive number and the name of the town are recorded. Also for each route, distance, starting station, ending station, and route name is recorded
- Drivers must have permission to work on specific routes that cross across borders (e.g. going abroad).
- Some employees serve as conductors for particular routes. For each conductor, amount collected by him so far is stored. One conductor may be allocated many routes. A driver cannot be a conductor and vice-versa.
- Conductors and drivers are allocated to routes on a turn-taking basis. For each turn, date of beginning and date of end is kept.
- Employees may work for one branch only. Branch should have atleast one employee.
- Vehicles are also used by one branch only. Branch can have many vehicles.
Answer the following questions for the above specification. (15+10 = 25 M)
(i) Draw an Entity-Relationship diagram for the above specification. Clearly indicate the cardinality constraints.
(ii) Convert your Entity-Relationship diagram to relational schema.
#Question 2
Consider the following table StockAnalysis. It contains the history of the % of amount increase with respect to its face value for a particular month and year, calling it NAV and say the fund name is JINDAL STEEL .(4+4+4+4+4 = 20 M)
StockAnalysis (year : Number(4), month : Number(2), NAV : Number(7,2))
Now answer the following queries using SQL:
(i) List the year(s) for which the average NAV is more than the total average NAV obtained for all years.
(ii) List all the years and the month(s) of that year for which the NAV obtained was maximum. Use subquery only .
(iii) List all those years for which record for one or more months is missing.
(iv) Create a copy of the above table naming it StockBackup using a suitable SQL.
(v) List all those years in which the percent increase of stock amount is at least 100% in atleast two months.
#Question 3
Consider the schema given in Question#2. (12+ 5 + 3 = 20 M)
(i) NAV value for the next month is predicted by computing weighted average of the NAV of last n recent months. The most recent month is given the largest weight. The weight for last n months is distributed using the formula Wi= i / ((n * n+1)/2) where i represents the month number when n months are numbered from least recent to most recent. NAV for next month is predicted as ∑Wi * NAVi where i ranges from 1 to n. Write a function predict_NAV which takes n as input and gives predicted NAV for next month. Handle the case of not having n months in the table by returning -2 using exception no_sufficient_months. For any other error, function returns -1.
(ii) Assume that there is another table ALERTS which stores the year and month during which the NAV score was either the highest or the lowest up to that time. Write a trigger NAV_alert which upon inserting or updating a row in StockAnalaysis checks for the alert. If the record is suitable for alert, it inserts a record in the ALERTS table.
Alerts(year : Number(4), month : Number(2), High_Low Char(1))
(iii) Write a trigger validate_record which prevents the record to be entered into StockAnalysis table if the year is not the current year and month is not the current month. Assume that functions get_current_year() and get_current_month() are available.
#Question 4
Consider the following Schedule of the transactions: (3 + 5 + 2 = 10 M)
|
T1 |
T2 |
T3 |
T4 |
|
|
Read(A) |
|
|
|
|
|
Read(A) |
|
|
|
Write(A) |
|
|
|
|
|
|
|
|
|
|
Write(A) |
|
|
|
|
|
|
|
Read(B) |
|
|
|
|
|
|
|
Read(B) |
|
|
|
|
Write(A) |
|
|
|
|
|
|
Read(A) |
|
|
|
|
Write(C) |
|
|
|
|
Commit |
|
|
|
|
|
Commit |
|
|
|
|
|
Commit |
|
|
|
|
|
Commit |
(i) Determine whether the given schedule is serializable using precedence graph.
(ii) Add lock and unlock instructions, so that they observe the strict two phase locking protocol.
(iii) Whether the schedule resulted from (ii) is dead lock free? Justify.
End of PART B
| Earning: Approval pending. |
