MCS-043 ASSIGNMENT SOLUTION (2020-21)

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


Q1. Consider a small institute in which students register through on-line mode for programmes run by the institute. A student can be a full time or part time . A program can be offered through a normal mode ( face to face) or on-line. Every student necessarily registers in at least one programme and at most two programmes. After completion of a program, students are issued a certificate. Assuming suitable attributes, design an EER Diagram for the same.

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

Answer : -




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

Answer : -

Table Name : course

Field NameData TypeConstraintDescription
course_codevarchar(10)Primary KeyUniquely identify each Course/Subject in this table
course_namevarchar(50)Name of the Subject

Table Name : professor

Field NameData TypeConstraintDescription
professor_idvarchar(10)Primary KeyUniquely identify each Professor in this table
p_namevarchar(50)Professor name
p_addressvarchar(100)Professor address
p_phonebigintProfessor phone number

Table Name : teach

Field NameData TypeConstraintDescription
course_codevarchar(10)Forign KeyCourse identification code
professor_idvarchar(10)Forign KeyProfessor identification number

Table Name : programme

Field NameData TypeConstraintDescription
p_codevarchar(10)Primary KeyUniquely identify each Programme in this table
programme_namevarchar(10)Name of the programme
total_feesintTotal fees require for the programme

Table Name : contents

Field NameData TypeConstraintDescription
p_codevarchar(10)Forign KeyProgramme identification code
course_codevarchar(10)Forign KeyCourse identification code

Table Name : class

Field NameData TypeConstraintDescription
c_codevarchar(20)Primary KeyUniquely identify each Class in this table
c_datedateClass date
start_timetimeClass starting time
end_timetimeClass ending time
course_codevarchar(10)Forign KeyCourse identification code
professor_idvarchar(10)Forign KeyProfessor identification number

Table Name : online_class

Field NameData TypeConstraintDescription
c_codevarchar(20)Foreign KeyClass identification code
login_urlvarchar(100)Login URL of the class
login_codevarchar(50)Login code of the class

Table Name : offline_class

Field NameData TypeConstraintDescription
c_codevarchar(10)Foreign KeyClass identification number
room_novarchar(10)Room number

Table Name : student

Field NameData TypeConstraintDescription
student_idvarchar(10)Primary KeyUniquely identify each Student in this table
s_namevarchar(50)Student name
s_addressvarchar(100)Student address
s_phonebigintStudent phone number

Table Name : part_time

Field NameData TypeConstraintDescription
student_idvarchar(10)Foreign KeyStudent identification number
total_no_of_classesintNumber of classes attend by the student

Table Name : full_time

Field NameData TypeConstraintDescription
student_idvarchar(10)Foreign KeyStudent identification number
percentage_of_attendanceintPercentage of attendance

Table Name : register

Field NameData TypeConstraintDescription
p_codevarchar(10)Foreign KeyProgramme identification code
student_idvarchar(10)Foreign KeyStudent identification number

Table Name : attendance

Field NameData TypeConstraintDescription
c_codevarchar(20)Foreign KeyClass identification code
student_idvarchar(10)Foreign KeyStudent identification number

Table Name : exam_schedule

Field NameData TypeConstraintDescription
course_codevarchar(10)Foreign KeyCourse identification code
exam_datedateExamination date
exam_timetimeExamination start time
durationintExam duration in minutes

Table Name : result

Field NameData TypeConstraintDescription
course_codevarchar(10)Foreign KeyCourse identification code
student_idvarchar(10)Foreign KeyStudent identification number
marksintObtain marks
gradevarchar(4)Obtain grade




Q1. c) Include generalization and aggregation features in the diagram, draw their tabular representations and UML class diagram. Explain

Answer : - Coming Soon




Q1. 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 : - An entity type should have a key attribute which uniquely identifies each entity in the entity set, but there exists some entity type for which key attribute can’t be defined. These are called Weak Entity type.

The entity sets which do not have sufficient attributes to form a primary key are known as weak entity sets and the entity sets which have a primary key are known as strong entity sets.

In the above diagram exam_schedule is the week entity.
exam_schedule(course_code, exam_date, exam_time, duration)

