Course Code : MCS-023
Course Title : Introduction to Database Management Systems
Assignment Number : MCA (2)/023/Assign/2010
Maximum Marks : 100
Weightage : 25%
Last Date of Submission : 15th October, 2010 (for July, 2010 session)
15th April, 2011 (for January, 2011 session)
BCA MCA Bsc B tech CS information technology
final year project
This assignment has four questions, which carries 80 marks. Answer all questions. Rest 20 marks are for viva voce. You may use illustrations and diagrams to enhance explanations. Please go through the guidelines regarding assignments given in the Programme Guide for the format of presentation. Answer to each part of the question should be confined to about 300 words.
Question 1: (20 Marks)
(i) “For creating a student information management system of a University a database management system is better than that of file management system.” Justify the statement given above. Now, assume that you are assigned the role of Database Administrator for the University database. What are the key responsibilities you have to handle?
(ii) Draw an E R Diagram for a system having the following requirements:
A University maintains data of its students, the programmes they are registered in and the address information of the students. A programme consists of many courses. The database needs to store the programme duration and fees. A course has a number of credits associated with it and may be the part of more than one programmes. Some of the constraints that may be assumed for the University database system are:
• A student can take only one programme at a time.
• A course may be part of more than one programme.
• The duration of the programme is in semesters. A course is taught in a typical semester of the programme.
List all the entity sets, attributes of each entity sets and relationship sets. Draw the E-R diagram for the requirements as listed above for the database system. You may use the concept of keys, cardinality etc. in a proper way. Make and state suitable assumptions, if any.
(iii) Create the relations from the E-R diagram that you have drawn for part (ii). The relations must be at least in 2 NF. You must do the following with the relations
a) Enter about 5 sets of meaningful data in each of the relations
b) Identify the domain of various attributes
c) Identify the primary keys of all the relations
d) Identify the Foreign keys and referential integrity constraints in the relations
(iv) Perform the following tasks using relational algebraic operations for the relations created at part (iii):
(a) List all the courses of MCA programme.
(b) Find the student name, programme code and the programme duration of the programme in which s/he is registered.
(c) Find the list of students in BCA programme.
Question 2: (20 Marks)
(i) What are the problems associated with data Redundancy in a relation? How can you solve those problems? Can referential integrity constraints help in addressing those problems? Give reasons in support of your answer.
(ii) Consider the following employee record in an organisation
Employee ( ID, Name, date of birth, date of joining, age, address, department, manger, IDs of projects working on, role in the project, project name, project team leader, duration of project, dependent names)
An employee works in one department. Each department is managed by one manager. An employee can work on many projects. A project has a team leader. An employee can have many dependents, however, one dependent can be related to only one employee.
Identify the functional dependencies in the relation given above. Normalise the relational up to BCNF. Make suitable assumptions, if any
(iii) Compare and contrast the features of sequential, indexed and hashed file organisations. Consider the problem (ii) in this question; propose a suitable file organisation for each of the relation created. Justify your selection of file organisation.
(iv) Consider a relation Student(ID: 9 characters, name: 25 characters, department: 10 characters, programme_code: 4 characters) having about 1,000,000 student records. The database is stored on a disk having a disk block size of 1 MB. Assume that the primary index of the relation is ID and this relation is required mostly for the application that generates programme wise list of student names in alphabetical order. Create a secondary index that will improve the performance of the system for the given application. Show how many block transfers will be saved on average due to creation of index. Make suitable assumptions if any.
Question 3: (20 Marks)
(i) Consider the following relations
Account (ac_number, ac_holdername, ac_holderaddress, ac_balance)
withdrawal (ac_number, wi_type, wi_date, wi_amount)
deposit(ac_number, de_by, de_date, de_type, de_amount)
Type of deposit or withdrawal may be – Cash or Cheque
Perform the following operations on these tables using SQL
a) Create the three tables giving suitable domains and constraints including referential actions.
b) Add one additional filed ac_type (it can be savings account, current account or student account) in the Accounts table. Create a secondary index on ac_number for the withdrawal and deposit tables.
c) Create a view named my_account that contains the information about my account.
d) Give only READ only permission to see my_account view to a user.
(ii) Perform the following queries using SQL for the relations of part (i):
a) Find the list of the account holders whose account balance is less than Rs.1000/-
b) Find the details of those account holders who have withdrawn more than Rs 1,00,000/- in the last month.
c) Find the sum of deposits that has been credited in each account in the last year.
d) Find an account holder who has not withdrawn any money in the last year.
(iii) Write the psedocode for the Withdrawal and Deposit transactions for the schema of the bank given in this question. Use two phase locking protocol and lock the data items suitably such that there is no concurrency related problem in the database.
(iv) What is a deadlock? How can you detect deadlock? Explain with the help of a Wait for graph. What are the possibilities that the deposit and withdrawal transactions as created in part (iii) will result into deadlock? Explain your answer.
Question 4: (20 Marks)
(i) Consider the database schema of Question 3. Create sample transaction log for the transactions that you have defined in Q3 part (iii). Explain how this log may be used to recover from failure.
(ii) Consider the database schema of Question 3, suggest at least two different types of users for the system. You may use my_account view for one of the users. Create an authorisation matrix for the relations. Make suitable assumptions, if any.
(iii) Now, assume that the bank schema as given in Question 3 is being implemented using distributed database. Suggest suitable data fragmentation needed for this distributed database.
(iv) Compare and contrast the two tier model to that of 3 tier model of client server architecture. Can you implement the database given in question 3 as a 3 tier model? Explain your answer.
Course Title : Introduction to Database Management Systems
Assignment Number : MCA (2)/023/Assign/2010
Maximum Marks : 100
Weightage : 25%
Last Date of Submission : 15th October, 2010 (for July, 2010 session)
15th April, 2011 (for January, 2011 session)
BCA MCA Bsc B tech CS information technology
final year project
This assignment has four questions, which carries 80 marks. Answer all questions. Rest 20 marks are for viva voce. You may use illustrations and diagrams to enhance explanations. Please go through the guidelines regarding assignments given in the Programme Guide for the format of presentation. Answer to each part of the question should be confined to about 300 words.
Question 1: (20 Marks)
(i) “For creating a student information management system of a University a database management system is better than that of file management system.” Justify the statement given above. Now, assume that you are assigned the role of Database Administrator for the University database. What are the key responsibilities you have to handle?
(ii) Draw an E R Diagram for a system having the following requirements:
A University maintains data of its students, the programmes they are registered in and the address information of the students. A programme consists of many courses. The database needs to store the programme duration and fees. A course has a number of credits associated with it and may be the part of more than one programmes. Some of the constraints that may be assumed for the University database system are:
• A student can take only one programme at a time.
• A course may be part of more than one programme.
• The duration of the programme is in semesters. A course is taught in a typical semester of the programme.
List all the entity sets, attributes of each entity sets and relationship sets. Draw the E-R diagram for the requirements as listed above for the database system. You may use the concept of keys, cardinality etc. in a proper way. Make and state suitable assumptions, if any.
(iii) Create the relations from the E-R diagram that you have drawn for part (ii). The relations must be at least in 2 NF. You must do the following with the relations
a) Enter about 5 sets of meaningful data in each of the relations
b) Identify the domain of various attributes
c) Identify the primary keys of all the relations
d) Identify the Foreign keys and referential integrity constraints in the relations
(iv) Perform the following tasks using relational algebraic operations for the relations created at part (iii):
(a) List all the courses of MCA programme.
(b) Find the student name, programme code and the programme duration of the programme in which s/he is registered.
(c) Find the list of students in BCA programme.
Question 2: (20 Marks)
(i) What are the problems associated with data Redundancy in a relation? How can you solve those problems? Can referential integrity constraints help in addressing those problems? Give reasons in support of your answer.
(ii) Consider the following employee record in an organisation
Employee ( ID, Name, date of birth, date of joining, age, address, department, manger, IDs of projects working on, role in the project, project name, project team leader, duration of project, dependent names)
An employee works in one department. Each department is managed by one manager. An employee can work on many projects. A project has a team leader. An employee can have many dependents, however, one dependent can be related to only one employee.
Identify the functional dependencies in the relation given above. Normalise the relational up to BCNF. Make suitable assumptions, if any
(iii) Compare and contrast the features of sequential, indexed and hashed file organisations. Consider the problem (ii) in this question; propose a suitable file organisation for each of the relation created. Justify your selection of file organisation.
(iv) Consider a relation Student(ID: 9 characters, name: 25 characters, department: 10 characters, programme_code: 4 characters) having about 1,000,000 student records. The database is stored on a disk having a disk block size of 1 MB. Assume that the primary index of the relation is ID and this relation is required mostly for the application that generates programme wise list of student names in alphabetical order. Create a secondary index that will improve the performance of the system for the given application. Show how many block transfers will be saved on average due to creation of index. Make suitable assumptions if any.
Question 3: (20 Marks)
(i) Consider the following relations
Account (ac_number, ac_holdername, ac_holderaddress, ac_balance)
withdrawal (ac_number, wi_type, wi_date, wi_amount)
deposit(ac_number, de_by, de_date, de_type, de_amount)
Type of deposit or withdrawal may be – Cash or Cheque
Perform the following operations on these tables using SQL
a) Create the three tables giving suitable domains and constraints including referential actions.
b) Add one additional filed ac_type (it can be savings account, current account or student account) in the Accounts table. Create a secondary index on ac_number for the withdrawal and deposit tables.
c) Create a view named my_account that contains the information about my account.
d) Give only READ only permission to see my_account view to a user.
(ii) Perform the following queries using SQL for the relations of part (i):
a) Find the list of the account holders whose account balance is less than Rs.1000/-
b) Find the details of those account holders who have withdrawn more than Rs 1,00,000/- in the last month.
c) Find the sum of deposits that has been credited in each account in the last year.
d) Find an account holder who has not withdrawn any money in the last year.
(iii) Write the psedocode for the Withdrawal and Deposit transactions for the schema of the bank given in this question. Use two phase locking protocol and lock the data items suitably such that there is no concurrency related problem in the database.
(iv) What is a deadlock? How can you detect deadlock? Explain with the help of a Wait for graph. What are the possibilities that the deposit and withdrawal transactions as created in part (iii) will result into deadlock? Explain your answer.
Question 4: (20 Marks)
(i) Consider the database schema of Question 3. Create sample transaction log for the transactions that you have defined in Q3 part (iii). Explain how this log may be used to recover from failure.
(ii) Consider the database schema of Question 3, suggest at least two different types of users for the system. You may use my_account view for one of the users. Create an authorisation matrix for the relations. Make suitable assumptions, if any.
(iii) Now, assume that the bank schema as given in Question 3 is being implemented using distributed database. Suggest suitable data fragmentation needed for this distributed database.
(iv) Compare and contrast the two tier model to that of 3 tier model of client server architecture. Can you implement the database given in question 3 as a 3 tier model? Explain your answer.
No comments:
Post a Comment