دانلود مقاله آزمایشگاه پایگاه داده ها

بخشی از مقاله

آزمايشگاه پايگاه داده ها

بسمه تعالي

دستور کار آزمايشگاه پايگاه داده ها

آزمايش 1
آشنايي با محيطQUERY ANALYZER ، نحوه ايجاد پايگاه داده وجداول
پيش آگاهي
مقدمه :آشنايي با RDBMSها
از زماني که در سال 1970مقاله آقاي کادتحت عنوان "مدل رابطه اي داده ها براي بانک هاي اطلاعاتي اشتراکي بزرگ" منتشر شد ، زمان زيادي نمي گذرد. بعد از مطرح شدن اين ايده پروژه تحقيقاتي تحت عنوان SYSTEM/Rدر IBM شکل گرفت که حاصل آن ايجاد اولين DBMS وزبان SQL بود. از آن زمان شرکت هاي مختلفي به توليدDBMS هاپرداختندتا اين که در سال1988،SQL-SERVER معرفي شد. علاوه بر اين در سال 1986، SQL توسطANSI استاندارد شد. نسخه اي از اين زبان را ، تحت عنوان TRANSACT-SQL استفاده مي نمايد. همان گونه که مي دانيد زبان SQL غيررويه اي است يعني در آن تنها درخواست کاربرارائه مي گرددوالگوريتم لازم براي اجراي آن توسط بخش هايي ازDBMS(بهينه ساز) توليد مي گردد .

هرسيستم مديريت پايگاه داده اي بر مبناي مدل رابطه اي ،يک RDBMS ناميده مي شود .اين سيستم هاازدو بخش عمده به شرح زيرساخته شده اند:
1- هسته :که کارهاي مديريتي را انجام مي دهد.

2- فرهنگ داده ها(DataDictionary ) : که شامل اطلاعاتي در مورد عناصرواشياء مختلف پايگاه داده اي تعريف شده مي باشد ازقبيلsysobjectsکه مشخصات اشياءمختلف تعريف شده درآن نگهداري مي گردد ياsysindexes و syscolumns که مشخصات شاخص هاومشخصات ستون هاي تعريف شده در آن ها ذکر مي گردد.

آشنايي با SQL-SERVERبه عنوان يک RDBMS
براي راهبري پايگاه داده ها، SQL-SERVERدو واسط گرا فيکي به نام هاي Enterprise Manager وQuery Analyzer را در اختيار قرار مي دهد. دراين مرحله فرض مي کنيم يک سرور ثبت شده وتعدادي شي Login وتعدادي پايگاه داده تعريف شده برروي آن داريم بنابراين ازمباحث مربوط به آنها فعلا چشم مي پوشيم . علاوه بر اين فرض مي کنيم که سرور فوق در حال سرويس دهي است.Query Analyzer اولين واسطي است که به کمک آن مي توانيد پرس وجو هاي T-SQLوتوابع و روال هاي ذخيره شده را اجرا کنيد . پس ازورودبهQuery Analyzer اطلاعات Login از شما خواسته مي شود که مي توانيد از تاييد اعتبار خود ويندوز استفاده کنيدو به اين محيط وارد شويد. اين محيط از دوپنجره اصلي تشکيل شده است. درپنجره سمت چپ با نام Object Browserمي توانيد اشيايي از قبيل نام سروري که به آن متصل شده ايد، پايگاه داده هاي موجود وبقيه اشياء اصلي مورد استفاده درSQL-SERVER را مشاهده وويرايش کنيد. در اين قسمت اشياء به صورت ساختار درختي نمايش داده مي شوند. در هرسرور موجود حداقل چهارپايگاه داده زير موجود است :

1-Master : شامل تمامي اطلاعات لازم براي مديريت پايگاه، مانند پايگاه داده هاي تعريف شده ومشخصات کاربران و رويه هاي ذخيره شده سيستمي وپيام هاي خطاست. جداولي که در اين پايگاه داده موجود است معمولاکاتالوگ سيستم ناميده مي شود .

2- Model : يک الگو براي ساخت پايگاه داده هاي جديد است وهر شي موجود در آن ،در پايگاه داده هاي جديد ايجاد مي شود. براي مثال اگر يک شناسه کاربر جديد در آن قرار دهيد ،در تمامي پايگاه هايي که بعد ازاين ايجاد مي شوند اين شناسه هم وجود دارد .
3-Msdb : در نگهداري برنا مه هاي زمان بندي سيستم وJob ها وتاريخچه نسخه هاي پشتيبان کاربرد دارد .

4- Tempdb: محل موقتي براي اشيائي است که نياز به فضاي موقتي دارند .
در زير پنجرهobject browser ،با کليک بر روي تب Templates مي توانيد بهTemplate هاي موجود در مورد هر شي دسترسي داشته باشيد . پنجره ديگر موجود در محيط Query Analyer پنجره پرس وجو است که از آن براي اجراي پرس وجو هاي تعاملي استفاده مي شود .

