My Blog List

Wednesday, January 18, 2012

RDBMS - A QuickView of Concepts


Relational DataBases:
4 major concepts.
1. keys
2. Relationships
3. Normal Forms i.e. Normalization
4. Integrity rules


>> Keys and Uniqueness
>> Primary Keys
-- Every row in table is unique
-- To achieve this, Primary Key
-- 2 types; Single and Composite
-- Simple -- Only one column will have unique value - Primary key
-- Composite - Combination of more than 1 column will have unique value - These columns are called as candidate keys; Main one is primary key; Others are alternate keys;
>> Foreign Keys
-- Column to be referred from related table to find matched row from a table which have a primary key.


>> Relationships
>> One to One
-- Every record in 1st table must have at most 1 record in 2nd table.
-- Also every record in 2nd table must have atmost 1 record in 1st table.
>> One to many
-- Every record in 1st table will have any number of records(0, 1 or higher) in 2nd table.
-- But every record in 2nd table will have atmost 1 record in 1st table.
>> Many to many
-- Every record in 1st table will have any number of records(0, 1 or higher) in 2nd table.
-- Also every record in 2nd table will have any number of records(0, 1 or higher) in 1st table.
-- Normally designed using multiple One to Many like Customer --> Orders --> OrderPaymentType
-- Every customer will have many orders. Also every OrderPayementType will be used for many Orders.
-- So, Many to Many relationship.


>> Normal Forms
>> 1NF
-- Every column for any row should be atomic. ie. should contain one value not list of values or array of values
-- Should not have repeating groups of columns
>> 2NF
-- 1NF + Non-key columns should be dependent on entire primary key (whatever single or combosite)
-- Should be able to get values of non-key columns when I know entire key columns values not by knowing any key column value.
>> 3NF
-- 2NF + Non-key columns should be independent each other.
>> Boyce/Codd NF
-- Same as 3NF. (Non-key columns should be functionally independent each other)
>> 4NF
>> 5NF


>> Integrity Rules
>> General Rules
>> Entity Integrity
-- Entire Primary key columns should not be null.
>> Referential Integrity
-- Should not allow to add record in 2nd table(Orders) without having record in 1st table(Customers)
-- Should not allow to orphan the record in 2nd table when I deleted/updated the record in 1st table. 3 ways to restrict.
1. DisAllow - Should not allow to delete record in 1st table if referred records there in 2nd table.
2. Cascade - Should delete/update the referred records also. ie. both tables should affect.
3. Nullify - Set null to foreign keys in 2nd table.
>> DataBase Specific Rules or Business Rules
-- Applicable only for particular database.
-- Used to avaoid wrong entries and garbages.
-- Like, CustomerOrderDate should be between Today's date to 10 days.
-- Like validation rules for field values.


Hope this will give some quick view of Relational DataBase Management systems...

No comments:

Post a Comment