Unit 3: Database Concepts and the Structured Query Language (SQL)

Database Concepts:

 Introduc on to Database:

o A database is a collec on of data stored in an organized manner for easy

access, management, and upda ng.

o The need for databases arises from the need to store large amounts of data in
an organized, structured way, ensuring efficiency and security.

 Database Management System (DBMS):

o A DBMS is so ware that manages databases, enabling users to create, access,

and manage databases.

o Examples: MySQL, Oracle, MS Access, PostgreSQL.

Rela onal Data Model:

 Concept of Domain:

o A domain refers to the set of permissible values that a column in a database

can take. For example, the "Age" column can only take integer values
between 0 and 120.

 Tuple:

o A tuple is a single row in a table represen ng a set of related data values (also
called a record).

o Example: In a table of students, a tuple could represent the details of one

student.

 Rela on:

o A rela on is a table with rows (tuples) and columns (a ributes). It stores data
in a structured format.

 Candidate Key:

o A candidate key is a set of one or more a ributes that uniquely iden fies a
record in a table. There can be mul ple candidate keys in a table.

 Primary Key:

o A primary key is a unique iden fier for a record in a table. It cannot be NULL
and must contain unique values.
 o Example: Student ID could be the primary key in a student table.

 Alternate Key:

o An alternate key is a candidate key that is not selected as the primary key but
can s ll uniquely iden fy a record.

Advantages of Using Structured Query Language (SQL):

 SQL provides a standardized method of interac ng with databases.

 It simplifies database opera ons like crea on, querying, upda ng, and dele on.

 SQL is used for both querying data and modifying the structure of the database
(through DDL and DML commands).

SQL Categories:

1. Data Defini on Language (DDL):

o DDL commands are used to define the database structure. They include:

 CREATE DATABASE: Creates a new database.

 CREATE TABLE: Defines a new table in the database.

 DROP: Deletes a database or a table.

 ALTER: Modifies an exis ng database object (such as a table).

2. Data Query Language (DQL):

o DQL is used to query and retrieve data from the database.

 SELECT: Retrieves data from one or more tables.

 FROM: Specifies the table(s) from which to retrieve the data.

 WHERE: Filters the data based on condi ons.

 Rela onal operators (>, <, =, !=)

 Logical operators (AND, OR, NOT)

 BETWEEN: Filters records within a range.

 IS NULL: Filters records where a column has a NULL value.

 IS NOT NULL: Filters records where a column does not have a

NULL value.
 3. Data Manipula on Language (DML):

o DML is used for inser ng, upda ng, or dele ng data in the database.

 INSERT: Adds new records to a table.

 DELETE: Removes records from a table.

 UPDATE: Modifies exis ng records in a table.

Introduc on to MySQL:

 MySQL is an open-source DBMS widely used for developing web applica ons and
handling large datasets.

 MySQL allows you to interact with the database using SQL commands.

Crea ng a Database Using MySQL:

1. CREATE DATABASE:

sql

CREATE DATABASE my_database;

o This command creates a new database named "my_database."

2. CREATE TABLE:

sql

CREATE TABLE students (

student_id INT PRIMARY KEY,

name VARCHAR(100),

age INT

);

o This command creates a table called students with columns for student ID,
name, and age.
Data Types in MySQL:

 INT: Integer values (e.g., 123)

 VARCHAR(n): Variable-length string (e.g., VARCHAR(100) can store up to 100

characters)

 DATE: Date in the format YYYY-MM-DD

 FLOAT: Floa ng-point numbers (e.g., 12.34)

 BOOLEAN: TRUE or FALSE values

Data Defini on Commands:

1. CREATE DATABASE:

Sql

CREATE DATABASE database_name;

o Creates a new database.

2. CREATE TABLE:

sql

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

...

);

o Creates a new table with specified columns and data types.

3. DROP DATABASE:

sql

DROP DATABASE database_name;

o Deletes the specified database.

 4. ALTER TABLE:

sql

ALTER TABLE table_name ADD column_name datatype;

o Adds a new column to an exis ng table.

Data Query Commands:

1. SELECT:

sql

SELECT * FROM students;

o Retrieves all records from the students table.

2. FROM:

sql

SELECT name FROM students;

o Retrieves the name column from the students table.

3. WHERE:

sql

SELECT * FROM students WHERE age > 18;

o Retrieves records of students whose age is greater than 18.

4. BETWEEN:

sql

SELECT * FROM students WHERE age BETWEEN 18 AND 25;

o Retrieves records of students whose age is between 18 and 25.

5. IS NULL / IS NOT NULL:

sql

SELECT * FROM students WHERE name IS NULL;

o Retrieves records where the name column has a NULL value.

Data Manipula on Commands:

1. INSERT:

sql

INSERT INTO students (student_id, name, age) VALUES (1, 'Alice', 20);

o Inserts a new student record with student ID 1, name "Alice", and age 20.

2. DELETE:

sql

DELETE FROM students WHERE student_id = 1;

o Deletes the student record where student_id is 1.

3. UPDATE:

sql

UPDATE students SET age = 21 WHERE student_id = 1;

o Updates the age of the student with student_id 1 to 21.

Conclusion:

 Understanding database concepts and SQL commands is essen al for interac ng with
databases, managing data, and ensuring its security and integrity.

 SQL provides a powerful toolset for querying, defining, and manipula ng data in a
database.