براي اجراي اسکريپت ها (مجموعه هايي از دستورات ) بايد يکي از پايگاه هاي داده اي موجود به عنوان پايگاه داده جاري انتخاب شود. براي اين کار از دستورUSE، استفاده مي شود. اين پنجره از دو قسمت تشکيل شده است، که يکي براي ويرايش دستورات و ديگري براي نمايش نتايج به کار مي رود. (قسمت اخير بعداز اجراي يک اسکريپت قابل مشاهده است.)کاربر مي تواند با استفاده از گزينهQuery، نحوه نمايش اين خروجي ها را به حالت Gridيا Textتنظيم کند يا يک فايل را به عنوان محل ذخيره خروجي هاي Query تعريف نمايد .

به ثبت رساندن وحذف وتغيير يک پايگاه داده جديد درSQL-SERVER
اصولا اطلاعات موجود در هر پايگاه داده در فايل ها ي آن پايگاه داده نگهداري مي شوند. اين امکان وجود دارد که فايل هاي مختلف ،گروه هاي مختلفي را تشکيل دهند که هر فايل به يکي از آن ها اختصاص داشته باشد در اين صورت مي توان اشياء پايگاه داده را در يک فايل خاص يا فايل هاي يک گروه ذخيره کرد. علاوه بر اين مثلا دراعمالي مانند پشتيباني (backup) مي توان به جاي کل پايگاه داده اي، گروه هاي فايل اصلي را پشتيباني کرديا بااعمال برنامه زماني مورد نظر، هر يک از گروه هارامستقلاپشتيباني کرد. براي پايگاه داده هاي تعريف شده در SQL-SERVER سه نوع فايل قابل تصور است :

1- فايل هاي Primary( باپسوند .mdf) :که حاوي اطلاعات راه اندازي پايگاه هستند وبه بقيه فايل هاي پايگاه داده ها اشاره دارند .
2- فايل هاي Secondary( باپسوند .ndf) : بقيه فايل هاي داده اي به جز فايل هاي داده اي اصلي در اين گروه قرار مي گيرند .
3- فايل هاي Log( باپسوند .ldf) : براي ثبت تراکنش هاي موجود در پايگاه به کار مي روندو عضو هيچ گروه فايلي نيستند. براي بسياري از پايگاه هاي داده اي معرفي گروه 1و3کافي است وممکن است پايگاه داده اي چندين فايل از نوع دوم داشته باشد يا هيچ فايلي از اين نوع نداشته باشد. يک فايل نمي تواند عضو بيش ازيک گروه فايلي باشد وفايل هاي سيستم در گروه فايل Primary قرار مي گيرند. (براي اطلاعات بيشتر در مورد نحوه استفاده از اين امکان در عمل مي توانيد به مراجع SQL-SERVERمراجعه کنيد .)

ايجاد جداول پايگاه داده
اين جداول به منظور تعريف فيلدهاي مورد استفاده در ايجاد يک پايگاه داده اي مورد استفاده قرار مي گيرند که در قسمت پيوست ساختار کلي يک جدول آورده شده است .
درهرجدول پايگاه داده اي براي اطمينان از درستي مقادير فيلدها انواع جامعيت داده اي، مورداستفاده قرار مي گيرد که ازانواع آن مي توان به موارد زير اشاره کرد :

کليداصلي(primarykey) :که شامل يک ياچندستون است که مقاديرموجود دردوسطر از ستون ها نميتوانند يکسان باشند. همچنين کليد اصلي نمي تواند مقدارتهي بپذيرد .

کليد کانديدا(unique) : مانند کليداصلي است با اين تفاوت که درستون هاي تعريف شده به عنوان کليد کانديدا مي تواند مقدار تهي هم وارد شود .
کليد خارجي(foreignkey) : براي ايجاد ارتباط بين داده هاي جداول پايگاه داده اي ، ازيک يا ترکيبي از چند ستون با عنوان کليد خارجي استفاده مي شود به طوري که داده هاي يک جدول با مقاديرکليد اصلي جدول مرتبط با آن پرمي شود.

Check : مقادير قابل پديرش يک ستون توسط اين محدوديت کنترل مي شود .
Constraints : براي اعمال محدوديت هاي داده اي ، مانند کليداصلي ،کليد خارجي و کليد کانديدا وcheckمورد استفاده قرار مي گيرد .
ستون هاي محاسباتي در تعريف جداول

اين ستون ها عبارت به کار رفته براي محاسبه داده را به جاي خودداده ذخيره مي کنند و قواعد زير را دارند :
• ستون هاي ارجاع شده در عبارت ستون محاسباتي بايد در همان جدول باشند .
• ستون محاسباتي شامل subquery نيست .

• اين ستون ها به عنوان جزئي از کليد يا انديس به کار نمي روند .
• نمي توانند محدوديتي از نوع default داشته باشند .(چرا؟)
• در دستورات insert وupdate ارجاعي به آن ها نداريم .
مثال1 :
Create table author

