SQL Training - 03

Identity Field:

• Indexed 
• No duplicate 
• Auto Increment 
• sorted
• Indexed 

Side Effect of Identity Field:

• Values cannot be changed 
• Gaps 

Normalization:

• we need it to prevent redundancy (Place are repeated)
• To avoid repeated columns (Address, 2 , 3)
• To avoid repeated rows 
• Referential integrity (Relations to make full record)
• Unity: each table must contains all related data
• Atomic: each cell must be one value 
• Dependency
• To prevent orphan rows (customer without address or addresses without customers)
• No Transitive 

- Use begin transaction and end transaction to execute more-than one sql statement 

- In One to many relations; you add in many table and after that you add in one table.

- In Addresses table; you can add email1, email2, phone1, phone2, telephone1, telephone2, fax1, fax2

SW Engineering:

• Gathering info
• Structured 
• OO
• Noun: classes
• Verbs: functions
• Each property in class is a field in DB
• Structred
• Verbs only 
• UML classes
• Class contains 
• Class Members
• Properties (Name, Age)
• Methods  (Add, Delete)
• Events(WhenDeleted, WhenAdded)

PrimMinister, Manager, Employee, Driver, Heads of Units, Drivers, Cleaners are Persons 

so the initial table is Person, 

SQL Training - 02

SQL Server Features:

• BI: Business Inelegance; Which means Transforming Data to Knowledge  

to help decision maker to make right decisions

• DB Engine: Takes care of exciting transact SQL statements,
•  security(Data is the most Valuable Asset, 
• by default it encrypt the data and you can encrypt it by key), Audit(Logging every transaction), Roles and Authentications(users)
• indexing

it contains Transact SQL (T-SQL) programming Interface -- Reaching Data anytime anywhere--  , Handel errors

How? using every techniques to access data 

SQL Server 2012 : T-SQL is a set bases more efficient  (One loop to retrieve data)

T-SQL was before 2012 T-SQL is Cursor base (Slow, not Up-todate, more IO )

• Replication: Distributing data to different locations using FTP(Push and Pull), Publisher(Master Database) and Subscriber
• SQL Agent: 
• Scheduling Tasks and Jobs
• Rebuilding Index
• Backups
• Alert Fail 
• High Availability (H.A): through 
• Mirroring(Server 1 and Server 2 and Witness)
• always on (replications)
• fail over clustering (stand by)
• Log shipping: Distributing log files on replicas 

** ADO.Net library it contains everything to access database (Offline and Classified)

During installation set the Default Collation to Arabic_100

to overcome on hamza you have to use Arabic collation 

------------
Backups 3 Models :

• Simple: no log backups so you can't recover the data at point of time, , 
• Full Model: no lose of data, you can restore to any point of time
• Bulk-logged Model: you can restore only to the end of any backup 

SQL Server Browser:

• List all available servers
• Connect to any available server 

Datatypes:

• Use the specific datatype for fields
• Use specific length for varchar, char
• The datatype varchar(max) not indexable 
• varchar reserves the needed space and not the value which you supplied but the char reserves the value you supplied, for example :varchar(100) and you have entered 10 digits, only 10 digits will reserved from the space, char(100) it reserves 100 digits even you didn't added any values 
• Use date when you don't need the time, use YYYY-MM-DD or DD-MM-YYYY

Why default values are recommended ??

to avoid Nulls, when binding values to web controls the null makes a problem


Table Properties:

• Name
• Schema 
• Constraints
• File Group 
• Number of Fields

Field Properties:

• Name
• Datatype
• Idexing
• Null-able 

Why to use allow null and set default value?

it allows the data-insert to insert null for some reason 

DefaultConnection in new Project in ASP using templates

after you have created the project you have to create new user account, after that you will see the .mdf and .ldf files in App_Data folder

SQL Training - 01

Schema: is a description of the data 

schema is SQL is a group of tables 

you can create a user and give him a schema; this user can't see any other tables from sachems

SQL Server Databases:

1. Master: contains meta of all dbs 
2. Temp:
3. Model: Like polymorphism (Create tables (Like Lookups), views, stored procedures,... to be inherited in and newly created database)
4. Ms: It contains Jobs, Alerts,
5. Resource
6. Distribution 

You mustn't make any changes in Master and MS databases

Default Collations for Arabic:

Use Arabic_100_CI/CS_AS_KS_WS after that you don't need to use nvarchar as datatypes you can use varchar to store arabic values 

* You can create multi-files(.nDF) to store tables, each table could be stored in different group, for example create A1.nDF, A2.nDF files as group, and put table T1 in group 1

* Not recommend to use Shrink in database but still you can use it. 

Views:

• Lite
• Executed
• Ready to read
• High performance and low time to execute
• Separated Privileges
• Denormalization 
•  No parsing needed after created