Database

The Entity-Relationship Model

Conceptual Modeling of a database

The Entity-Relationship (ER) Model

An Entity Relationship Diagram (ERD) is a visual representation of different data using conventions that describe how these data are related to each other. For example, the elements writer, novel, and consumer may be described using ER diagrams this way:

ER Diegram

Data Types Diagram

Practice MySql

Enter below commands in above runner line by line to Practice mysql.

Note: This is using SQLite not MySQL so not all MySQL commands can be run in runner.

.sql

-- To create database name "Students"
CREATE TABLE Students (id int, name varchar);

-- Add "dob" row in "Students" table
ALTER TABLE Students ADD dob date;

-- Insert data in "Students" table
INSERT INTO Students (id, name, dob) values (28, "Aman", 2003);
INSERT INTO Students (id, name, dob) values (29, "Jone", 2005);

-- Show all data in "Students" table
SELECT * FROM Students;

-- Update set "name" "Jonney" where "id" is "29"
UPDATE Students SET name = "Jonney" WHERE id = 29;

-- Show only "id" and "name" in "Students" table
SELECT id,name FROM Students;

-- Delete "Students" table
DROP TABLE Students;

DDL (Data Defination Language)

DDL or Data Definition Language consists of the SQL commands that can be used to define the database schema.

It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

DDL is a set of SQL commands used to create, modify, and delete database structures but not data.

Create

This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).

Syntax:

Syntax:
CREATE TABLE table_name
(
    column_1 datatype,
    column_2 datatype,
....
);

Example to create database:

.sql

CREATE DATABASE school;

Example to create table in database:

.sql

CREATE TABLE Students
(
    ROLL_NO int(3),
    24NAME varchar(20),
    SUBJECT varchar(20),
);

Alter

This is used to alter the structure of the database.

Syntax:

.sql

ALTER TABLE table_name
ADD column_name datatype;

Example: Let's say we have this data in table:

ID	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

We use the following SQL statement:

.sql

ALTER TABLE Persons ADD DateOfBirth date;

The table will look like this:

ID	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Drop

The DROP TABLE statement is used to drop an existing table in a database.

Syntex:

.sql

DROP TABLE table_name;

Example:

.sql

DROP TABLE Persons

Truncate

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

Syntax:

.sql

TRUNCATE TABLE table_name;

Example: Lets say we have this data: | ID | LastName | FirstName | Address | City | | --- | --- | --- | --- | --- | | 1 | Hansen | Ola | Timoteivn 10 | Sandnes | | 2 | Svendson | Tove | Borgvn 23 | Sandnes | | 3 | Pettersen | Kari | Storgt 20 | Stavanger |

We run this command on the table:

.sql

TRUNCATE TABLE person;

But when we run SELECT command:

.sql

SELECT * FROM person;

You can still select table in database but the columus and rows will be no longer there.

Unlike DROP which deletes whole table.

Rename

The RENAME TABLE syntax help in changing the name of the table.

Syntax:

.sql

RENAME old_table _name To new_table_name;

Example:

.sql

RENAME employees TO old_employees

DML (Data Manipulation Language)

DDL deals with the manipulation of data present in the database that belongs to the DML or Data Manipulation Language. This includes most of the SQL statements.

Select

Select statement is used to Retrieve data from the database. To select data into a table, SQL SELECT command can be used.

Syntax:

.sql

SELECT * from <table_name>;

Example:

.sql

SELECT * FROM Person;

This will show whole database. Like this:

.sql

| ROLL_NO | FIRST_NAME | LAST_NAME | MARKS |
| ------- | ---------- | --------- | ----- |
|    1    | Rita       | Goyal     | 67    |
|    2    | Kanika     | Mittal    | 91    |
|    3    | Reshma     | Kumari    | 100   |

Let's say we just want show ROLL_NO and MARKS, then we will use SELECT command like this:

.sql

SELECT ROLL_NO, MARKS FROM student_details

The output will be:

| ROLL_NO | MARKS |
| ------- | ----- |
| 1       | 67    |
| 2       | 91    |
| 3       | 100   |

Insert

