What is Database Management system (DBMS)

 Database Management System (DBMS)

Database Management System is related to the followings:

Data: is the collection of raw, facts and figures like college admission form consists of data.

Database: is an organized collection of related data. An organized collection of information in computerized format Database is an organized collection of related data that is stored in an efficient and compact manner. A Computerized representation of any organizations flow of information and storage of data

Database Management System: is the set of programs or system which is used to create and maintain the database. A DBMS is a collection of programs that are used to create and maintain a database. A DBMS is a software program that enables the creation and management of databases. DBMS is software collection of small programs to perform a certain operation on data and manage the data.

Relational Database Management System

A relational database management system (RDBMS) is a DBMS that is based on the relational model. An RDBMS is a collection of software programs for creating, maintaining, modifying and manipulating a relational database. An RDBMS is a database management system based on relational model.

Entity Relationship Diagram

Entity Relationship Diagrams (ERDs) illustrate the logical structure of databases. Entity-Relationship model is a logical representation of data in an organization.

What is Keys

A key is a set of attributes that can be used to identify or access a particular entity instance of an entity type (or out of an entity set).

A key can be simple, that is, consisting of a single attribute, or it could be composite which consists of two or more attributes. Keys are very important part of the Relational database. They are used to establish and identify the relationship between tables. They also ensure that each record within a table can be uniquely identified by a combination of one or more fields within a table.

1.     Super Key:

A superkey is a set of one or more attributes which taken collectively, allow us to identify uniquely an entity instance in the entity set.  Super Key is defined as a set of attributes within a table that uniquely identifies each record within a table. Super Key is a superset of a Candidate key. An attribute or a combination of the attribute that is used to identify the records uniquely is known as Super Key. A table can have many Super Keys. A combination of one or more columns in a table which can be used to identify a record in a table uniquely, a table can have any number of superkeys. A Super key is any combination of fields within a table that uniquely identifies each record within that table.  Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, Alternate key are a subset of Super Keys.

2.     Candidate Key:

A candidate key is a superkey that contains no extra attribute. It consists of minimum possible attributes.

A candidate is a subset of a super key. A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table. A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

3.     Primary Key:

It is the key selected by the database designer for unique identification. Primary Key is a candidate key that is most appropriate to become the main key of the table. It is a key that uniquely identifies each record in a table.

Primary key must hold a unique value for each record. A Candidate Key that is used by the database designer for unique identification of each row in a table is known as Primary Key. A Primary Key can consist of one or more attributes of a table. Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

4.     Alternate Key

The candidate keys that are not selected as primary key are known as alternate keys. Candidate keys which are not chosen as the primary key are known as alternate keys.  Alternate Key can be any of the Candidate Keys except for the Primary Key. E.g. of Alternate Key is “Name, Address” as it is the only other Candidate Key which is not a Primary Key. We cannot define the Alternate Key Separately from a Candidate Key, for a table, if there are two Candidate Keys and one is chosen as a Primary Key the other Candidate Key is known as the Alternate Key of that table. An Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

5.     Composite Key

A primary key that consists of two or more attributes is known as composite key. Key that consists of two or more attributes that uniquely identify an entity occurrence is called Composite key. Composite key, a key that is composed of two or more attributes.   If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key. Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

6.     Foreign Key

A foreign key is an attribute or set of attributes in a relation whose values match a primary key in another relation. A Foreign Key accesses data in some other related table via its Primary Key. One or more attributes in an entity type that represents a key, either primary or secondary, in another entity type. A foreign key is an attribute or combination of attribute in one base table that points to the candidate key (generally it is the primary key) of another table.  Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.  A column of one table points to the Primary Key column of another table to implement referential data integrity. A foreign key consists of one or more columns in a table whose value in one row uniquely identifies another row in the same or another table.

What are Views

Views are generally used to focus,  simplify, and customize the perception each user has of the database. Views can be used as security mechanisms by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables of the view.  A view is a virtual table. Suppose a user wants to view a few columns of a base table instead of all columns. A view can be created to fulfill this request. The view consists of only those columns of the base table that the user requires.

What are Database Anomalies?

Database Anomalies are the problems in relations that occur due to redundancy in the relations. These anomalies affect the process of inserting, deleting and modifying data in the relations.  Some important data may be lost if a relation is updated that contains database anomalies. It is important to remove these anomalies in order to perform different processing on the relations without any problem.

A functional dependency is the type of relationship between attributes. A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change

A transitive dependency is one that carries over another attribute.  Transitive dependency occurs when one non-key attribute determines another non-key attribute.

What is Normalization

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data. Database normalization is the process of organizing the field and table of a relational database to minimize data redundancy and dependency. Normalization is the process of organizing data in a database.

1.     First Normal Form:

A relation is in first normal form if and only if every attribute is single valued for each tuple. This means that each attribute in each row, or each cell of the table, contains only one value.  No repeating fields or groups are allowed. 

2.     Second Normal Form:

A relation is in second normal form (2NF) if and only if it is in first normal form and all the non-key attributes are fully functionally dependent on the key.  A relation is in second normal form if and only if it is in first normal form and all non-key attributes are fully functionally dependent on the key. Clearly, if a relation is in 1NF and the key consists of a single attribute,  the relation is automatically 2NF.

