| Course Code | MCS-023 |
| Course Title | Introduction to Database Management Systems |
| Assignment Number | BCA(3)/023/Assignment/2019-20 |
| Maximum Marks | 100 |
| Weightage | 25% |
| Last Dates for Submission | 15th October 2019 (For July 2019 Session) |
| 15th April 2020 (For January 2020 Session) |
This assignment has six questions carrying a total of 80 marks. Answer all the questions. Rest 20 marks are for viva-voce. You may use illustrations and diagrams to enhance your explanations. Please go through the guidelines regarding assignments given in the Programme Guide for the format of the presentation. Answers to each part of the question should be confined to about 300 words.
Q1.
Design an ER diagram for an ABC IT Training Institute that will meet the training needs for individuals and employees of corporate offices. Indicate the entities, relationships, cardinality, and key constraints. The description of the environment is as follows: The Institute offers 5 advanced courses of 3 months duration each. The Institute has 20 faculty and can handle up to 40 trainees per batch. The training will be conducted batch-wise. They can accommodate a maximum of 5 batches per day (2 batches in the pre-lunch session and 3 batches in the post-lunch session). The student can register up to 2 courses simultaneously. Training consists of theory and practicals. Theory and practicals are scheduled on alternate days. Each batch is assigned a faculty member who takes theory sessions as well as practical sessions. Sunday is a holiday for everyone. A test will be conducted per course every week to continuously evaluate the performance of the student. The question paper will be set by the faculty concerned whoever is teaching the batch. The result/grade will be declared at the end of the third month after conducting a course-end exam.
Q2.
Design the Relational Schema for the E-R diagram that you have drawn for part Question 1. The relations must be at least 2 NF. Perform the following on 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.
Q3.
(a) “For creating an Employee Management Information System of an Organisation a database management system(DBMS) is better or a file management system.” Justify the statement given above.
(b) Assume that you are assigned the role of Database Administrator for the Organisation database, mention the key responsibilities you have to handle?
Q4.
Given the relational schema:
Enroll (Eno, C_Id, Class) – Eno represents student number TEACH (Prof, C_Id, Class) – C_Id represents course number ADVISE (Prof, Eno) – Prof is project guide of Eno (Student’s enrol_no) PRE_REQ (C_Id, Pre_C_Id) – Pre_C_Id is prerequisite course GRADES (Eno, C_Id, Grade, Year) STUDENT (Eno, SName) – SName is student name
Write SQL statements for the following :
(i) List all students whose project guide is Prof.Murthy. (ii) List the grade for the student with ENo=1234 (iii) List those professors who teach more than one class.
(iv) List all the student names and Eno’s who got Grade A in the year 2018 in C_Id= 100.
(v) List all the students who have taken the pre-requisite course Pre_C_Id= 001.
Note: Make suitable assumptions, if any.
Q5.
What are the advantages of an indexed-sequential file organization? With the help of an example explain the structure of the indexed-sequential file.
Q6.
a) 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.
b) Consider the following employee record in an organization:
Employee ( ID, Name, date of birth, date of joining, age, address, department, manager, IDs of projects working on, role in the project, project name, project team leader, duration of the 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. Normalize the relation up to BCNF. Make suitable assumptions, if any.
c) 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 program 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 the creation of the index. Make suitable assumptions if any.