Week 3 Module – Fundamentals of Databases - September 28-October 4, 2020
Components of a Database
User - Users are the one who really uses the database. Users can be
administrator, developer or the end users.
Data or Database - As we discussed already, data is one of the important
factor of database. A very huge amount of data will be stored in the database
and it forms the main source for all other components to interact with each
other.
There are two types of data.
1. User data. It contains the data which is responsible for the
database, i.e.; based on the requirement, the data will be stored
in the various tables of the database in the form of rows and
columns.
2. Metadata. It is known as ‘data about data’, i.e.; it stores the
information like how many tables, their names, how many
columns and their names, primary keys, foreign keys etc.
basically these metadata will have information about each tables
and their constraints in the database.
DBMS - This is the software helps the user to interact with the database. It
allows the users to insert, delete, update or retrieve the data. All these
operations are handled by query languages like MySQL, Oracle etc.
Database Application - It the application program which helps the users to
interact with the database by means of query languages. Database
application will not have any idea about the underlying DBMS.
Week 3 Module – Fundamentals of Databases - September 28-October 4, 2020
DBMS Statements
Data Definition Language
CREATE – used to create objects in database
ALTER – alter the pattern of database
DROP – helps in detecting objects
RENAME – useful in renaming an object
CREATE statement or command is used to create a new database. In
structured query language the create command creates an object in a
relational database management system.
The commonly used create command is as follows
CREATE TABLE [name of table] ([ definitions of column ]) [parameters
of table]
Week 3 Module – Fundamentals of Databases - September 28-October 4, 2020
DROP statement destroys or deletes database or table. In structured
query language, it also deletes an object from relational database
management system.
Typically used DROP statement is;
DROP type of object name of object
ALTER statement enhance the object of database. In structured query
language it modifies the properties of database object.
The ALTER statement is:
ALTER type of object name of object
Week 3 Module – Fundamentals of Databases - September 28-October 4, 2020
RENAME statement is used to rename a database.
It’s statement is as follows:
RENAME TABLE old name of table to new name of table.
Data Manipulation Language
It has statements which are used to manage the data within the pattern
of objects. Some of the samples of the statements are as follows:
SELECT – useful in holding data from a database
INSERT – helps in inserting data in to a table
UPDATE – used in updating the data
DELETE – do the function of deleting the records
MERGE – this do the UPSERT operation i.e. insert or update operation
The SELECT statement allows you to read data from one or more
tables. To write a SELECT statement in MySQL, you follow this syntax:
Week 3 Module – Fundamentals of Databases - September 28-October 4, 2020
SELECT column_name FROM table_name;
The INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
The UPDATE statement is used to update existing records in a table:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Notice the WHERE clause in the UPDATE syntax: The WHERE clause
specifies which record or records that should be updated. If you omit the
WHERE clause, all records will be updated!
The DELETE statement is used to delete records from a table:
DELETE FROM table_name
WHERE some_column = some_value
Notice the WHERE clause in the DELETE syntax: The WHERE clause
specifies which record or records that should be deleted. If you omit the
WHERE clause, all records will be deleted!
These syntax elements are similar to the syntax elements used in
computer programming language.
Week 3 Module – Fundamentals of Databases - September 28-October 4, 2020
Transaction Control Language
It has commands which are used to manage the transactions or the
conduct of a database.
Some examples of it are:
COMMIT – use to save work
SAVE POINT – helps in identifying a point in the transaction, can be
rolled back to the identified point
ROLL BACK – has the feature of restoring the database to the
genuine point, since from the last COMMIT
SET TRANSACTION – have parameter of changing settings like
isolation level and roll back point
COMMIT command permanently save the transaction in to database.
It’s syntax is: Commit;
ROLL BACK command uses the save point command to jump to save point
in transaction.
It’ s syntax is: rollback to name-save point;
SAVE POINT command is used to save a transaction temporarily.
It’s syntax is: Save point name-save point;
HANDS-ON APPLICATION USING:
https://www.mysqltutorial.org/tryit/
