Sample Paper for DBMS(Data Base Management System)
Q.1: What is the difference between DATA BASE MANAGEMENT SYSTEM and RDBMS?
Ans: DATA BASE MANAGEMENT SYSTEM helps in storing, managing of data from collection of logically related information. And Relational DATA BASE MANAGEMENT SYSTEM also provides similar to what DATA BASE MANAGEMENT SYSTEM does but it provides relationship integrity.
Q.2: What are advantages of DATA BASE MANAGEMENT SYSTEM?
Ans: The advantages of DATA BASE MANAGEMENT SYSTEM are as follows:
1. Redundancy is controlled.
2. To enforce integrity constraints.
3. Providing backup and recovery.
4. Providing multiple user interfaces.
5. Unauthorized access is restricted.
Q.3: What are different types of Data languages?
Ans: DDL, VDL, SDL. DDL is Data definition language, View definition language, Storage definition language.
Q.4: What is a data model?
Ans: A data model is a collection of conceptual tools for describing data, relationships data and semantics and constraints.
Q.5: What is data independence? Mention few types of data independence.
Ans: Data independence means that the application is independent and free of the storage structure and access of any data. It is also the ability to change and modify the schema definition in one level that should not affect the schema definition in the next level, in other words.
Types of Data Independence are-
Physical data independence- it means that the modification in logical level must not be affected by the changes in physical level.
Logical Data independence means that the logical level should affect the view level. Logical data independence is difficult in application and hence tough to get desired results.
Q.6: What is E-R model?
Ans: The E-R data model is based on the real world. It consists of basic objects called entities. It also takes in consideration the relationship between these objects. While entities are described by a set of attributes.
Q.7: what is an attribute?
Ans: An attribute is a particular property which describes the entity.
Q.8: What is a relationship in terms of DATA BASE MANAGEMENT SYSTEM?
Ans: It is an association among two or more entities. Relationship Set is the collection or a set of all similar entities. While a Relationship type is the attribute that defines the set of associations, sets etc. in any given set of entity types.
Q.9: What is SQL?
Ans: SQL stands for Structural Query Language. SQL is an American Standards Institutes’ standard computer language for accessing and organizing database systems. SQL statements are used to retrieve data in the database.
Q.10: What is extension and intension?
Ans: Extension is the number of tuples available in the table at any instance. Extension is time dependant. Intension on the other hand is a constant value that gives the name, structure and other values to the table and the constraints which are laid on it.
DESCRIPTIVE TYPE QUESTION AND ANSWERS
Q.1 Briefly discuss the different layers of ANSI SPARC architecture. Define physical and logical data independence. How does this architecture help in achieving these?
Ans: The three layers of ANSI SPARC architecture are as follows:
• Internal view is at the lowest level of abstraction, closest to the physical storage method used. It indicates how the data will be stored and describes the data structures and access methods to be used by the database. There is one internal view for the entire database.
• Global or Conceptual View : At this level of abstraction all the database entities and the relationships among them are included. There is one conceptual view for the entire database.
• External or User View: The external or user view is at the highest level of database abstraction where only those portions of the database concern to a user or application programme are included. Any number if external or user views may exists for a given global or conceptual view. Data independence implies that change in one view must not require a change in the view(s) above. There are two types of data independence : logical and physical. Logical data independence means that the conceptual view can be changed without effecting the existing external view, i.e., a given record may be spilt or combined with other records but the external views need not be changed to reflect this. Physical data independence means that the physical storage structures or devices used to store the data can be changed without effecting the existing conceptual view or external view, i.e., if earlier indexed sequential files are used to store data and then the B- trees are used, even then the upper layers should not be effected. There are two different mappings between the layers as shown below in the diagram. The mapping between external and conceptual levels is responsible to provide logical data independence and the mapping between internal and conceptual levels is responsible to provide physical data independence.
Mapping Supplied by DBMS/ OS
Mapping Supplied by DBMS
External View(s) E1 E2 En
Q.2 Describe the storage structure of indexed sequential files and their access method.
Ans: Storage structure of Indexed Sequential files and their access : To gain fast random access to records in a file, we can use an index structure. An index record consists of a search key value and pointers to data records, which is associated with a particular search key. An ordered index stores the values of the search keys in sorted order. A file may have several indices on different search keys. If the files containing the records is sequentially ordered, a primary index is an index whose search key also defines the sequential order of the file. Such files are known as index sequential files. There are two types of ordered indices : dense and sparse. In dense index, and index record appears only for some of the search-key in the files as shown below.
To access a particular record with search key value, K, using dense index we search index record with search key value, K, from which reach the first entry of data record with search key value K. Then the data records are searched linearly to obtain the required record. If either the index record is not there or the linear search reaches the data record with different search key value, then the record is not there. Now for sparse index, we search index record with search key value, K or the index record with highest search key value less than K, from which reach the first entry of data record with search key value K or highest value less than K. Then the data records are searched linearly to obtain the required record. If the linear searches the data record with search key value greater than K, then the record is not there.
Q.3 Define the terms entity, attribute, role and relationship between the entities, giving examples for each of them.
Ans: Entity: An entity is a “thing” or “object” in the real world that is distinguishable from other objects. For example, a person and bank account can be considered as entities. Attribute: Entities are described in a database by a set of attributes, i.e., the characteristics of an entity are known as attributes. For example, name, age, date of birth, etc are attributes of the entity person. Similarly, account number, balance, nature of account, etc are attributes of the entity bank account. Relationship: A relationship is an association among the several entities. For example, a depositor relationship associates the entity person with a bank account.
Role: The function that an entity plays in a relationship is called that entity’s role. For example, in the relationship depositor mentioned above, the entity person plays the role of a customer in the relationship.
Q.4 What are the three data anomalies that are likely to occur as a result of data redundancy? Can data redundancy be completely eliminated in database approach? Why or why not?
Ans: The three type of anomalies that can arise in the database because of redundancy are insertion, deletion and modification/updation anomalies. Consider a relation emp_dept with attributes: E#, Ename, Address, D#, Dname, Dmgr# with the primary key as E#. Insertion anomaly: Let us assume that a new department has been started by the organization but initially there is no employee appointed for that department, then the tuple for this department cannot be inserted into this table as the E# will have NULL, which is not allowed as E# is primary key. This kind of a problem in the relation where some tuple cannot be inserted is known as insertion anomaly. Deletion anomaly: Now consider there is only one employee in some department and that employee leaves the organization, then the tuple of that employee has to be deleted from the table, but in addition to that the information about the department also will get deleted. This kind of a problem in the relation where deletion of some tuples can lead to loss of some other data not intended to be removed is known as deletion anomaly. Modification /update anomaly: Suppose the manager of a department has changed, this requires that the Dmgr# in all the tuples corresponding to that department must be changed to reflect the new status. If we fail to update all the tuples of the given department, then two different records of employee working in the same department might show different Dmgr# leading to inconsistency in the database. This is known as modification/update anomaly. The data redundancy. Cannot be totally removed from the database, but there should be controlled redundancy, for example, consider a relation student_report(S#, Sname, Course#, SubjectName, marks) to store the marks of a student for a course having some optional subjects, but all the students might not select the same optional papers. Now the student name appears in every tuple, which is redundant and we can have two tables as students(S#, Sname, CourseName) and Report(S#, SubjectName, Marks). However, if we want to print the mark-sheet for every student using these tables then a join operation, which is a costly operation, in terms of resources required to carry out, has to be performed in order to get the name of the student. So to save on the resource utilization, we might opt to store a single relation, students_report only.