MCS-043 ASSIGNMENT SOLUTION (2019-20)

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


Q1. Design a database for a university’s examination division which conducts examination, issue hall tickets to the students for appearing for TEE. It also issue grade cards to the successful candidates. It maintains data about (a) Programs (b) Study center (SC) (c) Regional center (RC) (d) Students (e) courses within a program.
The hall ticket should have student name, SC-code, RC code, Student id, Program code & course code and date of examination.
The grade card should have the following attributes Student id, Student name, program code, Course code and grade in each course.

(a) Draw the EER (extended ER) diagram for the above problem showing all entities, attributes and relationship. Also identify multivalued and derived attributes.

Answer : -




(b) Draw the appropriate tables and relationship among the tables for the above diagram and normalize the tables up to 3NF.

Answer : -

Table Name : regional_center

Field NameData TypeConstraintDescription
rc_codevarchar(10)Primary KeyUniquely identify each Regional Center in this table
rc_namevarchar(100)Name of the Regional Center
rc_addressvarchar(200)Address of the Regional Center
rc_phonebigintContact number of the Regional Center
no_of_study_centerintNumber of Study Center present under the Regional Center


Table Name : study_center

Field NameData TypeConstraintDescription
sc_codevarchar(10)Primary KeyUniquely identify each Study Center in this table
sc_namevarchar(100)Name of the Study Center
sc_addressvarchar(200)Address of the Study Center
sc_phonebigintContact number of the Study Center
rc_codevarchar(10)Foreign KeyRegional Center identification code


Table Name : program

Field NameData TypeConstraintDescription
p_codevarchar(10)Primary KeyUniquely identify each Program in this table
p_namevarchar(100)Name of the Program
total_feesintTotal fees require for the Program


Table Name : offer

Field NameData TypeConstraintDescription
rc_codevarchar(10)Foreign KeyRegional Center identification code
p_codevarchar(10)Foreign KeyProgram identification code


Table Name : teach

Field NameData TypeConstraintDescription
sc_codevarchar(10)Foreign KeyStudy Center identification code
p_codevarchar(10)Foreign KeyProgram identification code


Table Name : course

Field NameData TypeConstraintDescription
c_codevarchar(10)Primary KeyUniquely identify each Course/Subject in this table
c_namevarchar(100)Name of the Course
pointsintPoints of the Course


Table Name : contents

Field NameData TypeConstraintDescription
p_codevarchar(10)Foreign KeyProgram identification code
c_codevarchar(10)Foreign KeyCourse/Subject identification code


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)Present address of the Student
s_phonebigintContact number of the Student
sc_codevarchar(10)Foreign KeyStudy Center identification code
p_codevarchar(10)Foreign KeyProgram identification code


Table Name : part_time

Field NameData TypeConstraintDescription
s_codevarchar(10)Foreign KeyStudent identification code
no_of_classesintNumber of classes offer based on the Program taken by the student
class_durationintDuration of each class


Table Name : full_time

Field NameData TypeConstraintDescription
s_codevarchar(10)Foreign KeyStudent identification code
attendanceintAttendance of each Student


Table Name : exam_schedule

Field NameData TypeConstraintDescription
c_codevarchar(10)Foreign KeyCourse/Subject identification code
exam_datevarchar(10)Examination date
exam_timevarchar(10)Examination time


Table Name : hall_ticket

Field NameData TypeConstraintDescription
s_codevarchar(10)Foreign KeyStudent identification code
c_codevarchar(10)Foreign KeyCourse/Subject identification code
ec_codevarchar(10)Examination Center identification code


Table Name : grade_card

Field NameData TypeConstraintDescription
s_codevarchar(10)Foreign KeyStudent identification code
c_codevarchar(10)Foreign KeyCourse/Subject identification code
assignment_marksintAssignment Marks obtain by a student for a particular subject
theory_marksintTheory Marks obtain by a student for a particular subject
practical_marksintPractical Marks obtain by a student for a particular subject
statusvarchar(15)Indicate pass or fail status of a student for a particular subject




