MCS-043 ASSIGNMENT SOLUTION (2018-19)

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


Question 1 :

Consider a small training institute in which students register for skilled based program offered by the institute. A program can be full or a part-time or both. Every student necessarily registers in at least one program and at most four programmers. Faculty also can be full time or part time or both. A faculty can conduct not more than 3 courses per semester.

(a) Draw the EER (extended ER) diagram for the above organization showing all entities, relationship, aggregation, generalization /specialization and convert it into relational schemas

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 : Student

Field NameData TypeKey ConstraintsDescription
sidvarchar(15)Primary KeyStudent Identification Number
snamevarchar(50)Student Name
no_of_register_programintTotal number of program in which the student is registered


Table Name : Program

Field NameData TypeKey ConstraintsDescription
pcodevarchar(15)Primary KeyProgram Identification Code
pnamevarchar(50)Program Name
no_of_semesterintTotal number of semester present in the program


Table Name : FullTimeProgram

Field NameData TypeKey ConstraintsDescription
pcodevarchar(15)Foreign KeyProgram Identification Code
start_timeTimeEveryday's first class starting time
room_nointClass-room Number


Table Name : PartTimeProgram

Field NameData TypeKey ConstraintsDescription
pcodevarchar(15)Foreign KeyProgram Identification Code
start_timeTimeEveryday's first class starting time
durationintTotal time taken per day
room_nointClass-room Number


Table Name : Courses

Field NameData TypeKey ConstraintsDescription
ccodevarchar(15)Primary KeyCourse/Subject Identification Code
cnamevarchar(50)Course/Subject Name
semesterintUnder which semester the given Course/Subject is fallen
pcodevarchar(15)Foreign KeyProgram Identification Code


Table Name : Faculty

Field NameData TypeKey ConstraintsDescription
fidvarchar(15)Primary KeyFaculty Identification Number
fnamevarchar(50)Faculty Name
degreevarchar(25)Education Qualification/Degree


Table Name : FullTimeFaculty

Field NameData TypeKey ConstraintsDescription
fidvarchar(15)Foreign KeyFaculty Identification Number
salaryintMonthly Salary


Table Name : PartTimeFaculty

Field NameData TypeKey ConstraintsDescription
fidvarchar(15)Foreign KeyFaculty Identification Number
hour_rateintRate per hour


Table Name : Register

Field NameData TypeKey ConstraintsDescription
sidvarchar(15)Foreign KeyStudent Identification Number
pcodevarchar(15)Foreign KeyProgram Identification Code
program_categoryvarchar(12)Part-time or Full-time Program


Table Name : Teach

Field NameData TypeKey ConstraintsDescription
fidvarchar(15)Foreign KeyFaculty Identification Number
ccodevarchar(15)Foreign KeyCourse/Subject Identification Code
faculty_categoryvarchar(12)Part-time or Full-time Faculty




(c) Identify weak entity sets in the above diagram if any. How will you convert a weak entity set to a strong entity set? Illustrate.

Answer : - There are no weak entity present in the diagram.




(d) Identify multivalued dependency in the above diagram.

Answer : - multi-value dependency present in the tables - "Register" and "Teach"

A multi-value dependency exists when

In the table "Register" for each value of "sid" there is a well-defined set of values for "pcode" and a well-defined set of values for "program_category".
Therefore, sid → → pcode and sid → → program_category

Same as, in the table "Teach" for each value of "fid" there is a well-defined set of values for "ccode" and a well-defined set of values for "faculty_category".
Therefore, fid → → ccode and fid → → faculty_category




Question 2 :

Create an XML schema for the list of the courses to be offered in the second semester of MCA and their details (like, consumer code, consumer name, number of credits, assignments makes TEE marks)

Answer : -

