Introduction to SQL
1. Relationships in SQL
There are five types of relations in the databases: one-to-one, one-to-many, many-to-one, many-to-many, and self-referencing relationships. So, what’s the difference between these database relationship types? In the article, we’ll focus on each type separately.
A relationship between two database tables presuppose that one of them has a foreign key that references the primary key of another table.
Database entity — strictly speaking — is a person, place, thing, object, or any item about which data is stored in the database.
One to One (User-ID number)
A one-to-one relationship in a database occurs when each row in “table x” has only one related row in “table y”.
Example :
In the database, we want to store employees, but also their identity cards. We’re not interested in storing any other identity cards that were previously valid, so we need exactly 1 (or none) identity card for 1 employee.
We’ve been given these rules: One employee could have only one valid identity card in our system. One identity card could belong to only one employee. We haven’t used the word “many”, so this can’t be any type of relation including the word “many”.
It is used to create a relationship between two tables in which a single row of the first table can only be related to one and only one records of a second table. Similarly, the row of a second table can also be related to anyone row of the first table.
One to Many (Class-Student)
It is the most commonly used type. A “one-to-many” relationship occurs when one record in table 1 is related to one or more records in table 2.
Example :
Imagine that we want to store a list of all our customers in the database. For each customer, we also want to store the city where this customer is located, and we know that the customer will be in exactly one city.
When you want to determine the nature of the relation you need to establish between two tables just do this. In our example — For one city, we could have many different customers located in it. And the other way around — For one customer, we can have only one city it’s located in.
So, how to choose between these 3 different types of relations? If you said the word “many” only once, then this is one-to-many relation. If you would use the word “many” two times, the relation would be many-to-many. And if you wouldn’t use it at all, then it would be one-to-one.
Many to Many(Books Authors)
A many-to-many relationship occurs when multiple records in one table are related to multiple records in another table. This type is used when both tables could have multiple rows on the other side.
Example :
We need to store calls between employees and customers.
One employee, during the time, could call many customers. Also, one customer, during the time, could receive calls from many employees.
Notice that we’ve mentioned the word “many” two times. This is the signal we need to resolve this using many-to-many relation (out of 3 types of relations we have on disposal).
Add a table between tables employee and customer.
Add foreign keys (employee_id & customer_id) to that new table (call).
Now, when we look from the employee perspective, one employee could make many (multiple) calls. On the other hand, one customer could be related to many (multiple) calls. Therefore, many-to-many relation is implemented with adding a new table and one-to-many relations from both sides.
2. SQL Server Data Types
String
CHAR(Size) = It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1.
VARCHAR(Size) = It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters.
BINARY(Size) = It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1.
VARBINARY(Size)It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes.
TEXT(Size) = It holds a string that can contain a maximum length of 255 characters.
TINYTEXT = It holds a string with a maximum length of 255 characters.
MEDIUMTEXT = It holds a string with a maximum length of 16,777,215.
LONGTEXT = It holds a string with a maximum length of 4,294,967,295 characters.
ENUM(val1, val2, val3,…) = It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted.
Get Baris Gül’s stories in your inbox
Join Medium for free to get updates from this writer.
SET( val1,val2,val3,….) = It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list.
BLOB(size) = It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes.
Numeric
BIT(Size) = It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1.
INT(size) = It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255.
INTEGER(size) = It is equal to INT(size).
FLOAT(size, d) = It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter.
FLOAT(p) = It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, d) = It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter.
DECIMAL(size, d) = It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0.
DEC(size, d) = It is equal to DECIMAL(size, d).
BOOL = It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.
Date and Time Data Types
DATE = It is used to specify date format YYYY-MM-DD. Its supported range is from ‘1000–01–01’ to ‘9999–12–31’.
DATETIME(fsp) = It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1000–01–01 00:00:00’ to 9999–12–31 23:59:59'.
TIMESTAMP(fsp) = It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch(‘1970–01–01 00:00:00’ UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1970–01–01 00:00:01’ UTC to ‘2038–01–09 03:14:07’ UTC.
TIME(fsp) = It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from ‘-838:59:59’ to ‘838:59:59’.
YEAR = It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000.
3. Constraints
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
The following constraints are commonly used in SQL:
NOT NULL = Ensures that a column cannot have a NULL value.
UNIQUE = Ensures that all values in a column are different.
PRIMARY KEY = A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
FOREIGN KEY = Prevents actions that would destroy links between tables.
CHECK = Ensures that the values in a column satisfies a specific condition.
DEFAULT = Sets a default value for a column if no value is specified.
CREATE INDEX = Used to create and retrieve data from the database very quickly.
4. Normalization in SQL
1st Normal Form (1NF)
- There must be a primary key in each entity table.
- A table must not contain more than one domain.
- Each column should hold only one information.
- There should be no repeating columns
Second Normal Form
- For the second normal form, the relation must first be in 1NF.
- If, and only if, PK comprises a single attribute the relation is automatically in 2NF.
- If the relation has a composite PK, then each non-key attribute must be fully dependent on the entire PK and not on a subset of the PK (i.e., there must be no partical dependency or augmentation).
Third Normal Form
- To be in third normal form, the relation must be in second normal form.
- Also all transitive dependencies must be removed; a non-key attribute may not be functionally dependent on another non-key attribute.
