[email protected] +91 9175287066
This website is always under updation with new contents
Unit - I Database Concepts
Unit I
Unit II
Unit III
Unit IV
Unit V
What is metadata? Explain its association between fields.
Ans: Definition: Metadata is the data about the data i.e. information for accessing data.
1) Consider the ex. of a textbook which has index page or content page. This page contains the chapter numbers as well as page numbers or one can access given topic/subtopic using the indicated page numbers in the contents. In this ex. Content pages or index page is form the metadata.
2) Similarly in the database environment the data about the organization of files in the database is collectively stored in a domain. This domain is called as metadata or data dictionary.
3) Metadata or data dictionary contains the data such as fieldname, field type, and field size of a table. It also contains the description about the constraint applied on a field, keys and relationship of a table with another table.
Association between fields:
Association between fields of a file is the relationship in terms of occurrences of a data of a file. The association between the fields of a file can be classified as follows.
1. One – To – One Association
2. One – To – Many Association
3. Many – To – Many Association
4. Many – To – One Association
5. One – To – One conditional Association
1. One – To – One Association:- In this type of association value of one field of a file is associated with only one value of another field of that table.
For ex in an employee file one employee_no is associated with only one employee_address.

2. One – To – Many Association:- In this type of association value of one field of a file is associated with many values of another field of that table.
For ex. In a college a teacher can teach many subjects which is shown in the figure.

3. Many – To – Many Association:- In this type of association many values of one field of a file is associated is associated with many values of another field of that table.
For ex. In a college a student can take many subject for his semester and a subject can be elected by many students. This ex. is shown in the figure.


4. Many – To – One Association: In this type of association many values of one field of a file is associated is associated with one values of another field of that table.
For ex. In a hospital many patient can visit the ward i. e. general ward

5. One – To – One conditional Association:- In this type association one value of a column is associated with one value another column only when some sort of condition is true.
For ex.In a hospital a bedno has one to one association with a patient only when it is assigned to him. Hence this type of association is known as one to one conditional association.

Explain Data independence.
Ans: Definition:- The ability to modify a schema definition in one level without affecting a schema definition in the next higher level is called data independence.
Using data independence we can modify the structure of a table at one level without disturbing the next level that is logical or physical. The two data independence are Physical and logical. They are discussed as follows:-
1) Physical Data Independence:- Physical Data independence is the ability to modify the physical schema without causing application program to be rewritten modification at the physical level one occasionally necessary to improve performance.
2) Logical independence:-
2.1) It is the ability to modify logical schema without causing application program to be rewritten. Modification at the logical level is necessary whenever the logical structure of the database is altered.
2.2) Logical Data independence is more difficult to achieve as compare to physical data independence since application programs are heavily dependent on the logical structure of the data they access.
The concept of data independence is similar in many respects to the concepts of abstract data types in modern programming languages, because both hide implementation details from the user and allow user to concentrate on the general structure rather than on low level implementation.
Explain any four objectives of DBMS.
Ans: Following are some objectives of DBMS:-
1. Reduced data redundancy.
2. Consistency of data.
3. Flexiblility of file system.
4. Enhanced data sharing.
5. Increased programmer productivity.
6. Reduced program maintenance.
1. Reduced data redundancy:-Since the database approach suggest a single centralized database the amount of redundancy of data will be minimal and it will have various other related benefits.
2. Consistency of data:-The reduced data redundancy will minimize the presence of the some data in different files which will lead to consistency of data.
3. Flexibility of file system:- In database approach the database is designed based on bottom up approach which ensures that the end users have all the reports. This is possible mainly because all the reports that are currently used by various end users and also various expected future reports are taken into account while designing the database using the bottom up approach. As and when some changes in reports requirements occur, corresponding revision of the database can be done with minor changes in the database.
4. Enhanced data sharing:- Since the database approach results in an integrated centralized database, the same file can be used in different applications. This enhances the data sharing features.
5. Reduced program maintenance:-Different applications are developed under the coordination of the database administrator ,As a result there will be an integrated effort among the development groups in terms of file design a and program design. This will reduce the task of program maintenance.
6. Increased programmer productivity:-The programmer productivity is the measure of time taken to develop an application. In the database approach the data is separated from programs. This greatly solves the problem of data maintenance. Also there are many forth generations languages available to access and manipulate databases. Because of the advanced capabilities of 4GLs the time taken to develop an application will be significantly less when compared to the time taken to develop it using conventional file processing system. This amounts to an increase in the programmer productivity.
Explain any two disadvantages of Conventional file processing system.
Ans: A conventional file processing system store permanent record in various file and it needs different application programs to extract records from and add records to the appropriate files.
Following are some disadvantages of conventional file processing system.
1) Data redundancy and inconsistency:
Since different programmers create the files and application programs over a long period, the various files are likely to have different formats and the programs may be written in different programming languages. In some files same data is duplicated in several files.
For ex address and phone no. of a patient is appear in a file that consists of registration record as well as in a file that consist of treatment record .
This redundancy leads to higher storage and access cost. It also leads to data inconsistency. For ex if a patient address which is stored in various file may changed in only one file and other files contained the previous address. So same patient has different address on different file leads to data inconsistency.
2) Limited Data Sharing: In the conventional file processing system , since , the data is stored in decentralized manner on different stand –alone systems , there is a remote probability of sharing of the data . also , the limited data sharing is due to the nesting of files within each subsystem of the organization .
3) Low programmer productivity
Programmer productivity is a measure of time taken to develop an application. If the time taken to develop an application is lesser , the programmer productivity is higher and vice versa . since in the conventional file processing system , there will be inflexibility , poor enforcement of standards and excessive maintenance effort , the programmer productivity will become considerably lower.
Explain with example data abstraction in DBMS.
Ans:-Data Abstraction in DBMS:
1. A major purpose of DBMS is to provide users with an abstract view of the data. That is the system hides certain details of how the data are stored and maintained.
2. For the system to be usable it should retrieve the data efficiently. The need for efficiency has led design of complex data structures to represent data in database. Since many database users are not computer trained developers hide the complexities from users through several levels of abstraction which are describe below:-
a) Physical level
b) Logical level
c) View Level

Physical level: - The lowest level of abstraction describe s how the data are actually stored. The physical level describes complex low level data structures in detail.
2) Logical level:
1) The next higher level of abstraction describes what data are stored in the database and what relationship exists among those data. The logical level describes the entire database in terms of small number of relatively simple structures.
2)The implementation of the simple structure a at the logical level may involve complex physical level structures but the user of the logical level does not need to aware of this complexities.DBA who must decide what information to keep in the database, use the logical level of abstraction.
View level: - 1) The Highest level of abstraction describes only the part of the entire databases. Even though the logical level uses simpler structures, complexities remain because of the variety of information stored in large database. Many users of the databases systems do not need all this information; instead they need to simplify their interaction with the system. The view level of abstraction exists to simplify their interaction with the system. The system may provide many views for the same database.
2) The above figure shows relationship among three levels of abstraction. 3) We can consider the ex of data types in programming languages may clarify the distinction among levels of abstraction. Most high level programming languages support the notion of a record type .In Pascal like languages we may declare a record as follows:
Type customer= record
Customer-id: string;
Customer-name: string;
Customer- street: string;
Customer-city: string;
End;
4) At the physical level a customer rerecord can be described as a block of consecutive storage locations. The language complier hides this level of detail from programmers.
5) Similarly the database system hides many of the lowest level storage details from databases programmers. On the other hand DBA may be aware of certain details of the physical organization of the data.
6) At the logical the interrelationship of these record types is defined .Programmers using a programming languages usually work at this level of abstraction.DBA work at this level.
7) Finally at the view level computers users see a set of application programmers that hide details of the data type. Similarly at the view level several views of database are defined and database users see this view.
8) This level also provides a security mechanism to prevent users from accessing certain parts of database.
For ex tellers in a bank see only that part of the database that has information on customer accounts. They cannot access information about salaries of employees.
Describe the architecture of DBMS. Ans:- Architecture of DBMS.

