DATABASE MANAGEMENT SYSTEMS REVIEW
What is T-SQL?
SQL stands for Structured Query Language, as most of us know. The ‘T’ at the beginning stands for Transact or Transactional. We call Microsoft’s SQL implementation T-SQL. Just like T-SQL, there are implementations like Oracle’s PL/SQL or mySQL. In these implementations, we see distinctions only as a query language.
The logic in T-SQL works like this: T-SQL tells the database what we want and returns a response to us by the database engine. This response is usually a table. If we need to give an example of what can be done with T-SQL: Adding and deleting new records to the database. Creation of Stored Procedures. We can give examples such as sending queries to get specific information.
What is Database?
A database can be defined as an ‘organized collection of information’. The banking systems, websites and mobile applications we use in our daily lives work on a database. Many people in the world are engaged in activities that can be recorded, consciously or unknowingly. Relational or non-relational databases are used to track such activities.
Databases are kept in the form of tables. Structurally, more than one table containing different information can be used within a database. These tables may be related or not related to each other. Tables consist of columns and rows.
Primary Key
The primary key is used to uniquely separate records from each other in the database. TR Identity Numbers can be given as the most understandable example. It is unique for every Turkish citizen. If two people could have the same ID number, it would be very difficult to track who is doing what in which institution of the state.
Foreign Key
Foreign key is the key type that allows a Primary key to be referenced in another table and does not contain singular values.
For example, if we think that a ‘Citizen’ table contains name, surname and TR Identity Number; We can call the TR Identity Number as the primary key. Likewise, a ‘Contact’ table can contain a TR Identity Number, telephone number and contact type (such as e-mail, home number, work number). The TR Identity Number in this second table is Foreign key. The reason is; a citizen can have both work-number, home-number and e-mail address. Thus, the TR ID number column in the ‘Contact’ table is a foreign key; We say that we are sending a reference to the TR ID number in the ‘Citizen’ table.
What is Database Normalization?
The definition of normalization is to reduce redundant data redundancy by using data columns, tables, and relationships between tables. Let’s take an example to explain this:
Let’s say that in our first table, we have a lesson subject, lesson teacher and weekly lesson hours. In our second table, the course code, student number, student and student’s class should be included. When we talk about the second table in such tables, the number of information will increase since more than one student can take the same course code. However, the first table contains singular values and information. Such tables are called dimension tables. Our primary key is in this table.
Our second table shows the students who took the courses. We do not need to show the lesson teacher and weekly lesson hours in the first table in the second table. because the course code is common in both tables. The course code shows that it takes the information from there by referencing the table containing the primary key as a Foreign key in the second table.
Every table must contain a primary key. Foreign keys contain a primary key to reference tables. If we want to describe the work done in Turkish, Foreign key tells us, “This information actually comes from this table.” We can say what you said.
Normalization Levels
There are 3 types of normalization levels to keep databases at a consistent and performance level: These are; They are First Normal Form(1NF), Second Normal Form(2NF) and Third Normal Form(3NF).
1NF; Indicates that repeating columns cannot be found in the same table, only one value can be found in each column, and each row must be identified by a unique key.
2NF; The 1NF rules say that after the table is applied, it should be split into smaller pieces. The primary key and foreign keys of the tables separated by this level should match each other and there should be no inconsistency.
3NF; It says that only fields containing foreign keys should be left in the transaction table. By creating two dimension tables, splitting them into tables where only the information is kept, only the ids are kept in the transaction table, and the information in the dimension tables can be accessed only by reference. Thus, the number of columns is reduced.
SQL — Structured Query Language
SQL statements are structurally divided into four groups. These are; Data Definition Language (DDL — Data Definition Language), Data Manipulation Language (DML — Data Manipulation Language), Data Control Language (DCL — Data Control Language) and Transaction Control (TCL) operations that are used to manage what is done with DML.
Data Definition Language (DDL — Data Definition Language)
The data definition language is not concerned with what the data is, but where and how the data is kept.
The table contains commands used to create objects such as User.
Basic expressions of the data definition language: Create(used to create objects), Alter(used to modify Objects) and Drop(used to delete objects).
Data Manipulation Language (DML — Data Manipulation Language)
The data manipulation language operates on the held data.
Basic expressions of data manipulation language: Insert(To add new record), Update(Updates previous record), Delete(Used to delete record in Database) and Select(queries records in Database).
Data Control Language (DCL — Data Control Language)
It regulates the access rights to the data on the database.
It contains commands used to perform operations such as granting various authorizations to users and reversing authorizations.
Key expressions of the data control language: Grant(Allows users to manipulate records), Deny(Restricts user to use records), and Revoke(Revokes all previous restrictions and permissions).