(c) Include generalization and aggregation features in the diagram and draw their tabular representations and explain.

Answer : -

Generalization

Generalization is like a bottom-up approach in which two or more entities of lower level combine to form a higher level entity if they have some attributes in common.

In the above EER-Diagram both part_time and full_time entities has some common attributes - s_code, s_name, s_address, s_phone, p_code and sc_code.


Aggregation

In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its corresponding entities is aggregated into a higher level entity.

Tabular Representations - Do it Yourself




(d) Identify weak entity sets in the above diagram if any. Show how will you convert a weak entity set to a strong entity set? What is the need of such task?

Answer : - weak entity set is an entity set that does not contain sufficient attributes to uniquely identify its entities. In other words, a primary key does not exist for a weak entity set.

In the above EER-Diagram exam_schedule representated as a weak entity, because no Primary Key is present in this table.
exam_schedule (c_code, exam_date, exam_time)

To convert the weak entity - exam_schedule into strong entity we need to add exam_code attribute into this, which uniquely identify each row (record) in this table.
exam_schedule (exam_code, c_code, exam_date, exam_time)




(e) Identify multivalued dependency in the above diagram if any. Justify.

Answer : - Multivalued dependency occurs when two attributes in a table are independent of each other but, both depend on a third attribute.

A multivalued dependency consists of at least two attributes that are dependent on a third attribute that's why it always requires at least three attributes.

In the above EER-Diagram multivalued dependency exist in exam_schedule entity.
exam_schedule (c_code, exam_date, exam_time)
Here in the exam_schedule entity, exam_date and exam_time attributes are independent of each other but, both depend on c_code attribute.




(f) Create an XML schema for the grade card to be issued by the division having details : student id, programme code, course id, grade, consumer name, assignments marks, TEE marks and grade.

Answer : -

GradeCard.xsd

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.questionsolves.com"
xmlns="http://www.questionsolves.com"
elementFormDefault="qualified">
<xs:element name="GradeCard">
<xs:complexType>
<xs:sequence>
<xs:element name="StudentId" type="xs:integer"/>
<xs:element name="StudentName" type="xs:string"/>
<xs:element name="ProgrammeCode" type="xs:string"/>
<xs:element name="Course" maxOccurs="unbounded"/>
<xs:complexType>
<xs:sequence>
<xs:element name="CourseCode" type="xs:string"/>
<xs:element name="AssignmentMarks" type="xs:integer"/>
<xs:element name="TEEMarks" type="xs:integer"/>
<xs:element name="Grade" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>


GradeCard.xml

<?xml version="1.0"?>
<GradeCard
xmlns="http://www.questionsolves.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.questionsolves.com GradeCard.xsd">
<StudentId>105508022</StudentId>
<StudentName>Debabrata Panchadhyay</StudentName>
<ProgrammeCode>MCA</ProgrammeCode>
<Course>
<CourseCode>MCS-042</CourseCode>
<AssignmentMarks>90</AssignmentMarks>
<TEEMarks>68</TEEMarks>
<Grade>B</Grade>
</Course>
<Course>
<CourseCode>MCS-043</CourseCode>
<AssignmentMarks>85</AssignmentMarks>
<TEEMarks>42</TEEMarks>
<Grade>D</Grade>
</Course>
</GradeCard>




Q2. What is the fundamental difference between XML document and relational database? How is XML data stored in RDBMS? Explain.

Answer : -

Difference between XML Document and Relational Database

Other factors might influence your decision about which model to use. Some of those factors are :


Convert Table Data into XML File

Consider the following tables present in the SQL Server database -
department(dept_id, dept_name)
employee(emp_id, emp_name, salary, dept_id)

Query
SELECT * FROM department INNER JOIN employee ON employee.dept_id=department.dept_id FOR XML AUTO, ELEMENTS;

