MCS-023 ASSIGNMENT SOLUTION (2019-20)

If you have any queries please leave a message here
Your Message
×


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 20 faculty and can handle up to 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 up to 2 courses simultaneously. Training consists of theory and practical. Theory and practical 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.

Answer : -




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.

Answer : - Solve it Yourself

b) Identify the domain of various attributes.

Answer : -

Table Name : course

Field NameData TypeConstraintDescription
c_codevarchar(10)Primary KeyUniquely identify each course in this table
c_namevarchar(100)Course name
course_feesintTotal course fee


Table Name : faculty

Field NameData TypeConstraintDescription
f_codevarchar(10)Primary KeyUniquely identify each faculty in this table
f_namevarchar(100)Name of the faculty
f_addressvarchar(200)Current address of the faculty
f_phonebigintContact number of the faculty
f_qualificationvarchar(100)Educational qualification/degree of the faculty


Table Name : student

Field NameData TypeConstraintDescription
s_codevarchar(10)Primary KeyUniquely identify each student in this table
s_namevarchar(100)Name of the student
s_addressvarchar(200)Current address of the student
s_phonebigintContact number of the student


Table Name : teach

Field NameData TypeConstraintDescription
s_codevarchar(10)Foreign KeyStudent identification code
c_codevarchar(10)Foreign KeyCourse identification code
f_codevarchar(10)Foreign KeyFaculty identification code


Table Name : test_schedule

Field NameData TypeConstraintDescription
exam_codevarchar(10)Primary KeyUniquely identify each examination in this table
week_numberintRepresent week number on which week the examination was done
full_marksintFull marks of the examination paper
c_codevarchar(10)Foreign KeyCourse identification code


Table Name : test_result

Field NameData TypeConstraintDescription
s_codevarchar(10)Foreign KeyStudent identification code
exam_codevarchar(10)Foreign KeyExamination identification code
gradevarchar(2)


Table Name : classes

Field NameData TypeConstraintDescription
class_codevarchar(10)Primary KeyUniquely identify each class in this table
durationintClass duration
class_typevarchar(15)Define class type : theory, practical, examination
date_and_timedatetimeDate and time of the class
c_codevarchar(10)Foreign KeyCourse identification code
f_codevarchar(10)Foreign KeyFaculty identification code




c) Identify the Primary keys of all the relations.

Answer : - A primary key is a column in a table that uniquely identifies the rows in that table.

In the above ER-Diagram there are 5 Primary Key :




d) Identify the Foreign keys and referential integrity constraints in the relations.

Answer : - In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables. In simple words you can say that, a foreign key in one table used to point primary key in another table.

In the Referential integrity constraints, if a foreign key in Table-1 refers to the Primary Key of Table-2, then every value of the Foreign Key in Table-1 must be null or be available in Table-2.




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.

Answer : - According to me for creating a Employee Management Information System of an Organisation a Database Management System(DBMS) is better then a File Management System.

Drawbacks of File Management System


Advantage of DBMS over File Management System




Q3. b) Assume that you are assigned the role of Database Administrator for the Organisation database, mention the key responsibilities you have to handle?

Answer : - A database administrator’s (DBA) primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed.

Functions/Roles of a Database Administrator (DBA) -

  1. Deciding the Hardware Device - Depending upon the cost, performance and efficiency of the hardware, it is DBA who have the duty of deciding which hardware device will suit the company requirement. It is hardware that is an interface between end users and database so it needed to be of best quality.

  2. Software installation and Maintenance - DBA is responsible for installing the database software. He configure the software of database and then upgrades it if needed. There are many database software like oracle, Microsoft SQL, MySQL etc. in the industry so DBA decides how the installing and configuring of these database software will take place.

    If a new server is needed, the DBA handles the transfer of data from the existing system to the new platform.

  3. Managing Data Integrity - Data integrity should be managed accurately because it protects the data from unauthorized use. DBA manages relationship between the data to maintain data consistency.

  4. Database Backup and Recovery - If any company is having a big database, then it is likely to happen that database may fail at any instance. It is require that a DBA takes backup of entire database in regular time span. DBA has to decide that how much data should be backed up and how frequently the backup should be taken. Also the recovery of data base is done by DBA if they have lost the database.

  5. Tuning Database Performance - Database performance plays an important role for any business. If user is not able to fetch data speedily then it may loss company business. So by tuning an modifying sql commands a DBA can improves the performance of database.

  6. Capacity Planning - All the databases have their limits of storing data in it and the physical memory also has some limitations. DBA has to decide the limit and capacity of database and all the issues related to it.

  7. Database Accessibility - DBA writes subschema to decide the accessibility of database. He decides the users of the database and also which data is to be used by which user. No user has to power to access the entire database without the permission of DBA.

  8. Security - A DBA needs to know potential weaknesses of the database software and the company’s overall system and work to minimise risks. No system is one hundred percent immune to attacks, but implementing best practices can minimise risks.

    In the case of a security breach or irregularity, the DBA can consult audit logs to see who has done what to the data. Audit trails are also important when working with regulated data.

  9. Decides Validation Checks on Data - DBA has to decide which data should be used and what kind of data is accurate for the company. So he always puts validation checks on data to make it more accurate and consistence.

  10. Performance Monitoring - Monitoring databases for performance issues is part of the on-going system maintenance a DBA performs. If some part of the system is slowing down processing, the DBA may need to make configuration changes to the software or add additional hardware capacity.




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) - 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.

