DBMS Previous year question paper with answers for OCT 2012, model paper

DBMS Model Test Paper with answers - Osmania University

OCTOBER - 2012

DATABASE MANAGEMENT SYSTEMS

B.Sc., PAPER - III

OSMANIA UNIVERSITY

TIME : 3 HOURS                                                                                                      MAX. MARKS: 100

Answer all questions:

                                                                           Sec - A                                           (10x2=20 marks)                    

1. Define the following:

• Meta Data
• Data Models
• Subtype Discriminator
• Degree of relationships
• Write any 4 Arithemetic fiunctions
• Cursor in SQL
• Transaction
• Lock Granularity
• Data Warehouse
• Slice and Dice Technique

                                                                           Sec - B                                            (5x16=80 marks)

2. (a) Explain Codd's Relation database rules.
    (b) Explain the advantages of DBMS.

OR

    (c) Define a relation and explain the characteristics of relational table.
    (d) Define Business Rules.

3. (a) Explain EER Model Constraints briefly.
    (b) Explain super and subtype entities with example.

OR

    (c) What is an attribute? Explain different types of attributes with examples.
    (d) Write short notes on attribute inheritance, Specialization and entity clustering.

4. (a) Explain DDL and and DML commands with syntax and examples.
    (b) Write a short notes on set operations.

OR

    (c) Explain SDLC with the help of neat diagram
    (d) Explain View and Index in SQL.

5. (a) What is concurrency control? Writ about 3 main problems cause by concurrency control.
    (b) Write short note on wait/die and wound/wait schemes.

OR

    (c) Explain the characteristics of DDBMS.
    (d) Explain two phase commit protocol.

6. (a) Define the data warehouse properties.
    (b) Define OLAP and explain its properties.

OR

    (c) Describe various phases in Data Mining.
    (d) What are different technical role of DBA?

ANSWERS

1(a). Meta Data:

• Data about data is meta data. It is description of data items, i.e., structure, organization, format etc. It is also known a 'Respiratory'.

1(b) Data Models:

• Data Model is used to represent the data depending on the nature of the data. It has the following characterstics:
• Conceptual simplicity without compromising the semantic completeness of the database.
• Represent the real world as closely as possible.
• The representation must comply with consistency and integrity characterstics of any data model.
• Types of Data Model are:
• Hierarchical
• Network
• Relational
• Entity relationship
• Semantic

1(c) Subtype Discriminator:

• A Subtype discriminator is an object set that is subset of another objects etc.,

1(d) Degree of Relationships:

• Degree of relationship of the no. of entities it is associated with:
• Unary
• Binary
• Ternary     

1(e) Four Arithmetic function:

• ABS: Returns the absolute value of a number.
• Select ABSC(-2.44) froom dual.
• Round: Round is a value to a specified precision. 
• Select p-code round(price,1)from product.
• Ceil/Floor: Returns the largest integer equal to less than a number. floor returns smallest integer equal to less than a number.
• Select pcode, ceil(price), floor(price) from product.
• Max : To get maximum of given numbers.
• Min To get minimum of given numbers.

1(f) Cursor in SQL:

• To process a SQL statement Pl/SQL opens a work area (Private SQL AREA) called context area. PL/SQL uses the context area to execute the SQL statement and store procesing information. APL/SQL construct called a cursor lets yout name a context area access its stored information.

1(g) Transaction

• Action or series of actions, carried out by used or application, which reads(or) updates contents of database. Logic unit of world on database. Application program is series of transactions with non-database processing in between. Transforms database from one consistent state to another although consistency may be violated during transaction.

1(h) Lock Granularity

• The level and type of information that the lock protects is called locking granularity. Locking granularity affects performance. When a user cannot access a row or key, the user can wait for another user to unlock the row or key. If a user locks an entire page, a higher probability exists that more uses will wait for a row to the page. The ability of more than one user to access a set of rows is called concurrency. The goal of the database administrator is to increase concurrency to increase total performance without sacrificing performance for an individual user.

1(i) Data Warehouse:

