Sunday 20 March 2011

data models


Data models:
Defin:
it is a set of concepts that can be used to describe the data such as data types and its length, relationships and consistency and constraints(conditions).
It provides the abstraction.
There are 3 types of data models:
1)record based data model
2)object based data model
3)physical data model.

1.   Record based data model:

These models are used in describing the data at logical and view level of abstraction.
It is in record format.
Each record contains the fields, and each field contains the fixed length of values.
Again this model is divided into 3 models:
1)   hierarchical data model
2)   network data model
3)   relational data model

1)   hierarchical data model:

data stores in the form of records, each record contains the fields and each field contains the fixed length.
Here each record is connected with links.
Features:

It uses tree structure to represent the relationship.
It contains only one parent record and this record doesn’t have any parent record.
This parent record have child record.

Advantages:

It is very efficient to maintain for one-to-many relationships.
It allows handle large amount of data and it provides the integrity.
It is simple and easy to design.

Disadvantages:

Although it is simple, it is difficult to implement the data. (i.e during the updation of the data) it leads to data inconsistency.

It can not handle many-to-many relationships.

2)   Network Data Model:

It is used to explain the data at view and logical level
It provides the many-to-many relationships
In this records are connected through pointers.
Here graphs can be formed but not trees.

Advantages:

It provides the many-to-many relationships.
Data can be accessed very simple.
Fast accessing of the data.
The storage space is reduced due to less data redundancy.

Disadvantages:
It is difficult to design and use properly
It is difficult to changes the database.
Not user friendly.


 
RELATIONAL MODEL
           The Relational data model is currently the most popular data model is DATA BASE MANAGEMENT system.
Ø      Why it is famous means the data is represented is the form of simple row and column format.
Ø      Each data field is considered as column.each record is considered as row of table.
Ø      The data model is developed by E.F.Codd in 1970 which is based on relation.
Ø      We don’t find any parent-child relation shis,we find the data in the form of row and column.tables of data values.
Ø      The tables are called relations.

ADVANTAGES
1.      Easy to use.
2.      Many –to-many relationships
3.      Flexibility
4.      Security
5.      Fast data access, less storage.
6.      Data redundancy is controlled.
7.      Data manipulation language.

DISADVANTAGES:

Needs powerful hardware.
                       























2.OBJECT-BASED LOGICAL MODELS

Ø      It describes the data at the conceptual and new level.
Ø      It allows data constraints explicitly.
Ø      Again these object-based logical models are 30 types.
1.      Entity relationship model
2.      Object oriented model
3.      Binary model
4.      Semantic data model
5.      Infological model
6.      Functional data model

1.Entity relationship model:
Ø      Entity is a thing or object in the real world
Ø      It consists collection of objects, called ENTITY and of relationships among these objects(thing)
Ø      Ex: if u consider customer in a company

1.      Costomer consists:     
2.      Cust_id
3.      Cust_name
4.      Cust_street
5.      Cust_city
Here customer is an entity and cus_id,cust_name,cust_street,cust_city are the attributes

Ø      If you want to make relation between more than one entity (object)then use the following symbols

Rectangles: which represent entity sets
Ellipses: which represent attributes
Diamonds: which represent relationships among entity sets
Lines: which link attributes to entity sets and sets and sets to relationships

Customer                           depositor                             account
Cust_id                                                                      account number
Cust_name                                                                  balance
Cust_street
Cust_city

The E-R diagram indicates that there are two entity sets: customer, account, with attributes as
The above diagram shows a relationship depositor between customer and account.



3.PHYSICAL DATA MODEL:

Ø      A physical data model is a representation of a data design
Ø      It describes the data at the lowest level
Ø      These concepts are mainly done by the computer specialists but not for the end users
Ø      Data is represented as record formats
Ø      Very few models are there
Ex: unifying model
       Frame memory


INSTANCES AND SCHEMAS:

Instances:

The collection of information stored in the database at a particular moment is called AN INSTANCE of the database
Instance is also known as ‘snap-shop’

Ex: in a class strength is 60, this is known as schema, because 60 will not be exceeded at all.
At a particular day strength may be decreased, that particular day strength is known as instance.

Types of schemas:

Physical schema:
                        It describes database design at the physical level.
Conceptual (or) logical schema: describes different news of the database.

Data independence:

The ability to modify a schema(database structure) in one level without affecting a schema definition in next higher level is called data independence.
 There are two levels of data independence:

(1.) physical data independence:
          To improve the performance of the DBMS, modification at the physical level are necessary. The ability to modify the physical schema without affecting the application programs to be rewritten is called physical data independence.

2) Logical data independence:

