What is meant by DBMS and what is its utility?
A Database management system is a computerized record-keeping system. It is a repository or a container for a collection of computerized data files. The overall purpose of DBMS is to allow he users to define, store, retrieve and update the information contained in the database on demand.
The database utility is the interface between the ABAP Dictionary and the relational database underlying the R/3 System. You can use the database utility to edit all the database objects that are generated from objects of the ABAP Dictionary. It is mainly used when a table is changed in the ABAP Dictionary.
What is meant by a database?
A database is an organized collection of data, generally stored and accessed electronically from a computer system. … The database management system (DBMS) is the software that interacts with end-users, applications, and the database itself to capture and analyze the data.
What is DBMS and its purpose?
DBMS – Purpose. It is a collection of programs that enables the user to create and maintain a database. In other words, it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.
Mention the different languages present in DBMS
The different languages present in DBMS are as follows:
DDL(Data Definition Language) – Consists of commands which are used to define the database.
DML(Data Manipulation Language) – Consists of commands which are used to manipulate the data present in the database.
CL(Data Control Language) – Consists of commands which deal with the user permissions and controls of the database system.
TCL(Transaction Control Language) – Consist of commands which deal with the transaction of the database
What is normalization and what are the different types of normalization
Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables.
First Normal Form (1NF) – No repeating groups within rows.
Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.
Boyce-Codd Normal Form (BCNF) –A relation R is in BCNF if R is in Third Normal Form and for every FD, LHS is super key. A relation is in BCNF iff in every non-trivial functional dependency X –> Y, X is a super key.
What are the different types of keys in the database?
Primary key – The main purpose of the primary key is to provide a means to identify each record in the table.
The primary key provides a means to identify the row, using data within the row. A primary key can be based on one or more columns, such as first and last name; however, in many designs, the primary key is an auto-generated number from an identity column. All relational database management systems support primary keys. In fact, for a table to be considered a relational table, and in the first normal form, it must have a primary key.
Candidate Key – This is a set of attributes that can uniquely identify a table. Each table can have more than a candidate key. Apart from this, out of all the candidate keys, one key can be chosen as the Primary key. In the above example, since CustomerID and PanNumber can uniquely identify every tuple, they would be considered as a Candidate Key.
Super Key – This is a set of attributes which can uniquely identify a tuple. So, a candidate key, primary key, and a unique key is a superkey, but vice-versa isn’t true.
Unique Key – A unique key is also called a unique constraint. A unique constraint can be used to ensure rows are unique within the database. Makes sense, but wait!
Don’t we already do that with the primary key? Yep, we do, but a table may have several sets of columns that you want unique.
Alternate Key – Alternate Keys are the candidate keys, which are not chosen as a Primary key. From the above example, the alternate key is PanNumber .
Foreign Key – An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers. in the above example, the CustomerID from the Customers Table is referred to the CustomerID from the Customer_Payment Table.
Composite Key – A composite key is a combination of two or more columns that identify each tuple uniquely. Here, the CustomerID and Date_of_Payment can be grouped together to uniquely identify every tuple in the table.
Basic commands of DBMS
suppose you create one table name is world and you insert the from the table.
CREATE DATABASE database_name;
CREATE TABLE table_name;
INSERT INTO table_name (name of colums),values();
Read the notes about this table. Observe the result of running this SQL command to show the name, continent, and population of all countries.
SELECT name, continent, population FROM world;
How to use WHERE to filter records. Show the name of the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.
SELECT name FROM world WHERE population >= 200000000;
Give the name and the per capita GDP for those countries with a population of at least 200 million.
SELECT name, gdp/population FROM world WHERE population >= 200000000;
Show the name and population in millions for the countries of the continent ‘South America’. Divide the population by 1000000 to get population in millions.
SELECT name, population/1000000 FROM world WHERE continent = 'South America';
how the name and population for France, Germany, Italy
SELECT name, population FROM world WHERE name IN ('France', 'Germany', 'Italy');
Show the countries which have a name that includes the word ‘United’
SELECT name FROM world WHERE name LIKE '%United%';
Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population of more than 250 million. Show the countries that are big by area or big by population. Show name, population, and area.
SELECT name, population, area FROM world WHERE area > 3000000 OR population > 250000000;