• It is an integrated, subject - oriented, time variant, non-volatile database that provides support for decision making.
• Integrated: The data warehouse is a centralized, consolidated database that integrates data retrieved from the entire organization. 
• Subject - oriented: The data warehouse data is arranges and optimized to provide answers to questions coming form diverse functional areas within a company.
• Time variant: The warehouse data represent the flow of data though time. It can even contain projected data.
• Non-volatile: One data enter the Data warehouse they are never removed. The data warehouse is always growing.

1(j) Slice and dice technique:

• Slice is the act of picking a regular subset of a cube by choosing a single value for one of its dimensions.
• Dice operations produces a sub cube by allowing the analyst to pick specific values of multiple dimensions.

SEC - B

2 (a) Codd's Relation Database Rules:

• Codd's 12 rules are set of thirteen rules(numbered from 0 to 12) proposed by Edgar F. Codd, a pioneer of the relational model for databases, designed to define what is required form a database management system in order for it to be considered relational, i.e., an relational database management system. Rules are as follows:
• Rule 0: The system must qualify as relational, as a database and as a management system. 
• Rule 1: The information rule: All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables.
• Rule 2: The guaranteed access rule: All data must be accessible. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row.
• Rule 3: Systematic treatment of null values: The DBMS must allow each field to remain null.  Specifically it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values and independent of data types. It is also implied that such representation must be manipulated by the DBMS in a systematic way.
• Rule 4: Active online catalog based on the relational model: The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database's structure using the same query language that they use to access the database's data.
• Rule 5: The comprehensive data sub language rule: The system must support at least one real relational language that 
• Rule 6: The view updating rule: All views that are theoretically updatable must be updatable by the system.
• Rule 7: High - level insert, update and delete: The system must support set-at-a-time insert, update and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and tables. 
• Rule 8: Physical data independence: Changes to the physical level must not require a change to an application based on the structure.
• Rule 10: Integrity Independence: Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
• Rule 11: Distribution Independence: The distribution of portions of the database to various locations should be invisible to uses of the database. Existing applications should continue to operate successfully.
• Rule 12: The no subversion rule: If the system provides a low-level interface, then that interface cannot be used to subvert the system.

2(b) Advantages of DBMS:

• Program - Data independence: Data Independence is the ability to modify the definition of the data source in one level without effecting that definition of data source on the other or higher levels. 
• Data Independence is viewed in two categories:
• Logical Data independence: It is related to logical structure of the database system. 
• Physical Data independence: It is related to the implementation criteria of the database system.
• Minimal Data Redundancy: The design goal with the database approach is that all data files are integrated into a single, logical structure. Each data item is recorded in only one place in the database.
• Improved Data Consistency: By eliminating data redundancy the data inconsistency can be reduced to great extent. so updating data values is greatly simplified when each values is stored in one place only. 
• Improved data sharing:  Dbms has an extreme facility of data sharing. The data reside in it is once modified at one level would effect the all the application levels i.e., that change is share through out the environment. 
• Increased productivity of application development: A major advantages of the database approach is that it greatly reduces cost and time to develop new business applications. 
• Enforcement of standards: When the database approach is implemented with full management support, the database administration function should be granted to single-point authority, which is responsible for establishing and enforcing data standards.
• Improved data quality: The database provides a number of tools and processes to improve data quality. Database designers can specify integrity constraints that are execute by the DBMS.
• Improved data accessibility and responsiveness: With a relational database end users without programming experience can retrieve and display data, using the language called structural query language.
• Reduced program maintenance: Stored data must be changed frequently for a various reasons. As all the data items will stored only once in database, managing data becomes very easy, which reduces the program maintenance.

OR

2(c) Relation and characteristics of relational table.

• Tables are required to have at least one column. tables are not required to have rows. A table with no rows is called an empty table. The process of inserting tuples for the very first time into a table is called populating the table.For each column of a table there is a set of possible values called its domain. the domain contains all permissible values that can appear under that column. The order of the rows is irrelevant since they are identified by their content and not by their position within the table. No two row or tuples are identical to each other in all their entries.

• Characterstics of Relational table:    

