BY: FARAH ARIF

Relational Databases
Database: A database may be define as a collection of interrelated data stored together to serve
multiple applications.

DBMS: A database management system (DBMS) is a computer program designed to manage a large
amount of structured data, and run operations on the desired data requested by the users.

Data Redundancy: Duplication of data is called data redundancy.

Relational Model: 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.

Relational Model Concepts in DBMS:

 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.
 Relation Schema: A relation schema represents the name of the relation with its attributes.
 Record: It is nothing but a single row of a table, which contains a single record. It also called
Tuple.
 Degree: The degree is the number of attributes (columns) in a table.
 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: All the possible allowable values for an attribute is known as attribute
domain.
 Keys: Keys are an important part of a relational database and a vital part of the structure of a
table. They help enforce integrity and help identify the relationship between tables. There
are four main types of keys candidate keys, primary keys, foreign keys and alternate keys.
 Primary Key: A column or set of columns that uniquely identifies a row within a table is
called primary key.
 Candidate Key: Candidate keys are set of fields (columns with unique values) in the
relation that are eligible to act as a primary key.
 Alternate Key: Out of the candidate keys, after selecting a key as primary key, the
remaining keys are called alternate key.

BY: FARAH ARIF 1

  Foreign Key: Anon-key attribute, whose values are derived from the primary key of
some other table, is known as foreign key in its current table. In other words, a foreign
key is a column or a combination of columns that is used to establish a link between
two tables.

Example:
Emp_id Emp_name Department Salary Grade
101 Vipin Kumar Account 30000 B
102 Rahul Yadav Sales 25000 C
103 Ajay Soni HR 40000 A
104 Aman Singh Sales 26000 C
105 Atul Gupta Technical 33000 B
106 Amar Tiwari HR 42000 A

 Relation: Employee(Table also called Relation)

 Fields (Attributes/Columns): Emp_id, Emp_name, Department, Salary and Grade.
 Tuples (Rows/Records):
101 Vipin Kumar Account 30000 B
 Domain: Possible values of Department are ('Account','Sales','HR','Technical')
 Degree: 5 (Number of columns)
 Cardinality: 6 (Number of rows)
 Candidate Key: Emp_id and Emp_ name has unique values. Therefore, Emp_id and
Emp_name are candidate keys.
 Primary Key: Out of the Emp_id and Emp_name, Emp_id is the primary key.
 Alternate Key: Emp_name is the Alternate key.

View: A view is a database object that has no values. It is a virtual table, which is created according to
the result set of an SQL query. However, it looks similar to an actual table containing rows and
columns. Therefore, we can say that its contents are based on the base table. It is operated
similarly to the base table but does not contain any data of its own. Its name is always unique, like
tables.

MySQL Features: MySQL is a relational database management system (RDBMS) based on the SQL
(Structured Query Language) queries. It is one of the most popular languages for accessing and
managing the records in the table. MySQL is open-source and free software.
The following are the most important features of MySQL:

 Easy to use:MySQL is easy to use. We have to get only the basic knowledge of SQL. We can
build and interact with MySQL by using only a few simple SQL statements.
 It is secure: MySQL consists of a solid data security layer that protects sensitive data from
intruders. Also, passwords are encrypted in MySQL.
 Free to download: MySQL is free to use so that we can download it from MySQL official
website without any cost.

BY: FARAH ARIF 2

  Client/ Server Architecture: MySQL follows the working of a client/server architecture. There
is a database server (MySQL) and arbitrarily many clients (application programs), which
communicate with the server; that is, they can query data, save changes, etc.
 It is scalable: MySQL supports multi-threading that makes it easily scalable. It can handle
almost any amount of data, up to as much as 50 million rows or more. The default file size
limit is about 4 GB. However, we can increase this number to a theoretical limit of 8 TB of
data.
 Speed: MySQL is considered one of the very fast database languages, backed by a large number
of the benchmark test.
 High Flexibility: MySQL supports a large number of embedded applications, which makes
MySQL very flexible.
 Memory efficiency: Its efficiency is high because it has a very low memory leakage problem.
 High Performance: MySQL is faster, more reliable, and cheaper. It provides very high
performance results in comparison to other databases.
 Platform Independent:It can download, install, and execute on most of the available
operating systems.

Types of SQL Statements: The SQL statements are categorized into different categories based upon
the purpose. They are:
 Data Definition Language (DDL) statement
 Data Manipulation Language (DML) statement
 Transaction Control Language (TCL) Statement
 Data Control Language (DCL) Statement

SQL Language Statements

DDL DML TCL DCL

1. Create 1. Select 1. Commit 1. Grant
2. Alter 2. Insert 2. Rollback 2. Revoke
3. Drop 3. Update 3. Savepoint
4. Truncate 4. Delete 4. Set Transaction
5. Rename

BY: FARAH ARIF 3

 DDL: DDL is short name of Data Definition Language, which deals with database schemas and
descriptions, of how the data should reside in the database.
 CREATE: To create a database and its objects like (table, index, views, store
procedure, function, and triggers)
 ALTER: Alters the structure of the existing database
 DROP: Delete objects from the database
 TRUNCATE: Remove all records from a table, including all spaces allocated for the
records are removed
 RENAME: Rename an object

DML: DML is short name of Data Manipulation Language which deals with data manipulation and
it is used to store, modify, retrieve, delete and update data in a database.
 SELECT: Retrieve data from a database.
 INSERT: Insert data into a table.
 UPDATE: Updates existing data within a table.
 DELETE: Delete all records from a database table

TCL: TCL is short name of Transaction Control Language which deals with a transaction within a
database.
 COMMIT: Commits a Transaction.
 ROLLBACK: Rollback a transaction in case of any error occurs.
 SAVEPOINT: To rollback the transaction making points within groups.
 SET TRANSACTION: Specify characteristics of the transaction.

DCL: DCL is short name of Data Control Language which mostly concerned with rights,
permissions and other controls of the database system.
 GRANT: Allow users access privileges to the database.
 REVOKE: Withdraw users access privileges given by using the GRANT command

Data Dictionary: It is a file that contain METADATA (Data About Data).

History of MySQL: The project of MySQL began in the year 1979 in the form of UNIREG which was an
in-house database tool developed to manage databases. UNIREG was created by Michael Widenius.
In 1995 Michael Widenius, David Axmark and Allan Larsson founded MySQL AB in Sweden.
Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and
"SQL", the abbreviation for Structured Query Language. The logo of MySQL is a Dolphin,is named as
"Sakila".
The company was later acquired by Sun Microsystems in the year 2008 for about $1 billion.

BY: FARAH ARIF 4