We can convert any weak entity set to a strong entity set by simply adding appropriates attributes.
exam_schedule(exam_code, course_code, exam_date, exam_time, duration)




Q1. e) Identify multi-valued dependency in the above diagram if any. Justify.

Answer : - No multi-value dependency present in the above diagram




Q1. f) Create an XML schema for the grade card to be issued by the division having details: student id, programme code, course id, grade, program delivery mode, assignments marks, TEE marks.

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="ProgrammeCode" type="xs:string"/>
<xs:element name="Course" maxOccurs="unbounded"/>
<xs:complexType>
<xs:sequence>
<xs:element name="CourseID" 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>




Q2. What are XML databases? What are the benefits of using XML? What are its applications? Explain.

Answer : - Coming Soon




Q3. Define cursors, stored procedures and triggers and write SQL syntaxes for each. What are the advantages and disadvantages of stored procedures? What are the main features of a cursor?

Answer : -

Cursor - A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set

Syntax :

CURSOR cursor_name IS select_statement;

cursor_name – A suitable name for the cursor.
select_statement – A select query which returns multiple rows

Example

Copy data from "old_accounts" table to "new_accounts" table

Old Table - old_accounts (account_number, customer_id, branch_id, balance)
New Table - new_accounts (account_number, customer_id, balance)

CREATE OR REPLACE PROCEDURE copy_table AS
account_row old_accounts%ROWTYPE;
CURSOR account_cursor IS SELECT * FROM old_accounts;
BEGIN
OPEN account_cursor;
LOOP
FETCH account_cursor INTO account_row;
EXIT WHEN account_cursor%NOTFOUND;
INSER INTO new_accounts VALUES(account_row.account_number, account_row.customer_id, account_row.balance);
END LOOP;
CLOSE account_cursor;
COMMIT;
END;
/


Stored Procedures - A procedure is a module that performs one or more actions.

Syntax :

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [ IN | OUT | IN OUT ] type , parameter_name [ IN | OUT | IN OUT ] type , . . . . . )] { IS | AS }
BEGIN
< procedure_body >
END;
/

Example - Create a procedure to transfer amount from account A to account B.

account (account_number, customer_id, branch_id, balance)

CREATE OR REPLACE PROCEDURE amount_transfer
(debit_account IN NUMBER, credit_account IN NUMBER, amount IN NUMBER) AS
BEGIN
UPDATE account SET balance=balance-amount WHERE account_number=debit_account;
UPDATE account SET balance=balance+amount WHERE account_number=credit_account;
COMMIT;
END;
/


Triggers - Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