1. A Relation is a table with columns and rows.
2. It only applies to logical structure of the database, not the physical structure.
3. Attribute is a named column of a relation.
4. Domain is the set of allowable values for one or more attributes.
5. Tuple is a row of a relation.
6. Degree is the number of attributes in a relation.
7. Cardinality is the number of tuples in a relation
8. Relational Database is a collection of normalized relations with distinct relation names.
• Properties of Relational tables:
• Values are atomic
• Each row is unique
• Column values are of the same kind
• The sequence of columns is insignificant
• The sequence of rows is insignificant 
• Each column has a unique name

2(d) Business Rules.

• A business rule is a statement that denies the constraints which is use to control the operations and behavior of the system.
• Characteristics of Business Rules:
• Declarative: The business rule should clear and definable because it a statement of policy
• Atomic: Means that a business rule makes a rule, which is not divisible and posse some sufficient significance.
• Expressible: A rule should be real and at position to describe.
• Distinct: Business rule should not redundant, i.t. it should not transfer or alter.
• Business oriented: A business rule is stated in terms business people can understand

        Example of business rules:

1. A Customer may generates many invoices
2. An invoice is generated by only one customer
3. A training session cannot be scheduled for fewer than 10 employees or for more than 30 employees.

          The process of identifying and documenting business rules is essential to database design for several reasons:

• They help standardize the company's view of data.
• They can be communication tool between users and designers.
• They allow the designer to understand the nature, role and scope of the data.
• They allow the designer to understand business process.
• They allow the designer to develop appropriate relationship participation and to create accurate data model.

Translating business ruled to data model components:

• Standardize company's view of data
• Communication tool between users and designers
• Allow designer to understand business processes.
• Promote creation of an accurate data model
• Allow designer to understand the nature role and scope of data.

3(a) EER Model constraints:

• Two basic constraints can apply to a specialization generalization.
• Disjoint constraints: Specifies that the sub classes of the specialization must be disjoint. An entity can be a member of at most one of the sub classes of the specialization. If not disjoint specialization is over lapping that is the same entity may be a member of more than one subclass of the specialization.
• Completeness constraint: Total specifies that every entity in the super class must be a member of some subclass in the specialization/generalization shown in EER diagrams by a double line
• In general a super class that was identified thought the generalization process usually total., because the superclass is derived from the subclasses and hence contains only the entities that are in the subclass. 

3(b) Super and subtype entities with examples.

• Supertype: Supertype is a generic entity type that has a relationship with one or more subtypes.
• Subtypes: Subtype is a sub grouping of the entities in an entity type and that share the common attributes.
• Example: Supposet that an organization has 3 basic types of employees HOURLY, SALARIED EMPLOYEEDS AND CONTRACT CONSULTANTS.
• HOURLY EMPLOYEES(empno, empname, address, datehired, hourlyrate)
• SALARIED EMPLOYEES(empno, empname, address, datehired, annualsalary)
• CONTRACT CONSULTANTS(empno, empname, address, datehire, contractno, billingrate)
• Notice that all of the employee types have several attributes is common.
• in addition each type has one or more attributes distinct from the attributes of other types.

OR

3(c) Attribute and types of Attributes:

• Single valued Attribute: Every attribute can have a single value.
• Multi-valued attribute: It can have multiple values
• Derived Attribute: An attribute whose value is calculated from other attributes.
• Composite attributes: An attribute that can be further divided into additional attributes.

3(d) Attribute Inheritance, Entity Clustering:

• Attribute Inheritance is obtaining attributes of super type to its subtypes.
• Entity Clustering: A set of one or more entity types and associated relationship grouped into a single abstract entity type.Entity clustering is a usefl way to present a data model for a large and compels organization. 

4(a) DDL and DML commands:

• Data Maipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanenet to database, it can be rolled back.
• INSERT Command : It is used to insert data into a table.
•  insert into table_name values(data1, data2,...) 
• UPDATE : Update is used to update a row of a table.
• update table_name set column_name=value where condition;
• DELETE : It is used to delete data froma  table. Delete command can also be used with condition to delete a particular row.
• delete from student;
• 
  
•