A Database Management System (DBMS) Is A Software Package Designed To Define, Manipulate, Retrieve And Manage Data In A Database.
1.What is database?
A database is a collection of information that is organized. So that it can easily be accessed, managed, andupdated.
2.What is DBMS?
DBMS stands for Database Management System. It is a collection of programs that enables user to createand maintain a database.
3.What is a Database system?
The database and DBMS software together is called as Database system.
4.What are the advantages of DBMS?
I. Redundancy is controlled.
II. Providing multiple user interfaces.
III. Providing backup and recovery
IV. Unauthorized access is restricted.
V. Enforcing integrity constraints.
5.What is normalization?
It is a process of analyzing the given relation schemas based on their Functional Dependencies (FDs) andprimary key to achieve the properties(1).Minimizing redundancy, (2). Minimizing insertion, deletion and update anomalies.
6.What is Data Model?
A collection of conceptual tools for describing data, data relationships data semantics and constraints.
7.What is E-R model?
This data model is based on real world that consists of basic objects called entities and of relationship amongthese objects. Entities are described in a database by a set of attributes.
8.What is Object Oriented model?
This model is based on collection of objects. An object contains values stored in instance variables with inthe object. An object also contains bodies of code that operate on the object. These bodies of code arecalled methods. Objects that contain same types of values and the same methods are grouped together intoclasses.
9.What is an Entity?
An entity is a thing or object of importance about which data must be captured.
10.What is DDL (Data Definition Language)?
A data base schema is specifies by a set of definitions expressed by a special language called DDL.
11.What is DML (Data Manipulation Language)?
This language that enable user to access or manipulate data as organized by appropriate data model.Procedural DML or Low level: DML requires a user to specify what data are needed and how to get thosedata. Non-Procedural DML or High level: DML requires a user to specify what data are needed withoutspecifying how to get those data
12.What is DML Compiler?
It translates DML statements in a query language into low-level instruction that the query evaluation enginecan understand.
13.What is Query evaluation engine?
It executes low-level instruction generated by compiler.
14.What is Functional Dependency?
Functional Dependency is the starting point of normalization. Functional Dependency exists when arelation between two attributes allows you to uniquely determine the corresponding attributes value.
15.What is 1 NF (Normal Form)?
The first normal form or 1NF is the first and the simplest type of normalization that can be implemented ina database. The main aims of 1NF are to:
1. Eliminate duplicative columns from the same table.
JOBSMATE.COM
2. Create separate tables for each group of related data and identify each row with a unique column (theprimary key).
16.What is Fully Functional dependency?
A functional dependency X Y is full functional dependency if removal of any attribute A from X meansthat the dependency does not hold any more.
17.What is 2NF?
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionallydependent on primary key.
18.What is 3NF?
A relation is in third normal form if it is in Second Normal Form and there are no functional (transitive)dependencies between two (or more) non-primary key attributes.
19.What is BCNF (Boyce-Codd Normal Form)?
A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a
candidate key.
20.What is 4NF?
Fourth normal form requires that a table be BCNF and contain no multi-valued dependencies.
21.What is 5NF?
A table is in fifth normal form (5NF) or Project-Join Normal Form (PJNF) if it is in 4NF and it cannothave a lossless decomposition into any number of smaller tables.
22.What is a query?
A query with respect to DBMS relates to user commands that are used to interact with a data base.
23.What is meant by query optimization?
The phase that identifies an efficient execution plan for evaluating a query that has the least estimated costis referred to as query optimization.
24.What? is difference between DBMS and RDBMS?
DBMS provides a systematic and organized way of storing, managing and retrieving from collection oflogically related information. RDBMS also provides what DBMS provides but above that it providesrelationship integrity.
25.What is SQL?
SQL stands for Structured Query Language. SQL is an ANSI (American National Standards Institute)standard computer language for accessing and manipulating database systems. SQL statements are used toretrieve and update data in a database.
26.What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored inthe server database.
27.What is a view?
A view may be a subset of the database or it may contain virtual data that is derived from the database filesbut is not explicitly stored.
28.What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.
29.What is Index?
An index is a physical structure containing pointers to the data.
30.What is extension and intension?
Extension -It is the number of tuples present in a table at any instance. This is time dependent.
Intension -It is a constant value that gives the name, structure of table and the constraints laid on it.
31.What do you mean by atomicity and aggregation?
Atomicity-Atomicity states that database modifications must follow an?all or nothing? rule. Eachtransaction is said to be ?atomic.? If one part of the transaction fails, the entire transaction fails.
Aggregation - A feature of the entity relationship model that allows a relationship set to participate inanother relationship set. This is indicated on an ER diagram by drawing a dashed box around theaggregation.
32.What is RDBMS KERNEL?
Two important pieces of RDBMS architecture are the kernel, which is the software, and the datadictionary, which consists of the system- level data structures used by the kernel to manage the database.
33.Name the sub-systems of a RDBMS?
I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery,Distribution Control, Transaction Control, Memory Management, Lock Management.
34.How do you communicate with an RDBMS?
You communicate with an RDBMS using Structured Query Language (SQL)
35.Disadvantage in File Processing System?
Data redundancy & inconsistency.
36.What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.
37.What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may Specify the mapping between twoschemas.
38.Describe concurrency control?
Concurrency control is the process managing simultaneous operations against a database so that databaseintegrity is no compromised. There are two approaches to concurrency control. The pessimistic approachinvolves locking and the optimistic approach involves versioning.
39.Describe the difference between homogeneous and heterogeneous distributed database?
A homogenous database is one that uses the same DBMS at each node. A heterogeneous database is onethat may have a different DBMS at each node.
40.What is a distributed database?
A distributed database is a single logical database that is spread across more than one node or locationsthat are all connected via some communication link.
41.Explain the difference between two and three-tier architectures?
Three-tier architecture includes a client and two server layers.
The application code is stored on the application server and the database is stored on the database server.A two-tier architecture includes a client and one server layer. The database is stored on the database server.
42.Briefly describe the three types of SQL commands?
Data definition language commands are used to create, alter, and drop tables. Data manipulationcommands are used to insert, modify, update, and query data in the database. Data control languagecommands help the DBA to control the database.
43.List some of the properties of a relation?
Relations in a database have a unique name and no multivalued attributes exist. Each row is unique andeach attribute within a relation has a unique name. The sequence of both columns and rows is irrelevant.
44.Explain the differences between an intranet and an extranet?
An Internet database is accessible by everyone who has access to a Web site. An intranet database limitsaccess to only people within a given organization.
45.What is SQL Deadlock?
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely fora locked resource.
46.What is a Catalog?
A catalog is a table that contains the information such as structure of each file, the type and storage formatof each data item and various constraints on the data .The information stored in the catalog is calledMetadata.
47.What is data ware housing & OLAP?
Data warehousing and OLAP (online analytical processing) systems are the techniques used in manycompanies to extract and analyze useful information from very large databases for decision making
48.Describe the three levels of data abstraction?
Physical level: The lowest level of abstraction describes how data are stored.
Logical level: The next higher level of abstraction, describes what data are stored in database and whatrelationship among those data.
View level: The highest level of abstraction describes only part of entire database.
49.What is Data Independence?
Data independence means that the application is independent of the storage structure and access strategy ofdata.
50.How many types of relationship exist in database designing?
There are three major relationship models:-
One-to-one
One-to-many
Many-to-many
51.What is order by clause?
ORDER BY clause helps to sort the data in either ascending order to descending
52.What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of thedatabases, i.e., maintenance, validation task and status checks.
53.What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared.
54.What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHEREclause. Truncate will actually remove all the rows from a table and there will be no data in the table afterwe run the truncate command.
55.What is Hashing technique?
This is a primary file organization technique that provides very fast access to records on certain searchconditions.
56.What is a transaction?
A transaction is a logical unit of database processing that includes one or more database access operations.
57.What are the different phases of Transaction?
Analysis phase
Redo phase
Undo phase
58.What is ?transparent dbms??
It is one, which keeps its physical structure hidden from user.
59.What are the primitive operations common to all record management System?
Addition, deletion and modification.
60.Explain the differences between structured data and unstructured data.
Structured data are facts concerning objects and events. The most important structured data are numeric,character, and dates.
Structured data are stored in tabular form. Unstructured data are multimedia data such as documents,photographs, maps, images, sound, and video clips. Unstructured data are most commonly found on Webservers and Web-enabled databases.
61.What are the major functions of the database administrator?
Managing database structure, controlling concurrent processing, managing processing rights andresponsibilities, developing database security, providing for database recovery, managing the DBMS andmaintaining the data repository.
62.What is a dependency graph?
A dependency graph is a diagram that is used to portray the connections between database elements.
63.Explain the difference between an exclusive lock and a shared lock?
An exclusive lock prohibits other users from reading the locked resource; a shared lock allows other usersto read the locked resource, but they cannot update it.
64.Explain the "paradigm mismatch" between SQL and application programming languages.
SQL statements return a set of rows, while an application program works on one row at a time. To resolvethis mismatch the results of SQL statements are processed as pseudofiles, using a cursor or pointer tospecify which row is being processed.
65.Name four applications for triggers.
(1)Providing default values, (2) enforcing data constraints,
(3) Updating views and (4) enforcing referential integrity
66.What are the advantages of using stored procedures?
The advantages of stored procedures are (1) greater security, (2) decreased network traffic, (3) the fact thatSQL can be optimized and (4) code sharing which leads to less work, standardized processing, andspecialization among developers.
67.Explain the difference between attributes and identifiers.
Entities have attributes. Attributes are properties that describe the entity's characteristics. Entity instanceshave identifiers. Identifiers are attributes that name, or identify, entity instances.
68.What is Enterprise Resource Planning (ERP), and what kind of a database is used in an ERP application?
Enterprise Resource Planning (ERP) is an information system used in manufacturing companies andincludes sales, inventory, production planning, purchasing and other business functions. An ERP systemtypically uses a multiuser database.
69.Describe the difference between embedded and dynamic SQL?
Embedded SQL is the process of including hard coded SQL statements. These statements do not changeunless the source code is modified. Dynamic SQL is the process of generating SQL on the fly. Thestatements generated do not have to be the same each time.
70.Explain a join between tables
A join allows tables to be linked to other tables when a relationship between the tables exists. Therelationships are established by using a common column in the tables and often uses the primary/foreignkey relationship.
71.Describe a subquery.
A subquery is a query that is composed of two queries. The first query (inner query) is within the WHEREclause of the other query (outer query).
72.Compare a hierarchical and network database model?
The hierarchical model is a top-down structure where each parent may have many children but each childcan have only one parent. This model supports one-to-one and one-to-many relationships.
The network model can be much more flexible than the hierarchical model since each parent can havemultiple children but each child can also have multiple parents. This model supports one-to-one,one-to-many, and many-to-many relationships.
73.Explain the difference between a dynamic and materialized view.
A dynamic view may be created every time that a specific view is requested by a user. A materialized viewis created and or updated infrequently and it must be synchronized with its associated base table(s).