EXTERNAL LEVEL (highest level)
• It consists of a number of different external views of the Database.
• It describes part of the database for particular group of users.
• It provides a powerful and flexible security mechanism by hiding parts of the DB from certain users. The user is not aware of the existence of any attributes that are missing from the view.
• It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.
CONCEPTUAL LEVEL
It is also called as global view .It describes the logical structure of the entire database as seen by DBA. The description is given as follows
• What data is stored in the database?
• What relationships among the data?
• Complete view of the data requirements of the organization, independent of any storage consideration.
•Represents: entities, attributes, relations, constraints on data, semantic information on data, security, and integrity information Supports each external view: any data available to a user must be contained in or derivable from the conceptual level.
INTERNAL LEVEL
This view level is at the lowest level of database abstraction which is nearest to the physical storage. It describes physical representation of the database on the computer and it gives the details about how the data is stored in the database.
- It facilitates storage space allocation for data and indexes
- This level gives record description for storage and record placement
- Data compression, encryption can be done at this level.
PHYSICAL LEVEL
Managed by the OS under the direction of the DBMS. This level decides how much space should be allocated for the table and files.
Mapping between views:-
A mapping between external & conceptual views gives the correspondence among the records & the relationship of the records. The mapping between internal and conceptual view indicates how each conceptual record is to be stored.
Conceptual/internal mapping: To find the actual record (combinations) in physical storage that constitutes a logical record in the conceptual schema.
External/conceptual mapping: Map names in the user’s view onto the relevant part of the conceptual schema.

What are different types of users in DBMS?. Explain.
Ans:- There are four different types of users in DBMS differentiated by the way they expect to interact with the system. Different types of user interfaces have been designed for the different types of users.
1) Naive Users: - These are the unsophisticated user who interacts with the system by invoking one of the applications programs that have been written previously. For ex a bank teller who needs to transfer $50 from account A to Account B invokes a program called transfer. This program asks the teller for the amount of money to be transferred and the account to which the money is to be transferred. The typical user interface for naive user is a form interface where use can fill appropriate fields of the form. Naive user simply reads reports generated from the database.
2) Application Programmer: These are the computer professional who write application programs. Application programmers can choose from many tools to develop user interfaces. Rapid application development tools are tools that enable an application programmer to construct forms and report without writing a program. These are also special types of programming languages that combine imperative control structures for ex for loop, while lop etc. These languages are called as fourth generation languages.
3) Sophisticated users: These users interact with the system without writing programs. Instead they form their requests in a database query languages. They submit each query to a query processor whose function is to break down DML statement into instructions that the storage manager understands. Analysts who submit queries to explore data in the database fall in this category.
4) Specialized users: These are sophisticated users who write specialized database applications that do not fit into the traditional data processing framework. Among these applications are computer aided designs systems, knowledge base and expert systems that store data with complex data types for ex. Graphics data and audio data.
Define data-item
Data : The center of any information system is data, the basic facts upon which a company’s information need are generated. A System consists of interrelated entities each of which has a set of attributes. These attributes are called as data-item. For ex we can say roll no, class, result are the data-item of the student.
As data is defined as some instances of the attributes of the entities of the system. Consider an organization such as college who provide education. The entities of these systems are student, staff. Each of these has certain attributes for ex. Roll no, class subject etc. are the attributes of student or the data-item of student. Data is always a collection of data-item. IT is defined as raw or unorganized facts observations.
Explain File, records and field. Also give suitable example.
Ans: File:- a file is a collection of rows and column. A column is defines as the attributes of an entity. A file store data in the form of records. So a file is a collection of records having the same set of fields arranged in the same sequence.
For ex a layout of the file employee info is shown as follows:

Record:-A record is a collection of fields or attributes of an entity in a desired sequence. Every attributes has some value which from the record. Any files maintain the data in the form of record. For ex. the table as shown above has two records of the employee.
Field: a field is the lowest level of data item of an entity which is alternatively called as an attribute of that entity. Fields store data in a file in the form of record. Consider the entity employee which has the fields like empno, empname, salary and designation which is shown in the above table.
What is information? Explain.
Ans: Information: Processed data is called as information. In other word we can say that data is like a raw material and the information is like the product made using the raw material.
For ex. In company details of all employees data are maintained in proper file.
The master file such as employee_info, leave_info files contain all the basic information such as employees date of birth, his date of joining, salary designation, leave id ,leave type etc where as transaction file such as salary-transaction, leave-transaction which contain the details of salary ,leave taken by different employee. Transaction in the transaction files are done by using all masters file.
The information of a particular subsystem will act as data for another subsystem.

In the above table employee master file act as data for salary transaction file .Similarly Leave Master file act as data for Leave transaction File.
What are advantages of DBMS.
Ans: Following are some advantages of DBMS
1. Reduced data redundancy.
2. Consistency of data.
3. Flexibility of file system.
4. Enhanced data sharing.
5. Increased programmer productivity.
6. Reduced program maintenance.
1. Reduced data redundancy:-Since the database approach suggest a single centralized database the amount of redundancy of data will be minimal and it will have various other related benefits.
2. Consistency of data:-The reduced data redundancy will minimize the presence of the some data in different files which will lead to consistency of data.
3. Flexibility of file system: - In database approach the database is designed based on bottom up approach which ensures that the end users have all the reports. This is possible mainly because all the reports that are currently used by various end users and also various expected future reports are taken into account while designing the database using the bottom up approach. As and when some changes in reports requirements occur, corresponding revision of the database can be done with minor changes in the database.
4. Enhanced data sharing:- Since the database approach results in an integrated centralized database, the same file can be used in different applications. This enhances the data sharing features.
5. Reduced program maintenance:-Different applications are developed under the coordination of the database administrator ,As a result there will be an integrated effort among the development groups in terms of file design a and program design. This will reduce the task of program maintenance.
6. Increased programmer productivity:-The programmer productivity is the measure of time taken to develop an application. In the database approach the data is separated from programs. This greatly solves the problem of data maintenance. Also there are many forth generations languages available to access and manipulate databases. Because of the advanced capabilities of 4GLs the time taken to develop an application will be significantly less when compared to the time taken to develop it using conventional file processing system. This amounts to an increase in the programmer productivity.
Explain data, information and metadata.
Ans: -Data :The center of any information system is data, the basic facts upon which a company’s information need are generated. A System consists of interrelated entities each of which has a set of attributes. Data are some instances of the attributes of the entities of the system. Consider an organization such as college who provide education. The entities of these systems are student, staff. Each of these has certain attributes for ex. roll no, class subject etc. are the attributes of student.
So Occurrences of all these attributes of each of the entities are known as data. In the other words we can say that data is the unprocessed material of any system.
Information: Processed data is called as information. In other word we can say that data is like a raw material and the information is like the product made using the raw material.
For ex. In company details of all employees data are maintained in proper file.
The master file such as employee_info, leave_info files contain all the basic information such as employees date of birth, his date of joining, salary designation, leave id ,leave type etc where as transaction file such as salary-transaction, leave-transaction which contain the details of salary ,leave taken by different employee. Transaction in the transaction files are done by using all masters file.
The information of a particular subsystem will act as data for another subsystem.

