Grade 12 | Database Management System | Computer
76
DDL (Data Definition Language) and DML (Data Manipulation language)
SQL stands for Structured Query Language. It is an international standard database query language for accessing and managing data in database.
SQL was introduced and developed by IBM in early 1970s. IBM was able to demonstrate SQL which could be used to control relational database. SQL is not a complete programming language, It is only used for communicating with database. It provides platform which allows the users to query a database without getting depth knowledge of the design of the underlying tables.
SQL pronounced as “see”- “Quell” is made of three sub languages DDL, DML and DCL(Data Control Statement).
Basic structure of SQL
CREATE table table_name (field1 data_type, field2 data_type,..........);
CREATE table Student (SID NUMBER (4), Name CHAR (25) );
INSERT into table_name VALUES (List of values);
INSERT into Student VALUES (22, ‘Ram’);
SELECT field1, field2, ….. FROM table_name
SELECT SID, Name from Student
DDL is used by the database designers and programmers to specify the content and structure of the table. It isused to define the physical characteristics of records. It includes commands that manipulate the structure ofobject such as table. Example of these commands are Create, Alter, Drop, Rename etc.
To create a tableCREATE table table_name (field1 data_type, field2 data_type,..........);
CREATE table Student (SID NUMBER (4), Name CHAR (25) );
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE Customers DROP COLUMN Email;
DML is related with manipulation of records such as retrieval, sorting, display and deletion of records or data. Ithelps user to use query and display reports of the table. So, it provide techniques for processing the database. Itincludes commands to manipulate the information stored in the databases. Example of these commands areInsert, Delete, Select and Update etc.
INSERT into table_name VALUES (List of values);
INSERT into Student VALUES (22, ‘Ram’);
SELECT field1, field2, ….. FROM table_name
SELECT SID, Name from Student
DELETE FROM table_name WHERE condition;
DELETE FROM Customers WHERE CustomerName='Rajesh';
DCL is short name of Data Control Language which includes commands such as GRANT and mostly concernedwith rights, permissions and other controls of the database system.
GRANT - allow users access privileges to the databaseREVOKE - withdraw users access privileges given by using the GRANT command
GRANT SELECT,INSERT,UPDATE,DELETE on Employee To User1REVOKE INSERT On Employee To user1Database Model
Hierarchical Database System Structure was developed by IBM in the early 1960s. In a Hierarchical database,model data is organized in a tree-like structure. Data is Stored Hierarchically (top down or bottom up) format.
Data is represented using a parent-child relationship. In Hierarchical DBMS parent may have many children, butchildren have only one parent.
The network database structure was invented by Charles Bachman in 1969. Network database managementsystems (Network DBMSs) uses network structure to create a relationship between entities. The networkdatabase model allows each child to have multiple parents. It helps you to address the need to model morecomplex relationships like as the orders/parts many-to-many relationship. In this model, entities are organized ina graph which can be accessed through several paths. Eg: Integrated Data Store (IDS), Raima DatabaseManager.
This database is based on the relational data model, which stores data in the form of rows(tuple) andcolumns(attributes), and together forms a table(relation). A relational database uses SQL for storing,manipulating, as well as maintaining the data. E.F. Codd invented the database in 1970. Each table in thedatabase carries a key that makes the data unique from others. Examples of Relational databases are MySQL,Microsoft SQL Server, Oracle, etc.
Database Normalization is a technique of organizing the data in the database. Normalization is a systematicapproach of decomposing tables to eliminate data redundancy(repetition) and undesirable characteristics likeInsertion, Update and Deletion Anomalies. It is a multi-step process that puts data into tabular form, removingduplicated data from the relation tables.
Normalization is used for mainly two purposes,● Eliminating redundant(useless) data.
● Ensuring data dependencies make sense i.e data is logically stored.
If a table is not properly normalized and have data redundancy then it will not only eat up extra memory space butwill also make it difficult to handle and update the database, without facing data loss. Insertion, Updation andDeletion Anomalies are very frequent if database is not normalized. To understand these anomalies let us take anexample of a Student table.
rollno name branch hod office_tel401 Akon CSE Mr. X 53337402 Bkon CSE Mr. X 53337403 Ckon CSE Mr. X 53337404 Dkon CSE Mr. X 53337In the table above, we have data of 4 Computer Sci. students. As we can see, data for the fields branch,hod(Head of Department) and office_tel is repeated for the students who are in the same branch in thecollege, this is Data Redundancy.
if we have to insert data of 100 students of same branch, then the branch information will be repeated forall those 100 students.
These scenarios are nothing but Insertion anomalies.
Updation AnomalyWhat if Mr. X leaves the college? or is no longer the HOD of computer science department? In that case allthe student records will have to be updated, and if by mistake we miss any record, it will lead to datainconsistency. This is Updation anomaly.
Deletion AnomalyIn our Student table, two different informations are kept together, Student information and Branchinformation. Hence, at the end of the academic year, if student records are deleted, we will also lose thebranch information. This is Deletion anomaly.
1. First Normal Form2. Second Normal Form3. Third Normal Form
1. It should only have single(atomic) valued attributes/columns.
2. Values stored in a column should be of the same domain3. All the columns in a table should have unique names.
4. And the order in which data is stored, does not matter.
Rules for First Normal FormThe first normal form expects you to follow a few simple rules while designing your database, and they
Rule 1: Single Valued AttributesEach column of your table should be single valued which means they should not contain multiple values.
We will explain this with help of an example later, let's see the other rules for now.
Rule 2: Attribute Domain should not changeThis is more of a "Common Sense" rule. In each column the values stored must be of the same kind ortype.
For example: If you have a column dob to save date of births of a set of people, then you cannot or youmust not save 'names' of some of them in that column along with 'date of birth' of others in that column.
It should hold only 'date of birth' for all the records/rows.
Rule 3: Unique name for Attributes/ColumnsThis rule expects that each column in a table should have a unique name. This is to avoid confusion at thetime of retrieving data or performing any other operation on the stored data.
If one or more columns have same name, then the DBMS system will be left confused.
Rule 4: Order doesn't mattersThis rule says that the order in which you store the data in your table doesn't matter.
let's take an example where we will create a table to store student data which will have student's roll no.,their name and the name of subjects they have opted for.
Here is our table, with some sample data added to it.
rollno name Subject401 Akon OS, CN402 Bkon Java403 Bkon C, C++Our table already satisfies 3 rules out of the 4 rules, as all our column names are unique, we have storeddata in the order we wanted to and we have not inter-mixed different type of data in columns.
But out of the 3 different students in our table, 2 have opted for more than 1 subject. And we have storedthe subject names in a single column. But as per the 1st Normal form each column must contain atomicvalue.
How to solve this Problem?It's very simple, because all we have to do is break the values into atomic values.
Here is our updated table and it now satisfies the First Normal Form.
rollno name Subject401 Akon OS401 Akon CN402 Bkon Java403 Bkon C++403 Bkon C
For a table to be in the Second Normal Form,1. It should be in the First Normal form.
2. Primary Key is introduced3. And, it should not have Partial Dependency.
A table is said to be in the Third Normal Form when,1. It is in the Second Normal form.
2. And, it doesn't have Transitive Dependency.
A centralized database is basically a type of database that is stored, located as well as maintained at asingle location only. This type of database is modified and managed from that location itself. This locationis thus mainly any database system or a centralized computer system. The centralized location isaccessed via an internet connection (LAN, WAN, etc). This centralized database is mainly used byinstitutions or organizations.
● Since all data is stored at a single location only thus it is easier to access and coordinate data.
● The centralized database has very minimal data redundancy since all data is stored in a singleplace.
● It is cheaper in comparison to all other databases available.
● The data traffic in the case of a centralized database is more.
● If any kind of system failure occurs in the centralized system then the entire data will bedestroyed.
A distributed database is basically a type of database which consists of multiple databases that areconnected with each other and are spread across different physical locations. The data that is stored invarious physical locations can thus be managed independently of other physical locations. Thecommunication between databases at different physical locations is thus done by a computer network.
● This database can be easily expanded as data is already spread across different physicallocations.
● The distributed database can easily be accessed from different networks.
● This database is very costly and is difficult to maintain because of its complexity.
● In this database, it is difficult to provide a uniform view to users since it is spread across differentphysical locations.
A Database Administrator (DBA) is individual or person responsible for controlling, maintenance,coordinating, and operation of database management system. Managing, securing, and taking care ofdatabase system is prime responsibility.
They are responsible and in charge for authorizing access to database, coordinating, capacity, planning,installation, and monitoring uses and for acquiring and gathering software and hardware resources as andwhen needed. Their role also varies from configuration, database design, migration, security,troubleshooting, backup, and data recovery. Database administration is major and key function in any firmor organization that is relying on one or more databases. They are overall commander of Databasesystem.
● Database Administrator manages and controls three levels of database like internal level,conceptual level, and external level of Database management system architecture and indiscussion with comprehensive user community, gives definition of world view of database. It thenprovides external view of different users and applications.
● Database Administrator ensures held responsible to maintain integrity and security of databaserestricting from unauthorized users. It grants permission to users of database and contains profileof each and every user in database.
● Database Administrator also held accountable that database is protected and secured and thatany chance of data loss keeps at minimum.
● Decides hardware –They decides economical hardware, based upon cost, performance and efficiency of hardware,and best suits organisation. It is hardware which is interface between end users and database.
● Manages data integrity and security –Data integrity need to be checked and managed accurately as it protects and restricts data fromunauthorized use. DBA eyes on relationship within data to maintain data integrity.
● Database design –DBA is held responsible and accountable for logical, physical design, external model design, andintegrity and security control.
● Database implementation –DBA implements DBMS and checks database loading at time of its implementation.
● Query processing performance –DBA enhances query processing by improving their speed, performance and accuracy.
● Tuning Database Performance –If user is not able to get data speedily and accurately then it may loss organization business. Soby tuning SQL commands DBA can enhance performance of database.
Data Security safeguards digital data from unwanted access, corruption, or theft. It is a notion that impartsphysical security to hardware and software devices and covers all aspects of information security. It alsoimparts administrative and access controls and logical security to software applications. It also coverspolicies and procedures to be followed by a company.
Why is Data Security important?Data is a valuable asset that generates, acquires, saves, and exchanges for any company. Protecting itfrom internal or external corruption and illegal access protects a company from financial loss, reputationalharm, consumer trust degradation, and brand erosion. Furthermore, regulations for securing data, imposedby the government and the industry, make it critical for a company to achieve and maintain compliancewherever it does business.
This type of data security measures includes limiting both physical and digital access to critical systemsand data. This includes making sure all computers and devices are protected with mandatory login entry,and that physical spaces can only be entered by authorized personnel.
Similar to access controls, authentication refers specifically to accurately identifying users before theyhave access to data. This usually includes things like passwords, PIN numbers, security tokens, swipecards, or biometrics.
Good data security means you have a plan to securely access data in the event of system failure, disaster,data corruption, or breach. You’ll need a backup data copy, stored on a separate format such as a physicaldisk, local network, or cloud to recover if needed.
You’ll want to dispose of data properly and on a regular basis. Data erasure employs software tocompletely overwrite data on any storage device and is more secure than standard data wiping. Dataerasure verifies that the data is unrecoverable and therefore won’t fall into the wrong hands.
A computer algorithm transforms text characters into an unreadable format via encryption keys. Onlyauthorized users with the proper corresponding keys can unlock and access the information. Everythingfrom files and a database to email communications can — and should — be encrypted to some extent.