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

Deleting An End – User – Demarcated Database

An individual can remove a database as soon as it is not needed. This will result in deleting every database files as well as the information. Solely the end – users having the SYSTADMIN Role plus the database proprietor have the authorization to remove a database. The DROP DATABASE command is castoff to remove a database. The code of the DROP DATABASE command is as follows:

DROP DATABASE My_Database_Name

here,

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

For an instance, the subsequent SQL query removes the XYZBank database:

DROP DATABASE XYZBank

An individual can remove a database in the Object Explorer windows by means of right – clicking the Database folder in addition to choosing the Delete choice from the shortcut menu. An individual cannot remove any system – demarcated databases.

Handling Tables Or Relations

Begin a database designer an individual is required to form tables or relations form stowing the information. At the time of forming tables or relations in a relational database, an individual is required to stipulate few guidelines as well as constrictions for columns or attributes which will stipulate the type of information that is to be kept in the tables or relations. Moreover, an individual should stipulate the associations among numerous tables or relations. Certainly the table or relation which an individual is forming necessities to stock a huge size of information, then an individual can form a segregated (partitioned) table or relation. This benefit in refining the performance of the enquiries.

Furthermore to forming tables or relations, an individual is accountable for handling tables or relations. The handling of tables or relations includes altering tables or relations for introducing new columns / attributes or to alter the guidelines enforced on the tables or relations. It includes removing tables or relations, as and when not needed too.

Forming A Table Or Relation

In SQL Server, an individual can form a table or relation by means of CREATE TABLE command. The code of the CREATE TABLE command is as follows:

CREATE TABLE

[ My_Database_Name . [ My_Schema_Name ] . ] My_Table_Name

( { < My_Column_Defination > | < My_Computed_Column_Defination > }

[ < My_Table_Constatints > ] )

[ ON { My_Partition_Schema_Name ( My_Partition_Column_Name ) | My_Filegroup | “ Default ” } ]

[ { TEXTIMAGE_ON { My_Filegroup | “ Default ” } } ]

Where,

· My_Database_Name – It stipulates the label of the database where the table or relation is to be formed. When an individual does not stipulates a database title, the table or relation is formed in the recent database.

· My_Schema_Name – It stipulates the schema title to which the fresh table or relation fits. Schema is a rational cluster of database items in a database. Schemas benefit in enhancing the handling part of the items in a database.

· My_Table_Name – It stipulates the fresh table or relation title that an individual wants to provide. The table or relation title should be under 128 characters.

· My_Column_Defination – It stipulates the label of the column or attribute in addition the labels should be exclusive in the table or relation. The column or attribute title should be under 128 characters

· My_Computed_Column_Defination – It stipulates the code that creates the data for the calculated column or attribute. A calculated column or attribute do not occurs materially in the system memory however it is castoff to produce a calculated data.

For an instance, if an individual have the PrincipleAmount kept in one column or attribute, Rate kept in another column or attribute and the TimePeriod kept in another column or attribute. Now, individual need to calculate the EMI value which is a computed column or attribute. In this case the individual can make practice of My_Computed_Column_Defination to find the EMI value of the loan. The subsequent SQL query presentations the usage of My_Computed_Column_Defination:

( PrincipleAmount * Rate * TimePeriod ) / 100

· My_Table_Constatints – It is a noncompulsory keyword which stipulates the PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constrictions.

· My_Partition_Schema_Name – It stipulates the segregate (partition) schema title which describes the filegroups on which the segregate (partition) of a table or relation is linked to. It confirms that the segregate (partition) schema be inside the database.

· My_Partition_Column_Name – It stipulates the columns or attributes title on which a segregated (partitioned) table or relation will be segregate (partitioned).

· TEXTIMAGE_ON { My_Filegroup | “ Default ” } – These are the keywords which stipulate that the VARCHAR (MAX), VARBINARY (MAX), NVARCHAR (MAX), XML, IMAGE, TEXT, NTEXT as well as CLR end – user – demarcated form columns or attributes are kept on the quantified filegroup. If in present no huge size columns or attributes in the table or relation are present, the TEXTIMAGE_ON is not permitted.

Recommendations For Forming Tables Or Relations

An individual must think through the subsequent rules at the time of forming any tables or relations:

· Intended for forming whichever table or relation an individual must have the CREATE TABLE authorizations.

· The column or attribute labels in table or relation need to be exclusive; on the other hand the identical column or attribute label can be castoff in dissimilar tables or relations within the database.

· The table or relation label should be under 128 characters.

An individual can form a table or relation by means of right – clicking the Tables folder under the Database folder in the Object Explorer windows in addition to choosing the New Table choice from the shortcut menu.

Think through an instance, of the XYZBank . Customer . Details table or relation which is castoff to stock the customer details of the bank. The subsequent table or relation defines the arrangement of the XYZBank . Customer . Details table or relation:

CREATE TABLE [ XYZBank ] . [ Customer ] . [ Details ] ( CustomerID VARCHAR (10) NOT NULL , Name VARCHAR (30) NOT NULL, Gender VARCHAR (7) NOT NULL, Address VARCHAR (MAX) NOT NULL , EmailID VARCHAR (25) , Phone INT NOT NULL , SocialSecurityID VARCHAR (15) NOT NULL , AccountType NVARCHAR (15) NOT NULL )

In the upcoming part we will be discussing about how to apply information reliability and how to apply constrictions.