In the above table employee master file act as data for salary transaction file. Similarly Leave Master file act as data for Leave transaction File.
Metadata:-
Definition: Metadata is the data about the data i.e. information for accessing data.
1) Consider the ex. of a textbook which has index page or content page. This page contains the chapter numbers as well as page numbers or one can access given topic/subtopic using the indicated page numbers in the contents. In this ex. Content pages or index page is form the metadata.
2) Similarly in the database environment the data about the organization of files in the database is collectively stored in a domain. This domain is called as metadata or data dictionary.
3) Metadata or data dictionary contains the data such as fieldname, field type, and field size of a table. It also contains the description about the constraint applied on a field, keys and relationship of a table with another table.
Disadvantage of DBMS:-
A database system generally provides on-line access to the database for many users. In contrast, a conventional system is often designed to meet a specific need and therefore generally provides access to only a small number of users. Because of the larger number of users accessing the data when a database is used, the enterprise may involve additional risks as compared to a conventional data processing system in the following areas.
1. Confidentiality, privacy and security.
2. Data quality.
3. Data integrity.
4. Enterprise vulnerability may be higher.
5. The cost of using DBMS.
1. Confidentiality, Privacy and Security
When information is centralized and is made available to users from remote locations, the possibilities of abuse are often more than in a conventional data processing system. To reduce the chances of unauthorized users accessing sensitive information, it is necessary to take technical, administrative and, possibly, legal measures.
Most databases store valuable information that must be protected against deliberate trespass and destruction.
2. Data Quality
Since the database is accessible to users remotely, adequate controls are needed to control users updating data and to control data quality. With increased number of users accessing data directly, there are enormous opportunities for users to damage the data. Unless there are suitable controls, the data quality may be compromised.
3. Data Integrity
Since a large number of users could be using a database concurrently, technical safeguards are necessary to ensure that the data remain correct during operation. The main threat to data integrity comes from several different users attempting to update the same data at the same time. The database therefore needs to be protected against inadvertent changes by the users.
4. Enterprise Vulnerability
Centralizing all data of an enterprise in one database may mean that the database becomes an indispensible resource. The survival of the enterprise may depend on reliable information being available from its database. The enterprise therefore becomes vulnerable to the destruction of the database or to unauthorized modification of the database.
5. The Cost of using a DBMS
The database approach provides a flexible alternative where new applications can be developed relatively inexpensively. The flexible approach is not without its costs and one of these costs is the additional cost of running applications that the conventional system was designed for. Using standardized software is almost always less machine efficient than specialized software.
Explain entity ,attributes and relationship.
Ans:- Entity:
1) An entity is a thing or an object in the real world that exists and is distinguishable from all other objects. For instance, each person in an enterprise is an entity as he can be uniquely identified as one particular person in the enterprise.
2) An entity has set of property and the values for the same set of properties may uniquely identify an entity. For ex a person may have a person-id whose value uniquely identifies that person. Similarly loan can be an entity and loan number l-15 uniquely identifies the loan entity.
3) A entity may be concrete such as person or a book or it may be abstract such as a loan or a holiday or a concept.
4) An entity set is a set of entities of the same type (e.g., all persons having an account at a bank) that share the same properties or attributes. The set of all people who are customer in bank can be defined as entity set customer
5) Entity sets need not be disjoint. For example, the entity set employee (all employees of a bank) and the entity set customer (all customers of the bank) may have members in common.
Attribute:
1) An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set.
2) The designation of an attribute for an entity set expresses that the database stores similar information concerning each entity in entity set, however each entity may have its own value for each attribute.
3) Possible attribute of customer entity set are customer-id, customer-name, Customer Street.
4) Each entity has value for its attributes. These set of permissible values are called as domain. The attributes are classified depending on their use. They are as follows:
5) Simple and composite attributes: simple attribute not divided into subparts where as a composite attribute are divide into the subparts. For ex a name attribute is a composite attribute which is further divide into firstname, middlename, lastname.
6) Single valued and multivalued attributes: when attribute ha a single value then it is called as single valued attribute. For ex. Loan-number attribute for a loan entity refers to only one loan number. When attributes has multiple values known as multivalued attributes. For ex an employee has many phno like landline number, mobile number etc.
7) Derived attributes: The value of these types of attributes is derived from the values of other related attributes or entities. For ex the customer entity set has an attribute loan-held which represents how many loans a customer has from the bank. we can derive the value for these attributes by counting the number of loans entities associated with that customer.
Relationship:
A relationship is an association among several entities. For ex. We can define a relationship that associates customer Ram with loans L-15.This relationship specifies that Ram is customer with Loan number 15. As shown in the table below customer and loan are two distinct entity having attributes custid, custname, loan_id ,amount .The two table relate on the basis of a common field i.e.custid

Explain the different components of DBMS.
Ans:
Components of DBMS
A schematic representation of components of DBMS is shown in the fig. It is clears from the fig. that core subsystem of the DBMS is consists of DBMS Utilities, database and data dictionary/Directory.
1) DBMS Utilities are like Oracle, Sybase, Focus, IDMS, IMS, etc. These are the database languages which are in turn used to develop different applications. Many of these languages belong to Fourth generation’s language. Which have many distinct features when compared to DbaseIII, Dbase, and FoxPro. These GLs has the capabilities of generating source code for different applications just by clicking buttons through a menu driven option.
2) Database is a collection of interrelated files. In DBMS which has an integrated single database all the problems of conventional file system will be minimized even though the time taken to design and implement a robust database will be relatively longer when compared to the design of files in the conventional file processing system.
3) Data dictionary /Directory are a system which maintains data about data in the database. It is also known as system containing metadata. This will contain information about following.
• Number of files in the database.
• Name of the files
• Association between files
• Structure of each file, which includes the list of fields in it, type of each field length of each field, key filed and alternate name of each field.
4) Application Developer: These are the qualified programmer /analyst who are available with the company to develop different applications which are required for the users. They use other DBMS component to develop different applications for satisfying organizational information need.
5) User Group: These are the people belonging to different sub systems of a company. These user groups will use the different applications which are developed by the application developers of the company.
6) Database Administrator: - It is the apex body which controls all the software entities, database entities, application developers and user group.
The different arrows in fig show the existence of interface between the respective pair of components of the total system .The DBA has association with all other entities. The application developer has association with the database management system, database and DD/D. The user groups have association with the database management system and DBA. The DBMS and DD/D are closely together.