SecondSemester.dtd
<!ELEMENT MCA (Semester)>
<!ELEMENT Semester (SemesterNo, Course+)>
<!ELEMENT SemesterNo (#PCDATA)>
<!ELEMENT Course (CourseCode, CourseName, Credits, AssignmentMarks, TEEMarks)>
<!ELEMENT CourseCode (#PCDATA)>
<!ELEMENT CourseName (#PCDATA)>
<!ELEMENT Credits (#PCDATA)>
<!ELEMENT AssignmentMarks (#PCDATA)>
<!ELEMENT TEEMarks (#PCDATA)>

Courses.xml
<?xml version="1.0" ?>
<!DOCTYPE MCA SYSTEM "SecondSemester.dtd">
<MCA>
<Semester>
<SemesterNo>2</SemesterNo>
<Course>
<CourseCode>MCS-021</CourseCode>
<CourseName>Data and File Structures</CourseName>
<Credits>4</Credits>
<AssignmentMarks>100</AssignmentMarks>
<TEEMarks>100</TEEMarks>
</Course>
<Course>
<CourseCode>MCS-022</CourseCode>
<CourseName>Operating System Concepts and Networking Management</CourseName>
<Credits>4</Credits>
<AssignmentMarks>100</AssignmentMarks>
<TEEMarks>100</TEEMarks>
</Course>
<Course>
<CourseCode>MCS-023</CourseCode>
<CourseName>Introduction to Database Management Systems</CourseName>
<Credits>3</Credits>
<AssignmentMarks>100</AssignmentMarks>
<TEEMarks>100</TEEMarks>
</Course>
<Course>
<CourseCode>MCS-024</CourseCode>
<CourseName>Object Oriented Technologies and Java Programming</CourseName>
<Credits>3</Credits>
<AssignmentMarks>100</AssignmentMarks>
<TEEMarks>100</TEEMarks>
</Course>
<Course>
<CourseCode>MCSL-025</CourseCode>
<CourseName>Lab Course </CourseName>
<Credits>4</Credits>
<AssignmentMarks>100</AssignmentMarks>
<TEEMarks>100</TEEMarks>
</Course>
</Semester>
</MCA>




Question 3 :

How will you enforce referential integrity constraints in Oracle? Explain with the help of an example?

Answer : - Referential integrity requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in one table to another table must be valid.

In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key.

Consider a bank database, which contains two tables :
CUSTOMER (CUSTOMER_ID, NAME, ADDRESS, PHONE)
ACCOUNTS (ACCOUNT_NO, ACCOUNT_TYPE, ACCOUNT_BALANCE, CUSTOMER_ID)

To uniquely identify each customer in the CUSTOMER table, a primary key column named CUSTOMER_ID is created.

To identify a customer and bank account relationship in the ACCOUNTS table, an existing customer in the CUSTOMER table must be referenced. Thus, the CUSTOMER_ID column – also created in the ACCOUNTS table – is a foreign key.This column is special because its values are not newly created. Rather, these values must reference existing and identical values in the primary key column of another table, which is the CUSTOMER_ID column of the CUSTOMER table.

Referential integrity is a standard that means any CUSTOMER_ID value in the CUSTOMER table may not be edited without editing the corresponding value in the ACCOUNTS table.


Question 4 :

Draw a simple Use Case diagram for a library system and explain?

Answer : -




Question 5 :

(i) What are triggers and cursors and their uses? Explain with the help of an example for each.

Answer : - 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
[ REFERENCING OLD AS o NEW AS n ]
[ 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;
/


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

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;
/




(ii) Write a trigger that restricts modifying an employee table beyond 2 hrs overtime per day.

Answer : -




Question 7 :

What are the parameters for measuring cost for performing a query. Discuss the algorithm and the related cost of performing selection operation?

Answer : - Information about relations and attributes :

Selection Operation

σA=a(R) where a is a constant value, A an attribute of R

Selection Operation (cont.)

S2 – Binary search, i.e., the file ordered based on attribute A(primary index)

cost(S2) = [log2(BR)] + [SC(A, R)/FR] - 1

[log2(BR)] = cost to locate the first tuple using binary search

Second term = blocks that contain records satisfying the selection.

If A is primary key, then SC(A, R) = 1, hence cost(S2) = [log2(BR)]




Question 8 :

Explain SQL related security commands?

Answer : - SQL commands are mainly categorized into four categories as discussed below :

1) DDL(Data Definition Language) - DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.

Examples of DDL commands :

2) DML(Data Manipulation Language) - The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements.

Examples of DML commands :

3) DCL(Data Control Language) - DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.

Examples of DCL commands :

4) TCL(transaction Control Language) - TCL commands deals with the transaction within the database.

Examples of TCL commands :




Question 9 :

Explain clustering in data mining?

Answer : - Data mining is the process of analysing data from different perspectives and summarizing it into useful information. Data mining involves the anomaly detection, association rule learning, classification, regression, summarization and clustering.

A cluster is a collection of data objects that are similar to one another within the same cluster and are dissimilar to the objects in other clusters. Clustering is important in data analysis and data mining applications. It is the task of grouping a set of objects so that objects in the same group are more similar to each other than to those in other groups. A good clustering algorithm is able to identity clusters irrespective of their shapes. The stages involved in clustering algorithm are as follows :

Raw Data → Clustering Algorithm → Clusters of Data




Question 10 :

With the help of a suitable example, explain insertion and deletion anomalies.

Answer : -

Product_idProduct_nameSupplier_idSupplier_name
P-101
Motherboard
S-45
Intel
P-405
Processor
S-45
Intel
P-390
RAM
S-50
Kingston
P-650
Harddisk
S-10
Seagate
P-800
Monitor
S-80
Samsung

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 product id "P-800", we lost all information about the supplier id "S-80". Because in this table it is the only one tuple which contain all information about this supplier id.

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 product unless we have atlast one supplier.




Question 11 :

How is the check pointing information used in the recovery operation in case of the system crash in DBMS.

Answer : -


Recovery using Checkpoint

In the following manner, a recovery system recovers the database from this failure :




Question 12 :

Consider the following database
Employees ( emp-name, streets, city, age)
Working( emp-name, department)
Designation ( emp-name, designation, salary).

Write the relational algebraic expressions and SQL statements for the following queries :

(a) Find the name, street & cities of all employees working for department D1 and D2 as Section Officers and earning salary more then 30000

Answer : - SELECT e.emp-name, e.streets, e.city FROM Employees AS e, Working AS w, Designation AS d WHERE e.emp-name=w.emp-name AND w.department='D1' AND w.department='D2' AND e.emp-name=d.emp-name AND d.designation='Section Officer' AND d.salary>30000;

(b) Find all the employees who are working as Deputy Registrar and living in the same cities.

Answer : -




Question 13 :

How does PostgreSQL perform storage and indexing of tables? Also discuss the type of indexes in PostgreSQL?

Answer : - Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

The basic syntax of CREATE INDEX is as follows −
CREATE INDEX index_name ON table_name;

A single-column index is one that is created based on only one table column.
syntax - CREATE INDEX index_name ON table_name (column_name);

A multicolumn index is defined on more than one column of a table.
syntax - CREATE INDEX index_name ON table_name (column1_name, column2_name);

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table.
syntax - CREATE UNIQUE INDEX index_name ON table_name (column_name);

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate.
syntax - CREATE INDEX index_name ON table_name (conditional_expression);

Implicit indexes are indexes that are automatically created by the database server when an object is created.

There are many types of indexes in PostgreSQL, as well as different ways to use them. Let’s review all these indexes.

B-Tree index - B-Tree is the default index that you get when you do CREATE INDEX. Virtually all databases will have some B-Tree indexes. The B stands for Balanced, and the idea is that the amount of data on both sides of the tree is roughly the same. Therefore the number of levels that must be traversed to find rows is always in the same approximate number. B-Tree indexes can be used for equality and range queries efficiently. They can operate against all datatypes, and can also be used to retrieve NULL values. B-Trees are designed to work very well with caching, even when only partially cached.

Hash index - Hash is one of the most popular indexing algorithms. But only the equate operator works on it, thus the query planner will only use an index with a hash algorithm if we do an equal operation searching for it. Another point to note is that Hash index is not WAL (Write Ahead Log) logged, so if the database crash we can’t rebuild the index and would need to REINDEX the entire column.

GIN index - GIN stands for Generalized Inverted Index. GIN is designed for handling cases where the items to be indexed are composite values, and the queries to be handled by the index need to search for element values that appear within the composite items. For example, the items could be documents, and the queries could be searches for documents containing specific words.

GiST index - GiST stands for Generalized Search Tree and isn’t a single indexing scheme but rather an infrastructure that makes it possible to implement indexing schemes for new data types by providing a balanced tree-structured access method.

GiST indexes are used for operations beyond equality and range comparisons and the standard Postgres distribution includes GiST operator classes for geometric data types, network addresses, range types, and text search documents.

SP-GiST index - Space partitioned GiST is also an infrastructure for implementing new indexing schemes, but instead of providing balanced tree-structures it provides partitioned search trees. These can be used to implement different types of non-balanced data structures that can yield fast lookups if they are partitioned using a rule that matches the queries they are used for.

BRIN index - Block range indexes are designed to handle very large tables in which the rows’ natural sort order correlates to certain column values. For example, a table storing log entries might have a timestamp column for when each log entry was written. By using a BRIN index on this column, scanning large parts of the table can be avoided when querying rows by their timestamp value with very little overhead.



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