SQL - Structured Query Language
ANSI
Data- Raw facts
process- read/fetch/query - SQL
Information - processed data
SQL -
DQL - Data Query Language
- SELECT (from
where
join
group by
having
order by)
DDL - Data Definition Language
- CREATE
- ALTER
-ADD
-MODIFY
-DROP
-RENAME
-SET UNUSED
- DROP
- TRUNCATE
- RENAME
- COMMENT
DML - Data Manipulation Language
- INSERT
- UPDATE
- DELETE
- MERGE
TCL - Transaction Control Language
- COMMIT
- ROLLBACK
DCL - Data Control Language
- GRANT
- REVOKE
Databases - keeping all the data in an organised format
TABLES
columns - attributes/properties
rows- records/tuples
DBMS (Database Management System)- software/system to manage the databases
Reduced Redundancy -
Eliminiated Inconsistency -
Ease of Accessing Data-
Authoriation of Access-
Concurrent Access-
Backup and Recovery -
Characterisctics of DBMS
ACID
- Atomicity
- Consistency
- Isolation
- Durability
Advantages of DBMS
- reducing redundancy
- organised data( ease of access on the database)
- security (authorization, backuDps and recoveries)
- parallel access(sharing)
- speed
- elimination of inconsistencies
Disadvantage
- Cost on h/w and s/w
- size
- complexity
- upgradation
Architecture
1 tier - same machine server and client
2 tier - client server
3 tier - client application server database server
n tier -
Schema
external schema- application
conceptual schema -logics and conceptual mapping
internal schema - database
hierachical DBMS
network DBMS
ER Model
RDBMS
ORDBMS
NOSQL
unique - one of its own kind
null - blank or empty - unavailable/unknown/not applicable
not null - mandatory
Primary Key - unique + not null
Alternate/Secondary key - unique + not null
Referential/ Foreign Key -
Carditnality - no of times enity will participate in a relationship
1 to 1 relationship
1 to many relationship
many to 1 relationship
many to many relationship
Degree - participation of the entity
unary relationship - only one entity is particpating
binary relationship - two enities are particpating
n -ary relationship - more than two enities are participating
articipation
total participation - mandatory participation (not null) ======= total
participation
partial participation - optional participation (null) ---------- partial
participation
relationship
weak
strong - single rhombus
weak - double rhombus
CONVERT ER DIAGRAMS TO TABLES
===============================
entity - > table
all single valued attributes -> columns
key value -> primary key column
composite attibute --> merge into single column/ convert into seprate table/
separate columns
mutlivalued -> separate tables
derived attribute --> will not be part of table .. can be expressions in
programming
ER diagram of Bank has the following description :
Bank have Customer.
Banks are identified by a name, code, address of main office.
Banks have branches.
Branches are identified by a branch_no., branch_name, address.
Customers are identified by name, cust-id, phone number, address.
Customer can have one or more accounts.
Accounts are identified by account_no., acc_type, balance.
Customer can avail loans.
Loans are identified by loan_id, loan_type and amount.
Account and loans are related to bank’s branch.
NORMALIZATION
1NF - if contain multi valued columns then make them atomic
empid empname phoneno city
001 manish 43455252, 2423253 chennai
002 harry 5345345 chandigarh
003 amit 23423423,21313344 delhi
empid empname phoneno city
001 manish 43455252 chennai
001 manish 2423253 chennai
002 harry 5345345 chandigarh
003 amit 23423423 delhi
003 amit 21313344 delhi
sno sname subjects stream
001 ABC english,maths,chem,bio,it science
002 BHT english,maths,chem,bio,eco science
003 GFDG english,maths,chem,bio,phy science
sno sname subject stream
001 ABC english science
001 ABC maths science
001 ABC chem science
001 ABC bio science
001 ABC it science
002 BHT english science
002 BHT maths science
002 BHT chem science
002 BHT bio science
002 BHT eco science
003 GFDG english science
003 GFDG maths science
003 GFDG chem science
003 GFDG bio science
003 GFDG phy science
2NF - table has to be in 1 NF
all non key attibute should be full functionally dependent on PK
lect_id lecturer_name subject lecturer_age
1001 ANITA IT 38
1001 ANITA PYTHON 38
1002 Dr. Sinha CHem 45
1003 MOHIT PHYsics 35
1003 MOHIT DYNAMICS 35
lect_id lecturer_name lecturer_age
1001 ANITA 38
1002 Dr. Sinha 45
1003 MOHIT 35
lect_id subject
1001 IT
1001 PYTHON
1002 CHem
1003 PHYsics
1003 DYNAMICS
3NF - table has to 2NF
if there is no transitive dependency on PK ,then put it in the different
table
emp_id emp_name emp_state emp_city
101 a delhi delhi
102 b haryana ambala
103 c telangana hyderabad
104 d delhi delhi
105 e delhi delhi
emp_id emp_name emp_state
101 a delhi
102 b haryana
103 c telangana
104 d delhi
105 e delhi
emp_state emp_city
delhi delhi
haryana ambala
telangana hyderabad
