RDBMS

 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, ...

 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...

 Indexing Introduction to Indexing: TC for below queries: select * from students; O(n) select * from students where id = 100; O(n) without any indexing. Data get stored in disks. select * from students where id = 500;  This data will be stored in memory over some location in some blocks. In this case we need to search all the blocks and when data is found then we will send it to CPU through RAM. If we anyway know the memory location for data then we don't need to search it and don't need to read all blocks of disk as reading disk is very time consuming task. Latency Comparison Numbers (~2012) ---------------------------------- L1 cache reference 0.5 ns Branch mispredict 5 ns L2 cache reference 7 ns 14x L1 cache Mutex lock/unlock 25 ns Main memory reference 100 ns 20x L2 cache, 200x L1 cache Compress 1K b...

 Subqueries Subqueries This is very intuitive way of writing queries. Breakdown bigger problems into smaller ones. Will use result of smaller problems to get final answer. Process of working on it. Suppose we have a question to answer: Get students detail who have psp greater than student with id as 1. So to solve it steps are: 1. Get student2 psp     select psp from students where id = 1; 2. Now use it to generate the other details.     select * from students where psp > (select psp from students where id = 1);  Time complexity of this query is O(N2) as for each row of data for main query inner subquery will run on the table all records and then the result extracted from the inner query will be used for comparison. Subqueries are costlier then joints with respect to resources usage and even in time complexity. use school; show tables; create table students( id int not null primary key auto_increment, name varchar(500), b_id int , psp int, CONSTRAINT fk_...

 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...