DBMS basic knowledge information

Competition system - when many users are using the DB (like e-mail) / 
also working with enormous volumes of information

RDBMS -> store data in tables / using relational algebra /
also known as database server / all rows are with same size (e.g 1MB)

table scheme:
primary key - entity - foreign key

relationship multiplicity:
1.one-to-many (country-towns)
parent table - child table
used to create hierarchy 
2.many-to-many (student-courses)
implemented through additional table
Students -> StudentsCourses <- Courses
3.one-to-one (human-student) / inheritance in DB
primary key is also foreign key(at the same time) in the child table,
the child table primary key uses the parent table primary key
 
DB integrity - can not delete 'country' table if there is there are
'town' entries 

DB normalization - avoiding repeating data 
the repeating data is put in new table 
sometimes de-normalizations is being done due to performance
1st normal form -> data is stored in tables / fields in the rows are
atomic (inseparable) values / no repetition in a single row /
primary key for each table
2nd -> all 1st / no columns that depend on part of primary key
3rd -> all 2nd / dependences between columns are only 'depend on primary key'
4th -> all 3rd / most one column in each table with multi-valued attribute

indices speed up search in certain column / group of columns
usually implemented as B-trees ,but slows down changing values 
used mostly in big tables (more than 50,000 records)

Store procedures -> extend SQL when need to do things local

View -> created mostly by security reasons

Triggers -> special store procedures activated when some event occurs

Transactions -> protect DB from crash
sequence of DB operations that are executed as a single unit

Not Only SQL DB -> more flexible 

procedure vs function -> func return result
trugger -> store procedure that is called in certain conditions

NO ARRAYS -> to create use temporary table starting with # 

3 types of functions :
- aggregate -> except many parameters and returns scalar result (e.g. 0)
- scalar -> except single/no value and returns some number
- rowset -> except single/no parameters and returns table

EXEC Procedure_Name 
SELECT Function_Name()

In TRIGGERS there are two system tables: (like UPDATE)
- inserted(for the new data) 
- deleted(for the old data))

AVOID SQL INJECTION ->
if in the user field we put ' and press Login - if the server responds (not valid pass)
internal server error => sql injection!
most common styntax -> ' or 1=1 -- (it always returns true)

Solution -> SQL Parameters; not as string concat, but as variable value!
e.g. cmd.Parameters.AddWithvalue(@name, name);

Performance problems:
1. N+1 Query (almost in ADO.Net)
If we have 1 query to retrieve data, hidden are made additional queries to get
data from other related tables.
e.g. foreach(var customer in context.Products)
{
 Console.WriteLine("Product: {0} {1} {2}", 
 product.ProductName, product.Supplier.CompanyName,
 product.Category.CategoryName);
}

solution => foreach(var customer in context.Products.Include("Supplier")
          .Include("Category"))


2. Incorrect use of ToList()
Invoke as late as possible, after all filtering, grouping and joins

3. Incorrect use of SELECT * (NOT ALWAYS HARMFUL because of the data cash/cache)
If we need only the entity.Name, we must not select everything in the Entity
e.g.
DataBase db = new Database();
 foreach(var category in db.Categories)
{
 ConsoleWriteLine(category.CategoryName) 
}
 solution -> var query =
  from cat in db.Categories,
  select cat.CategoryName;
 foreach(var name in query)
{
 ConsoleWriteLine(name) 
}

4. Deleting objects faster with native SQL
In Entity FrameWork we first read the data and then delete it !
solution ->
 bd.Database.ExecuteSqlCommand(
 "DELETE FROM Categories WHERE CategoryId = {0}", 47);

Projection -> causes useless traffic (e.g. for just contact.Name we get all contact data)
solution => anonymous types e.g. ...Select(x => new {ContactName}); 

DO NOT USE break in foreach, it will leave the connection open !

Entity Framework three types of modeling workflow:
1. Database first -> create models as database tables using native SQL / MS MStudio
2. Model first -> create models via visual EF designer in VS
3. Code first -> Write models and combine them in DbContext