Database : Understanding Database

1. Database à Tables à Rows & Columns
2. Rows à Tuples
3. Columns àattributes
4. Tables have Constraints,Indexes,Defaults,Customized User data types
5. Declarative referential integrity (DRI) constraints can be added to the tables to make sure that interrelated data in different tables remains consistent.
6. Tables can have indexes similar to those in books that enable rows to be found quickly.
7. Database can have Tables,Stored Procedures,Triggers,Functions,Views.
8. For example, you create a database named MyCompanyDB to manage the data in your company. In the MyCompanyDB database, you create a table that is named Employees to store information about each employee. The table also contains columns that are named EmpId, LastName, FirstName, Dept, and Title. To make sure that no two employees share the same EmpId and that the Dept column contains only valid numbers for the departments in your company, you must add constraints to the table.Because you want to quickly find the data for an employee, based on the employee ID or last name, you define indexes. You will have to add a row of data to theEmployees table for each employee, so you have to also create a stored procedure named AddEmployee. This procedure is customized to accept the data values for a new employee and perform the operation of adding the row to the Employees table. You may need a departmental summary of employees. In this case, you define a view called DeptEmps that combines data from the Departments and Employees tables and produces the output. 
9. An instance of SQL Server can support many databases. Each database can store either interrelated or unrelated data from other databases.
10. You should not create any user objects, such as tables, views, stored procedures, or triggers, in the master database. The master database contains system-level information used by the instance of SQL Server, such as logon information and configuration option settings.
11. System Databases: Master database,Msdb database,Model database,Resource database,Tempdb database
12. Master database:The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.
13. Msdb database:The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.
14. Model database:The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.
15. Resource database:The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such assys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of theResource database with the older version.
16. Tempdb database:The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.Internal objects that are created by the SQL Server 2005 Database Engine, for example, work tables to store intermediate results for spools or sorting. Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
17. SQL Server does not support users directly updating the information in system objects such as system tables, system stored procedures, and catalog views. Microsoft does not support triggers defined on the system tables, because they might modify the operation of the system.
18. You should not code Transact-SQL statements that directly query the system tables, unless that is the only way to obtain the information that is required by the application. Instead, applications should obtain catalog and system information by using :System catalog views,SQL-SMO ,Windows Management Instrumentation (WMI) interface ,Catalog functions, methods, attributes, or properties of the data API used in the application, such as ADO, OLE DB, or ODBC. 

19. Every SQL Server 2005 database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. 

20. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.

21. The transaction log supports the following operations:

• Recovery of individual transactions.
• Recovery of all incomplete transactions when SQL Server is started.
• Rolling a restored database, file, filegroup, or page forward to the point of failure. 
• Supporting transactional replication.
• Supporting standby-server solutions.

22. The following table defines the database states.