Database systems are divided into four parts for different functions. Some functions may be provided by the operating system. The database management system is structured and interfaces with various users as shown in fig.
The major components are described below:
• Data definition language compiler: DDL compiler converts the data definition statements into a set of tables. These tables contain the metadata concerning the database and are in a form that can be used by other components of the DBMS.
• Data Manager: It is the central software component of the DBMS. It is sometimes referred to as the database control system. One of the function of the data manager is to convert operations in the user‘s queries coming directly via the query processor or indirectly via an application program from the user’s logical view to physical file system. The data manager is responsible for interfacing with the file system. In addition the task of enforcing constraints to maintain the consistency and integrity of the data as well as its security are also performed by the data manager.
• File manager: Responsibility for the structure of the files and managing the file space rests with the file manager. It is also responsible for locating the block containing the required record, requesting the block from disk manager. • Query processor: The database user retrieves data by formulating a query in the data manipulating language provided with the database. The query processor is used to interpret the online user’s query and convert it into an efficient series of operations in a form capable of being sent to the data manager for execution.
• Telecommunication system: Online users of a computer system whether remote or local, communication with it by sending or receiving messages over communication lines. These messages are routed via an independent software system called a telecommunication system or a communication control program. It is not the part of the DBMS but the DBMS works closely with this system. The online users may communicate with the database directly or indirectly via a user interface and an application program.
• Data Files : It contains the data portion of the database.
• Data dictionary: DD is a database itself which documents the data. Each database user consults the DD to learn what each piece of data and various data field of it.
What is metadata? Differentiate between conventional file processing system and databse system.
Ans: Definition: Metadata is the data about the data i.e. information for accessing data.
1) Consider the ex. of a textbook which has index page or content page. This page contains the chapter numbers as well as page numbers or one can access given topic/subtopic using the indicated page numbers in the contents. In this ex. Content pages or index page is form the metadata.
2) Similarly in the database environment the data about the organization of files in the database is collectively stored in a domain. This domain is called as metadata or data dictionary.
3) Metadata or data dictionary contains the data such as fieldname, field type, and field size of a table. It also contains the description about the constraint applied on a field, keys and relationship of a table with another table.

How does database contribute to efficient and effective utilization data.
Ans: Due to its suitable features database store the data efficiently as compared to conventional file. The features are described below:
1) Centralization of data: In DBMS though data stored in different files or table but all access can be make under the control of Database administrator.
2) Data dictionary: The structure of table like details of the field which include data type size of each field is stored on the DD. It also information of relationship between tables.
3) It provides the user a user friendly environment to work though the data internally stored in complex format. It provides several tools which are even easy for naïve user.
4) It isolates work area depend on the user’s ability. For ex. Highly experienced programmer takes the responsibility of DBA. Another part that is development is taken by another category called application programmer which develop application program for accessing data from data storage.
Explain:- Ans: Data independence: Qi1b of summer 2005
Data integration:
1) Database integrity means the correctness and consistency of data. It is another form of database protection. Security means that the data must be protected from unauthorized operations. Integrity is related to the quality of data. Integrity is maintained with the help of integrity constraints. These constraints are the rules that are designed to keep data consistent and correct. They act like a check on the incoming data. It is very important that a database maintains the quality of the data stored in it. DBMS provides several mechanisms to enforce integrity of the data.
2) Types of Integrity: Two types of data integrity are as follows:
Entity Integrity: The entity integrity is a constraint on primary key value. It states that any attribute of a primary key cannot contain null value. If primary key contains null value, it is not possible to uniquely identify a record in a relation. Entity integrity ensures that it should be easy to identify each entity in the database.
Referential Integrity: The referential integrity is a constraint on foreign key value. It states that if a foreign key exists in a relation, the foreign key value match the primary key value of some tuple in its parent relation. Otherwise the foreign key value must be completely null.
Explain each of the following association between fields by giving suitable examples:
Ans: Association between fields:
Association between fields of a file is the relationship in terms of occurrences of a data of a file. The association between the fields of a file can be classified as follows.
1. One – To – One Association
2. One – To – Many Association
3. Many – To – Many Association
4. Many – To – One Association
1. One – To – One Association:- In this type of association value of one field of a file is associated with only one value of another field of that table.
For ex in an employee file one employee_no is associated with only one employee_address.

2. One – To – Many Association:- In this type of association value of one field of a file is associated with many values of another field of that table.
For ex. In a college a teacher can teach many subjects which is shown in the figure.

3. Many – To – Many Association:- In this type of association many values of one field of a file is associated is associated with many values of another field of that table.
For ex. In a college a student can take many subject for his semester and a subject can be elected by many students. This ex. is shown in the figure.

