What is Database Architecture ?

Each database will be divided into two types of implementations
•Logical implementation
Tables, views, procedures ..etc
•Physical implementation
.LDF, .MDF and NDF files

When using a database, you work primarily with the logical components such as tables, views, procedures ..etc The physical implementation of files is largely transparent. Typically, only the database administrator needs to work with the physical implementation.

What is Physical Data Base ?

Physical Db will be divided in to 3 types:
1) .MDF 2) .LDF 3) .NDF

MDF is a file extension associated with Microsoft SQL Server primary data file.
LDF is a file extension associated with SQL Server Secondary Transaction log file.
NDF is a file extension associated with SQL Server Secondary Data file.
.MDF & .NDF having DB related data
.LDF having logs related data

On Physical Database contains File Groups and Transaction log
In File Group Contains Data Files
Data File contains Tables
Tables contains Extents
Extents contains Pages
pages Contains Rows

Transaction log is User defined file group by default it's

Logical - database objects like view,Sp,functions,Triggers,Indexes and roles etc..

What is the role of msdb database?

The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

What is the role of master database?

The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. master records the initialization information for SQL Server; always have a recent backup of master available.

What are the benefits of Normalization?

Some of the benefits of normalization include:
• Faster sorting and index creation.
• Narrower and more compact indexes.
• Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements.
Fewer null values and less opportunity for inconsistency, which increase database compactness.

What are the disadvantages of Normalization?

As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance.