150 likes | 255 Views
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.,. Files to store a Database.
E N D
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.,.
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.
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
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
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
Types of Databases • SQL Server provides two types of databases they are, • System Databases, and • User Defined Databases.
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
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.
User Defined Databases • The Databases that are created by users are called User Defined Databases. • Examples – employee, payroll, inventory, production, student, etc.,.
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.
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.
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.
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.
Creating a Database with File Arguments • CREATE DATABASE <db_Name> [ ON (<datafilespecification>) LOG ON (<logfilespecification>) ]
The Arguments in file specification • NAME=logical_name, • FILENAME=‘o/sfilename’, • SIZE=initialDBsize, • MAXSIZE=maxDBsize/UNLIMITED, • FILEGROWTH=growth_incr_value