Insert statement is used to add records to the existing table. To insert data into a table, SQL INSERT INTO command can be used.

Syntax:

.sql

Insert into <table_name> (column list) values (column values);

Example:

Let's say we have this table: | ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | | ------- | ---------- | --------- | ----- | | 1 | Rita | Goyal | 67 | | 2 | Kanika | Mittal | 91 | | 3 | Reshma | Kumari | 100 |

Now we insert 4th entry by using following commands:

.sql

INSERT INTO student_details (ROLL_NO, FIRST_NAME, LAST_NAME, MARKS) VALUES (4, 'Arpit', 'Garg', 82);

The table will be now like this: | ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | | --- | --- | --- | --- | | 1 | Rita | Goyal | 67 | | 2 | Kanika | Mittal | 91 | | 3 | Reshma | Kumari | 100 | | 4 | Arpit | Garg | 82 |

Update

Update command is used to update existing data within a table.

Syntex:

.sql

UPDATE <table_name> SET column_number =value_number WHERE condition;

Example:

Let's say we have this table: | ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | | --- | --- | --- | --- | | 1 | Rita | Goyal | 67 | | 2 | Kanika | Mittal | 91 | | 3 | Reshma | Kumari | 100 | | 4 | Arpit | Garg | 82 |

Let run the Update command to update the table:

.sql

UPDATE student_details SET MARKS = 92 WHERE ROLL_NO = 4;

Now the updated table will be like this: | ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | | --- | --- | --- | --- | | 1 | Rita | Goyal | 67 | | 2 | Kanika | Mittal | 91 | | 3 | Reshma | Kumari | 100 | | 4 | Arpit | Garg | 92 |

Delete

DELETE allows us to remove single or multiple records from the database tables depending upon the condition we specify in the WHERE clause.

If no condition is specified in the WHERE clause, then it will delete all the rows of a table.

Syntax of DELETE command:

.sql

DELETE FROM table_name WHERE condition;

Here, the condition is specified using the WHERE clause and only those records that fulfill the condition are deleted.

Example:

Let's say we have this table: | ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | | --- | --- | --- | --- | | 1 | Rita | Goyal | 67 | | 2 | Kanika | Mittal | 91 | | 3 | Reshma | Kumari | 100 | | 4 | Arpit | Garg | 92 |

To run DELETE command:

.sql

DELETE FROM student_details WHERE MARKS < 80;

The following will the table: | ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | | --- | --- | --- | --- | | 2 | Kanika | Mittal | 91 | | 3 | Reshma | Kumari | 100 | | 4 | Arpit | Garg | 92 |

DCL (Data Control Language)

Data Control Language(DCL) deals with the commands used in SQL that permit a user to access, modify or work on the different privileges in order to control the database. It allows the database owner to give access, revoke access, and change the given permissions as and when required. DCL is basically used for enforcing data security.

Grant

GRANT is a DCL command used to grant(give access to) security privileges to specific database users. It is mostly used to restrict user access to INSERT, DELETE, SELECT, UPDATE, EXECUTE, ALTER or to provide privileges to user's data.

Syntax:

.sql

GRANT <privileges> ON <object name> TO <user/roles>

Example:

Here all privilege access given to anybody working with the database

.sql

GRANT ALL ON product_stock TO PUBLIC;

Revoke

REVOKE is a DCL command that is used to revoke the permissions/access that was granted via the GRANT command. It is mostly used to revert back to the time when no access was specified, i.e., withdrawing the permission that was authorized to carry out specific tasks.

Syntax:

.sql

REVOKE <privileges> ON <object name> FROM <user/roles>

Example:

Here all privilege access taken to anybody working with the database

.sql

REVOKE ALL ON product_stock TO PUBLIC;

TCL (Transaction Control Language)

TCL stands for Transaction Control Language in SQL.

Transaction Control Language (TCL) is a set of special commands that deal with the transactions within the database.

Basically, they are used to manage transactions within the database.

TCL commands are also used for maintaining the consistency of the database.

Commit

The COMMIT command in SQL is used to permanently save any transaction into the database.

Generally, whenever we use any DML command such as INSERT, UPDATE, or DELETE, the changes made by these commands are , not permanent.