Whenever the logical structure of the database is altered (changes), modifications at the logical level are necessary. The ability to modify the logical schema without effecting application programs to be rewritten is called logical data independence.

Data models:
Defin:
it is a set of concepts that can be used to describe the data such as data types and its length, relationships and consistency and constraints(conditions).
It provides the abstraction.
There are 3 types of data models:
1)record based data model
2)object based data model
3)physical data model.

1.   Record based data model:

These models are used in describing the data at logical and view level of abstraction.
It is in record format.
Each record contains the fields, and each field contains the fixed length of values.
Again this model is divided into 3 models:
1)   hierarchical data model
2)   network data model
3)   relational data model

1)   hierarchical data model:

data stores in the form of records, each record contains the fields and each field contains the fixed length.
Here each record is connected with links.
Features:

It uses tree structure to represent the relationship.
It contains only one parent record and this record doesn’t have any parent record.
This parent record have child record.

Advantages:

It is very efficient to maintain for one-to-many relationships.
It allows handle large amount of data and it provides the integrity.
It is simple and easy to design.

Disadvantages:

Although it is simple, it is difficult to implement the data. (i.e during the updation of the data) it leads to data inconsistency.

It can not handle many-to-many relationships.

2)   Network Data Model:

It is used to explain the data at view and logical level
It provides the many-to-many relationships
In this records are connected through pointers.
Here graphs can be formed but not trees.

Advantages:

It provides the many-to-many relationships.
Data can be accessed very simple.
Fast accessing of the data.
The storage space is reduced due to less data redundancy.

Disadvantages:
It is difficult to design and use properly
It is difficult to changes the database.
Not user friendly.





1)Create a student table with attributes pino, name, marks ,avg.
2)insert the values into the tables at a time
Syntax: insert into <existing table> values(&attribute1, &attribute2,….);
Ex: insert into student values(‘&name’, &pino,&marks,&avg);
3) update <existing table> set attribute1=<value>,attribute2=<value> where (condition);
Ex: update student set marks=590,avg=60.25 where pino=345;
4)write a query to change the structure of student table by adding a new column.
Syntax:
Alter table <existing table > add attribute datatype(size);
Example: alter table student add address varchar(30);
5)write a query to modify the structure of student table by changing the size of address.
Syntax: alter table <existing table> modify attribute data type(size);
Example: alter table student modify address char2(40);

1)write a query to set some data for the name and avg in the records.
Syntax: update <exitsting table> set attribute1=<value>,attribute2=<value> where <condition>;
Example: update student name=’raj’, avg=76.89 where pino=56;

2)write a query to change the structure of student by adding a new column mobileno.
5) waq to select the names and marks of students whose marks are above 60;
Ex: select name, marks from student where marks>60;
6)write a query that sets the content data base from student in ascending order based on pinno.
Sysnax: select attribute(s) from <existing table > order by attribute;
Query(example): select * from student order by pino;




Database structure:
1)explain about database structure?
Database structure is divided into two parts:
1)   storage management
2)   query processor

1)   storage management:
storage management works as an interface between data stored in database and the application.

That is data is stored in disk as file system using operating system. The storage manager converts DML Statements into low level file systems.

i)TRANSACTION MANAGER:
 as the name specifies, it manages the transactions such that data remain in consistent state even after the transaction. It also provides for optimal concurrent transaction executions.

·       File management: file management allocated space for the data on disk and provides a data structure to define the information saved on disk that provides for easy access.
·       Buffer management: buffer management handles the transfer of data from disk onto the main memory and decides on what data to be kept in main memory.
·       Authorization management: this module allows only authorized users to access the data.
·       Integrity management: this module handles on the integrity constraints that are said on the data.  Storage management uses the following data structure i)data files ii)data dictionary iii)indices.
(II) QUERY PROCESSOR:
·       Interpreter Of DDL: DDL statements written by DBA to define the schema are interpreted and stored in the data dictionary.
·       Compiler of data manipulation language statements: as any other compiler, DML compiler converts the DML statements in low-level instructions. I also optimizes the query i.e., perform “query optimization”
·       Query evaluation DML compiler converts DML statements into low level instructions which are evaluated by query evaluation machine.








1)Create a student table with attributes pino, name, marks ,avg.
2)insert the values into the tables at a time
Syntax: insert into <existing table> values(&attribute1, &attribute2,….);
Ex: insert into student values(‘&name’, &pino,&marks,&avg);
3) update <existing table> set attribute1=<value>,attribute2=<value> where (condition);
Ex: update student set marks=590,avg=60.25 where pino=345;
4)write a query to change the structure of student table by adding a new column.
Syntax:
Alter table <existing table > add attribute datatype(size);
Example: alter table student add address varchar(30);
5)write a query to modify the structure of student table by changing the size of address.
Syntax: alter table <existing table> modify attribute data type(size);
Example: alter table student modify address char2(40);

