DBMS 5 Marks Question and Answers
DBMS 5 Marks questions particularly asked from these questions . if you need to answer these questions you must refer complete material.
These answers will help only Main theme.
- Define Data dictionary?
- Data dictionary is a collection of system defined tables.
- This includes elementary-level data items (fields), group and record-level data structures and relational tables.
- It keeps track of relationships that exist between various data structures.
- Data dictionary is the main source of information for any RDBMS.
- All data dictionary tables belong to SYS user account.
- The system defined tables are created automatically whenever the data base is created.
- What is a data model? Name any four data models.
- A conceptual method of structuring data is called Data Model.
- The development of systems based on three principal data models.
- These four data models are
- What is an entity?
- Anything about which data are to be collected and stored.
- It can be anything in the real world, which has got certain properties, which is identified.
- Define View
- A view is a virtual table in the database defined by a query. A view does not exist in the database as a stored set of data values.
- The row and columns of data are visible through the view are produced by the query that defines the view.
- What is SQL join?
- Join is a set operation.
- Join operations take two tables and return another table as a result.
- It is used to define relationship between columns of 2 different tables.
- It is required to retrieve data from more than one database table.
JOIN: It allows information to be combined from two or more tables. JOIN is the real power behind the relational database, allowing the use of independent tables linked by common attributes.
- What is star schema?
The star schema is a data modeling technique used to map multi – dimensional decision support data into a relational database. Star schema creates a multi –dimensional database schema from the existing relational database. Star schemas yield an easily implemented model for multidimensional data analysis. The basic star schema has four components.
- Attribute hierarchies
In the relational model, keys are important because they are used to ensure that each row in a table is uniquely identified. They are also used to establish relationships among tables and to ensure the integrity of the data. Therefore, a proper understanding of the concepts and use of keys in the relational model is very important. A key consists of one or more attributes that determines other attributes.
Key: – A key is an attribute or combination of attributes that identifies (determine) other attributes uniquely.
Simple key: The key which consist only one attribute is called as “simple key”.
Super key: An attribute (or combination of attributes) that uniquely identifies each row in a table. Or
Super key of an entity set is a set of one or more attributes whose values uniquely determine each entity.
Composite key: The key which consist multiple attributes is called as “composite” key. (OR)
If a primary key contains two or more attributes then that type of primary key is called composite key.
Candidate Key: A minimal (irreducible) super key. A super key doesn’t contain a subset of attributes that is itself is a super key.
Primary Key: A candidate key selected to uniquely identify all other attribute values in any given row. Cannot contain null entries.
Secondary Key: An attribute (or combination of attributes) used strictly for data retrieval purpose.
Foreign Key (Referential integrity constraint): – It is used to establish relation between two tables.
- Used to define relationship between 2 Tables.
- It allows Null and duplicates values.
It can be related to either Primary key or unique constraint column of other Table.
PK / UNQ <—–> FK
- Define System catalog.
The system catalog is defined as detailed system data dictionary that describes all objects within the data base. System catalog contains data about table name, its owner, created date, the number of columns in each table, the data type of each column, index file names, index creators, authorized users and all the access privileges. Sometimes both the term data dictionary and system catalog are used interchangeably.
- Define normalization.
It is a step by step procedure used to design the final database objects from raw data by avoiding “Data Redundancy” and improving “Data Integrity and Data Concurrency “.
- Define entity clustering.
Some wide information’s systems have over 1000 entity types and relationships. In this case, we cannot define the entire organizational data model into the ER diagram. To overcome this problem approach is to create multiple ER diagrams. But in this situation the problem is the total picture could not exist. To solve this problem, we use the method of entity clustering.
Entity clustering is a useful way to represent a data model for a large and complex organization, here we group the related entity types and its associated relationships called entity clusters.
- What is the role of DBA?
A database administrator is a person or a group responsible for authorizing access to the
database, for coordinating and monitoring its use, and for acquiring s/w and h/w resources as needed.
The functions of DBA include
- Database Design
- User Training
- Database Security and Integrity
- Database System Performance
- What is the multi-valued Attribute?
An attribute that can have multiple values is called Multi-Valued Attribute. For example, a person can have several college degrees. A person can have multiple phone numbers.
- What are the different levels of data abstraction?
There are three levels of abstraction/Architecture:
Internal/ Physical level: The lowest level of abstraction describes how data are stored.
Conceptual/Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
External/View level: The highest level of abstraction, enable to view or access only a part of the database.
- Define database schema.
It is a collection of related data base objects placed under one user account known as schema.
External schemas or user views.
- Write any two differences between DROP and TRUNCATE commands.
Drop: – It is used for to drop database object (table). Whenever we can use this command we can loss the data permanently we cannot recollect.
Sql> drop table <table name>
Truncate: The truncate command is used to delete the records (data) only not table structure. The general syntax for truncate command is.
Syntax: Truncate table <table name>
Sql> truncate table student;
- Define OLAP.
OLAP create an advanced data analysis environment that supports decision making, business modeling and operations research. OLAP system shares four main characteristics.
- They main Multi- dimensional data analysis techniques.
- They provide advanced database support
- They provide easy-to-use end user interfaces.
- They support client / server architecture.
- What is a business rule?
A business rule is a brief precise description of policy, or procedure or principle within the organization. Business rules are not universal, different organizations may have different business rules.
- Write syntax of a table creation of SQL.
Create: – The create command is used to create a table. The general syntax for create statement is.
Sql> create table <table name> (<column name1> data type (size), <column name2> data type (size), ………….);
Sql> create table emp(eno number(4),ename varchar2(15), zip number(6), hdate date);
- Give an example query with ‘where’ clause.
Where clause: – it is used for to filter the data based on given condition known as restriction.
Syntax: select [distinct] <col 1>, <col 2> … from <tab 1> , <tab 2> where <condition>;
Display details of employee whose salary more than 20000
Sql> select *from emp where sal >20000;
- Write syntax of UPDATE command.
Update: – The update command is used to modify an existing data in table.
- The update command updates a single column or more than one column at a single time.
- Specific rows would be updated based on specific condition in where clause. The general syntax for update command is
Update <Table name> set <column name> = <value> [where <condition> ];
Increase salary 10000 for eno=103
Sql>Update emp set sal =10000 where eno=103;
- Name five commercial RDBMS Software.
- Define information system.
Information system facilitates the transformation of data into information and allows the user for managing both data and information. Database occupies major portion of an information system that provides facilities for data storage, manipulation and retrieving.
- What constitutes a DBMS?
The database system is composed of the five major pars.
- What is normal form?
A normal form is a state of relation that results from applying simple rules regarding functional dependencies or relationships between attributes to the relation.
- Why E-R modeling is used?
The Entity relationship model is a detail graphical representation of data from an organization. An ER Model is normally expressed in terms of ER diagrams, which is graphical representation of an ER Model.
Benefits of ER Modeling
- Documents information for the organization in clear, precise format.
- Provides a clear picture of scope of the information requirement.
- Provides an easily understood pictorial map for the database design.
- Offers an effective framework for integrating multiple applications.
- What is meant by de-normalization?
De-normalization is a strategy that database managers use to increase the performance of database infrastructure. It involves adding redundant data to a normalized database to reduce certain types of problems with database queries that combine data from various tables into a single table.
- Why database is important.
It is a collection of programs working together to control Data Manipulations ( add , change ,remove), Retrievals ( Read ) and sharing on Database.
DBMS is to provide an environment that is both convenient and efficient for people to use in retrieving and storing information.
The main objective of database is record the history for the future use.
- Write any two advantages of DBMS.
2. Restricting unauthorized access.
3. Providing persistent storage for program objects and data structures.
4. Providing multi-user interfaces.
5. Representing complex relationships among data.
6. Enforcing integrity constraints.
7. Providing backups and recovery.
- What is functional dependency?
A functional dependency is a constraint between two attributes or two sets of attributes. If there are two attributes A and B, then attribute B is said to be functionally dependent on attribute A if each value in A determines
- What is Generalization and Specialization?
Generalization: An object set that is a superset of another object set.
Specialization: An object set that is a subset of another object set.