4. Many – To – One Association: In this type of association many values of one field of a file is associated is associated with one values of another field of that table.
For ex. In a hospital many patient can visit the ward i. e. general ward

	i) STUDENT (stud#, stud_name, specialization, averagemark)
	ii) ENROLLMENT (stud#, subjectcode#, mark)
	iii) Subject (subject_code#, subject_name, Max_mark)
	
Define different relations on above database.
Ans: In the above database the relation student is related to Enrollment. And this relationship is One-To-Many which means that one student can take many subject In his academic years. In student relation the attribute stud# has defined primary key on it. So it acts as a parent table for Enrollment relation.
Enrollment relation is related to Subject relation using one to one relationship. In enrollment table it contains two foreign key columns one is stud# and another is on subjectcode#. So enrollment relation act as child table for student relation as well as for Subject relation.
Subject relation is related to enrollment relation using One-To-Many relationship because a student can take many subjects for his academic session. In subject relation primary key is defined on subject code. So it acts as parent table for Enrollment relation.

what is hashed file organization? Explain the steps of remainder algorithm for hashing.
Ans: Hashed file organization:
Hashing involves computing the address of a data item by computing a function on the search key value.
A hash function h is a function from the set of all search key values K to the set of all bucket addresses B. Bucket is the unit of storage that can store one or more records. A bucket is typically a disk block but could be smaller or largere than a disk block.
o We choose a number of buckets to correspond to the number of search key values we will have stored in the database.
o To perform a lookup on a search key value , we compute , and search the bucket with that address.
o If two search keys i and j map to the same address, because , then the bucket at the address obtained will contain records with both search key values.
o In this case we will have to check the search key value of every record in the bucket to get the ones we want.
o Insertion and deletion are simple.
Modulo-division Method
• This is also known as division remainder method.
• This algorithm works with any list size, but a list size that is a prime number produces fewer collisions than other list sizes.
• The formula to calculate the address is:
Address = key MODULO listsize + 1
Where listsize is the number of elements in the arrary.
Example:
Given data :
Keys are : 137456 214562 140145
137456 % 19 +1 = 11
214562 % 19 + 1 = 15
140145 % 19 + 1 = 2

(Note: student can write any five from the following functions)
1. Data Dictionary Management:
Data Dictionary is where the DBMS stores definitions of the data elements and their relationships (metadata). The DBMS uses this function to look up the required data component structures and relationships. When programs access data in a database they are basically going through the DBMS. This function removes structural and data dependency and provides the user with data abstraction. The Data Dictionary is often hidden from the user and is used by Database Administrators and Programmers.
2. Data Storage Management:
This particular function is used for the storage of data and any related data entry forms or screen definitions, report definitions, data validation rules, procedural code, and structures that can handle video and picture formats. Users do not need to know how data is stored or manipulated.
3. Data Transformation and Presentation:
This function exists to transform any data entered into required data structures. By using the data transformation and presentation function the DBMS can determine the difference between logical and physical data formats.
4. Security Management:
This is one of the most important functions in the DBMS. Security management sets rules that determine specific users that are allowed to access the database. Users are given a username and password or sometimes through biometric authentication (such as a fingerprint or retina scan) but these types of authentication tend to be more costly. This function also sets restraints on what specific data any user can see or manage.
5. Multiuser Access Control:
Data integrity and data consistency are the basis of this function. Multiuser access control is a very useful tool in a DBMS, it enables multiple users to access the database simultaneously without affecting the integrity of the database.
6. Backup and Recovery Management:
Backup and recovery is brought to mind whenever there is potential outside threats to a database. For example if there is a power outage, recovery management is how long it takes to recover the database after the outage. Backup management refers to the data safety and integrity; for example backing up all your mp3 files on a disk. 7. Data Integrity Management:
The DBMS enforces these rules to reduce things such as data redundancy, which is when data is stored in more than one place unnecessarily, and maximizing data consistency, making sure database is returning correct/same answer each time for same question asked.
8. Transaction Management:
This refers to how a DBMS must supply a method that will guarantee that all the updates in a given transaction are made or not made. All transactions must follow what is called the ACID properties.
A – Atomicity: states a transaction is an indivisible unit that is either performed as a whole and not by its parts, or not performed at all. It is the responsibility of recovery management to make sure this takes place. C – Consistency: A transaction must alter the database from one constant state to another constant state. I – Isolation: Transactions must be executed independently of one another. Part of a transaction in progress should not be able to be seen by another transaction. D – Durability: A successfully completed transaction is recorded permanently in the database and must not be lost due to failures.
What is generalization and aggregation E-R models ? Explain with examples. Ans:
Specialization or generalization: An entity set may include sub groupings of entities that are distinct in some way from other entities in the set. For instance a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. These sub groupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set. Consider the entity set account with attributes account number and balance. An account is further classified as being one of the following:-
i) Savings account and
ii) Checking account
Each of this account type is further described by a set of attributes that includes all the attributes of entity set account plus additional attributes. For example saving account entities are described further by the attribute interest-rate, where as checking account entities are further described by the attribute overdraft-amount. The process of designating sub groupings within an entity set is specialization. The specialization of account allows us to distinguish among accounts based on the type of account.
Generalization defines a is-a-kind of relationship in which one class shares its structure and/or behavior with one or more other classes. Consider the example of checking account entity from the above figure with attributes account_number, balance and overdraft_amount and the saving account entity set with the attributes account_number, balance and the interest_rate.
There are similarities between the cheking_account entity set and the saving_account entity set in the sense that they have several attributes in common. This commonality can be expressed by generalization, which is a containment relationship that exists between a higher level entity set and one or more lower level entity set. Here the account entity set is the higher level entity set and the checking account is the lower level entity set.
Aggregation - a feature of the entity relationship model that allows a relationship set to participate in another relationship set. This is indicated on an ER diagram by drawing a dashed box around the aggregation. The E-R model cannot express relationships among relationships.
Consider a DB with information about employees who work on a particular project and use a number of machines doing that work. We get the E-R diagram shown in Figure (A)

Figure A : E-R diagram with redundant relationships
Relationship sets work and uses could be combined into a single set. However, they shouldn't be, as this would obscure the logical structure of this scheme.
The solution is to use aggregation.
• An abstraction through which relationships are treated as higher-level entities.
• For our example, we treat the relationship set work and the entity sets employee and project as a higher-level entity set called work.
Transforming an E-R diagram with aggregation into tabular form is easy. We create a table for each entity and relationship set as before. The table for relationship set uses contains a column for each attribute in the primary key of machinery and work.
Explain direct file organization.
Ans: A Direct file is one whose records are stored in such a way that access to any one of them is direct – that is without going through a lot of records to get to the one(s) you want.
Records in a Direct File are not placed one after the other – rather a location for the file is set aside on disk and then the records are written to any location that is free within this area. A technique known as “hashing” uses a Key to produce the location on the disk for each of the Records – and it is through this that individual records can be accessed directly.
A Direct file provides the fastest possible access to records. ISAM (indexed-sequential-access method) also provides users with direct access to individual records. Direct file is typically the best when access time is critical and when batch processing is not necessary.
Access time is the interval between the moment at which an instruction control unit initiates a call for data and the moment at which delivery of the data is completed. For example, direct access memory is faster than sequential access memory.
A Direct file uses a formula to transfer the primary key to the location of each record. This formula is called a Hashing algorithm. Therefore, no index is needed to locate individual records. Many hashing algorithms have been developed. In general, the primary key value is divided by a prime number, which corresponds to the maximum number of storage locations allocated for the records of this file. The reminder obtained in this division is then used as the relative address of a record, but relative address can be translated into physical locations on the storage medium.
For example you input a Student ID Number, a mathematical formula is applied to it, and the resulting value is the value that points to the storage location on disk where the record can be found.
Explain entities, attributes and associations example.
Entity is a thing in the real world with an independent existence and entity set is collection or set all entities of a particular entity type at any point of time. It is used to define object such as student, customer, employee. Take an example: a company have many employees ,and these employees are defined as entities(e1,e2,e3....) and all these entities having same attributes are defined under ENTITY TYPE employee and set{e1,e2,.....} is called entity set.
Attributes: An entity can be defined by its property. These properties describe the characteristics of an entity and it is used to store data about an entity.
In a database management system (DBMS), an attribute may describe a component of the database, such as a table or a field, or may be used itself as another term for a field.
Types of attributes:
Single valued and multivalued attributes: The attributes all have a single value for a particular entity are said to be single valued. For ex. Loan entity refers to only one loan number. An entity which has multiple value such as an employee entity set with the attribute phone_number. An employee may have zero, one or several phone numbers and different employees may have different numbers of phone. This type of attribute is said to be mutivalued.
Derived attribute: The value for this type of attribute can be derived from the values of others related attributes or entities. Suppose that the customer entity set has an attribute age , which indicates the customer’s age. If the customer entity set also has an attribute date of birth, we can calculate age from date of birth and the current date. Thus age is derived attribute.
Association:
What are different models use in DBMS? Explain any one of them.
Ans: Data Model can be defined as an integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization.
A data model comprises of three components:
• A structural part, consisting of a set of rules according to which databases can be constructed.
• A manipulative part. Defining the types of operation that are allowed on the data (this includes the operations that are used or updating or retrieving data from the database and for changing the structure of the database).
• Possibly a set of integrity rules, which ensures that the data is accurate.
The purpose of a data model is to represent data and to make the data understandable. There have been many data models proposed in the literature. They fall into three broad categories:
• Object Based Data Models
• Physical Data Models
• Record Based Data Models
The object based and record based data models are used to describe data at the conceptual and external levels, the physical data model is used to describe data at the internal level.
1. It is an example of record-based model. In record based models the database is structured in fixed –format. Relational model contains a collection of tables.
2. These tables are used to represent both data and the relationship among those data. Each table has multiple columns with unique names.
3. The relational model has three basic components :
a. 1) Data structures: It is the set of attributes and relation.
b. Data manipulation: These are the operations on relation.
c. Data integrity rules.
5. Fig shows a sample relational database. It consists of two tables. One is customer table and another is account table.
Customer Table:
• The customer table stores the customer information for example the customer Yash has id 002-1140 lives on Satara Road and has an account number 512.
• In relational data model the data structure is used to store data and relation among the data. It is used to represent both entities and the relationship between them.
• Relations are stored in table. Rows of relation called as tuples of the relation and columns are called attributes of the relation.
• Each attribute of a relation has distinct name. The values for an attribute or a column are taken from a set of values. These set of values are known as Domain.
• The basic building blocks of the relational model are listed and are shown in fig.
o Relation: It is a table of data e.g. Cust table.
o Tuple: It is row of table.
o Attribute: It is a column of table.
o Cardinality: The number of tuples in a relation is called as cardinality of the relation.
o Degree: The number of attributes in a relation is called as degree of a relation.
o Primary key: It is used to identify each row uniquely. It is also called as integrity constraint.
What are the advantages and disadvantages of index sequential file?
Ans: Advantges:
An Indexed Sequential file can be accessed in two ways
1. Sequentially reading through each of the records from the beginning in the Main data file – ie exactly the same way as a sequential file OR
2. Using the Index file (which is a MUCH smaller file) to determine the location on disk of the Record – and then accessing that Record using the Direct manner.
3. They can be quite efficiently used for sequential processing of high activity ratio applications.
Disadvantages:
An Indexed file has problems with lots of records that are added and deleted i.e. deleted records are not really deleted – they are “flagged” as deleted and additions to the file are not placed in sequential order in the main data file – they are placed in any available space – and this is catered for in the Index file.
The Indexed Sequential File is not really suitable for Volatile files i. e. the files in which a lot of records are being written and deleted.
Differentiate between network and hierarchical data model in DBMS.
Ans: Network data model:
• It is based on Record based data model.
• Data in Network is represented by the collection of records and the relationships among data are represented by links.
• Links are nothing but the pointers.
• The records in the database are organized as collection of arbitrary graph.
• A network database is a collection of records.
• In graphical representation these records are connected to one another through links.
• A link is an association between two records in the database.
• A data structure diagram is used to represent the design of a network database. In this diagram boxes are used to represent record types and lines are used to represent links.
• Following figure shows sample network databse suing the same information as represented in fig.

