Organization of Databases as well as Tables (Relations) Part – 2

Classifying the Database Records

Every single database is kept as a group of files on the hard drive of one’s computer. These files are comprised of the followings:

· Primary Data File (PDF) – The primary data file (PDF) has the database items. The primary data file (PDF) can be castoff for the system tables or relations as well as items and the subordinate files can be castoff to stock end – user information as well as items. The primary data file (PDF) has an . MDF extension. A database should be comprised of a primary data file (PDF).

· Secondary Data File (SDF) – The secondary data file (SDF) stock the database items too. The actually huge databases might essential numerous secondary data files (SDF) distributed over several disks (hard disks). A database should not contain any secondary data files (SDF), when the primary data file (PDF) is sufficiently big to grasp every information in the database. The secondary data file (SDF) has an . NDF extension.

· Transaction Log File (TLF) – The transaction log file (TLF) keep a track of every alteration which have happened in the database as well as the trades which had triggered those alterations. The transaction log files (TLF) has every trade data as well as can be castoff to recuperate a database. As a minimum one (1) transaction log file (TLF) should be present for any database, at the same time there can be numerous transaction log file (TLF). The extreme size of a transaction log file (TLF) is 512K. The size of the transaction log file (TLF) must be in between 25 – 40 percent of the size of the database. The transaction log file has an .LDF extension. A database should be comprised of one (1) transaction log file (TLF).

The database files are kept in filegroups form. A filegroup is a gathering of different files. The database is encompassed of a primary filegroup in addition to some user – defined filegroups. The primary filegroup holds the primary data file (PDF) as well as some additional files which are not placed into some supplementary filegroup. The primary filegroup holds the system tables or relations too. As soon as any item is formed in the database, without stating the filegroup, it is allotted to by default filegroup of that particular database. Simply a single filegroup in a database can be the by default filegroup. The user – demarcated filegroup is the filegroup which is formed by the end – users. An individual can form filegroups to dispense the information among numerous filegroups for refining the efficiency of the database.

Forming An End – User – Demarcated Database

Additionally to the system databases, the SQL Server has end – user – demarcated databases as well, where the end – user stock as well as handle their data. As soon as the end – user form a database, it is stowed as a group of files on the individual’s hard drive of the computer.

In the direction of forming end – user – demarcated database, an individual can practice the CREATE DATABASE command. The code of the CREATE DATABASE command is as subsequent:

CREATE DATABASE My_Database_Name

[ ON [ PRIMARY ] [ < filespecification > ] ]

[ LOG ON [ < filespecification > ] ]

< filespecification > : : =

( [ NAME = My_Logical_FileName , ]

FILENAME = ‘ My_OS_FileName ’ ,

[ SIZE = My_Size_Specification , ]

[ MAXSIZE = { My_Max_Size | UNLIMITED } , ]

[ FILEGROWTH = My_Growth ] )

here,

· My_Database_Name – It is the label of the fresh database that an individual wants to provide.

· ON – It stipulates the hard disk files castoff for stowing the information part of the database that is the information files.

· PRIMARY – It stipulates the related < filespecification > list which describes files in the main filegroup.

· LOG ON – It stipulates the hard disk files castoff for stowing the log files.

· NAME = My_Logical_FileName – It stipulates the rational label for the file.

· FILENAME = My_OS_FileName – It stipulates the operating system file label for the file.

· SIZE = My_Size_Specification – It stipulates the preliminary proportions of the file demarcated in the < filespecification > list.

· MAXSIZE = My_Max_Size – It stipulates the extreme proportions to which the file demarcated in the < filespecification > list can cultivate.

· FILEGROWTH = My_Growth – It stipulates the development percentage increase of the file demarcated in the < filespecificatin > list. The FILEGROWTH configuration for a file cannot go beyond the MAXSIZE value.

In the direction of forming a database, an individual should be an associate of the DBCREATOR Server Role. Moreover, an individual should have the CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE authorizations too.

The subsequent SQL query forms a database titled as XYZBank for storing the information relating with a bank.

CREATE DATABASE XYZBank

The previous command forms a database titled as XYZBank in the “ C : Program Files Microsoft SQL Server MSSQL Data folder ”. The information file label of the database is XYZBank . mdf and the log file label is XYZBank_Log . ldf.

An individual can form a database in the Object Explorer windows by means of right – clicking the Database folder in addition to choosing the New Database choice from the shortcut menu. The minute a database is formed, the end – user, who forms that database, spontaneously turn out to be the proprietor of that database. The proprietor of the database is known as DBO.

Subsequently after forming a database, an individual might want to perceive the particulars of the databases too. Intended for this persistence, an individual can practice the SP_HELPDB syntax. The code of SP_HELPDB statement is as follows:

SP_HELPDB ( My_Database_Name )

here,

· My_Database_Name – It is the label of the database for which an individual wants to know the particulars.

For an instance consider the following:

SP_HELPDB ( XYZBank )

Giving Another Name To An End – User – Demarcated Database

An individual can give a new name to any database at any time when it is necessary. Solely the system administrator otherwise the database proprietor can change the name of a database. The SP_RENAMEDB stored procedure is castoff to provide a new name to a database. The code of the SP_RENMAEDB command is as follows:

SP_RENAMEDB My_Old_Database_Name , My_New_Database_Name

here,

· My_Old_Database_Name – It is the existing label of the database that an individual want to change.

· My_New_Database_Name – It is the fresh label of the database that an individual want to provide.

For an instance, the subsequent SQL query gives another name to the XYZBank database:

SP_RENAMEDB XYZBank , ZYXBank

An individual can rename a database in the Object Explorer windows by means of right – clicking the Database folder in addition to choosing the Rename choice from the shortcut menu.

In the upcoming part we will be discussing about how to delete an end – user – demarcated database, in what way to handle the tables or relations, how to create a table or relation and what are the recommendations for creating the tables or relations