Syntax :

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT [OR] | UPDATE [OR] | DELETE } [ OF col_name ] ON table_name
[ FOR EACH ROW ]
WHEN(condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
/

Example - Write a trigger for a bank which show the transaction details of an account when transaction is made from this account.

account (account_number, customer_id, branch_id, balance)

CREATE OR REPLACE TRIGGER transaction
AFTER UPDATE OF balance ON Account
FOR EACH ROW
WHEN (NEW.balance > 0)
DECLARE
amount NUMBER(10,2);
BEGIN
IF :NEW.balance > :OLD.balance THEN
amount := :NEW.balance - :OLD.balance;
dbms_output.put_line('Amount Credit : ' || amount);
ELSE
amount := :OLD.balance - :NEW.balance;
dbms_output.put_line('Amount Debit : ' || amount);
END IF;
dbms_output.put_line('Old Balance : ' || :OLD.balance);
dbms_output.put_line('New Balance : ' || :NEW.balance);
END;
/


Advantages of Stored Procedures


Disadvantages of Stored Procedures




Q4. Explain primary key, foreign key, super key and composite key with the help of an example for each key. Also write the important features of each.

Answer :

Primary Key - A primary key is a column (or columns) in a table that uniquely identifies the rows in that table.

Table Name - Employee

Employee_idEmployee_nameDepartment_id
E-0001Debabrata PanchadhyayD-001
E-0005Amit DasD-002
E-0008Sohini DasD-002
E-0010Debarghya ChakrabortyD-001

In the Employee table Employee_id is the primary key.

The value placed in primary key columns must be unique for each row : no duplicates can be tolerated. In addition, nulls are not allowed in primary key columns.


Foreign Key - Foreign keys are columns that point to primary key columns.

Table Name - Employee

Employee_idEmployee_nameDepartment_id
E-0001Debabrata PanchadhyayD-001
E-0005Amit DasD-002
E-0008Sohini DasD-002
E-0010Debarghya ChakrabortyD-001

Table Name - Department

Department_idDepartment_nameTotal_employee
D-001Website Devlopment50
D-002Software Devlopment25

Employee_id is the primary key of the table Employee and Department_id is the primary key of the table Department but in Employee table Department_id is the foreign key that points to the primary key in the Department table.


Super Key - A super key is a combination of all possible attribute which can uniquely identify the rows(tuples) in a table. This means that a superkey may have some extra attribute which isn't necessary for uniquely identifying the rows in the table.

Table Name - Employee

Employee_idEmployee_nameDepartment_id
E-0001Debabrata PanchadhyayD-001
E-0005Amit DasD-002
E-0008Sohini DasD-002
E-0010Debarghya ChakrabortyD-001

In the given Employee table we can have the following keys as the super key.
1. { Employee_id }
2. { Employee_id, Employee_name }
3. { Employee_id, Department_id }
4. { Employee_id, Employee_name, Department_id }


Composite Key - If any single attribute of a table is not capable of being the key i.e it cannot identify a row uniquely, then we combine two or more attributes to form a key. This is known as a composite key.

Table Name - Result

Student_idCourse_codeMarks
MCA-00001MCS-04172
MCA-00001MCS-04368
MCA-00002MCS-04175
MCA-00002MCS-04362

The Composite Key in Result table is { Student_id, Course_code }




Q5. What are the algorithms that can be used to implement JOIN operation? What is the criteria for selecting a particular join algorithm? Explain Hash Join algorithm and perform its cost calculation

Answer : - Coming Soon




Q6. What is concurrency control? What is the need of applying concurrency control methods in database? Give an example. Write the algorithm of timestamp based ordering protocol and explain through an example. What are the problems with this algorithm and what are the proposed solutions for it?

Answer : - Coming Soon




Q7. How is recovery with concurrent transactions performed? Discuss.

Answer : - Coming Soon




Q8. State the differences between classification and clustering. Write an algorithm of K-Means clustering. Explain the algorithm with the help of an example.

Answer : - Coming Soon




Q9. What are Semantic databases? Give the features of semantic databases. Discuss the process of searching the knowledge in these databases.

Answer : - The semantic data model is a method of structuring data in order to represent it in a specific logical way. It is a conceptual data model that includes semantic information that adds a basic meaning to the data and the relationships that lie between them. This approach to data modeling and data organization allows for the easy development of application programs and also for the easy maintenance of data consistency when data is updated.

In this data model the data is organized in such a way that it can be interpreted meaningfully without human intervention.

How Does Semantic Data Work ?
Data is organized based on binary models of objects, usually in groups of three parts: two objects and their relationship. For example, if one wanted to represent a cat sitting on a table, the data organization might look like this: CAT TABLE. The objects (cat and table) are interpreted with regard to their relationship (sitting on). The data is organized linearly, telling the software that since CAT comes first in the line, it is the object that acts. In other words, the position of the word tells the software that the cat is on the table and not that the table is sitting on the cat. Databases designed around this concept have greater applicability and are more easily integrated into other databases.

Goals of Semantic Data
Semantic data systems are designed to represent the real world as accurately as possible within the data set. Data symbols are organized linearly and hierarchically to give certain meanings like the one described above. By representing the real world within data sets, semantic data allow machines to interact with worldly information without human interpretation.

Applications of Semantic Data
Semantic data is very promising for the enterprise world. Database Management Systems can be integrated with one another and compared. For example, since a company's entire infrastructure is represented within the data model, the model can be compared to those of the company's vendors to identify areas of inconsistency and possible improvement. This would help streamline the relationship between company and vendors, making database sharing and integration much simpler. Environments and systems can also be organized graphically within a database to give a more visually-based representation of that system or environment. Recently, a semantic language called Gellish was developed as a formal language to represent data models. Gellish can be interpreted solely by computers and needs no human interaction.



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


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us