Keys

 Keys

Candidate Key:

Minimum super key is known as candidate key. As we need the minimum combination of columns or even if we have a single columns as unique identifier then that candidate column/columns can be considered as candidate key.


example: If we have s_id and c_id and attendence as columns of a table where combination of s_id and c_id can identify a record but they can't identify the record independently then.
s_id and c_id combination is candidate key as they are sufficient to identify the record.

So combination of s_id, c_id and attendence can be a super key but it cannot be a candidate key as without attendence in a combination we can identify the record, so to identify a record s_id and c_id need to work together and they cannot be replacable.



Primary Key:

A column which can identify record uniquely is known as Primary Key, among candidate keys we need to choose only on column which can identify the record or we can say that minimum candidate key can be a primary key.

We usually create primary key and never select existing column from the table as it should be used for lots of work.

Properties:

  • Used to sort table records.
  • Output of any query can be sorted by using primary key efficiently.
  • Indexing for a table is done on primary key.

Criteria for a good Primary Key:

  • Be fast for sorting like UUID
  • Have smaller size (as used for indexing so must comsume minimum space)
  • Should not be changed.

Composite Keys:

  • Combination of columns to identify the record uniquely.
  • SK, CK and PK are considered as composite key.

SQL:

  • Structured Query Language
  • Used to interact with databases.
  • It allows to create tables, fetch data from them, update data, manage user permissions
  • It helps us to do CRUD on table and thier records.
    • Create
    • Read
    • Update
    • Delete
So database is resource and RDBMS is a management system to manage the resources and SQL is the language which will enable to interact with database through desired RDBMS.

So inshort RDBMS will ask SQL to work on database.

SQL is a case insensitive.

Create:

Used to create database and table as well.

Syntax:
  • create database <database_name>
  • create table <table_name> ( column_name datatype constraint)

example:
create database school;

create table student(
s_id int primary key auto_increment,
first_name varchar(100) not null, -> varchar makes space efficient as it limit means larges value which it can attain not like char which will assume that all space will be filled and hence will waste the space.
last_name varchar(50)
);

Foreign Keys:

Foriegn key is the column which will help to have relationship between two tables.

Properties:

  • Need to be unique as will be used to identify the records uniquely.
  • As if we will remove the record from either of the tables hence it will cause data inconsistancy issue as in one table a value will be referred but will not be able to fetch it as it won't exist.
  • To take care of it we need to take care of deletion and updation operations on tables.
    • Use cascade keyword so that if record is removed from the base table where same foreign key is primary key then if we remove the record from base table then automatically record will get removed from the table which is using it as foreign key to fetch information from base table.
    • Use set null - It will give option in place of removing data from the fact table (foreign table which is having the column as foreign key) to attain NULL as value to represent that record from base table is removed.
    • No action - Do not take any action and allow to have orphan record. By default happens in MySQL.
    • Set default - it will allow to assign value to the foreign key is base table record is deleted.

So if you have cascade effect then you cannot delete any record from foreign key table until you delete the foreign key information from base table which.

example:
if student table is using b_id as foreign key then you cannot delete any record from student table until you remove b_id from batches table. Simialry updating record in base table will show effect in fact table which is using the column as foreign key in it.

Sample commands:

use sakila;

show tables;

select * from film;

show create table film;

describe table film;

-- create database
create database school;

use school;

-- create table 
create table batches(
b_id int primary key auto_increment,
batch_name varchar(50) not null
);

-- insert dummy data to batch table.
insert into batches(b_id, batch_name) values 
(1, 'Batch A'),
(2, 'Batch B'),
(3, 'Batch C' );

select * from batches where batch_name = 'batch c';

-- create a student table
create table students(
s_id int auto_increment primary key,
first_name varchar(50) not null,
last_name varchar(50),
batch_id int,
foreign key (batch_id) references batches(b_id) on delete cascade on update cascade);

insert into school.students (first_name, last_name, batch_id)
values
('john', 'marcos', 1),
('puneet', '', 2);


select * from school.students;

delete from school.students where s_id = 2;

delete from school.batches where b_id = 1;

update batches set b_id = 4 where b_id = 2;

show create table students;

alter table students add constraint fk_student_batch foreign key (batch_id)
references batches(b_id) on delete set null on update set null;

alter table students drop foreign key students_ibfk_1;


select * from students;

select * from batches;

update batches set b_id = 5 where b_id = 4;









Comments

Post a Comment

Popular posts from this blog

Aggregate Queries and Views

 Aggregate Queries and Views Aggregate Functions It provide consolidated result it means work on records and then will provide summary of it or the consolidated result of it. It is basically used to find the facts and figures of table. Means max and other similar type of functions which are consolidate in nature. count - it take count of only not null values and will ignore the null values. max min avg sum notes : Distinct is not an aggregate function as it don't combine the records it just help to remove the duplicate records. All aggregate functions by default work on integers or anything which can be converted into countable information. count: Pseudo code: table = [] count = 0 for row in table:     if row[b_id]:          count++ print(count) Note: You cannot use aggregate functions in nested loops. Group by: Trying to make some groups in a data by using some criteria or conditions. It use the column to form the group and take action on that gr...
Read more

Schema Design

Image
 Schema Design Schema Blue print of out database of to define how are we going to create and use our tables in a database. It gives information about: Structure of Database Tables in Database Columns in a Table Primary Key Foreign Key Index Pictorial Representation How to approach a schema design?  Requirement: Scaler will have multiple batches For each batch, we need to store the name, start month and current instructor Each batch of  Scaler will have multiple students. Each batch has multiple classes. For each class, store the name, date and time, instructor of the class. For evey student, we store their name, graduation year, university name, email, phone no. Every student has a buddy, who is also a student A student may move from one batch to another For each batch a student moves to, the date of starting is stored. Every student has a mentor. For every mentor, we store their name and current company name. Store information about all mentor sessions ( time, duration, ...
Read more

Transactions in SQL

Image
 Transactions Now as you can see that in example A is a person who has 1000 rupees in his account and he want to transfer 500 rupees to B who has account balance as 5000. So now this information is stored in a table. Steps to make is possible programatically: Get account balance of A. - > DB call. Check, if A >= amount. - > logical call not a DB call. Debit Rs. 500 from A and updte table. - > DB call Update the balance of B and update the table. - > DB call These 4 steps mechanism complete 1 transaction between two people. Take a scenario: Multiple person are trying to send amount to same accont. Like how it happens at the time of shopping when multiple person comes to purchase goods from a shop then they pay money from their account to shop keeper account. Now suppose a developer has created a method for transaction to transfer amount from one account to another. Scenario: Multiple person are making transaction and transfering amount to same person say is as shopkee...
Read more