• In network model any data item can be linked to any other item.
• This structure allows One- To – One, One- To- Many , Many – To – One, Many-To- Many relationship. Network model has a complex structure. Hierarchical Data Model:
• It is one of the types of record based data model. It is similar to the network data model.
• In both models the records and the links are used to represent data and relationship among data respectively.
• In this model records are organized as a collection of trees rather than arbitrary graphs.
• To represent hierarchical database tree structure diagram is used. In this diagram boxes are used to represent record types and lines are used to represent links.
• For example consider a customer and account database. They are represented as two record types. A sample database is shown in fig.
• In the above sample database all customers and accounts records are organized in the form of a rooted tree, where the root of the tree is a dummy node.
• Hierarchical model is sometimes called as tree structure. It is composed of the hierarchy of elements called nodes.
• The upper most level of hierarchy has only one node called the root. No element can have more than one parent.
• The structure allows one to- one, one-to –many relationships.
• The disadvantage of this structure is wastage of storage space.
Discuss sequential file.
Ans: Sequential file:
• A sequential file is one whose records are arranged in an order – that is by a key of some kind. To put the transactions in order the file has to be sorted according to the order of the Key Field. Examples of Key fields would be Tax File Number (TFN), University Student Number or Medicare Number.
• To gain access to a record in a sorted sequential file involves going though all the records until the desired record is encountered. This is why it is called sequential and Tape is a common way of storing sequential files. Sequential files can also be stored on Disk.
• Updating the Sequential must occur by a Batch process which first involves sorting the new data. The next step is to read the sequential file (which itself is in order) and insert the new items in their correct locations – and then rewriting the new file in the process
• Systems that are suitable for sequential batch processing are systems that are able to collect data and accept output at a leisurely rate. You cannot get instant results with a Batch processed Sequential File
what is hashed addressed direct file organization? Give steps to find the hashed address for each and every key of the file.
Hashed addressed direct file organization :
The keys of records in the hashed addressed direct file organization are scattered randomly throughout an area called hashing table which enables the users to access each and every record in a rapid manner directly without using indexes. Hence it is considered to be the most efficient methods for accessing files in most of the online system.
The hashed file consists of primary storage area and overflow storage area. Each of this area consists of several buckets and slots. Each bucket has a set of slots. A slot is the smallest storage location in the hashed file organization.
Each and every key value of a record will be stored in a slot of bucket using a given hashing algorithm.
There are many algorithms to find the hashed address for each and every key of the file of our interest. An efficient key conversion algorithm is remainder algorithm.
Steps to find the hashed address for each and every key of the file are as follows:
step 1. Identify the total number of records in the file.
Step 2. Decide the number of slots per bucket.
Step 3. Determine the approximate number of buckets required to store the keys of all the records of the file. This should be a prime number.
Step 4. Consider the first key.
Step 5. Divide that key by the prime number and find the remainder. Let it be R.
Step 6. Check whether ther is at least one unfilled slot in the Rth bucket of the primary storage area. If the answer is yes, go to step 7, otherwise go to step 8.
Step 7: Store the current key in the first unfiled slot of the Rth Bucket. Then go to step 9.
Step 8.Store the current key in the overflow storage area.
Step 9. Consider the next key.
Step 10. Repeat step 5 through step 9 until all the keys are placed either n the primary storage area or in the overflow area of the hashed file structure.
Explain the properties of id keys.
Ans: In the relational model we represent the entity by a relation and use a tuple to represent an instance of the entity. Different instances of an entity type are distinguishable and this fact is established in a relation by th requirement that no two tuples of the same relation can be same. In the instance of an employee relation values of an attribute such as emp# may be sufficient to distinguish between employees tuples. Such a subset of attributes let us say X of a relation R(R), X is the subset of R, with the following time independent properties is called the key of the relation:
Unique identification: In such tuple of R, the values of X uniquely identify that tuple. To elaborate if s and t represent nay two tuples of relation and if values s[x] and t[x] for the attributes in X in the tuples s and t are the same, then s and t must be the same tuple. Therefore s[X] =t[X]=>s=t. Here the symbol => is used to indicate that the left hand side logically implies the right –hand-side.
Nonredundancy: No proper subset of X has the unique identification property, i.e. no attribute K belongs to X can be discarded without violating the unique identification property.