(au_id1 int primary key clustered,
unique nonclustered, au_id2 int

au_degree smallint,
au_name char(16) not null,
au_family char(16) not null,
au_address char(30) null)
CONSTRAINT au_degree_chk check((au_degree>=0) and(au_degree<=100)
در جدول تعريف شده بالا ،براي هر نويسنده دو شماره ازنوع int تعريف شده که روي اولي شاخص خوشه اي و روي دومي شاخص غيرخوشه اي قرارگرفته است. علاوه بر اين روي شماره دوم محدوديت يکتا بودن اعمال مي شود. سومين ستون نيز به درجه نويسنده اشاره مي کند که با checkروي آن محدوديت بازه اي اعمال مي شود .

جدول هاي موقتي
اين جداول زماني که اتصال به سرورقطع شود از بين مي روند. مهم ترين انواع جدول هاي موقتي مانند متغيرهاانواع عمومي و محلي هستند. تفاوت اين دو نوع در اين است که ازجداول سراسري همه کاربران سيستم مي توانند استفاده کنندو با ## شروع مي شوند. جداول محلي فقط براي کاربر سازنده خود قابل استفاده هستند و با # شروع مي شوند. براي ايجاد اين جداول از دستور CREATEاستفاده مي شود :

CREAET TABLE #myTmpTable
(Name VARCHAR(30) NOT NULL ADDRESS VARCHAR(50))
از دستور SELECT ---INTO مي توان براي تعريف و پرکردن يک جدول موقتي به صورت همزمان استفاده کرد .
Select Name,Cname INTO #myTmpTable
From STD AS S

INNER JOIN
CRS AS C
ON C.S#=S.S#

شاخص ها در SQL-Server
شاخص ها در SQL-Serverاشياء خاصي هستند که اين امکان را فراهم مي آورند که بتوان بر اسا س مقادير يک يا چند ستون به سرعت به سطر هاي يک جدول دسترسي پيدا کرد. SQL-Server، دو نوع شاخصClustered و Nonclustered رادر اختيار کاربر براي تعريف قرار مي دهد .SQL-Server براي پياده سازي شاخص ها از ساختار B-TREEاستفاده مي کند که در آن برگ ها حاوي داده هاي واقعي هستند .

1- شاخص Clustered: دراين نوع شاخص ، داده ها واقعا از نظر فيزيکي مرتب مي شوند. در اين نوع انديس برگ ها داده هاي واقعي هستند . هميشه با معرفي يک ستون به عنوان کليد اصلي به صورت خودکار روي آن يک شاخص Clustered ساخته مي شود .

2- شاخص Nonclustered: در اين نوع شاخص برگ ها بر مبناي ستون هايي که به عنوان شاخص تعريف شده اند مرتب مي شوند ولي تفاوت آن با نوع قبلي اين است که در صورتي که قبلا روي جدول شاخص Clusteredايجاد نشده باشد برگ هاي انديس Nonclustered حاوي آدرس ذخيره تاپل(ونه خود تاپل ) متناسب با کليد شاخص شده است ودرغيراين صورت حاوي مقدار کليد شاخص دارمتناسب با آن تاپل هستند .

با توجه به توضيحات بالا رعايت نکات زير توصيه مي شود :
• براي ستون داراي شاخص Clusteredاز مقاديرحجم داراستفاده نکنيد. زيرا علاوه بر اينکه زمان جستجو در ستون با شاخص Clusteredافزايش مي يابد ، حجم ذخيره سازي شاخص هاي Nonclustered هم افزايش مي يابد. (چرا ؟ )
نکته: سعي کنيد ابتدا شاخصClustered را ايجا دکنيد و بعد شاخص هاي Nonclustered راتعريف کنيد . (چرا ؟)
تعريف شاخص:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ ON filegroup ]

توضيح بعضي پارامترهاي تعريف بالا:
• کلمه کليدي Unique مشخص مي کندکه مقادير تکراري در شاخص غيرمجازند .
• ASCبيان کننده اين است که مي خواهيم شاخص به صورت صعودي ساخته شود .DESC نيز درخواست
نزولي بودن شاخص را بيان ميکند .

• با استفاده از گزينه ON filegroup مي توان يکي از گروه هاي فايلي را به عنوان محل ذخيره شاخص معرفي کرد .
مثال 2: فرض کنيد مي خواهيم شاخصي با نام Snumindex را روي ستون شمار ه دانشجويي به صورت نزولي در جدول STD ايجاد کنيم :
Create clustered index Snumindex on STD(S# DESC )
On Primary

سوال : فرض کنيد درخواست هاي زيادي مطرح مي شود که در آن ها معمو لا در گزينه Where سوال روي شماره دانشجويي هاي پايين مطرح مي شود آياحذف کردن اين انديس مي تواند زمان پاسخگويي ر ابه طور نسبي افزايش دهد؟
تغيير يک جدول

ALTER TABLE author
ADD column_b INT IDENTITY اضافه کردن يک ستون با محدوديت کليد اصلي
CONSTRAINT column_b_pk PRIMARY KEY,

/* Add a column with a constraint to enforce that */
/* nonnull data is in a valid phone number format. */
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),