Hence, before closing the current session, we may roll back any changes made through these commands.

Syntax:

COMMIT;

Rollback

The rollback command in TCL is used for restoring the database to the last committed state.

In other words, the rollback command restores the database to its original state since the last command that was committed.

Syntax:

.sql

ROLLBACK;

Savepoint

The SAVEPOINT command in TCL is basically used to temporarily save a transaction so that we can roll back to that point (saved point) whenever required.

Syntax:

.sql

SAVEPOINT savepoint_name;

An Entity Relationship Diagram (ERD) is a visual representation of different data using conventions that describe how these data are related to each other.

Example of ER Diagram:

ER Diagram

In the diagram, the elements inside rectangles are called entities while the items inside diamonds denote the relationships between entities.

Features

Entity

An entity can be a person, place, event, or object that is relevant to given system.
For Example, a school system may include student, teacher, major courses, subject, fees, and other items.
Entites are represented in ER diagrams by a rectangles and named using singular nouns.

Weak Entity

A weak entity is an entity that depends on teh existence of another entity.
A weak entity is represented in a database by a separated table, but it does not have a primary key of its own.
It relies on the primary key of the entity on which it depends.
Example:
- Consider a databse of orders placed by customers at a retail store. "customer" entity might be the identifying entity, while the "order" entity is the weak entity. Each order is associated with specific customer, and the customer's primary key (e.g. a customer ID) is used as the primary key for the order as well. This means that the order cannot be uniquely identified without also specifying the customer to which it belongs.
The single identifying entity can have multiple weak entities associated with it.
Diagram:
```
+---------+      +--------------+
|         |      |+------------+|
| Orders  |------||Orders Items||
|         |      |+------------+|
+---------+      +--------------+
```

Attribute

An attributes is a characterstic or property of an entity.
Attributes are represented as columns in a database table, with each row representing a single entity and each column representing an attribute of that entity.
Example: A "customer" table might include attributes such as "name", "address," and "phone number," with each attribute storing information about a perticular customer.
There are two types of attributes:
- Multivalued Attribute
- Derived Attribute

Multivalued Attribute

If an attribute can have more than one value it is called an multivalued attribute.
It is important to note that this is different to an attribute having it own attributes.
Example: A "customer" entity can have multiple "phone_numbers" values.
Diagram:

Derived Attribute

An attribute based on another attribute.
Example: "Age" can be derived from "Date of birth".
Diagram:

Relationship

A relationship describes how entities interact.
Example: The entity “carpenter” may be related to the entity “table” by the relationship “builds” or “makes”.
Relationships are represented by diamond shapes and are labeled using verbs.

Recursive Relationship

The same entity participates more than once in the relationship.
Example: An employee can be a supervisor and be supervised, so there is a recursive relationship.

Extended Features

Generalization

A generalization hierarchy is a form of abstraction that specifies that two or more entities that share common attributes can be generalized into a higher-level entity type called a super type or generic entity.
The lower level of entities becomes the subtypes, or categories, to the super type. Subtypes are dependent entities.
Example: Account is the higher-level entity set and saving account and current account are lower-level entity sets.
Diagram:

Specialization

Specialization is the process of taking subsets of the higher-level entity set to form lower-level entity sets.
It is a process of defining a set of subclasses of an entity type, which is called the super class of the specialization.
The process of defining subclass is based on the basis of some distinguish characteristics of the entities in the super class.
Diagram:

Aggregration

Aggregration is a process when relation between two entities is treated as a single entity.
Example: The relationship between Center and Course together, is acting as an Entity, which is in relationship with another entity Visitor.
Diagram:

Resources

Keys are used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables.

Example: ID is used as a key in the "student" table because it is unique for each student.

Primary Key

It is the first key.
Used to identify one and only one instance of an entity uniquely.

Candidate Key

A candidate key is an attribute or set of attributes that can uiquely identify a tuple.
The candidate keys are as strong as the primary key.
Example: "employee" table, id is best suited for the primary key. The rest of the attributes like SSN, Passport_Number, License_Number are considered a candidate key.

Super Key

Super Key is an attribute set that can uniquely identify a tuple.