Introduction to Databases

1. Introduction to Databases • A DB is a collection of tables where we store the Data about a Business, and/or some other objects such as Views, Stored Procedures, Triggers, and User Defined Functions, etc.,.

2. Files to store a Database • SQL Server maps a Database using a set of Operating System files. • All Data and Objects in the Database are stored within the Operating System files.

3. Primary Data File • This file contains the startup information of the database and is used store the data. • Every Database has “ONE” data file. • The extension of this data file is .mdf

4. Secondary Data File • These files are used to store datathat doesn’t fit into the primary data file. • This is an optional file. • The extension of the secondary data file is .ndf

5. Transaction Log File • This file stores the log information used to recover the Database. • Every database has “ONE” log file. • The extension for transaction log file is .ldf

6. Types of Databases • SQL Server provides two types of databases they are, • System Databases, and • User Defined Databases.

7. System Databases • Along with the installation of the SQL Server, FOUR Databases will be created automatically called System Databases, they are • master • model • msdb • tempdb

8. System Databases • master – Stores all Databases information. • model – acts as a model to the new Database. • msdb – SQL Server agent will use this. • tempdb – temporary works will be stored in this. • pubs – Training purpose only. • northwind – Training purpose only.

9. User Defined Databases • The Databases that are created by users are called User Defined Databases. • Examples – employee, payroll, inventory, production, student, etc.,.

10. Syntax: CREATE DATABASE <db_Name> Example: • CREATE DATABASE sampledb • To execute the command • Click on the Run Button on the tool bar with the mouse. • Press F5 key on the Keyboard.

11. System Stored Procedure • Sp_help – This stored procedure lists all the databases available in the SQL Server. • Sp_helpdb <db_name> - This stored procedure lists all the properties of a given database.

12. To activate the database created by us, in order to create database objects under it • Select from the list in the Tool Bar the DB name that we have created or • Type a command in the Query Analyzer Editor Pane by following the syntax as shown below.

13. Syntax: Use <db_Name> Example: use sampledb • This command activates the given database name as the current database and the objects that will be created will be placed under this newly activated DB.

14. Creating a Database with File Arguments • CREATE DATABASE <db_Name> [ ON (<datafilespecification>) LOG ON (<logfilespecification>) ]

15. The Arguments in file specification • NAME=logical_name, • FILENAME=‘o/sfilename’, • SIZE=initialDBsize, • MAXSIZE=maxDBsize/UNLIMITED, • FILEGROWTH=growth_incr_value