Answer : - SELECT STUDENT.ENo, SName FROM STUDENT, ADVISE WHERE STUDENT.ENo=ADVISE.ENo AND Prof='Murthy';

ii) List the grade for the student with ENo=1234

Answer : - SELECT C_Id, Grade FROM GRADES WHERE ENo=1234;

iii) List those professors who teach more than one class.

Answer : - SELECT DISTINCT T1.Prof FROM TEACH AS T1 WHERE (SELECT COUNT(T2.Class) FROM TEACH AS T2 WHERE T1.Prof=T2.Prof) > 1;

iv) List all the student names and ENo’s who got Grade A in the year 2018 in C_Id= 100.

Answer : - SELECT STUDENT.ENo, SName FROM STUDENT, GRADES WHERE STUDENT.ENo=GRADES.ENo AND Grade='A' AND Year=2018 AND C_Id=100;

v) List all the students who has taken the pre-requisite course Pre_C_Id= 001.

Answer : - SELECT STUDENT.ENo, SName FROM STUDENT, ENROL, PRE_REQ WHERE STUDENT.ENo=ENROL.ENo AND ENROL.C_Id=PRE_REQ.C_Id AND Pre_C_Id=001;




Q5. What are the advantages of indexed-sequential file organisation? With the help of an example explain the structure of indexed-sequential file.

Answer : - In indexed-sequential file organisation records are stored in order of primary key in the file. Using the primary key, the records are sorted. For each primary key, an index value is generated and mapped with the record. This index is nothing but the address of record in the file.

In this method, if any record has to be retrieved, based on its index value, the data block address is fetched and the record is retrieved from memory.

Advantages of Indexed-Sequential File Organisation




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.

Answer : - Redundancy means having multiple copies of same data in the database. This problem arises when a database is not normalized.

Consider a table office with attributes emp_id, emp_name, salary, dept_id, dept_name

emp_idemp_namesalarydept_iddept_name
E-1001Amit Das45000D-102PHP
E-1002Sohini Das30000D-102PHP
E-1008Swarup Paul20000D-104JAVA
E-1020Ronit Das40000D-104JAVA
E-1025Somraj Dey25000D-104JAVA
E-1028Ankita Das20000D-105ORACLE
E-1045Pran Banik18000D-104JAVA
E-1050Arnab Roy15000D-102PHP

As it can be observed that values of attribute dept_id, dept_name is being repeated which can lead to problems. Problems caused due to redundancy are : Insertion anomaly, Deletion anomaly, and Updation anomaly.

Insert Anomalies - An insert anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes.
For example - We can not add any department unless we have atlast one employee.

Delete Anomalies - A delete anomalies exists when certain attributes are lost because of the deletion of other attributes.
For example - If we delete the tuple which contain the department-id "D-105", we lost all information about the department-id "D-105". Because in this table it is the only one tuple which contain all information about this department-id.

Update Anomalies - An update anomaly exists when one or more instances of duplicated data is updated but not all.
For example - Cosider "JSP" is the new name respect to the department-name "JAVA". You need to update all tuples which contain the department-name "JAVA".

Normalization is the process of splitting relations into well structured relations that allow users to insert, delete and update tuples without introducing database inconsistencies.

Anomaly problem can be remove from the table office, if we break this into two tables -
employee (emp_id, emp_name, salary, dept_id)
department (dept_id, dept_name)

Table Name : employee

emp_idemp_namesalarydept_id
E-1001Amit Das45000D-102
E-1002Sohini Das30000D-102
E-1008Swarup Paul20000D-104
E-1020Ronit Das40000D-104
E-1025Somraj Dey25000D-104
E-1028Ankita Das20000D-105
E-1045Pran Banik18000D-104
E-1050Arnab Roy15000D-102

Table Name : department
dept_iddept_name
D-102PHP
D-104JAVA
D-105ORACLE




Q6. 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.

Answer : -

employee (employee_id, employee_name, date_of_birth, joining_date, age, address, department, manager, project_id, role_in_the_project, project_name, project_team_leader, project_duration, dependent_name)

Functional Dependencies

employee_id → employee_name, date_of_birth, joining_date, age, address, department
department → manager
project_id → project_name, project_team_leader, project_duration
employee_id, project_id → role_in_the_project
dependent_name → employee_id

Table Structure

employee_details (employee_id, employee_name, date_of_birth, joining_date, age, address, department)
department_details (department, manager)
project_details (project_id, project_name, project_team_leader, project_duration)
employee_role (employee_id, project_id, role_in_the_project)
employee_dependent(dependent_name, employee_id)




Q6. 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.

Answer : - Solve it Yourself



ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us