Description
| Course Code : | MCS-23 |
|---|---|
| Course Title : | Introduction to Database Management System |
| Assignment Number : | BCA(3)/023/Assignment/2019-20, MCA(2)/023/Assignment/19-20 |
| Maximum Marks : | 100 |
| Weightage : | 25% |
| Last Date of Submission : | 15th October, 2019 (for July 2019 session) 15th April, 2020 (for January 2020 session) |
| Solution Type : | Softcopy (PDF File) |
This assignment has six questions carrying a total of 80 marks. Answer all 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 presentation. Answer 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. Clearly indicate the entities, relationships, cardinality and the key constraints. The description of the environment is as follows:
The Institute offers 5 advanced courses of 3 months duration each. The Institute has 20faculty and can handle upto 40 trainees per batch. The training will be conducted batch wise. They can accommodate maximum 5 batches per day (2 batches in the pre-lunch session and 3 batches in the post-lunch session). The student can register upto 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 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 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 in 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 a 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:
ENROL (ENo, C_Id, Class) -ENo represents student number
TEACH (Prof, C_Id, Class) –C_Id represents course number
ADVISE (Prof, ENo) -Profis 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 has taken the pre-requisite course Pre_C_Id= 001.
Note: Make suitable assumptions, if any.
Q5. What are the advantages of indexed-sequential file organisation? With the help of an example explain the structure of 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, 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 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 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.
Reviews
There are no reviews yet.