/* Add a nonnull column with a default. */
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
توجه : براي انجام آزما يش ها سينتکس دستورات create database، Alter database، create table،Alter table و انواع داده اي موجود در ضميمه را مطالعه کنيد.

دستور کار:
بخش اول-آشنايي با Query Analyzer ونحوه اجراي دستورات T-SQL
1- بعد از loginکردن با نام عبوري که به شما داده مي شود و روي سروري که به شما معرفي مي شود وارد محيط Query Analyzer شويد .
2- روي سروري که به آن Loginکرده ايد کليک کنيد وپايگاه داده هاي موجود روي اين سرور را مشاهده کنيد .چه پايگاه داده هايي روي اين سرور به ثبت رسيده است؟
3- به کمک دستور exec رويه هاي ذخيره شده sp_help (براي پايگاه داده pubs ) و sp_help dbرا اجرا کنيد . در پنجره results خروجي هايي هر کدام را مشاهده مي کنيد . در مورد عملکرد آن ها توضيح دهيد؟
( اين دوازرويه هاي ذخيره شده (stored procedure) سيستمي هستند که بعدا با نحوه ايجاد ومديريت اين رويه ها آشنا مي شويد .)
4- الف- اسکريپت زير را به يکباره اجرا کنيد ويک فايل را به عنوان محل ذخيره خروجي ها تعريف کنيد . ( دسته مجموعه اي از دستورات T-SQL است که همگي به يکباره به موتور پايگاه ارسال مي شوند و کامپايل وبهينه سازي و اجرا مي شوند. اسکريپت نيزمجموعه اي از يک يا چند دسته است که به صورت گروهي ذخيره مي شوند. از اسکريپت ها براي مثلا بخشي از کار بارگذاري داده ها (Data overloading )يا نگهداري پايگاه داده استفا ده مي شود. مثلا اگر نياز به انجام چندکارمستقل باشد، اسکريپتي از چنددسته مي نويسيم و براي مشخص کردن دسته ها از هم از Goدر بين آنها استفاده مي کنيم. بدين ترتيب خطاهاي روي هر دسته اعم از کامپايل ياخطاهاي زمان اجرا ، اجراي دسته هاي قبلي وبعدي را مختل نمي کند . )
Use pubs
Select * from authors
Go
Use Northwind
Select EmployeeID from Employees
where City='London'

ب- در دسته دومEmployeeID را بهEmployee تغييردهيدواين اسکريپت رااجرا کنيد . آياتغيير يک دسته دراجراي کل اسکريپت تاًثير گذار است ؟ توضيح دهيد.
بخش دوم - ايجاد يک پايگاه داده جديد

1- اسکريپتي براي ايجاد پايگاه داده اي بانا م Testبا مشخصات زير اجرا کنيد :
• يک فايل اصلي با مشخصات اندازهMB 20 باحداکثررشد مساوي 100MBو با رشد 2MBدرهربارودر گروه فايل اصلي
• يک فايل ثانويه با مشخصات اندازه 5MBحداکثر اندازه 10MBوبا رشد 1MBو در گروه فايلي با نام test واين گروه فايلي را گروه فايلي پيش فرض قرار دهيد .
• يک فايل ثبت تراکنش(log file ) با مشخصات اندازه 10MB حداکثر اندازه 30MBوبا رشد 20%

2- رويه هاي ذخيره شدهsp_helpfilegroup وsp_helpfile را روي پايگاه داده اي که ايجاد کرده ايد اجرا کنيد . چه خروجي هايي مشاهده مي کنيد ؟
3- اسکريپتي را اجرا کنيد که گروه فايلي test وفايل هاي آن را حذف کند . (اصولا براي حذف هر گروه فايلي ابتدا گروه فايلي ديگري رابه عنوان گروه فايل پيش فرض تعريف ميکنيم در صورتي که گروه فايلي حذف شوند پيش فرض باشد سپس تمام فايل هاي آن گروه فايلي وبعد خودآن را حذف مي کنيم .)
4- پايگاه داده فوق را به registration تغيير نام دهيد .(از رويه sp_renamedb استفاده کنيد .)

5- کليه جداول پايگاه داده registrationراکه درزيرمشخص شده، باتوجه به محدوديت هاي موردنظرتعريف کنيد .(کليه اسکريپت هاي نوشته شده را ذخيره کنيد .)
جداول موردنيازدرآزمايشگاه :

)STD ( فايل اطلاعات دانشجو
قيود کليد خارجي
و ارجاع کليد اصلي امکان null نوع داده اي نام فارسي نام لاتين
1- ازنوع clusteredتعريف شود،
2- رقم اول هرشماره از1تا9 ،بقيه ارقام از0تا9 × Int شماره دانشجويي S#
Varchar(16) نام Name
Varchar (20 فاميل Family
رقم اول بين1تا9 ،رقم دوم بين0تا9 Tinyint رشته تحصيلي Field
F:زن , M:مرد
Check (sex=’M’ or sex=’f’) Char(1)
جنسيت
)F-M) Sex
× Dec(5,2) معدل کل Gpa
× Varchar(40) آدرس Address
Int کدشهر Citycode
رقم اول هرشماره از1تا9 ،بقيه ارقام از0تا9 × Int شماره تلفن TelNo

