DBMS

Página 1

Normalization - used to reduce data reduncdancy (duplication) and improve data integrity

1 NF - single value attributes should be there, Eg. if 2 phone numbers in 1 cell (123,456) , convert to 2 diff rows

composite key - if to identify a row, we need more than 1 column, we combine primary key and other key to form a composite key

prime_attribute - columns part of composite_key

Sumary

2nf - part of primary key can derive non_prime attribute

3nf - non_prime attribute can derive non_prime

bcnf - non_prime can derive prime attr

2 NF - (must be 1NF , No partial dependecy , table must have compoiste key for 2NF to occur). Let composute key columns be AB

if column C is only dependent on one of the composite key columns, Eg (C is only depenedent on B) , no need to use C there. Make another table which has column B and C.

Eg. student_id(primary), subject(candidate), student_name, marks, teacher_id

Here 1 student_id can have multiple subjects , so student_id is not enough. Composite key will be (student_id, subject)

since teacher_id is only dependent on subjecct, we can remove it and move to another table

3 NF - (must be 1NF and 2 NF, No transitive dependicy, Occurs if you can guess one column value based on some other column value)

If col B is dependent on A and column C is depnedt on B, no need to put C there, put B and C in sepearet table

Eg. student_id(primary), student_name, exam_type ( can be viva, tgeory etc), exam_marks(viva-20, theory 100)

exam_marks dependent only on exam_type , so no need to put here

https://www.youtube.com/watch?v=NNjUhvvwOrk 5 min

https://www.youtube.com/watch?v=xPzgK6sOCfg 8 min

https://www.youtube.com/watch?v=ABwD8IYByfk 12 min

Página 2

SQL vs MONGO

FORIGN KEY - NO FOREIGN KEY

VERTICAL SCLABLE ONLY AS ADDING NEW COLUMN IS DIFFICULT - VERTICL AND HRIZNTL SCALABLE AS NEW FIELDS(cols) can be add in a document(row)

relational data - write performance is better

useful for structured schema data or relational data - used for schema free data or non realtional data

has tables - has colections

REDIS

cache memory for fast access of data

10 servers access sql for data (qry takes 15sec)

we can check redis server for query data, if not exist redis queries to sql for data and stores it in cache.

next time other server queires for tht data, first checki n redis, (cache hit or miss) if hi reurn data instanlty else query on sql and repopulate cache

Página 3

JOIN

Inner join - only matching rows from both tbl

left outer join - all from tbl1, match from tbl2 else null

right outer - similar

full outer - all from tbl1 and tbl2, if match show in same row, if no match found, new row and null values for other tbl columns

equijoin - innerjoin w used with equality (i think maybe same as inner)

natural join - same as equi but here, matching column will be shown only once nstead of twice from both tables

Default join is inner join

Select colA1, colA2, colB1

from A

JOIN B on A.colA3 == B.colB2

JOIN C on A.colA4 == C.colC1

WHERE C.colC2 < 100; (or hwatever condiiton)

not equal operator - <>

LIMIT n - to show only top n rows

BETWEEN 100 and 200 - including both

Página 4

Sibqueries

Independent Nested Queries: In independent nested queries, query execution starts from innermost query to outermost queries. The execution of inner query is independent of outer query, but the result of inner query is used in execution of outer query. Various operators like IN, NOT IN, ANY, ALL etc are used in writing independent nested queries.

IN: If we want to find out S_ID who are enrolled in C_NAME ‘DSA’ or ‘DBMS’, we can write it with the help of independent nested query and IN operator. From COURSE table, we can find out C_ID for C_NAME ‘DSA’ or DBMS’ and we can use these C_IDs for finding S_IDs from STUDENT_COURSE TABLE.

STEP 1: Finding C_ID for C_NAME =’DSA’ or ‘DBMS’

Select C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME = ‘DBMS’

STEP 2: Using C_ID of step 1 for finding S_ID

Select S_ID from STUDENT_COURSE where C_ID IN

(SELECT C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME=’DBMS’);

Co-related Nested Queries: In co-related nested queries, the output of inner query depends on the row which is being currently executed in outer query. e.g.; If we want to find out S_NAME of STUDENTs who are enrolled in C_ID ‘C1’, it can be done with the help of co-related nested query as:

Select S_NAME from STUDENT S where EXISTS

( select * from STUDENT_COURSE SC where S.S_ID=SC.S_ID and SC.C_ID=’C1’);

For each row of STUDENT S, it will find the rows from STUDENT_COURSE where S.S_ID = SC.S_ID and SC.C_ID=’C1’. If for a S_ID from STUDENT S, atleast a row exists in STUDENT_COURSE SC with C_ID=’C1’, then inner query will return true and corresponding S_ID will be returned as output.

Página 5