DBMS Tables
A table is a set of data represented by columns and rows. A column is referred to as a field and a row is a combination of column values and is referred to as a record. Tables contain a unique set of characteristics and they store data of the same type in each row.
Data Fields
A data field which is one piece of information you track in your database. Each data field in the table can define the characteristics of its data as a string value, numeric value, date and/or time values.
Relationships
A relational database contains tables that relate to another table by using a relationship. The tables are connected by a common field. The relationships are defined as:
One to Many: this is the most common type of a table relationship. For every record in Table A, there are multiple records in Table B.
Example: There is a one to many relationship between the Customers table and Orders table. A customer may have many orders in the Order table.
Many to Many: For every record in Table A, there are multiple records in Table B, and vice versa. In this case, a third table called a Join Table is created to which will contain only unique values
Example: Many to Many relationship between Orders and Products with the table ProductsOrders functioning as the Join Table. The table ProductsOrders holds all the details about each order and what products it contains. Its primary key is a combination of the primary key of the Orders and Products table.
One to One – the rarest type of a table relationship. It is used for security and organization to avoid empty fields that only apply to some records in a table.
Keys
Key fields are used to build the relationships between data in different tables.
Primary Key: Usually auto-numbered, unique ID for internal tracking and for matching records between related tables. Each value is unique to the table and cannot be null.
Foreign Key: A field in a related table pointing back to the Primary Key in another table. For auto-numbered fields it is defined as a number value.
Features of Primary Keys
The Primary key should always be the first field in each table, followed by any foreign key(s).
There is only one Primary Key per table.
Primary keys should never be actual data – not even something unique like a Social Security number or Student ID. These values are obtained from an outside source and, while they seem unique and reliable, they could produce data entry errors.
Structured Query Language (SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert, etc. to carry out the required tasks.
These SQL commands are mainly categorized into four categories as:
DDL – Data Definition Language
DQl – Data Query Language
DML – Data Manipulation Language
DCL – Data Control Language
N:B Though many resources claim there to be another category of SQL clauses TCL – Transaction Control Language
1.DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.
Examples of DDL commands:
CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
DROP – is used to delete objects from the database.
ALTER-is used to alter the structure of the database.
TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
COMMENT –is used to add comments to the data dictionary.
RENAME –is used to rename an object existing in the database.
2.DQL (Data Query Language) :
DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it.
Example of DQL:
SELECT – is used to retrieve data from the database.
3.DML(Data Manipulation Language): The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
Examples of DML:
INSERT – is used to insert data into a table.
UPDATE – is used to update existing data within a table.
DELETE – is used to delete records from a database table.
4.DCL(Data Control Language): DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions and other controls of the database system.
Examples of DCL commands:
GRANT-gives users access privileges to the database.
REVOKE-withdraw user’s access privileges given by using the GRANT command.
TCL(transaction Control Language): TCL commands deal with the transaction within the database.
Examples of TCL commands:
COMMIT– commits a Transaction.
ROLLBACK– rollbacks a transaction in case of any error occurs.
SAVEPOINT–sets a savepoint within a transaction.
SET TRANSACTION–specify characteristics for the transaction.
Thank you.
The word computer originates from the word compute which means to calculate. It was initially used to refer to human beings that perform calculations. A computer has been defined so many forms by different authors. Some of the definitions are as follows: - Computer :- is an electronic device that accepts data as input Process the data and gives out information as output. - Computer :- It can be defined as an electronic or electromechanical device that is capable of accepting data, holds a means of instruction in its memory, process the information given by following sets of instructions to carry out a task without human intervention and at the end provide significant result. - Computer :- is any machine which accepts data and information presented to it in a prescribed form,carry out some operations on the input and supply the required result in a specified format as information or as signals to control some other machines or process. - Computer :- is an ele...
Comments