1-ازنوع uniqueتعريف شود،
-2رقم اول هرشماره از1تا9 ،بقيه ارقام از0تا9
Bigint شماره شناسايي ملي Ssno
Datetime تاريخ تولد Birthdate

فايل درس ( CRS )
قيود کليد خارجي
و ارجاع کليد اصلي امکان null نوع داده اي نام فارسي نام لاتين
رقم اول هرشماره از1تا9 ،بقيه ارقام از0تا9 × Char(7) شماره درس C#
Varchar (30) نام درس Cname
Dec(2,1) تعداد واحد درس Unit
Check
(0=<passgrade <=20)
Dec(5,2) حداقل نمره قبولي
دردرس Passgrade
‘p’= وعملي‘t’=تئوري
Check(Crstype=’t’ or
crstype =’p’)
Char (1) نوع درس
(تئوري – عملي) Crstype

) STDTRM فايل ترم دانشجو (
قيود کليدخارجي
و ارجاع کليد اصلي امکان null نوع داده اي نام فارسي نام لاتين
رقم اول بين3تا9 ،رقم دوم وسوم بين0تا9 ورقم چهارم بين1تا3
×

Char(4) شماره ترم TrmNo
STD(S#) × Int شماره دانشجويي S#
× Dec(5,2) معدل ترم TrmGpa

) REG ( فايل ثبت نام
قيود کليد خارجي
و ارجاع کليد اصلي امکان null نوع داده اي نام فارسي نام لاتين
رقم اول بين3تا9 ،رقم دوم وسوم بين0تا9 ورقم چهارم بين1تا3
STDTRM(S#,TRMNO)
×

Char(4) شماره ترم
TrmNo

STD(s#)
STDTRM(S#,TRMNO) × Int شماره دانشجويي S#
CRS(c#) × Char(7) شماره درس C#
Check(0=<Grade<=20) × Dec(5,2) نمره دردرس Grade

) PREREQ ( فايل پيش نياز
قيود کليد خارجي
و ارجاع کليد اصلي امکان null نوع داده اي نام فارسي نام لاتين
CRS(c#) × Char(7) شماره درس C#
CRS(c#) ×
Char(7) شماره درس
پيش نياز Cp#

Check(1=<seqno <=5) ×
Tiny int شماره چندمين
پيش نيازيا هم نياز SeqNo

) COREQ ( فايل هم نياز
قيود کليد خارجي
و ارجاع کليد اصلي امکان null نوع داده اي نام فارسي نام لاتين
CRS(c#) × Char(7) شماره درس C#
CRS(c#) ×
Char(7) شماره درس
هم نياز Cc#
Check(1=<seqno<=5) ×
Tiny int شماره چندمين
پيش نيازيا هم نياز SeqNo

) CODEFILE ( کد فايل
قيود کليد خارجي
و ارجاع کليد اصلي امکان null نوع داده اي نام فارسي نام لاتين
× varchar(8) رشته تحصيلي Field
× Varchar(4) نوع Type
×
Varchar(30) شرح Desc

سوال :
1- آيا روي هر جدول محدوديتي روي تعداد ايندکس هاي قابل تعريف وجود دارد؟
2-آيا ساخت فهرست به طور نامحدود مشکلي براي سيستم (ازنظر زمان عمليات ) روي هريک ازعمليات select,insert,delete,update ايجاد مي کند؟

بخش سوم – آشنايي با نحوه حذف يک پايگاه داده وجداول
براي حذف اشياءيک پايگاه داده از دستور dropاستفاده مي کنيم.
۱- پايگاه داده جديدي با نام test تعريف کرده وجدول مثال زده شده در قسمت پيش مطالعه را براي آن تعريف کنيد .
۲- با استفاده از دستور Alter table ستون نگهدارنده SSNO را(از جدول STD) حذف کنيد .با چه خطايي برخورد مي کنيد .چرا؟
۳- بعد از حذف محدوديت ايندکس گذاشته شده بر روي SSNO ,خود SSNOراحذف کنيد .
۴- اطلاعات دانشجويي مجازي را در جدول STDوارد کنيد .
۵- اطلاعات دروس مجازي با شماره هاي 1024345و1025123و861026را در جداول CRS وPREREQوCOREQوارد کنيد . (فرض کنيد درس 1024345پيش نيازدرس 1025123وهم نياز درس861026 است .)
۶- سعي کنيد درس 1027456را براي تنها دانشجوي موجود در جدول STDدر ترم 3832ثبت نام کنيد .آيا اين کار امکان پذير است چرا؟ با اين کار کدام يک از قواعد جامعيت داده ها نقض مي شود؟ آيا در اين مرحله امکان ثبت نام درس1025123 براي اين دانشجو امکان دارد ؟
۷- ستون حذف شده SSNOر ابه جدول STDبا محدويت کليد ثانويه يا ايندکس غير خوشه اي بودن و غير قابل تهي بودن براي اين جدول تعريف کنيد ؟با چه خطايي برخورد مي کنيد؟
۸- با استفاده از عبارت WITH NOCHECKمرحله قبل را تکرار کنيد .
۹- حال با استفاده از دستور update يک شماره شناسايي ملي براي دانشجوي مورد نظر وارد کنيد .
۱۰- درس شماره 1024345را براي دانشجوي فوق ثبت نام کنيد؟
۱۱- در اين مرحله سعي کنيد جدول STDراdropکنيد ؟آيا اين کار امکان پذير است؟چرا؟
۱۲- تمامي رکوردهاي موجوددرجداول STDو REGراحذف کنيد؟ اين کار به چه ترتيبي بايد انجام شود؟
13- با استفاده از فرمان Alter tableدو ستون با عنوان هاي TotpassunitوTotregunitکه به ترتيب نگهدارنده تعداد کل واحد گذرانده وتعداد کل واحد اخذ شده هستند را به جدول STDاضافه کنيد .

ضميمه آزمايش اول:
تمامي مطالب اين ضميمه بر گرفته از Sql-Server 2000 Online booksمي باشد.در صورت نياز مي توانيد به اين منبع هم مراجعه کنيد.
1-CREATE DATABASE
Creates a new database and the files used to store the database
Syntax
CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]
Arguments
database_name
Is the name of the new database. Database names must be unique within a server and conform to the rules for identifiers. database_name can be a maximum of 128 characters, unless no logical name is specified for the log. If no logical log file name is specified, Microsoft® SQL Server™ generates a logical name by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical log file name is less than 128 characters.
ON
Specifies that the disk files used to store the data portions of the database (data files) are defined explicitly. The keyword is followed by a comma-separated list of <filespec> items defining the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items defining user filegroups and their files.
n
Is a placeholder indicating that multiple files can be specified for the new database.
LOG ON
Specifies that the disk files used to store the database log (log files) are explicitly defined. The keyword is followed by a comma-separated list of <filespec> items defining the log files. If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is 25 percent of the sum of the sizes of all the data files for the database.
All databases have at least a primary filegroup. All system tables are allocated in the primary filegroup. A database can also have user-defined filegroups. If an object is created with an ON filegroup clause specifying a user-defined filegroup, then all the pages for the object are allocated from the specified filegroup. The pages for all user objects created without an ON filegroup clause, or with an ON DEFAULT clause, are allocated from the default filegroup. When a database is first created the primary filegroup is the default filegroup. You can specify a user-defined filegroup as the default filegroup using ALTER DATABASE:
ALTER DATABASE database_name MODIFY FILEGROUP filegroup_name DEFAULT
Each database has an owner who has the ability to perform special activities in the database. The owner is the user who creates the database. The database owner can be changed with sp_changedbowner.
To display a report on a database, or on all the databases for an instance of SQL Server, execute sp_helpdb. For a report on the space used in a database, use sp_spaceused. For a report on the filegroups in a database use sp_helpfilegroup, and use sp_helpfile for a report of the files in a database.
Permissions
CREATE DATABASE permission defaults to members of the sysadmin and dbcreator fixed server roles. Members of the sysadmin and securityadmin fixed server roles can grant CREATE DATABASE permissions to other logins. Members of the sysadmin and dbcreator fixed server role can add other logins to the dbcreator role. The CREATE DATABASE permission must be explicitly granted; it is not granted by the GRANT ALL statement.
CREATE DATABASE permission is usually limited to a few logins to maintain control over disk usage on an instance of SQL Server.
Examples

A. Create a database specifying multiple data and transaction log files
This example creates a database called Archive with three 100-MB data files and two 100-MB transaction log files. The primary file is the first file in the list and is explicitly specified with the PRIMARY keyword. The transaction log files are specified following the LOG ON keywords. Note the extensions used for the files in the FILENAME option: .mdf is used for primary data files, .ndf is used for the secondary data files, and .ldf is used for transaction log files.
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
B. Create a database without specifying SIZE
This example creates a database named products2. The file prods2_dat becomes the primary file with a size equal to the size of the primary file in the model database. The transaction log file is created automatically and is 25 percent of the size of the primary file, or 512 KB, whichever is larger. Because MAXSIZE is not specified, the files can grow to fill all available disk space.
USE master
GO
CREATE DATABASE Products2
ON
( NAME = prods2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\prods2.mdf' )
GO
C. Create a database with filegroups
This example creates a database named sales with three filegroups:
• The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for these files is specified as 15 percent.
• A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
• A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

2-ALTER DATABASE
Adds or removes files and filegroups from a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file. ALTER DATABASE provides the ability to change the database name, filegroup names, and the logical names of data files and log files.
Syntax
ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
database
Is the name of the database changed.
ADD FILE
Specifies that a file is added.
TO FILEGROUP
Specifies the filegroup to which to add the specified file.
filegroup_name
Is the name of the filegroup to add the specified file to.
ADD LOG FILE
Specifies that a log file be added to the specified database.
REMOVE FILE
Removes the file description from the database system tables and deletes the physical file. The file cannot be removed unless empty.
ADD FILEGROUP
Specifies that a filegroup is to be added.
filegroup_name
Is the name of the filegroup to add or drop.
REMOVE FILEGROUP
Removes the filegroup from the database and deletes all the files in the filegroup. The filegroup cannot be removed unless empty.
MODIFY FILE
Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft SQL Server is restarted.
To modify the logical name of a data file or log file, specify in NAME the logical file name to be renamed, and specify for NEWNAME the new logical name for the file.
Thus:
MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).
For optimum performance during multiple modify-file operations, several ALTER DATABASE database MODIFY FILE statements can be run concurrently.
MODIFY NAME = new_dbname
Renames the database.
MODIFY FILEGROUP filegroup_name { filegroup_property | NAME = new_filegroup_name }
Specifies the filegroup to be modified and the change needed.
If filegroup_name and NAME = new_filegroup_name are specified, changes the filegroup name to the new_filegroup_name.
If filegroup_name and filegroup_property are specified, indicates the given filegroup property be applied to the filegroup. The values for filegroup_property are:
READONLY
Specifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. Only users with exclusive database access can mark a filegroup read-only.
READWRITE
Reverses the READONLY property. Updates are enabled for the objects in the filegroup. Only users who have exclusive access to the database can mark a filegroup read/write.
DEFAULT
Specifies the filegroup as the default database filegroup. Only one database filegroup can be default. CREATE DATABASE sets the primary filegroup as the initial default filegroup. New tables and indexes are created in the default filegroup—if no filegroup is specified in the CREATE TABLE, ALTER TABLE, or CREATE INDEX statements
WITH <termination>
Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. Only one termination clause can be specified and it follows the SET clauses.
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after the specified number of seconds or immediately. If the termination clause is omitted, transactions are allowed to commit or roll back on their own.
NO_WAIT
Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.
<state_option>
Controls user access to the database, whether the database is online, and whether writes are allowed.
SINGLE_USER | RESTRICTED_USER | MULTI_USER
Controls which users may access the database. When SINGLE_USER is specified, only one user at a time can access the database. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. MULTI_USER returns the database to its normal operating state.
OFFLINE | ONLINE
Controls whether the database is offline or online.
READ_ONLY | READ_WRITE
Specifies whether the database is in read-only mode. In read-only mode, users can read data from the database, not modify it. The database cannot be in use when READ_ONLY is specified. The master database is the exception, and only the system administrator can use master while READ_ONLY is set. READ_WRITE returns the database to read/write operations.
<cursor_option>
Controls cursor options.
CURSOR_CLOSE_ON_COMMIT ON | OFF
If ON is specified, any cursors open when a transaction is committed or rolled back are closed. If OFF is specified, such cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.
CURSOR_DEFAULTLOCAL | GLOBAL
Controls whether cursor scope defaults to LOCAL or GLOBAL.
<auto_option>
Controls automatic options.
AUTO_CLOSE ON | OFF
If ON is specified, the database is shut down cleanly and its resources are freed after the last user exits. If OFF is specified, the database remains open after the last user exits.
AUTO_CREATE_STATISTICS ON | OFF
If ON is specified, any missing statistics needed by a query for optimization are automatically built during optimization.
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic shrinking.
AUTO_UPDATE_STATISTICS ON | OFF
If ON is specified, any out-of-date statistics required by a query for optimization are automatically built during optimization. If OFF is specified, statistics must be updated manually.
<sql_option>
Controls the ANSI compliance options.
ANSI_NULL_DEFAULT ON | OFF
If ON is specified, CREATE TABLE follows SQL-92 rules to determine whether a column allows null values.
ANSI_NULLS ON | OFF
If ON is specified, all comparisons to a null value evaluate to UNKNOWN. If OFF is specified, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.
ANSI_PADDING ON | OFF
If ON is specified, strings are padded to the same length before comparison or insert. If OFF is specified, strings are not padded.
ANSI_WARNINGS ON | OFF
If ON is specified, errors or warnings are issued when conditions such as divide-by-zero occur.
ARITHABORT ON | OFF
If ON is specified, a query is terminated when an overflow or divide-by-zero error occurs during query execution.
CONCAT_NULL_YIELDS_NULL ON | OFF
If ON is specified, the result of a concatenation operation is NULL when either operand is NULL. If OFF is specified, the null value is treated as an empty character string. The default is OFF.
QUOTED_IDENTIFIER ON | OFF
If ON is specified, double quotation marks can be used to enclose delimited identifiers.
NUMERIC_ROUNDABORT ON | OFF
If ON is specified, an error is generated when loss of precision occurs in an expression.
RECURSIVE_TRIGGERS ON | OFF
If ON is specified, recursive firing of triggers is allowed. RECURSIVE_TRIGGERS OFF, the default, prevents direct recursion only. To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.
<recovery_options>
Controls database recovery options.
RECOVERY FULL | BULK_LOGGED | SIMPLE
If FULL is specified, complete protection against media failure is provided. If a data file is damaged, media recovery can restore all committed transactions.
If BULK_LOGGED is specified, protection against media failure is combined with the best performance and least amount of log memory usage for certain large scale or bulk operations. These operations include SELECT INTO, bulk load operations (bcp and BULK INSERT), CREATE INDEX, and text and image operations (WRITETEXT and UPDATETEXT).
Under the bulk-logged recovery model, logging for the entire class is minimal and cannot be controlled on an operation-by-operation basis.
If SIMPLE is specified, a simple backup strategy that uses minimal log space is provided. Log space can be automatically reused when no longer needed for server failure recovery.

Important The simple recovery model is easier to manage than the other two models but at the expense of higher data loss exposure if a data file is damaged. All changes since the most recent database or differential database backup are lost and must be re-entered manually.
The default recovery model is determined by the recovery model of the model database. To change the default for new databases, use ALTER DATABASE to set the recovery option of the model database.

TORN_PAGE_DETECTION ON | OFF
If ON is specified, incomplete pages can be detected. The default is ON.
Remarks
To remove a database, use DROP DATABASE. To rename a database, use sp_renamedb.Before you apply a different or new collation to a database, ensure the following conditions are in place:
1. You are the only one currently using the database.
2. No schema bound object is dependent on the collation of the database.
If the following objects, which are dependent on the database collation, exist in the database, the ALTER DATABASE database COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action:
• User-defined functions and views created with SCHEMABINDING.
• Computed columns.
• CHECK constraints.
• Table-valued functions that return tables with character columns with collations inherited from the default database collation.
3. Altering the database collation does not create duplicates among any system names for the database objects.
These namespaces may cause the failure of a database collation alteration if duplicate names result from the changed collation:
• Object names (such as procedure, table, trigger, or view).
• Schema names (such as group, role, or user).
• Scalar-type names (such as system and user-defined types).
• Full-text catalog names.
• Column or parameter names within an object.
• Index names within a table.
Duplicate names resulting from the new collation will cause the alter action to fail and SQL Server will return an error message specifying the namespace where the duplicate was found.
You cannot add or remove a file while a BACKUP statement is executing.
To specify a fraction of a megabyte in the size parameters, convert the value to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5MB (1.5 x 1024 = 1536).
Permissions
ALTER DATABASE permissions default to members of the sysadmin and dbcreator fixed server roles, and to members of the db_owner fixed database roles. These permissions are not transferable.
Examples
A. Add a file to a database
This example creates a database and alters it to add a new 5-MB data file.
USE master
GO
CREATE DATABASE Test1 ON
(
NAME = Test1dat1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
GO
ALTER DATABASE Test1
ADD FILE
(
NAME = Test1dat2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
GO
B. Add a filegroup with two files to a database
This example creates a filegroup in the Test 1 database created in Example A and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default filegroup.
USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO

ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1

ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO
C. Add two log files to a database
This example adds two 5-MB log files to a database.
USE master
GO
ALTER DATABASE Test1
ADD LOG FILE
( NAME = test1log2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test2log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1log3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test3log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
GO
D. Remove a file from a database
This example removes one of the files added to the Test1 database in Example B.
USE master
GO
ALTER DATABASE Test1
REMOVE FILE test1dat4
GO
E. Modify a file
This example increases the size of one of the files added to the Test1 database in Example B.
USE master
GO
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
GO
F. Make the primary filegroup the default
This example makes the primary filegroup the default filegroup if another filegroup was made the default earlier.
USE master
GO
ALTER DATABASE MyDatabase
MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

3-Sql-Server Data Types:
Exact Numerics
Integers
bigint
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
int
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
smallint
Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
tinyint
Integer data from 0 through 255.
bit
bit
Integer data with either a 1 or 0 value.
decimal and numeric
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Approximate Numerics
float
Floating precision number data from -1.79E + 308 through 1.79E + 308.
real
Floating precision number data from -3.40E + 38 through 3.40E + 38.
datetime and smalldatetime
datetime
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
Character Strings
char
Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar
Variable-length non-Unicode data with a maximum of 8,000 characters.
text
Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
Unicode Character Strings
nchar
Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar
Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.
ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
Binary Strings
binary
Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary
Variable-length binary data with a maximum length of 8,000 bytes.
image
Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.