I was trying to learn the basic database concepts and noted the following.
-
Relational database (one of the database model) – Data is stored in tables
- Table row is tuple and column is attribute
- Key components – Entity, Attribute and Keys, Relationship
- Entity – Place, object, ppl
- Non-key attributes describe an entity while key uniquely identifies an entity
- Relationship – defines the association between attributes. Can be 1 to 1, 1 to many, many to many
-
Types of databases -
- OLTP (Online Transaction Processing) – data is stored for quick CUD operation, redundant data minimized
- OLAP (Online Analytical Processing) – data stored in multi-dimensional views, optimized for reading data
- Primary key, foreign key relationship – ensures data integrity
- Normalization – Process of organizing data and table in database, done by creating table and relationship, reduces data redundancy
-
Degree of normalization
- No Normal Form – single table contain all the info, repetition of data
- 1st NF – remove repeating group
- 2nd NF -
- 3rd NF -
-
Other SQL Server objects
- Constraints – rules to maintain DB integrity like not null, unique
-
Indexes – structure in DB to find and access data quickly. Two types -
- Clustered – changes the way data is stored on the hard disk, quick read but insertion is time taking
- Non-clustered – no physical rearrange of data, pointer is created for where the data is physically stored
- Stored Procs
- Functions
- Views
-
Trigger – Code that is run when insert, update or delete happens on a table, have performance impact
- Insert triggers
- Instead Of
For those who think this is more of study notes than a blog, I used Office OneNote 2007 for taking notes and have directly published it to my blog. The best part is I don’t have to write complete sentencesJ.
