[Database] SQL
Structured Query Language (SQL)
SQL is a language used for interacting with Relational Database Management Systems(RDBMS). You can use SQL to get the RDBMS to do things like CRUD(Create, Rerieve, Update, Delete), to create and manage database, to design and create database tables, to perform administration tasks (security, user management, import or export, etc)
SQL implementations vary between systems. Not all RDBMS follow SQL standard but the concepts are the same but the implementaion may vary.
SQL is actually a hybrid language, it’s basically 4 types of language in one.
- Data Query Language (DQL)
- Used to query the database for information
- Get information that is already stored there
- Data Definition Language (DDL)
- Used for defining database schemas
- Data Control Language (DCL)
- Used for controlling access to the data in the database
- User & Permissions management
- Data Manipulation Language (DML)
- Used for inserting, updating and deleting data from the database
Attribute
In general, an attribute is a characteristic. In a database management system(DBMS), an attribute refers to a database component, such as a table. It also may refer to a database field. Attributes describe the instances in the column of a database.
In relational databases, attributes are the describing characteristics or properties that define all items pertaining to a certain category applied to all cells of a column.
The rows, instead, are called tuples, and represent data sets applied to a single entity to uniquely identify each item. Attributes are, therefore, the characteristics of every individual tuple that help describe its unique properties.
Think of a table in relational database as being analogous to an electronic spreadsheet. An attribute is simply one non-null cell in the spreadsheet, or the conjunction of a column and row.
It stores only one piece of data about the object represented by the table where the attribute belongs. For example, the tuple ccan be an Invoice entity. The attirbutes of an invoice might be Price, Number, Date or Paid/Unpaid.
Beyond the self-explanatory simple or single-value attributes, there are several types of attributes available.
- Composite attribute: is an attribute composed of several other simple attributes. For example, the Address attribute of an Employee entity could consist of the Street, City, Postal code and Country attributes.
- Multivalued attribute: is an attribute where more than one description can be provided. For example, an Employee entity may have more than one Email ID attributes in the same cell.
- Key attribute or primary attribute: is an ID, key, letter or number that uniquely identifies that item. For example, it can be the number of a certain invoice (e.g. the individual ID of that invoice). A table that contains a single key attribute is considered a strong entity. However, a table might contain more than one key attribute if it’s derived from other tables.
- Derived attribute: as the name implies, these are derived from other attributes, either directly or through specific formula results. For example, the Age attribute of an Employee could be derived from the Date of Birth attribute. In other instances, a formula might calculate the VAT of a certain payment, so that whenever the cell with the attribute Payment is filled, the cell with the derived attribute VAT automatically calculates its value.
DBMS Keys
What are keys in DBMS
KEYS in DBMS is an attribute or set of attributes which helps you to identify a row in a relation. They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. Key is also helpful for finding unique record or row from the table. Conclusionly, Database key is also helpful for finding unique record or row from the table. Following table is an example.
|Employee ID|First Name|Last Name|
|—|—|—|
|01|Andrew|Johnson|
|02|Tom|Hidlston|
|03|Alex|Dunphy|
In the above-given example, employee ID could be a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID.
Why we need a Key?
Here are some reasons for using SQL key in the DBMS system.
- Keys help you to identify any row of data in table. In a real world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys ensure that you can uniquely identify a table record despite these challenges.
- Allows you to establish a relationship between and identify the relation between tables
- Help you to enforce identity and integrity in the relationship
Types of Keys in Database Management System
There are mainly seven different types of Keys in DBMS and each key has it’s different functionality:
- Super Key - A super key is a group of single or multiple keys which identifies rows in a table.
- Primary Key - Primary key is a column or group of columns in a table that uniquely identify every row in that table.
- Candidate Key - Candidate Key is a set of attributes that uniquely identify tuples in a table. Candidate Key is a supeer key with no repeated attributes.
- Alternate Key - is a column or group of columns in a table that uniquely identify every row in that table.
- Foreign Key - is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity.
- Compound Key - has two or more attributes that allow you to uniquely recognize a specific record. It is a possible that each column may not be unique by itself within the database.
- Composite Key - is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.
- Surrogate Key - An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key.
Super Key example
A super key is a group of single or multiple keys which identifies rows in a table. A super key may have additional attributes that are not needed for unique identification. |EmpSSN|EmpNum|EmpName| |—|—|—| |9812345098|AB05|Shown| |9876512345|AB06|Roslyn| |199937890|AB07|James|
In the above-given example, EmpSSN and EmpNum EmpName are super keys.
Primary Key example
Primary key is a column or group of columns in a table that uniquely identify every row in that table. The primary key can’t be a duplicate meaning the same value and can’t appear more than once in the table. A table can’t have more than one primary key.
Rules for defining Primary Key:
- Two rows can’t have the same primary key value
- It must for every row to have a primary key value
- The primary key field can not be NULL
- The value in a primary key column can never be modified or updated if any foreign key refers to that primary key
In the following example, StudId is a primary key. |StudID|Roll No|First Name|Last Name|Email| |—|—|—|—|—| |1|11|Tom|Price|abc@fake.com| |2|12|Nick|Wright|xyz@fake.com| |3|13|Dona|Natan|def@fake.com|
Alternate Key example
ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.
In this table, StudID, Roll No, Email are qualified to become a primary key. But since StudID is the primary key, Roll No, Email becomes the alternative key. |StudID|Roll No|First Name|Last Name|Email| |—|—|—|—|—| |1|11|Tom|Price|abc@fake.com| |2|12|Nick|Wright|xyz@fake.com| |3|13|Dona|Natan|def@fake.com|
Candidate Key example
CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.
Properties of Candidate Key:
- It must contain unique values
- Candidate key may have multiple attributes
- Must not contain null values
- It should contain minimum fields to ensure uniqueness
- Uniquely identify each reord in a table
Foreign key example
FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table.
There are example, Dept table and Teacher table. |DeptCode|DeptName| |—|—| |001|Science| |002|English| |005|Computer|
| Teacher ID | Fname | Lname |
|---|---|---|
| B002 | David | Warner |
| B017 | Sara | Joseph |
| B009 | Mike | Brunton |
In this key in dbms example, we have two table, teach and department in a school. However, there is no way to see which search work in which department.
In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.
| Teacher ID | DeptCode | Fname | Lname |
|---|---|---|---|
| B002 | 002 | David | Warner |
| B017 | 002 | Sara | Joseph |
| B009 | 001 | Mike | Brunton |
Surrogate Key example
SURROGATE KEYS is An artificial key which aims to uniquely identify each record. This kind of partial key in dbms is unique because it is created when you don’t have any natural primary key. They do not lend any meaning to the data in the table. Surrogate key is usually an integer. A surrogate key is a value generated right before the record is inserted into a table.
| Fname | Lastname | Start Time | End Time |
|---|---|---|---|
| Anne | Smith | 09:00 | 18:00 |
| Jack | Francis | 08:00 | 17:00 |
| Anna | McLean | 11:00 | 20:00 |
| Shown | Willam | 14:00 | 23:00 |
Above, given example, shown shift timings of the different employee. In this example, a surrogate key is needed to uniquely identify each employee.
Surrogate keys in sql are allowed when No property has the parameter of the primary key and the primary key is too big or complicated.
Core Datatype
INT -- Whole Numbers
DECIMAL(M, N) -- Decimal Numbers
VARCHAR(M) -- String of text of length M
BLOB -- Binary Large Object, Stores large data
DATE -- 'YYYY-MM-DD'
TIMESTAMP -- 'YYYY-MM-DD HH:MM:SS' used for recording
ON DELETE SET NULL and ON DELETE CASCADE
- ON DELETE: When this row gets deleted then
- SET NULL: Set referenced row by foreign key null
- CASECADE: Delete cascading rows too (If deleted foreign key is primary key, then it is recommanded to be handled with cascade)
Trigger
Trigger is basically block of sql code which we can write, and find certain action then should happen when operation gets performed in database.
ER Diagram
This following figure is an basic ER Diagram.
So, it would be applied to that.
Following company data storage requirements would be good examples.
Company Data Storage Requirements
The company is organized into branches. Each branch has a unique number, a name, and a particular employee who manages it.
The company makes it’s money by selling to clients. Each client has a name and a unique number to identify it.
The foundation of the company is it’s employees. Each employee has a name, birthday, sex, salary and a unique number.
An employee can work for one branch at a time, and each branch will be managed by one of the employees that work there. We’ll also want to keep track of when the current manager started as manager.
An employee can act as a supervisor for other employees at the branch, an employee may also act as the supervisor for employees at other branches. An employee can have at most one supervisor.
A branch may handle a number of clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.
Employees can work with clients controlled by their branch to sell them stuff. If nescessary multiple employees can work with the same client. We’ll want to keep track of how many dollars worth of stuff each employee sells to each client they work with.
Many branches will need to work with suppliers to buy inventory. For each supplier we’ll keep track of their name and the type of product they’re selling the branch. A single supplier may supply products to multiple branches.
Example of practical database
Leave a comment