Ignou/4th semester/solved assignment/Advanced Database management system/2019

                             Questions

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. (20 marks)
(a) Draw the EER (extended ER) diagram for the above organization
showing all entities, relationship, aggregation, generalization
/specialization and convert it into relational schemas
(b) Draw the appropriate tables and relationship among the tables for
the above diagram and normalize the tables up to 3NF
(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.
(d) Identify multivalued dependency in the above diagram.

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) (5 marks)

Question 3: How will you enforce referential integrity constraints in Oracle?
Explain with the help of an example? (5 marks)

Question 4: Draw a simple Use Case diagram for a library system and
explain? (5 marks)

Question 5: (i) What are triggers and cursors and their uses? Explain with the
help of an example for each. (5 marks)
(ii) Write a trigger that restricts modifying an employee table beyond
2 hrs overtime per day. (5 marks)

Question 6: Create and explain an object oriented database for the following
UML diagram. Make assumption about attributes and functions.
 (5 marks)

Question 7: What are the parameters for measuring cost for performing a query.
Discuss the algorithm and the related cost of performing selection
operation? (5 marks)

Question 8: Explain SQL related security commands? (4 marks)

Question 9: Explain clustering in data mining? (3 marks)

Question 10: What the help of a suitable example, explain insertion and deletion
anomalies. (4 marks)

Question 11: How is the check pointing information used in the recovery operation
in case of the system crash in DBMS. (4 marks)

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
 (3 marks)
(b) Find all the employees who are working as Deputy Registrar and
living in the same cities. (3 marks)

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

                                 Ans
Ans(13)

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discusses a certain topic, you have to first refer to the index, which lists all topics alphabetically and then refer to one or more specific page numbers.

An index helps to speed up SELECT queries and WHERE clauses; however, it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

The CREATE INDEX Command

The basic syntax of CREATE INDEX is as follows −

CREATE INDEX index_name ON table_name;

Index Types

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST and GIN. Each Index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.

Single-Column Indexes

A single-column index is one that is created based on only one table column. The basic syntax is as follows −

CREATE INDEX index_name
ON table_name (column_name);

Multicolumn Indexes

A multicolumn index is defined on more than one column of a table. The basic syntax is as follows −

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Whether to create a single-column index or a multicolumn index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the multicolumn index would be the best choice.

Unique Indexes

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. The basic syntax is as follows −

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Partial Indexes

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. The basic syntax is as follows −

CREATE INDEX index_name
on table_name (conditional_expression);

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Example

The following is an example where we will create an index on COMPANY table for salary column −

Que(8)

Ans-

SQL GRANT REVOKE Commands

DCL commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOKE. Only Database Administrator's or owner's of the database object can provide/remove privileges on a database object.

SQL GRANT Command

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

The Syntax for the GRANT command is:

GRANT privilege_name 
ON object_name 
TO {user_name |PUBLIC |role_name} 
[WITH GRANT OPTION]; 

SQL REVOKE Command:

The REVOKE command removes user access rights or privileges to the database objects.

The Syntax for the REVOKE command is:

REVOKE privilege_name 
ON object_name 
FROM {user_name |PUBLIC |role_name} 
Que(9) Ans--

What is Clustering?

Clustering is the process of making a group of abstract objects into classes of similar objects.

Points to Remember

• A cluster of data objects can be treated as one group.
• While doing cluster analysis, we first partition the set of data into groups based on data similarity and then assign the labels to the groups.
• The main advantage of clustering over classification is that, it is adaptable to changes and helps single out useful features that distinguish different groups.

• Requirements of Clustering in Data Mining

  The following points throw light on why clustering is required in data mining −
  • Scalability − We need highly scalable clustering algorithms to deal with large databases.
  • Ability to deal with different kinds of attributes− Algorithms should be capable to be applied on any kind of data such as interval-based (numerical) data, categorical, and binary data.
  • Discovery of clusters with attribute shape − The clustering algorithm should be capable of detecting clusters of arbitrary shape. They should not be bounded to only distance measures that tend to find spherical cluster of small sizes.
  • High dimensionality − The clustering algorithm should not only be able to handle low-dimensional data but also the high dimensional space.
  • Ability to deal with noisy data − Databases contain noisy, missing or erroneous data. Some algorithms are sensitive to such data and may lead to poor quality clusters.
  • Interpretability − The clustering results should be interpretable, comprehensible, and usable.

  Clustering Methods

  Clustering methods can be classified into the following categories −
• Partitioning Method
• Hierarchical Method
• Density-based Method
• Grid-Based Method
• Model-Based Method
• Constraint-based Method

Que/(10) 

Ans--