MySQL-Database Management-1

What is Data?

In simple words data can be facts related to any object in consideration. For example your name, age, height, weight, etc are some data related to you. A picture, image , file , pdf etc can also be considered data.

What is a Database?

Database is a systematic collection of data. Databases support storage and manipulation of data. Databases make data management easy. Let's discuss few examples.

An online telephone directory would definitely use database to store data pertaining to people, phone numbers, other contact details, etc.

WHY DO WE NEED DATABASE

To manage large chunks of data: if size of data increases into thousands of records, it will simply create a problem to manage. Database can manage large amount of data.

Accuracy: Through validation rule in database, data accuracy can be maintained.

Ease of updating data: With the database, we can flexibly update the data according to our convenience. Moreover, multiple people can also edit data at same time.

Security of data: With databases we have security groups and privileges to restrict access.

Data integrity: In databases, we can be assured of accuracy and consistency of data due to the built in integrity checks and access controls.

 

Advantages of Database System

●     Databases reduce Redundancy: It removes duplication of data because data are kept at one place and all the application refers to the centrally maintained database.

●     Database controls Inconsistency: When two copies of the same data do not agree to each other, then it is called Inconsistency. By controlling redundancy, the inconsistency is also controlled.

●     Database facilitates Sharing of Data; Data stored in the database can be shared among several users.

●     Database ensures Security: Data are protected against accidental or intentional disclosure to unauthorized person or unauthorized modification.

●     Database maintains Integrity: It enforces certain integrity rules to insure the validity or correctness of data. For ex. A date can’t be like 31/31/2000.

●     Database enforce standards:

 

What is a Database Management System (DBMS)?

Database Management System (DBMS) is a collection of programs which enables its users to access database, manipulate data, reporting / representation of data.

It also helps to control access to the database.

Types of DBMS

Let's see how the DBMS family got evolved with the time. Following diagram shows the evolution of DBMS categories.

There are 4 major types of DBMS. Let's look into them in detail.

Hierarchical DBMS

In a Hierarchical database, model data is organized in a tree-like structure. Data is Stored Hierarchically (top down or bottom up) format. Data is represented using a parent-child relationship. In Hierarchical DBMS parent may have many children, but children have only one parent.

Network Model

The network database model allows each child to have multiple parents. It helps you to address the need to model more complex relationships like as the orders/parts many-to-many relationship. In this model, entities are organized in a graph which can be accessed through several paths.

Relational model

Relational DBMS is the most widely used DBMS model because it is one of the easiest. This model is based on normalizing data in the rows and columns of the tables. Relational model stored in fixed structures and manipulated using SQL.

Object-Oriented Model

In Object-oriented Model data stored in the form of objects. The structure which is called classes which display data within it. It defines a database as a collection of objects which stores both data members values and operations.

 

What is Relational Model

The relational model represents the database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship.

The table name and column names are helpful to interpret the meaning of values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized.

 

Relational Model Concepts

Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno, NAME,etc.

Tables – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes.

Tuple – It is nothing but a single row of a table, which contains a single record.

Relation Schema: A relation schema represents the name of the relation with its attributes.

Degree: The total number of attributes which in the relation is called the degree of the relation.

Cardinality: Total number of rows present in the Table.

Column: The column represents the set of values for a specific attribute.

Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.

Relation key - Every row has one, two or multiple attributes, which is called relation key.

Attribute domain – Every attribute has some pre-defined value and scope which is known as attribute domain

Domain :It is a collection of values from which the value is derived for a column.

What are Keys?

A DBMS key is an attribute or set of an attribute which helps you to identify a row (tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table.

Example:

Employee ID	FirstName	LastName
11	Manoj	Kumar
22	Dhyan	Chand
33	Anuj	Khare

In the above-given example, employee ID is a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID.

Why we need a Key?

Here, are reasons for using Keys in the DBMS system.

Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys ensure that you can uniquely identify a table record despite these challenges.

Allows you to establish a relationship between and identify the relation between tables

Help you to enforce identity and integrity in the relationship.

What is a Primary Key?

PRIMARY KEY is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can't be a duplicate meaning the same value can't appear more than once in the table. A table cannot have more than one primary key.

 

Rules for defining Primary key:

Two rows can't have the same primary key value

It must for every row to have a primary key value.

The primary key field cannot be null.

The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

Example:

In the following example, StudID is a Primary Key.

StudID	Roll No	First Name	LastName	Email
1	11	Yug	Sengar	abc@gmail.com
2	12	Bhavya	Srivastava	xyz@gmail.com
3	13	Navya	Singh	mno@yahoo.com

What is the Alternate key?

ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.

Example:

In this table, StudID, Roll No, Email are qualified to become a primary key. But since StudID is the primary key, Roll No, Email becomes the alternative key.

StudID	Roll No	First Name	LastName	Email
1	11	Yug	Sengar	abc@gmail.com
2	12	Bhavya	Srivastava	xyz@gmail.com
3	13	Navya	Singh	mno@yahoo.com

What is a Candidate Key?

CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

Properties of Candidate key:

It must contain unique values

Candidate key may have multiple attributes

Must not contain null values

It should contain minimum fields to ensure uniqueness

Uniquely identify each record in a table

Example: In the given table Stud ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table.

StudID	Roll No	First Name	LastName	Email
1	11	Yug	Sengar	abc@gmail.com
2	12	Bhavya	Srivastava	xyz@gmail.com
3	13	Navya	Singh	mno@yahoo.com

What is the Foreign key?

FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table.

Example:

DeptCode	DeptName
001	Science
002	English
005	Computer

Teacher ID	Fname	Lname
B002	Karan	Awasthi
B017	Pragati	Bakshi
B009	Narain Ji	Srivastava

In this example, we have two table, teach and department in a school. However, there is no way to see which search work in which department.

In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.

Teacher ID	DeptCode	Fname	Lname
B002	002	Pragati	Bakshi
B017	002	Manju	Dubey
B009	001	Karan	Awasthi

This concept is also known as Referential Integrity.