Output :
<department>
<dept_id>102</dept_id>
<dept_name>HTML and Graphics</dept_name>
<employee>
<emp_id>E-0005</emp_id>
<emp_name>Subhadip Giri</emp_name>
<salary>45000</salary>
<dept_id>102</dept_id>
</employee>
</department>
<department>
<dept_id>105</dept_id>
<dept_name>DOT NET</dept_name>
<employee>
<emp_id>E-0020</emp_id>
<emp_name>Amit Das</emp_name>
<salary>65000</salary>
<dept_id>105</dept_id>
</employee>
<employee>
<emp_id>E-0085</emp_id>
<emp_name>Sohini Das</emp_name>
<salary>40000</salary>
<dept_id>105</dept_id>
</employee>
</department>


Restore department Table Data from XML File

--Setup a variable to take the file data
DECLARE @filedata XML

--Import the file contents into the variable
SELECT @filedata=BulkColumn FROM OPENROWSET(BULK 'D:\Test\Department.xml',SINGLE_BLOB) AS X

--Insert the xml data into our department table (dept_id, dept_name)
INSERT INTO department(dept_id, dept_name)
SELECT

--"data" is our xml content alias
data.value('dept_id[1]','int') AS dept_id,
data.value('dept_name[1]','varchar(20)') AS dept_name

--This is the xpath to the individual records we want to extract
FROM @filedata.nodes('/department') AS X(data);


Restore employee Table Data from XML File

--Setup a variable to take the file data
DECLARE @filedata XML

--Import the file contents into the variable
SELECT @filedata=BulkColumn FROM OPENROWSET(BULK 'D:\Test\Department.xml',SINGLE_BLOB) AS X

--Insert the xml data into our employee table (emp_id, emp_name, salary, dept_id)
INSERT INTO employee(emp_id, emp_name, salary, dept_id)
SELECT

--"data" is our xml content alias
data.value('emp_id[1]','varchar(10)') AS emp_id,
data.value('emp_name[1]','varchar(20)') AS emp_name,
data.value('salary[1]','int') AS salary,
data.value('dept_id[1]','int') AS dept_id

--This is the xpath to the individual records we want to extract
FROM @filedata.nodes('/department/employee') AS X(data);




Q3. Write an algorithm that checks whether to the concurrent transactions are in deadlock or not ?

Answer : -


Q4. What are views? What is their significance in DBMS? How are views created in SQL? Explain the concept with the help of an example pertaining to the design of University’s examination system (refer to Q1)

Answer : -

There are many advantages using view :


Syntax :

CREATE or REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name(s) WHERE condition;

A view can be dropped using a DROP statement as :
DROP VIEW view_name;


Example : COMING SOON




Q5. Discuss the algorithm and the related cost of performing Selection operation.

Answer : - The selection operation can be performed in a number of ways. Let us discuss the algorithms and the related cost of performing selection operations.




Q6. What is a timestamp? What is the use of timestamp protocols in distributed database? How does timestamp generation take place in distributed database?

Answer : - In a multiprogramming environment where multiple transactions can be executed simultaneously, it is highly important to control the concurrency of transactions. We have concurrency control protocols to ensure atomicity, isolation, and serializability of concurrent transactions. Concurrency control protocols can be broadly divided into two categories −


Timestamp Based Protocols

The most commonly used concurrency protocol is the timestamp based protocol. This protocol uses either system time or logical counter as a timestamp.

Lock-based protocols manage the order between the conflicting pairs among transactions at the time of execution, whereas timestamp-based protocols start working as soon as a transaction is created.


Timestamp Concurrency Control Algorithms

Timestamp-based concurrency control algorithms use a transaction’s timestamp to coordinate concurrent access to a data item to ensure serializability. A timestamp is a unique identifier given by DBMS to a transaction that represents the transaction’s start time.

These algorithms ensure that transactions commit in the order dictated by their timestamps. An older transaction should commit before a younger transaction, since the older transaction enters the system before the younger one.

Timestamp based ordering follow three rules to enforce serializability −


Optimistic Concurrency Control Algorithm

