Faculty Of Computer Science
Database Concept
Lecturer: Lutfullah Haqnesar
Chapter Two
Database Language
Agenda
DBMS Architecture
Schema and Instance
Database Language
Data Definition Language
DBMS Architecture
• DBMS architecture describe that how users are connected to the
database to get their request done.
• The client/server architecture consists of many PCs and a workstation
which are connected via the network.
• Database architecture can be of types like: 1-tier architecture, 2-tier
architecture and 3-tier architecture.
DBMS Architecture
DBMS Architecture
1-Tier Architecture:
• In this architecture, the database is directly available to the user. It
means the user can directly connected to the DBMS and uses it.
• Any changes done here will directly be done on the database itself.
• It doesn't provide a handy tool for end users.
• The 1-Tier architecture is used for development of the local application,
where programmers can directly communicate with the database.
DBMS Architecture
2-Tier Architecture
• The 2-Tier architecture is same as basic client-server.
• In the two-tier architecture, applications on the client end can directly
communicate with the database at the server side.
• The user interfaces and application programs are run on the client-side.
• The server side is responsible to provide the functionalities like: query
processing and transaction management.
2-tier Architecture
DBMS Architecture
3-Tier Architecture
• The 3-Tier architecture contains another layer between the client and
server.
• In this architecture, client can't directly communicate with the server.
• The application on the client-end interacts with an application server
which further communicates with the database system.
• The 3-Tier architecture is used in case of large web application.
3-tier architecture
Schema and Instance
Instance
• The data which is stored in the database at a particular moment of
time is called an instance of the database.
Schema and Instance
Schema
• The overall design of a database is called schema.
• A database schema is the skeleton structure of the database.
• A schema contains schema objects like table, foreign key, primary key,
views, columns, data types, stored procedure, etc.
• A database schema can be represented by using the visual diagram. That
diagram shows the database objects and relationship with each other.
Schema and Instance
• A schema diagram can display only
some aspects of a schema like the
name of record, data type, and
constraints.
• Other aspects can't be specified
through the schema diagram.
• For example, the given figure neither
show the data type of each data item
nor the relationship among various
files.
Database Language
• A DBMS has appropriate languages and interfaces to express
database queries and updates.
• Database languages can be used to read, store, delete and update the
data in the database.
Types of Database Language
Data Definition Language (DDL)
• DDL stands for Data Definition Language.
• It is used to define structure of a database. Using the DDL statements,
you can create the skeleton of the database.
• It is used to create schema, tables, constraints, etc. in the database.
Data Definition Language (DDL)
Here are some tasks that come under DDL:
1. Create: It is used to create objects in the database.
2. Alter: It is used to alter the structure of the database.
3. Drop: It is used to delete objects from the database.
4. Truncate: It is used to remove all records from a table.
5. Rename: It is used to rename an object.
6. Comment: It is used to comment on the query.
How to install MySQL?
• To install MySQL, we will suggest you to install AMP (Apache, MySQL,
PHP) software stack. It is available for all operating systems.
• XAMPP (Cross, Apache, MySQL, PHP, Perl) for Cross Platform.
How to install MySQL?
• Click on the below link provided to download the XAMPP server according
to your window requirement.
https://sourceforge.net/projects/xampp/
• After downloading double click and install XAMPP on your system
Create Database
Syntax:
Create Database database_name;
Example:
create database Rana;
Display Database
• Show databases;
• drop database database_name;
Creating Table
• CREATE TABLE statement is used to create a new table in the database.
• To create a table, you have to name that table and define its columns and
datatype for each column.
Creating Table
Syntax:
• CREATE TABLE table_name ( column_definition1, column_definition2,
..., table_constraints );
Example:
• CREATE TABLE employee_table (id int, name varchar(45), occupation
varchar(35), age int, PRIMARY KEY (id) );
Displaying Table
• show TABLES;
• DESCRIBE employee_table;
Alter Table
2. ALTER TABLE Statement
In Oracle, ALTER statement specifies how to add, modify, delete columns in
a table. It is also used to rename a table.
Alter Table
1 How to add column in a table
ALTER table table_name ADD column_name column-definition;
Example:
alter table emp add age number(5);
2 How to modify column of a table
ALTER table table_name MODIFY column_name column_type;
• Example:
alter table emp1 modify name varchar2(40);
Alter Table
3 How to drop column of a table
ALTER TABLE table_name DROP COLUMN column_name;
Example:
alter table emp1 drop column age;
4 How to rename column of a table
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
Example:
alter table emp1 rename column name to emp_name;
Alter Table
5 How to rename table
ALTER TABLE table_name RENAME TO new_table_name;
Example:
alter table emp1 rename to emp;
Data Definition Language (DDL)
3. Oracle DROP TABLE Statement
• Oracle DROP TABLE statement is used to remove or delete a table.
Syntax
DROP TABLE table_name;
Example:
drop table emp;
Data Definition Language (DDL)
4. TRUNCATE TABLE statement
• the truncate statement is used to remove all the records of a table. It
works same as the delete statement but without using where clause.
Syntax:
Truncate table table_name;
Example:
truncate table emp;
Data Definition Language (DDL)
5. SQL Comment:
comments can be placed in queries. Comments can be a single line as well
as multi-line.
Example:
rename emp to /* this is a comment */ emp12;
END OF CHAPTER
