ER model follows separate notations for representing data into entities, attributes and relationship between the entities.
ER-Modeling Diagram Notations:- Entities, Attributes and Relationship forms the components of ER Diagram and the defined symbols and shapes are summarized below in Table.

ER Component	Description (how it is represented)	Notation
Entity - Strong	Simple rectangular box
Entity - Weak	Double rectangular boxes
Relationships	Rhombus symbol - strong
Between Entities	Rhombus within rhombus - Weak
Attributes	Ellipse symbol connected to the entity
Key Attribute for Entity	Underline the attribute name inside Ellipse
Derived Attribute for Entity	Dotted ellipse inside main ellipse
Multivalued Attribute for Entity	Double Ellipse

​​​​​​​

(a) Create Database - used to create new SQL Database. The Syntax and example to create student DB is given below.
Syntax: CREATE database databasename;
Example: mysql>create database studentDB;
The database created now can be viewed using the following Syntax.
mysql>Show databases;
As a result, the newly created student DB will be listed.
(b) Drop Database - used to remove any of the existing SQL Database. The Syntax and example to delete student DB is given below.
Syntax: DROP database database name;
Example: mysql>DROP database student DB;
The deleted database will not be viewed, when we list all databases using the Syntax, Show databases;
(c) Select Database - Many databases are available in the repository, from which the suitable database is selected using the below command.
Syntax: USE databasename;
Example: mysql>USE student DB;
Once the database is selected, multiple operations are performed as per the needs of the application.
(i) Insert Record - Database will have multiple tables. Tables are created using the create command.
Syntax: INSERT INTO table name VALUES (Value'i, Value 2, Value 3);
(ii) Select Record-From the multiple records available in the table, the enquired data are retrieved from the table-using the SELECT command.
Syntax: SELECT C
Syntax: SELECT * from table name;
SQL Select Record List

First name	Last name	Age
Krishna	S	10
Sugal	S	14
Arun	J	15
Mani	K	18

(iii) Deleting Record- The existing record in a table is removed from the table using DELETE command.
Syntax: DELETE from tablename WHERE columnname="value";
SQL Delete Record List

First name	Last name	Age
Krishna	S	10
Sugal	S	14
Arun	J	15

(iv) Modifying Record - SQL provides us with modifying and updating the existing records in a table using UPDATE command.
Syntax: UPDATE table name SET column1 = "new value"; Where column2 = "value2";
Example: mysql> UPDATE Bio data SET age = 13 WHERE Firstname = "Krishna";
(v) Sorting Records - The Query results are listed in Ascending and Descending order using the command ORDER BY.
Syntax: select from table name ORDER BY column name;
Example: select from Bio data ORDER BY Firstname;
(vi) Grouping Records, Having Clause - In SQL we can have identical data in a group.
Using the GROUPBY Command, the rolno's are grouped and marks are added up against the Rollno with SUM (marks)
(vii) Joining Tables - When we have to select data from more than 2 tables SQL JOIN clause is used.

SQL is a Structured Query Language is a standard language used for accessing and manipulating databases.
Different types of SQL commands are:-
(1) Data Definition Language;
(2) Data Manipulation Language;
(3) Data query Language;
(4) Transaction Control Language;
(5) Data Control Language.
(a) Data Definition Language (DDL):- The DDL commands are used to define database schema.
(Structure), and also to create and modify the structure of the database object in the database.
CREATE, ALTER, DROP,RENAME and TRUNCATE commands belongs to this category.
SOL DDL COMMANDS List

Commands	Description
CREATE	Used to create database or tables
ALTER	Modifies the existing structure of database or table
DROP	Deletes a database or table
RENAME	Used to rename an existing object in the database
TRUNCATE	Used to delete all table records

(b) Data Manipulation Language (DML):- These SQL commands deals with the manipulation of data present in the database.
INSERT, UPDATE, and DELETE commands belongs to this category.
SOL DME COMMANDS List.

Commands	Description
INSERT	Adds new rows into database table.
UPDATE	Modifies existing data with new data within a table.
DELETE	Deletes the records from the table.

(c) Data Query Language (DQL):- SELECT is the only SQL command used to fetch or retrieve the data from database tables that come under DQL.
SOL DOL COMMANDS List

Commands	Description
SELECT	Retrieve data from the table.

(d) Transaction Control Language (TCL):- These SQL commands manage the transactions in SQL databases and also helps to save the change in the database permanently.
COMMIT, ROLLBACK, SET TRANSACTION and SAVEPOINT commands belongs to this category.
SQL TCL COMMANDS List

Commands	Description
COMMIT	Permanent save into database.
ROLLBACK	Restore database to original form since the last COMMIT.
SET TRANSACTION	SET TRANSACTION command set the transaction properties such as read-write or read only access.
SAVE POINT	used to temporarily save a transaction so that we can rollback to that point whenever required.

(e) Data Control Language (DCL):- The SQL commands that implement security on database objects like table, view, stored procedure etc,... GRANT and REVOKE commands belongs to this category.
SQL DCL COMMANDS List

Commands	Description
Grant	used to give permission to specific users on specific database objects like table, view etc.
Revoke	used to take out permission from specific users on specific database objects like table, view etc.

1. Database Administrators (DBA's) who takes care of configuration, installation, performance, security and data backup.
2. DBA's posses the skills on database design, database queries, RDMS, SQL and networking. The primary task is the creation of new user and providing them with access rights.
Creating New User Account to MySQL
1. In the MySQL database, there exists a table named user.
2. The newly created account must have an entry in this user table.
3. The user account is activated with various access rights like INSERT, SELECT and UPDATE.
5. Consider the user table has the following fields host, name, password, select_priv, insert_priv and update_priv.
6. A new user account is added with values to the user table using the following INSERT query in MySQL database.
7. The Syntax for inserting record is INSERT INTO table name (Parameter 1, Parameter 2, Parameter3..) VALUES (Valuel, Value2, Value3..);
8. All the query will terminate with semicolon (;).
mysql> INSERT INTO user (host, name, password, select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest, PASSWORD('guest 123'), 'Y, Y, 'Y');
mysql>FLUSH PRIVILEGES;
9. The above command is executed after every new account creation.
10. This command is similar to rebooting the server so that newly created account and the access privilege are updated in the server.
11. Manual server rebooting is avoided by this command.
12. The inserted record is retrieved using SELECT query and the structure is shown below Tables:
mysql>SELECT * FROM user WHERE name=" 'guest';
Example Table

host	name	password	select_priv	insert_priv	Update_priv
localhost	guest	j2gd6vxd1bj3k13g4	Y	Y	Y

Since MySQL is more secured, it provides function to encrypt the password. Thus the password 'guest 123' is encrypted and stored as 'j2gd6vxd1bj3k13g4' using PASSWORD() function. The parameters select_priv, insert priv and update_priv are few privileges set for the new user. If the flag is set as 'Y' then access is granted and if flag set as 'N' then access is denied.

Privileges	Action Performed (If Granted)
Select_priv	User can select rows from database tables.
Insert_priv	User can insert rows into database tables.
Update_priv	User can update rows of database tables.
Delete_priv	User can delete rows of database tables.
Create_priv	User can create new tables in database
Alter_priv	User can make changes to the database structure.

The process of creating, implementing and maintaining the enterprise data in a system is known as Designing of databases.
1. The performance and success of an application depends on good database design'.
2. MySQL provides performance dashboard, reports and statistics regarding the design of database.
3. Create Conceptual Design:- It is the primary phase in database design, where detailed discussion about the creation of databases, tables, columns and data types is discussed based on the requirement.
At the end, the model is to attain the expectation.
4. The three major parts that forms a database are Tables, Queries and Views
(i) Tabies:- Contains multiple rows and columns. Each row is a record and column is an attribute. Records are inserted into the table.
(ii) Queries:- It is a question with multiple conditions Posted to the database.
(iii) Views:- A set of stored queries.
5. Create Table:- In an application each page reveals some functionality. Each such functions are designed to a table For example, In an online shopping site like Amazon, multiple pages are maintained like customer profile, products, ect, ... All these can be created using tables.
6. Create Columns: The Customer table contains the columns like firstname, lastname, phone, email, age, etc, ,,.,(i.e.) Column determines what values are stored.

DBA - Database Administrator uses few commands to control the entire database. These commands are known as Administrative MySQL Commands.
Some of the commands which are frequently used are
(a) USE Database
(b) SHOW Database
(c) SHOW Tables
(d) SHOW COLUMNS FROM table name
(e) SHOW INDEX FROM table name
(f) SHOW TABLE STATUS LIKE tablename\G.
(a) USE Database:
This command is used to select the database in MySQL for working. If exists a database named test, it is used as working database using the below Syntax.
Syntax:
mysql > use test;
Database changed
mysql>
SHOW Databases
Lists all the databases available in the database server.
Syntax:
mysql > show databases;

Database List

Database

test

mysql

employee

students

parents

SHOW Tables
Lists all the tables available in the current database
Syntax:
mysql > show tables;

Table List

Tables_in_ test

school

salary

employee

library

sports

SHOW COLUMNS FROM tablename
Lists all the attributes, attribute type, Is Null value permitted, key information, default value and other information for the given table. The show columns for sports table is given below.
Syntax:
mysql > show columns from sports;

Column List
Field	Type	Null	Key	Default	Extra
Team_Id	int(5)	yes		null
Team_name	Team_name varchar( 10)	yes		null
Team_size	int(5)	yes		null
Team_rank	int(5)	yes		null

(e) SHOW INDEX FROM tablename
The query shows all the indexes for the given table.
Syntax:
mysql > show indexes from sports;
(f) SHOW TABTE STATUS LIKE tablename\G
This command provides with a detailed report on the performance of the table.

Database management Systems has RDBMS (Relational Database Managernent System) and DBMS (Object Database Management System).
DBMS has two basic concepts.
(1) ACID Properties
(2) Concurrency Control and Locking.
(1) ACID Properties :
The acronym stands for Atomicity, Consistency, Isolation and Durability.
Atomicity:
1. It follows the thumb rule "All or Nothing", while updating the data in database for the user performing the update operation.
2. This update operation is called as transaction and it either commits or aborts.
Consistency:
It ensures that the change in data value to be constant at any given instance. This property helps in the successful transaction.
Isolation:
1. This property is needed during concurrent transaction.
2. When multiple users do the transactions by accessing same object at the same time, the transaction is known as concurrent transaction.
3. To prevent the conflict in database update, the transactions are isolated from other user and serialized. This is also known as Degree of Consistency.
Durability:
It is defined as the system's ability to recover all committed transactions during the failure of storage or the system.
(2) Concurrency Control and Locking :
1. It is the DBMS mechanism used for data sharing.
2. When the same data is shared among multiple users, proper access control is needed and privilege of changing the applications data item is controiled through Locking.

ER Component	Description (how it is represented)	Notation
Entity - Strong	Simple rectangular box
Entity - Weak	Double rectangular boxes
Relationships	Rhombus symbol - strong
Between Entities	Rhombus within rhombus - Weak
Attributes	Ellipse symbol connected to the entity
Key Attribute for Entity	Underline the attribute name inside Ellipse
Derived Attribute for Entity	Dotted ellipse inside main ellipse
Multivalued Attribute for Entity	Double Ellipse

1. Primary Key:
(i) This key of relational table identifies each record in the table in a unique way.
(ii) A primary key which is a combination of more than one attribute is called a composite primary key.
(iii) The candidate key that is chosen to perform the identification task is called the primary key
2. Foreign Key:
(i) A foreign key is a "copy" of a primary keythat has been exported from one relation into another to represent the existence of a relationship between them
(ii) A foreign key is a copy of the whole of its parent primary key i.e if the primary key is composite, then so is the foreign key.
(iii) Foreign key values do not (usually) have to be unique.
(iv) Foreign keys can also be null.
(v) A composite foreign key cannot have some attribute(s) null and others non-null.
3. Candidate Key:
(i) Each super key is called a candidate key.
(ii) A candidate key is selected from the set of Super Key.
(iii) While selecting candidate key, redundant attributes should not be taken.
(iv) The candidate key is also known as minimal super keys.
4. Super Key:
(i) An attribute or group of attributes, which is sufficient to distinguish every tuple in the relation from every other one is known as Super Key.
5. Composite Key (Compound Key):
(i) A key with more than one attribute to identify rows uniquely in a table is called Composite key.
(ii) This is also known as Compound Key.