In systems with low conflict rates, the task of validating every transaction for serializability may lower performance. In these cases, the test for serializability is postponed to just before commit. Since the conflict rate is low, the probability of aborting transactions which are not serializable is also low. This approach is called optimistic concurrency control technique.

This algorithm uses three rules to enforce serializability -


Distributed Timestamp Concurrency Control

In a centralized system, timestamp of any transaction is determined by the physical clock reading. In a distributed system, the concept of clock reading cannot be used as they reading of the clock is not the same globally. Hence for a distributed system the timestamp of a transaction is identified by using the ID of the site along with the clock reading of that particular site.

For implementing timestamp ordering algorithms, each site has a scheduler that maintains a separate queue for each transaction manager. During transaction, a transaction manager sends a lock request to the site’s scheduler. The scheduler puts the request to the corresponding queue in increasing timestamp order. Requests are processed from the front of the queues in the order of their timestamps, i.e. the oldest first.


Distributed Optimistic Concurrency Control Algorithm

The extension of optimistic concurrency control algorithm is Distributed optimistic concurrency control algorithm, accompanied by the following rules -




Q7. How are implementations of triggers in Oracle different from the standard implementations ?

Answer : -


Q8. Explain multiple granularities with the help of an example. How is locking done in such a case ?

Answer : -

For example : Consider a tree which has four levels of nodes.

In this example, the highest level shows the entire database. The levels below are file, record, and fields.


Intention Mode Lock

In the 2-phase locking protocol, shared (S) and exclusive (X) lock modes are used, but there are three additional lock modes with multiple granularity :

ISIXSSIXX
IStruetruetruetruefalse
IXtruetruefalsefalsefalse
Struefalsetruefalsefalse
SIXtruefalsefalsefalsefalse
Xfalsefalsefalsefalsefalse

The multiple-granularity locking protocol uses the intention lock modes to ensure serializability. It requires that a transaction Ti that attempts to lock a node must follow these protocols :

Observe that in multiple-granularity, the locks are acquired in top-down order, and locks must be released in bottom-up order.

Note that transactions T1, T3 and T4 can access the database concurrently. Transaction T2 can execute concurrently with T1, but not with either T3 or T4.




Q9. What are the characteristics of multimedia & mobile databases? Explain the design challenges of these database?

Answer : -

Multimedia Databases

A Multimedia database (MMDB) is a collection of related for multimedia data. The multimedia data include one or more primary media data types such as text, images, graphic objects (including drawings, sketches and illustrations), animation sequences, audio and video. These data types are broadly categorized into three classes:

Contents of Multimedia Database Management System (MMDBMS)

Challenges of Multimedia Database

  1. Multimedia databases contains data in a large type of formats such as .txt(text), .jpg(images), .avi(videos), .mp3(audio) etc. It is difficult to convert one type of data format to another.
  2. Multimedia database consume a lot of processing time, as well as bandwidth.
  3. The data size of multimedia is large such as video; therefore, multimedia data often require a large storage.


Mobile Databases

Mobile computing devices (example - smartphones and PDAs) store and share data over a mobile network, or a database which is actually stored by the mobile device. This could be a list of contacts, price information, distance travelled, or any other information.

Many applications require the ability to download information from an information repository and operate on this information even when out of range or disconnected. An example of this is your contacts and calendar on the phone. In this scenario, a user would require access to update information from files in the home directories on a server or customer records from a database. This type of access and work load generated by such users is different from the traditional workloads seen in client–server systems of today.

Mobile users must be able to work without a network connection due to poor or even non-existent connections. A cache could be maintained to hold recently accessed data and transactions so that they are not lost due to connection failure. Users might not require access to truly live data, only recently modified data, and uploading of changing might be deferred until reconnected.


Challenges of Mobile Database System




Q10. What is assertion rule mining? Write apriority algorithm for finding frequency item set. Discuss it with suitable examples.

Answer : -


Q11. Draw a simple Use case and a class diagram for a university’s examination system.

Answer : -





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