1) Select operation: this operation can be used to extract specified rows from the table that satisfy the given predicate.
2) The sigma (б) is used to denote the selection
3) The predicate appears as the subscript to б. The argument relation is given in parenthesis following the б as given below б predicate(argument relation) 4) Several predicated can be combined together by using logical connectives (ѵ) and or (Λ) .
5) The comparison operators such as =,!=,==, <,<=,>=, can be used in the selection predicate.
6) For ex. Suppose we want to extract the information of only those customer who lives in pune city then the relation is as shown in fig.

2) Project Operation:
1) It is a unary operation used to extract the specified columns from the table.
2) It is denoted by the symbol π.
3) By using project operation we can list those attribute that we want in the resulting relation as a subscript to π and the argument is written in the parenthesis as shown below.
What is relational calculus? Explain tuple calculus with example. Ans: Relational calculus:
• It is a query system where queries are expressed as variables and formulas on these variables. These variables are called as tuple variables. The formulas describe the properties of the required result relation without specifying the method of evaluation. Relational calculus means calculus with relations which are based on predicate calculus. Relational calculus may be based on tuple or domain.
• Depending on this there are two types of relational calculus.
o Tuple oriented calculus
o Domain oriented calculus
• It is a non procedural language and uses a formal language. It uses a natural language used to express the logical arguments in arguments in mathematics.
• Tuple oriented calculus is a non procedural query language. It describes the desired information without giving a specific procedure for obtaining that information.
• A query in topple oriented relational calculus is expressed as {t|p(t)}
o It is set of all tuples t such that the predicate P is true for t.
o t[A]- represents value of that tuple (t) on attribute for t.
o TєR – represent that tuple is in relation R.
Example:-
1)	Find the branch name, loan number, customer-name and amount for loans of over Rs 2000.
{t\t є borrow Λ t [amount]>1200}
2)	 Find all customers who have an account at all branches located in pune.
In this example “for all” construct is denoted by Ѵ.
a.	Ѵ t є r(Q(t))
This means Q is true for all tuples in relation r.
Formal definition of tuple oriented calculus:
 A tuple relational calculau expression is of the from 
{t|P(t)}
Where P- formula
	T- tuple variable
Several tuple variable may appear in a formula. A tuple variable is said to be a free variables unless it is quantified by a      Or Ѵ   otherwise it is a bound variable.
A tuple relational calculau formula is built up out of atoms. An atom has  one of the following  forms.
1)	Sєr  where s is a tuple variable  and r is a relation.
2)	 S[x] Ѳu[y]
S,u – tuple variables
X – attribute on which  s is defined 
y- attribute  on which  u is defined 
Ѳ – comparision  opearator < such as <,<=,=,>,>
We require that  x and y have domains whose members can be compared by  Ѳ
What is relational database? Explain attributes and domains with example.
Ans: Short for relational database management system and pronounced as separate letters, a type of database management system (DBMS) that stores data in the form of related tables. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways.
An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.

Attribute: consider an employee relation. It consists of five columns namely emono, ename, job, sal ,deptno etc. This columns heads as the attributes of the relation.
As an entity in an enterprise is characterized by its properties. These properties are nothing but the attributes of the relation.
Domain:
• A domain can be defined as the set of scalar values of the same data type.
• Consider a customer relation shown in fig. For example the domain of customer number is the set of all possible customer numbers. Similarly the domain of balance amount is the set of all integers greater than Zero and less than one million .
• Thus the domain of an attribute can be defined as the set of allowable values from which the actual attribute values can be obtained.
• The values of an attribute must be taken from the domain only. Thus at any given time the set of cust_id# values in Cust relation must be the subset of the set of values appearing in the cust _id domain.
• A domain is of two types:
Write short note on SQL.
Ans:
Abbreviation of structured query language, and pronounced either see-kwell or as separate letters. SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center in 1974 and 1975. SQL was first introduced as a commercial database system in 1979 by Oracle Corporation.
Database management is a complicated process, which has been considerably rationalized by the SQL programming language. As its full name (Structured Query Language) implies, SQL is responsible for querying and editing information stored in a certain database management system.
Historically, SQL has been the favorite query language for database management systems running on minicomputers and mainframes. Increasingly, however, SQL is being supported by PC database systems because it supports distributed databases (databases that are spread out over several computer systems). This enables several users on a local-area network to access the same database simultaneously.
Now into its third decade of existence, SQL offers great flexibility to users by supporting distributed databases, i.e. databases that can be run on several computer networks at a time. Certified by ANSI and ISO, SQL has become a database query language standard, lying in the basis of a variety of well established database applications on the Internet today. It serves both industry-level and academic needs and is used on both individual computers and corporate servers. With the progress in database technology SQL-based applications have become increasingly affordable for the regular user. This is due to the introduction of various open-source SQL database solutions such as MySQL, PostgreSQL, SQLite, Firebird, and many more.
Explain any three data manipulation commands in SQL.
Ans: SQL data manipulation:
Data manipulation is essential for SQL tables - it allows you to modify an already created table with new information, update the already existing values or delete them.
With the INSERT statement, you can add new rows to an already existing table. New rows can contain information from the start, or can be with a NULL value.
Syntax:
INSERT INTO  tablename (Columnname1, columnname2,………) VALUES (Expression1, Expression2……..);
An example of an SQL INSERT
INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America');
With the UPDATE statement, you can easily modify the already existing information in an SQL table.
Synatx: 
UPDATE  tablename SET COLUMNNAME=Expression, COLUMNNAME=Expression WHERE WHERE search condition; 

An example of an SQL UPDATE
UPDATE phonebook SET address = 'North America', phone = '+1 123 456 7890' WHERE firstname = 'John' AND lastname = 'Doe'; 
Delete Command: 
With the DELETE statement you can remove uneeded rows from a table.
Synatx: 
DELETE FROM tablename 
	Or 
DELETE FROM tablename WHERE search condition; 
An example of an SQL DELETE
DELETE FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe'; 
Explain the Like operator in SQL with a suitable example.
Ans: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The LIKE Operator: The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax:
SELECT column_name(s) FROM table_name
WHERE column_name LIKE pattern
LIKE Operator Example
The "Persons" table:
Now we want to select the persons living in a city that starts with "s" from the table above.
We use the following SELECT statement:
Select * from persons where city like ’s%’;
The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
The result-set will look like this:

