Before we dive into MySQL topics, I want to give you little bit background on what RDBMS exactly means and some of the important concepts related to it.
RDBMS stands for relational database management system. Data is stored in the tables.
Each table has specific number of columns and a set of records. We can retrieve, add, remove, update or delete the records from single or multiple tables using a language called as SQL – Structured Query Language.
Popular relational database management systems
- IBM DB2
- IBM Informix
- Microsoft SQL Server
Some key concepts to know are mentioned below.
Primary Key – This is the name of the column in a table that helps us identify the unique record in a table. Primary key does not allow null, unique
Foreign Key – This is the name of the column from a table that points to the primary key in another table.
Unique key – data in column can not be duplicated
Referential Integrity – When we have a foreign key in a table and if we try to delete the record pointed by the foreign key in another table, we would not be allowed to do so. This is called as referential integrity.
Candidate key – Minimal set of keys that identify each record uniquely in the table
Non prime attribute – Column that is not a part of candidate key
Find columns that can uniquely identify the records in a table.
(C1,C3) (C2,C3) (C1,C2,C3)
Then find the those sets that do not have proper subset. These are the candidate keys in a table.
(C1,C2,C3) has a proper subset. So we will not consider it as a candidate key.
Codd, researcher in RDBMS, had proposed normalization rules for a DBMS.
Normalization helps in data integrity and reduces data redundancy.
List of normalized forms
A table is in 1NF if it stores atomic (indivisible) values for each attribute (column) in the table and each row in the table is unique.
For example in an employee table(with three columns – empid, name, skills), we can have a column with name skills. In that column, we can store multiple skills
for each employee(tuple). Data stored in skills column is not atomic, this table does not meet 1NF.
To comply with 1NF, we will have to store the skill values seperately in each row. This will involve creating multiple
records for the same employee. This will convert the table in 1NF.
A table is in 2NF if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key.
In our employee table, name attribute is a non-prime attribute. But is not dependent on the whole of candidate key(empid and skills columns).
It is dependent upon the empid (subset of candidate key). So this table is not in 2NF.
To get the database in 2NF, we need to split this table in 2 tables as shown below
A table is in 3NF if The table is in 2NF and each non-prime attribute (column) of table is non-transitively dependent on every key of R.
Consider below table. Here (product name + product version) are candidate keys. Non-prime attribute ManufacturerMainOffice is transitively dependent on candidate key. So this table is not in 3NF.
To make the table 3NF compliant, we will need to split it in 2 tables as shown below.
A table is considered as normalized if it meets 3NF.
If the table is not normalized, it can lead to update and delete anomaliesER Diagrams are used to design the database schema.
Entity Relationship Model
Each entity is like a table. Each entity has attributes. Attributes can be considered as columns in the database. There are relationships between entities. Relationships between entities can be established using foreign key concept.
In MySQL, we have 3 types of relationships. We can use ER diagrams to show the relationships between different entities.
- one to one – In this relationship, for one record in one table, we have only one record in another table. example – A person holding a passport.
- one to many – In this relationship, for one record in one table, we can have multiple records in another table. example – A customer can have multiple orders
- Many to Many – In this relationship, 3 tables are involved. For one record in one table, we can have multiple records in second table. Also for one record in third table, we can have multiple records in second table. example – 1 or more authors can write a multiple books. To implement the many to many relationship, you should add 3rd table to create the relationship.
All of the above concepts can be better understood with the help of example.
In all our lessons, we would be working with below tables. There is no standard convention to define the table or column names But having consistent naming convention is always better. So we would be using Table names in Capital Letters. Column names would follow PascalCase naming conventions.
A CUSTOMER table has below mentioned columns.
- CustomerId – Primary Key
ADVERTS – This is a table containing all advertisements posted by the customers.
- AdId – Primary Key
- CustomerId – Foreign Key pointing to customer record. If we enforce referential integrity, we would not be able to delete the record from the customer table where specific customer has put an advertisement. For each ad, we would be able to find the details of the customer who put that advertisement. Without referential integrity, we could have scenario where there is a record in ADVERTS table but there is no record of the customer in CUSTOMER table who put that ad.
CATEGORY – This is a table containing all categories and sub categories for the advertisements.
- CatId – Primary Key