Grade 12 -Unit 3 –Database Management (Short Note)
- Database is a shared collection of logically related data.
- The term Relational Database Management System (RDBMS) usually
refers to various types of software systems developed in order to manage
databases.
- RDBMS is used to create, maintain, and provide controlled access to a
relational database.
- A relational database is based on a relational data model.
- Data are stored in a two dimensional table, which contains columns or
fields and rows or records. Each column of a table represents an attribute or
data value, and each row in a table represents a tuple or record.
- Attributes are the set of properties to describe the instances of the entity.
- A record is a row or a tuple in the table. It contains a single data value in
each column.
- SQL (Structured Query Language) is a standard language for accessing
and manipulating a database.
- SQL is a special-purpose query language meant for interacting with
relational databases such as Microsoft Access.
- Understanding how SQL works can help create better queries and make it
easier to understand how to fix a query that is returning unwanted results.
- SQL consists of a number of commands with further options to allow you to
carry out your operations with a database.
- Based on their purposes, three categories of SQL commands are presented
below.
-
1. Data Definition Language (DDL): DDL contains commands that allow
you to create or modify tables and establish relationship between tables
in your database structure.
2. Data Manipulation Language (DML): DML contains commands that
are used to manage the database by performing operations such as
inserting, updating, deleting, and navigating through data.
Grade 12 -Unit 3 –Database Management (Short Note)
3. Data Query Language (DQL): is used for querying or selecting all or
subsets of data from a database.
1. Data Definition Language (DDL)
- DDL is part of SQL that is used to create and restructure a database.
- Some of the most fundamental DDL commands include CREATE TABLE,
ALTER TABLE, and DROP TABLE
CREATE TABLE Command
- The CREATE TABLE command is used to create a new table in a database.
- The CREATE TABLE command provides various options to create a table.
CREATE TABLE table_name(
Column1 datatype [Primary Key],
[Column2 datatype][REFERENCES table_name2(Column1)],
[Column3 datatype],
[Column4 datatype]…)
ALTER TABLE command
- Once a table is created, it can be modified using the ALTER TABLE
command.
- Using the ALTER command, you can add column(s), drop column(s), and
change column definitions. It is also used to establish relationship between
tables.
DROP TABLE command
- Dropping a table is actually one of the easiest thing to do.
- Use the DROP TABLE command to delete the table already created.
Grade 12 -Unit 3 –Database Management (Short Note)
e.g DROP TABLE COURSE
2. Data Manipulation Language
- Data Manipulation Language (DML) consists of commands that allow you
to manage the database by performing operations such as inserting,
updating, deleting, and navigating through data.
- The DML commands include INSERT, UPDATE and DELETE
INSERT command
- INSERT command helps to insert new records to a table.
- The INSERT command can be used in one of the two options shown in the
table below.
Option 1: INSERT INTO table_name VALUES (value1, value2, value3 ...)
Option 2: INSERT INTO table_name (column1, column2…) VALUES
(value1, value2 …)
UPDATE command
- The UPDATE command does not add new records to a table, nor does it
remove records. It simply updates existing records in the table.
- The UPDATE command is used to change a value of one or more fields in
an existing table row or number of rows.
- The general syntax of an UPDATE command is given below.
UPDATE table_name
SET column1 = value1,
column2 = value2...
Grade 12 -Unit 3 –Database Management (Short Note)
WHERE condition
e.g
UPDATE Teacher SET T_Salary = 20000 WHERE T_sex=’female’;
DELETE command
- The DELETE command is used to delete a record or multiple records from
the database.
- DELETE command does not remove the table structure, rather it only
deletes the data that is currently being held by the table structure.
DELETE FROM table_name WHERE condition;
3. Data Query Language (DQL ) – SELECT Command
- Data Query Language (DQL) is a data query language for relational
DBMSs.
- It provides a SELECT command for querying all or subset of records from
one or more tables of a database.
- A SELECT SQL statement takes the general form as follows:
SELECT field1, [field2]
FROM table
[WHERE criterion];