1)write a query to set some data for the name and avg in the records.
Syntax: update <exitsting table> set attribute1=<value>,attribute2=<value> where <condition>;
Example: update student name=’raj’, avg=76.89 where pino=56;

2)write a query to change the structure of student by adding a new column mobileno.
5) waq to select the names and marks of students whose marks are above 60;
Ex: select name, marks from student where marks>60;
6)write a query that sets the content data base from student in ascending order based on pinno.
Sysnax: select attribute(s) from <existing table > order by attribute;
Query(example): select * from student order by pino;




Database structure:

Database structure is divided into two parts:
1)   storage management
2)   query processor

1)   storage management:
storage management works as an interface between data stored in database and the application.

That is data is stored in disk as file system using operating system. The storage manager converts DML Statements into low level file systems.

i)transaction manager:
 as the name specifies, it manages the transactions such that data remain in consistent state even after the transaction. It also provides for optimal concurrent transaction executions.

·       File management: file management allocated space for the data on disk and provides a data structure to define the information saved on disk that provides for easy access.
·       Buffer management: buffer management handles the transfer of data from disk onto the main memory and decides on what data to be kept in main memory.
·       Authorization management: this module allows only authorized users to access the data.
·       Integrity management: this module handles on the integrity constraints that are said on the data.  Storage management uses the following data structureL i)data files ii)data dictionary iii)indices.
(II) query processor:
·       INTERPRETER OF DATA DEFINITION LANGUAGE STATEMENTS: DDL statements writtey by DBA to define the schema are interpreted and stored in the data dictionary.
·       Compiler of data manipulation language statements: as any other compiler, DML compiler converts the DML statements in low-level instructions. I also optimizes the query i.e., perform “query optimization”
·       Query evaluationL DML compiler converts DML satemts into low level instructions which are evaluated by query evaluation machine.




GENERALIZATION,SPECIALAZATION AND AGGREGATION:

Generalization:

Ø     Generalization is a special case of specialization
Ø     Generalization is the result of taking the union of two or more different entity sets to form higher level entity set
Ø     That is combinding low-level entity sets and producing a highlevel entity set is known as generalization

Example:

In a bank there are two types of customers
1.     current account customers
2.     saving account customers

Ø     these two are represented by two entity sets
Ø     if we generalize there entity sets we can get an entity set customers of the bank.

 
REDUCE THE ER DIAGRAMS TO TABLES

A database that is represented as an ER diagrams an ER diagrams can be represented by a collection of tables. for each entity set and for each relationship set in the database, that is a unique table that is assigned the name of the corresponding entity set (or) relationship set. each table has multiple columns, each is unique name.

For example, consider the entity set names employee of the employee entity set has the attributes eno, ename, sal,

The table corresponding to the EMPLOYEE has three columns as under.

ENO       ENAME    SAL
Em-001   ravi            6000
cm-002   anji             7000
ec-001     gopi           8000
me-003    venki         9000
ee-002     babu rao      10000

if we consider,the entity set dept of the ER diagram

imp quesitons for DBMS


RDBMS
SECTION-A

  1. Explain disadvantages in file based processing.(markch/ap09,oc/nov09)
  2. Differentiate between hierarchical model and network model.(mark/ap2009)
  3. Define instance and scheme( oct/nov09 08)
  4. List the responsibilities of database manager.
  5. What are symbols used in E-R diagrams?(March/April.2009,oct/nov.07)
  6. Explain data independence. (march/april.09,oct/nov.07/nov2010)
  7. What are the difference between generalization and specialization
  8. Explain about concept of aggregation. (oct/nov.09)
  9. What do you means by SQL? What are the benefits of SQL?.
  10. What is an embedded SQL?
  11. What do you meant by pseudo column? Explain with examples. (oct/nov.09,08)
  12. What are the differences between Char and varchar2 data types?(M/A.09)
  13. Write any four data functions with syntax and examples
  14. State the four features of SQL. (March/April 2009)
  15. What is the process of creating a Table
  16. Define a sequence
  17. Difference between view and table.
  18. Write the names of four schema objects.
  19. Define is PL/SQL global and system variables.
  20. List the features of PL/SQL.
  21. What are Naming Conventions in PL/SQL?
  22. List any four predefined PL/SQL exceptions.
  23. Compare various parameter modes.
  24. What is a cursor? Explain the management of explicit cursor.
  25. What is implicit cursor?
  26. What is a stored sub program
  27. List the advantages of packages
  28. How to compile a PL/SQL program?
  29. What is packaged procedure? Give example.
  30. Define package.