Explain tree concept hierarchical data model.
Ans: The database schema is represented as a collection of tree structure diagrams. For each such diagram there exists one single instance of a database tree. The root of this tree is a dummy node. The children of that node are instances of the appropriate records types. Each such child instance in turn has several instances of various record types. The following diagram is nothing but a corresponding tree structure diagram which is shown in fig.
The above replication has two major drawbacks.
i) Data inconsistency on data duplication occurs.
ii) Waste of space is unavoidable.
The solution is to introduce the concept of virtual record i. a record contain no data value. It does contain a logical pointer to a particular physical record. When a record is to be replicated in several database trees, we keep a single copy of that record in one tree and replace every other record with a virtual record containing a pointer to that physical record.
What is normalization? Explain relation with more than one candidate key.
Ans: The normalization is the process based on the concept of normal forms. If any relation satisfies a certain set of conditions then it is said to be in a particular normal forms.
For example: A relation is said to be in first normal form if and only if it satisfies the condition that it contains scalar value sonly.
The normal are used to minimize various types of anomalies and inconsistencies during database design. All normalized relations are in 1NF some 1NF relations are also in 2NF and some 2NF relations are in 3NF.
A procedure by which a relation in some given normal from say 2NF can be converted into a set of relations in more desirable form, say 3NF. This procedure is called as “Normalization procedure”
Thus normalization procedure is the successive reduction of a given collection of relations to some more desirable from. It is also the reversible process i. e. From the output of the normalization procedure we can obtain the input and no information is lost in the normalization process.
what is IMS system. Explain it.
Ans: IMS (Information Management System) is a database and transaction management system that was first introduced by IBM in 1968. Since then, IMS has gone through many changes in adapting to new programming tools and environments
IMS consists of two major components, the IMS Database Management System (IMS DB) and the IMS Transaction Management System (IMS TM). In IMS DB, the data is organized into a hierarchy. The data in each level is dependent on the data in the next higher level. The data is arranged so that its integrity is ensured, and the storage and retrieval process is optimized. IMS TM controls I/O (input/output) processing, provides formatting, logging, and recovery of messages, maintains communications security, and oversees the scheduling and execution of programs. TM uses a messaging mechanism for queuing requests.
IMS's original programming interface was DL/1 (Data Language/1). Today, IMS applications and databases can be connected to CICS applications and DB2 databases. Java programs can access IMS databases and services.
Explain DBTG data manipulation.
Ans: Following are the data manipulation facilities provided by the DBTG.
1) Run unit it refers to each process or task that is running under the control of DBMS. The process may be a user’s application program containing DMS commands or interactive sessions with a user. The DBMS maintains separate records of the envoronment of each such run unit.
2) Currency indicators: For each record type known to the run unit, the DBMS maintains a marker to indicate the current record position during the execution of the run unit. These record position markers or pointers are called currency indicator. The DBTG maintains a number of currency indicators for each run unit. For each record type known to the run unit there is a currency indicator called the current record of a record type.
3) Database status register: In addition to the currency indicators, the DBMS maintains, for each run unit number of status indicators for the user’s application programme. The term used by the DBTG for these indicators is special registers, however in reality these are hardware register. The registers are DB- Status, DB-Set- Name. DB-Status is a special register to store the appropriate databse status indicator during the execution of any DML command that refer to the database. When an error is detected during the execution of a DML command that refer to the database, the DBMS uses this register to store the name of the set type involved in the command.
4) Following is a list of DML commands of many DBMSs based on DBTG model
Find: Locates the required occurrences of an existing record.
Get: accesses a record occurrences specified by the currency indicator of the record and places it into the template area for the record type in the UWA of the run unit.
Modify: Changes or updates the value of one or more data item in the current record of the run unit.
List the essential requirement of database security.
Ans: Reasons for the database security:
• Failures of various forms during normal operation. For example transaction processing or storage media loss. Proper recovery procedures are normally used to recover from failures occurring during transaction processing. Lack of such procedures could lead to inconsistencies in the database.
• Concurrent usage anomalies. Proper synchronization mechanism is used to avoid data inconsistencies due to concurrent usage.
• System error. A dial in user may be assigned the identity of another dial –in user who was disconnected accidentally or who hung up without going through a log –off procedure.
• Improper authorization: The authorizer can accidentally give improper authorization to a user which could lead to database security and or integrity violations.
• Hardware failures: For example memory protection hardware that fails could lead to software errors and culminate in database security and or integrity violations.
• A computer system operator or system programmer can intentionally by pass the normal security and integrity mechanism alter or destroy the data in the database or make unauthorized copies of sensitive data.
• An authorized user can get access to a secure terminal or to the password of an authorized user and compromise the database. Such users could also destroy the database files.
who is database administrator? Explain its role?
Ans: database administrator (short form DBA) is a person responsible for the design, implementation, maintenance and repair of an organization's database. They are also known by the titles Database Coordinator or Database Programmer, and is closely related to the Database Analyst, Database Modeller, Programmer Analyst, and Systems Manager. The role includes the development and design of database strategies, monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, co-ordinate and implement security measures to safeguard the database. Employing organizations may require that a database administrator have a certification or degree for database systems (for example, the Microsoft Certified Database Administrator)
The functions of the database administrator can be listed as follows:
1. Develop a plan for the organization’s database/data resource.
2. Organize and staff the database administration (DBA) functions.
3. Supervise the dba functions.
4. Act as a bridge in communicating between managers, users and application developers.
5. Establish and supervise the following systems and procedures for maintaining and safeguarding the database:
• Usage and security monitoring
• Performance monitoring and load balancing
• DBMS trouble-shooting
Explain resource locking in distributed database.
Ans: Resource locking in distributed database: The problem due to concurrent access can be avoided by locking the required records when it is accessed for the transaction with respect to the user from Department X and releasing it after the updated data is written back into the respective record of the database so that the accessing of the required data for the transaction with respect to the user from Department Y is denied till the first transaction is fully executed by x. The sequence of actions to be carried out to have meaningful updates in the database with respect to the above example is presented below:
• Identify the required record with respect to the transaction of the user from Department.
• Lock that record.
• Read the content of that record and bring it to the working area.
• Write the content of the record in the working area.
• Write back the updated versions of the record in the database.
• Unlock that record for the other users.
• Lock the above record and proceed with the transaction of the user from the Department Y.
• Read the content of that record and bring it to the working area.
• Update the content of the record in the working area.
• Write back the updated versions of the record in the database.
The above discussion explains the fact that the resources should be locked and unlocked in a particular sequence to have better concurrency control. The database locked at different levels. The extent to which the database is locked is known as locking granularity. While executing a transaction then it is known as coarse granularity. If the required data within a single record with respect to the transaction is locked, then it is known as fine granularity.
Describe object database System.
Ans: It is database model in which information is represented in the form of objects as used in object –oriented programming.
Objects databases generally recommended when there is a business need for high performance processing on complex data. When database capabilities are combined with object programming languages capabilities, the result is an object database management system (ODBMS) An ODBMS makes database appear as programming language objects in one or more object programming languages. An ODBMS extends the programming language with transparently persistent data, concurrency control, data recovery, associative queries and other capabilities.
Strength of ODBMSs:
1. Better support for complex Applications: ODBMSs have provided better support for certain applications requiring high performance and modeling of complex relationships and heavily inter-related data that have traditionally not been well served by other DBMSs.
2. Enhance programmability: It has stated that “Using a highly integrated, object-oriented database management system to build applications and store reusable code can save 20% to 30% of development cost beyond that achieved solely by object oriented programming”. The seamless integration of an application programming language and database DDL/DML enables further savings in code.
3. Reduce problems of referential Integrity: One of the major problems with relational databases has been that of the dangling references. This problem has been solved in object databases by giving the responsibility of pointer maintenance to the database itself rather than to each application.
For accessing computer programs go to TECHNOLOGY