تحقیق مقاله دستور کار آزمایشگاه پایگاه داده ها

 

بسمه تعالی

 

 

 

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

 

 

آزمایش 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<>

در جدول تعریف شده بالا ،برای هر نویسنده دو شماره ازنوع 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کرده اید کلیک کنید وپایگاه داده های موجود روی این سرور را مشاهده کنید .چه          پایگاه داده هایی روی این سرور به ثبت رسیده است؟