Section-b

  1. What is database model?(oct/nov08)
  2. What do you meant by generalization, specialization and aggregation. (Oct/Nov.09,08,07)
  3. Explain the various mapping cardinalities. (Oct/Nov.07)
  4. Explain the various normal forms in details. (Oct/Nov.09)
  5. What is database abstraction? Explain with the help of a diagram.(m/ap09,oc/no-09)
  6. Explain the functions of Database Administrator
  7. Explain various data types available in SQL. (Oct/Nov.2009, March/April.2009)
  8. Explain about any 12 function of SQL. (MARCH/APRIL 2009)
  9. Explain the following SQL statements (a) insert (b)update   (March/April.2009)
  10. (a)Explain the various data format models in SQL.                        (b)Explain set operators of SQL
  11. Explain group function of SQL. (Oct/Nov.2008, 2007)
  12. (a.)Explain about DML statements (Oct/Nov.2009)                                                             (b) explain about data types supported by SQL. (Oct/Nov.2009)
  13.  Explain the process of creating (a) sequence (b)cluster (c)synonym (d) index
  14. Explain the procedure for creating, altering and dropping a sequence
  15. (a.) Explain the space management of database table.  (b) Explain about indexes.
  16. What is an integrity constraint? List the various types of integrity constraints.
  17. What is a view? Explain the syntax for creating a view.
  18. Explain the procedure of creating, altering and dropping tables.
  19. Explain the following commands: (i.)commit (ii)rollback and save point.
  20. Explain the various data types of PL/SQL in detail.
  21. Explain propagation and re-raising of exceptions.
  22. What is an exception? Explain the rising of an exception.
  23. Explain about implicit and explicit cursor.
  24. (a)Explain recursion with an example.
  25. Explain package state and dependency.
  26. Explain procedure overloading in PL/SQL with an example.

Advanced PL/SQL

  1. What is stored Sub Program?
Ans: it is a sub program which may be procedure or function in a database object that are logically group of SQL and PL/SQL programming language statements together to perform a specific task.
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.
There are two types of cursors in PL/SQL:

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

Implicit Cursors:

When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected.
When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table.
Attributes
Return Value
Example
%FOUND
The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row.
SQL%FOUND
The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row.
%NOTFOUND
The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row.
SQL%NOTFOUND
The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row.
%ROWCOUNT
Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT
SQL%ROWCOUNT

For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
DECLARE  var_rows number(5);
BEGIN
  UPDATE employee 
  SET salary = salary + 1000;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('None of the salaries where updated');
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
  END IF; 
END; 
In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.

Saturday 19 March 2011

ADBMS PREVIOUS QUESTION PAPERS









BOARD DIPLOMA EXAMINATION,(C-09)
MAR/APR-2013 QTN PAPER
DCME – VI SEMESTER EXAMINATION
ADVANCED DATABASE SYSTEM
SHORT QTNS:

1.what is concurrency control?
2.what is meant by multiple site?
3.what is the adv of adbms?
4.what are the issues to be considered in the design of a distributed database?
5.what is meant by late binding?
6.Define OODBMS?
7.what are the facts and dimensions?
8.Compare datawarehouse and operational data?
9.Differentiate between datamining and olap?
10.What is predictive modelling?
                            PART-B(LONG ANS)
11.Explain transaction transparency?
12.Explain data allocation?
13.Explain about inter object relation ship
14.explain the features of OODBMS
15.Explain ROLAP client server architecture
16.compare operational data with decision support data
17.explain data mining methodology in detail
18.explain(a)directed data mining and (b)undirected datamining




Friday 18 March 2011

external question papers


(POLYTECHNIC)

Practical Lab Examination Oct 2010                       Maximum Marks :60
YEAR /SEMESTER :II Year / III Sem                                Time : 3 Hours
SUBJECT CODE & NAME : RDBMS LAB      Date:


1. ( a). WAQ to create a sequence ?
b. WAQ that retrieve the marks of students arrange in ascending order and   descending order?

2. a. WAQ to create synonym?
b. WAQ to find out max, min, sum, avg, count from student table?

3                    a. WAQ to create view?
b. WAQ to display current system date?

4. a. Write a PL/SQL program to display addition of two numbers.
   b. Write a query to create sequence?

5. a. Write a PL/SQL program to display factorial of given number?
   b. WAQ to display names of students whose name start with alphabet ‘S’?
  c. WAQ to display names of students whose name end with alphabet ‘a’ ?