3.     Third Normal Form

A  relational table is in third normal form (3NF) if it is already in  2NF  and every non-key column is non-transitively dependent upon its primary key.  In other words, all non-key attributes are functionally dependent only upon the primary key. 

What is Joining

A join is a special form of the cross product of two tables.

1.     Equi–Join:

This is the most used type of join. In equijoin, rows are joined on the basis of values of a common attribute between the two relations. It means relations are joined on the basis of common attributes between them which are meaningful.

2.     Outer Join:

 In outer join all the tuples of left and right relations are part of the output.  It means that all those tuples of left relation which are not matched with right relation are left as null.  Similarly, all those tuples of  right  relation which  are not matched with left relation are left as Null.

3.     Semi-Join:

In  semi  join,  first  we take the natural join of two relations then we project  the attributes of the first table only. So after join and matching the common attribute of both relations only attributes of first relation are projected.  

What is Relationship

A relationship is a link that relates two entities that share one or more attributes. Relationships allow you to describe the connections between different database tables in a powerful way. The relationship represents an association between two or more entities.  

1.     One-to-many relationships:

The most common relationship used when creating relational databases.  A row in a table in a database can be associated with one or (likely) more rows in another table.  An example of a one-to-many relationship is a single order has many items in that order.  

2.     One-to-one relationship:

A row in a table is associated to one and only one row in another table.  An example of a one-to-one relationship is a person can have one social security number and a social security number can only be assigned to one person.

3.     Many-to-many relationships:

When one or more rows in a table are associated with one or more rows in another table An example of a many-to-many relationship is a table of customers who can purchase many different products and a table of products that can be purchased by many different customers.

DML QUERIES INSERT,UPDATE,DELETE,SELECT

What are Database Anomalies

Data anomaly means the same type of data present in the database as duplication. So while updating or modifying the information in the database we get the problem of data inconsistency to solve this problem we need to remove the duplicated data

An anomaly is something inconsistent in its surroundings. For example, it’s an anomaly that they accepted a boy into an all-girl school. or the quiet boy is an anomaly to his abnormally loud family.

The anomaly is something that is an error. Anomaly refers to something not normally found in a given situation/environment.

Introduction

The problem with a flat-file database is that we can get update anomalies. The different types of anomaly are described below.

What is Insertion anomaly

An insertion anomaly means that it is difficult to insert new records into the database.
It was not possible to insert the details about Mr. O'Hara because he was not part of any pupil record.
1. Update anomaly

An update anomaly occurs when the same data item has to be updated more than once. This can lead to errors and inconsistency of data. Updating Miss Dewar's class required the data to be updated in 2 different places. In a larger database, the update may have had to have been performed numerous times.
2. Deletion anomaly

A deletion anomaly occurs when data is lost because of the deletion of other data. If we delete Ali's record from the database, we are also deleting the fact that Mr. Ali  teaches PE in PE Room 2

What is Data Anomalies Anomalies are problems that can occur in poorly planned, un-normalized databases where all the data is stored in one table (a flat-file database).  Insertion Anomaly - The nature of a database may be such that it is not possible to add a required piece of data unless another piece of unavailable data is also added. E.g. A library database that cannot store the details of a new member until that member has taken out a book. Deletion Anomaly - A record of data can legitimately be deleted from a database, and the deletion can result in the deletion of the only instance of other, required data, E.g. Deleting a book loan from a library member can remove all details of the particular book from the database such as the author, book title etc.

What is Use case diagram:

Use case diagram defines the functionality's boundaries of the actor in the system that he can perform.

Entity Relationship Diagram:

Entity Relationship Diagram describes the relation/interaction of Entities with each other.

Database Diagram:

Database diagrams show the structure of data in our database.  (Database Diagram gives the overall overview of database structure only), it means what table and fields are used in the database. It is also called quick overview.

One to One Relationship:

1 to 1     Example: person > gender, male or female, student id

One to Many Relationships:

1 to M    Example: student > courses

Many to Many Relationships:

M to M   Example: Teachers > students   City ID is auto incremented.

What is DML?

This DML language is used to insert, delete, update, and select data in the database. Its example is SQL

Black box testing

 It is a testing in which we are not concerned with internal coding, design and structure of a process but we only concerned with inputs and outputs.

White Box testing

In which we are concerned with internal coding, design and structure of the process.

Black box testing

In black box testing only check the functionality of software not checked its detail.

White box testing

In white box testing, check the software components and also checked the detail and checked that how they work actually.

Black box testing

In black box testing, check the whole software not its details.

White box testing

In white box testing, check its internal processes of software and also checked its detail.

What is SQL? SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

What is SQL?

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard

SQL is a standard language for accessing databases.

ERD Diagram:

An ER Diagram is a special graphic that is used to present relationship between entities in a database.

Database:

A database schema is the collection of the metadata that describe the relation in a database.

CRUD

C stand for create

R stand for read

U stand for an update

D stand for delete

SQL stand for a standard query language

SQL is not a full feature programming language; it is simply data sub- language.