6.a. Writa a PL/SQL program to find out largest of two numbers?
   b. WAQ to modify the structure of a student table by changing the data type of address     to varchar.
c. WAQ to modify the structure of a student table by deleting the field address.

7.a Writa PL/SQL program to illustrate for any program for exeception handling?
 b. WAQto find out abs, sqrt, power, ascii, remainder of a given number? (25)
8.a WAQ to vreate a swquence?
 b. WAQ to delete the record from the student table whose marks are less than 100?
 C. WAQ to change the balbe name?
9.a. WAQ to create a synonym?
 b. WAQ to that retrives the Pinno, names of students – whose marks are in the range of 400 (or )750?

10.a. WAQ to create a view?
 b. WAQ to insert a record into student table for all fields at a time by using direct values and by using ‘&’ operator?
c. PL/SQL program to multiplication of two numbers?

11. a. Write a PL/SQL to display division of two nos?
 b. Write to remove the specified character from left side an right side of given string?
c. Write a query to convert a string upper to lower?

12.a Write a PL/SQL to illustrate for any program for exception handling?
 b. Write a query to delete tha record from the student table whose marks are less than 100?

13.a. Write a query to create sequence?
 b. Write a query that retrieves the pin no, attendance of students whose marks are above 480 using not operator?

14.a. WAQ to create view?
 b. WAQthat retrives names, marks of students who secure marks 500?

15.a WAQ to find out how many months between 2dates?
 b. WAQ to add months to a given date?
c. Write a PL/SQL program to find out larges of two numbers using (if/else)?

16.a. Write a PL/SQL Program to display factorial of given numbers?
 b. Write a query to find out sqare root of given numbers?

17. Write a PL/SQL to find out addition of two numbers?
 b. Write query to create swquence?.

18. a. Wr4ite a PL/SQL to illustrate for any Program for exception handling?
b. WAQ to remove the specified character formleft side and right side of the string?

19.a. Write a query to create a view?
b. write a query to display current system date?
c. Write a query to display current user name?

20. Write a query to find out factorial of given number?
b. Write a query that retrives the marks of students are arrange in ascending & descending order?



Internal Examiner                                          External Examiner

 Practical Lab Examination Oct 2010                       Maximum Marks :60
YEAR /SEMESTER :II Year / III Sem                                Time : 3 Hours
SUBJECT CODE & NAME : RDBMS LAB      Date:


1.        Write a query to insert values into student table using (i) single insertion process. (ii) Multiple insertion process.
2.       (a) Write a Query to create a student table student (sname, sno, marks, address (b) description of the table.
3.       Write  a Query to alter a table using (i) adding (ii) modifying
4.       (a) write a query to delete a row? Whoever have pin no 234. (b) write a query to select whoever have marks 540?
5.        (a) Write a Query to select sum, avg, min, max of marks from student table? (b) write a query to change marks whoever have pin no 150?
6.       Write a Query using drop command. (i) to drop a column (ii) to drop a table
7.        write a query using dual table (i) addition (ii) subtraction (iii) multiplication (iv) division
8.       (a)Write a Query to create a view? (b) Write a Query to drop a view?
9.       (a) write a query to create a Synonym? (b) write a query to drop a Synonym? (c) Write a query to that retrieves the pinno, names of students –whose marks are in the range of 400 (or) 750?
--------------------------------------------------------------------------------------------------------

OCTOBER/NOVEMBER-2010(C-05)




3232
BOARD DIPLOMA EXAMINATION, (C-05)

 
OCTOBER/NOVEMBER-2010
DCM-III SEMESTER EXAMINATIONS

PART-A          10*3=30

  1. Define First Normal Form.
  2. Define the following terms: (1) Entity (b)Relationship (c)generalization.
  3. write short notes on Network model.
  4. create a table named Account with following attributes and constraints : acno, name, actype, loc, balance, acno must be unique and not null.
  5. write short note on format models for date and number.
  6. list the advantages of views.
  7. define cluster and give its use.
  8. what are global and local variables?
  9. define function and explain.
  10. how to create package specification.

PART-B
  1. explain E.F.CODD’s rules for RDBMS.           10
  2. Explain the process to reduce ER diagrams to tables.
  3. (a)explain Oracle tools. (b)define sub query and explain.
  4. (a)list various operators in SQL. (B)explain how to manage space in SQL.
  5. Explain the procedure of creating, altering and dropping tables.
  6. Explain how to create a procedure in PL/SQL.
  7. (a)Explain about recursion.                                                                    5        (b)explain the concept of propagation of exception.                             5
  8. explain the concept of stored sub programs.                                          10