Shahriar Pirnia pirnia

Data Base پایگاه داده ها نمونه سوال و جمع بندی Shahriar Pirnia http://www.pirnia.net

مسائل نمونه در محیط عملیاتی دانشکده پایگاه داده با رابطه های Lesson (درس) ، Class (کلاس)، Grade(نمره)، Professor(استاد)، Group(گروه آموزشی)،Student(دانشجو) مفروض است. ساختار جداول و یک نمونه ازهر جدول و مفهوم هر رکورد در ذیل آمده است ، همچنین جهت وضوح بیشتر و بیان ارتباطات میان موجودیت ها و مشخص شدن کلیدهای خارجی و کلیدهای اصلی تعاریف جداول نیز در پایین آمده است. راهنمایی : وقتی دانشجو کلاسی را اخذ مینماید در ابتدای ترم به ازای هر کلاسی که اخذ کرده یک رکورد در جدول Grade اضافه می شود که ستون Grade آن null است . پس از پایان ترم نمره دانشجو در ستون Grade درج می شود.

Lesson درس Professor استاد Class کلاس (ارتباط بین موجودیتهای درس و استاد) Group گروه Grade نمره

دانشجو Student

سوال 1 : مشخصات دانشجویانی را بدهید که با استاد رضا احمدی در نیمسال اول 1382 درس پایگاه داده ها اخذ نموده اند . (صرفا با استفاده از SUBQUERY ) (می دانیم کد شناسایی استاد احمدی 85235 و کد درس پایگاه داده ها 7232 است . SELECT * FROM STUDENT WHERE id IN (SELECT STUDENTID FROM GRADE WHERE CLASSID IN (SELECT ID FROM CLASS WHERE PROFESSORID= '85235' AND LESSONCODE='7232' AND CLASSTERM='1' AND CLASSYEAR='82'))

سوال 2 : QUERY سوال قبل را صرفا با استفاده از JOIN بنویسید . SELECT STUDENT.* FROM STUDENT,GRADE,CLASS WHERE STUDENT.ID= GRADE.STUDENTID AND GRADE.CLASSID= CLASS.ID AND CLASS.PROFESSORID= '85235' AND CLASS.LESSONCODE='7232' AND CLASS.CLASSTERM='1' AND CLASS.CLASSYEAR='82'

سوال 3 : مشخصات کلاسهای گروه کامپیوتر را از سیستم حذف نمایید. (راهنمایی : می دانیم کد گروه کامپیوتر 11 است . ضمنا توجه کنید اساتید گروه کامپیوتر ممکن است دروسی از گروههای دیگر مانند دروس علوم پایه را نیز تدریس کنند.) DELETE FROM CLASS WHERE LESSONCODE IN (SELECT LESSONCODE FROM LESSON WHERE GROUPCODE='11')

سوال 4 : لیست متشکل از کد هر درس و تعداد کلاسهای آن درس در نیمسال اول 1382 را بدهید . SELECT LESSONCODE,COUNT(*) AS NUMBER روش اول : FROM CLASS GROUP BY LESSONCODE, CLASSYEAR, CLASSTERM HAVING CLASSYEAR=82 AND CLASSTERM=1 SELECT LESSONCODE,COUNT(*) AS NUMBERروش دوم : FROM CLASS WHERE CLASSYEAR=82 AND CLASSTERM=1 GROUP BY LESSONCODE

سوال 5 : در مورد کلاس با کد شناسایی 101 ، کد شناسایی استاد اشتباها 93522 درج شده . کد شناسایی استاد آن کلاس را به 85243 اصلاح نمایید . UPDATE CLASS SET PROFESSORID=85243 WHERE ID=101

سوال 6 : صرفا با استفاده از EXISTS یا NOT EXISTS مشخصات اساتیدی را بدهید که در نیمسال اول 1382 حداقل یک درس را تدریس نموده اند . SELECT PROFESSOR.* FROM PROFESSOR WHERE EXISTS (SELECT * FROM CLASS WHERE PROFESSOR.ID=CLASS.PROFESSORID AND CLASS.CLASSYEAR=82 AND CLASS.CLASSTERM=1)

جمع بندی :SQL تهیه شده در DB/2 تمام امکانات متعارف جبر رابطه ای و محاسبات رابطه ای را دارد . (SQL هم مبتنی است بر جبر رابطه ای و هم مبتنی است بر محاسبات رابطه ای . از این دیدگاه می توان گفت هم روشمند است Procedural و هم ناروشمند nonprocedural .) و ضمنا SQL اکمال رابطه ای دارد . (is relationally complete) زبانی دارای اکمال رابطه ای است اگر قدرت اشتقاق derivation آن هم ارز با قدرت جبر رابطه ای یا محاسبات رابطه ای باشد . یعنی هر رابطه ای که به کمک جبر رابطه ای یا محاسبات رابطه ای قابل اشتقاق باشد از universe رابطه ها توسط آن زبان نیز قابل اشتقاق باشد . UNIVERSE OF RELATION زبان Y با مکانیزم اشتقاق متفاوت Syntax متفاوت زبان X جبر رابطه ای یا محاسبات رابطه ای رابطه R مشتق شده همان R همان R از میان دو زبان که هردو اکمال رابطه ای دارند آنی که مکانیزم اشتقاق ساده تر و احکام کمتری داشته باشد بهتر است .

سطح ادراکی : DB2-SQL : DDL CREATE TABLE SP (S# CHAR(6) NOTNULL; P# CHAR(8) NOTNULL; QTY SMALLINT) PRIMARY KEY (S#,P#) FOREIGN KEY S# REFERENCE S . . . ON DELETE CASCADE; . . . FOREIGN KEY P#REFERENCE P . . . ON DELETE CASCADE; EXPAND ALTER TABLE tablename ADD Cal . Def ALTER TABLE S ADD ACCOUNT # INTEGER;

نکته اول : در این دستور NOTNULL نمی توان نوشت . زیرا مقدار در نظر گرفته شده برای آن NULL است . • نکته دوم : اجرای این دستور سبب کسترش فیزیکی جدول نمی شود . اجرای این حکم سبب اصلاح تعریف جدول و اصلاح Catalog می شود . گسترش فیزیکی جدول کی انجام می شود ؟ وقتی که یک عمل بازیابی صورت بگیرد و در پی آن سیستم سطر بازیابی شده جدول را گسترش می دهد . • محدودیتها : • هربار فقط یک ستون می توان Alter کرد . • امکان حذف ستون در Alter پیش بینی نشده و فقط ADD می کند . • دستور : DROP TABLE tablename . . . • تعریف جدول از کاتالوگ حذف می شود . فایل متناظر حذف می شود . کلیه فضاهای اشغال شده توسط جدول آزاد می شود . • تمام شاخصهای ایجاد شده روی Table نا معتبر می شود . تمام دیدهای تعریف شده روی جدول نا معتبر می شود . کاتالوگ متناسبا اصلاح می شود . • حکم CREATE INDEX : شاخص ای ایجاد می کند روی صفت یا صفات خاصه که طراح بگوید . • نکته : صرف قراردادن دستور PRIMARY KEY در تعریف جدول سبب ایجاد INDEX نمی شود .بلکه باید حتما دستور صریح CREATE INDEX را بزنید . CREATE [UNIQUE] INDEX indexname ON TABLENEME(Cal.name); CREATE INDEX SX ON S(S#) CRAETE INDEX SPX ON SP(S#,P#) در صورت قید این کلمه سیستم دیگر صفت تکراری برای صفت خاصه INDEX شده نمی پذیرد .

CREATE [UNIQUE] INDEX indexname ON tablename (column [order][,column [order]] . . .) CREATE INDEX X ON B (P,Q DESC , R) CREATE UNIQUE INDEX SN ON S (SNAME) CREATE XSC ON S(CITY) DROP INDEX indexname DROP INDEX SN ALTER TABLE tablename ADD column datatype ALTER TABLE S ADD DISCOUNT INTEGER

نکته : وجود حکم شاخص در سطح ادراکی قابل توجیه نیست . زیرا جدول پیاده سازی مفهوم ریاضی رابطه است در محیط انتزاعی و در محیط انتزاعی INDEX که یک استراتژی دستیابی Filing است نباید مطرح باشد . لذا اين حکم را باید نوعی حکم داخلی تلقی کرد . در واقع به طراح امکان می دهد تا در طراحی پایگاه فیزیکی دخالت داشته باشد . در همین حد که شاخص بزنیم یا نزنیم . پیاده سازی INDEX بصورت FREE می باشد ساختار چهارم ذخیره و بازیابی است Dynamic ایجاد می شود و Dynamic هم حذف می شود . (کلید اصلی امکانی است برای آدرس دهی Tuple . این آدرس دهی در سطح ادراکی است .) • گفتیم که اگر بخواهیم یکتایی مقدار آن ستون را تضمین کنیم حتما باید روی آن ستون Index ایجاد کنيم با UNIQUE OPTION این کار یعنی اختلاط دو مقوله Data definition و Access definition . و این دو از هم از نظر منطقی مجزا هستند . یکی در محیط انتزاعی است و یک در محیط فیزیکی فایلینگ . • با DROP INDEX شاخص محو می شود هرگاه لازم نباشد . فضای اشغال شده توسط درخت INDEX آزاد می شود که به این فضا INDEX SPACE می گویند و تمام ACCESS Plan هایی که مبتنی بر این INDEX تولید شده اند نامعتبر می شوند .

جمع بندی احکام سطح ادراکی DB/2 : (اختلاف نسخه های مختلف SQL با هم در قبول کردن یا نکردن دستور PEIMARY KEY یا FOREIGN KEY و tuple هایی است که برای صفات خاصه می پذیرد . احکام DDL سطح ادراکی در DB/2 : 1- ساده 2- محدود 3- تاحدی کافی (غیر از مفهوم میدان)(منظور از کلمه تا حدی بر می گردد به اشکالات ALTER) 4- دارای پویایی (استفاده پویا از احکام)(یعنی همزمان که پایگاه ار می کند user ها کار می کنند .) می توان سطح ادراکی را تغییر داد . (در صورتی که در پایگاههای Prerelational برای تغییر یک صفت خاصه باید ارتباط user ها با پایگاه قطع شود . سیستم بخوابد ، پایگاه Load و Reload شود . نکته مهم : پایگاه رابطه ای نیاز به طراحی دارد : تدریجی طراح می تواند هسته اصلي پایگاه را ایجاد کند و به تدریج گسترش دهد . انعطاف پذیر این طراحی منتها هر Table ای که CREATE می شود ، احتیاج به طراحی دارد .

سطح خارجی DB/2 : ( پیاده سازی مفهوم استاندارد VIEW ) سطح خارجی با نام VIEW پیاده سازی شده است . CREATE VIEW DROP VIEW شکل کلی : CREATE VIEW VIEW NAME [(view-cals)] AS SUBQUERY . . . [WITH CHECK OPTION] احکام DDL خارجی یادآوری :VIEW رابطه ای است مجازی که روی یک یا بیش از یک رابطه تعریف می شود . از این نقطه نظر که داده ذخیره شده متناظر خاص خود ندارد . نکته : تمام امکانات SELECT در تعریف VIEW می تواند مورد استفاده قرار گیرد . بنابراین تا آنجا که به تعریف VIEW مربوط می شود هر رابطه قابل اشتقاق توسط SELECT می تواند در تعریف VIEW بیاید . از این نقطه نظر می گوییم تعریف VIEW در DB/2 از مکانیزم بسیار بالایی برخوردار است .

مثال : CREATE VIEW C2SUP(SNUM,STA) AS SELECT S#,STATUS FROM S WHERE CITY=‘C2’; نکته مهم : وجود حکم SELECT در تعریف VIEW بدین معناست که سیستم بازیابی انجام دهد . یعنی حکم SELECT اجرا نمی شود . صرفا مکانیسم اشتقاق VIEW را مشخص می کند . تعریف VIEW در کاتالوگ سیستم نگهداری می شود به عنوان بخشی از شمای خارجی کاربر تا هرگاه لازم باشد سیستم به آن مراجعه کند . (وقتی کاربر بخواهد با پایگاه از دید خود کار کند . ) سیستم اسم VIEW را با userID ترکیب می کند با اسم یکتای داخلی برای VIEW تولید کند . یکتا : UID + viewname INTERNAL VIEW NAME EXTERNAL VIEW NAME نکته مهم : چون VIEW منطقا یک رابطه است بنابراین می توان روی VIEW ،VIEW تعریف کرد . مثال :CREATE VIEW GOOD-SUP AS SELECT S#,STATUS FROM C2-SUP WHERE STATUS>20

GOOD-SUP VIEW تعریف شده روی VIEW دیگر C2-SUP سطح خارجی VIEW تعریف شده روی جدول مبنا ارتفاع انتزاع سطوح انتزاعی سطح ادارکی SFILE . . . سطح داخلی

نکته : به شرحی که خواهیم دید همین مکانیزم تعریف VIEW روی VIEW امکانی است برای تامین استقلال داده ای منطقی DROP VIEW VIEWNAME تعریف VIEW را از کاتالوگ حذف می کند . عملیات در VIEW : 1- بازیابی : همان حکم SELECT که در سطح ادراکی داشتیم عمل می کند . (اصل وحدت احکام) زیرا VIEW بهرحال نوعی رابطه است . (دلیل اینکه چرا عمل می کند) 2- ذخیره سازی DML خارجی SELECT SNUM,STA FROM C2-SUP WHERE STATUS>20 مثال : این حکم عمل کننده در سطح خارجی باید تبدیل شود به حکمی عمل کننده در سطح ادراکی . سیستم برای این کار شرط داده شده در تعریف VIEW را ترکیب می کند با شرط یا شرایط داده شده در حکم بازیابی از VIEW و حکم عمل کننده در سطح ادراکی را می سازد . SELECT S#,STATUS FROM S WHERE CITY=‘C2’ AND STATUS>20 مثال : میزان overhead سیستم در این تبدیل بستگی دارد به حجم شرایط data وارد شده .

نکته : عملیات بازیابی از VIEW با استفاده از امکانات SELECT مشکل عمده ای ندارد . ( محدودیتهایی در سیستمهایی از جمله در DB2 وجود دارد . مثلا اگر در تعریف VIEW از Group By استفاده شود در حکم SELECT از VIEW دیگر نمی توان از Group By استفاده کرد . (یعنی Group By دو Level نمی پذیرد .) عملیات ذخیره سازی در VIEW :(VIEW UPDATING)(یعنی درج و تغییر و حذف در VIEW) همان DML عملیات ذخیره سازی : همان احکام UPDATE ، DELETE و INSERT عمل می کند . نکته مهم : عملیات ذخیره سازی در VIEW ها هنوز با مشکلاتی همراه است . به این معنا که تمام VIEW های متصور قابل به هنگام سازی نیستند . به شرطی که خواهیم دید . (البته آن دسته از VIEW هایی که قابل به هنگام سازی هستند تبدیل حکم از خارجی به ادراکی و سپس داخلی انجام می شود .) (یعنی INSERT در سطح خارجی را سیستم تبدیل می کند به INSERT در سطح ادراکی) سابقه امر : دیدهای چمبرلینی : دیدهایی قابل بهنگام سازی هستند که دو شرط زیر را داشته باشند : 1- روی جدول مبنا تعریف شده باشند . 2- هر سطر VIEW متناظر با یک سطر مشخص از جدول مبنا باشد و هر ستون VIEW متناظر با یک ستون مشخص و نامدار از جدول مبنا باشد . بنابراین از نظر چمبرلین دیدهایی که بصورت پیوند تعریف شده اند قابل UPDATE نیستند .

این یک View کاملا چمبرلینی است ( بدلیل وجود S# در View) CREATE VIEW V1 AS SELECT S#,CITY FROM S WHERE Status >15 ; S# Sname Status CITY S# CITY S1 … 10 C2 S2 ... 17 C3 S3 … 17 C3 S4 … 16 C3 S5 … 23 C1 S2 C2 S4 C2 S5 C3

مثال مثال UPDATE FROM V1 WHERE S#=S5; UPDATE V1 SET CITY =‘C13’ WHERE S#=‘S4’ ; UPDATE S SET CITY =‘C13’ WHERE S#=‘S14’ AND STATUS>15 ; DELETE FROM S WHERE S# =‘S15’ [ AND STATUS >15 ] INSERT INTO V1 VALUES (S14 ,C11) ; مثال منجر به درج سطر ناقص در S ، بروز پدیده Null Value در Database می شود که پدیده مطلویي نیست . اگر User بخواهد این سطر را ببیند به دلیل اینکه Status>15 معلوم نیست نمی تواند.برای کاربر V1 به عينيت در آوردن این سطر علیرغم درج در ِDatabase نا ممکن اسنت INSERT INTO S VALUE (S11,,’C11’)

نکته : ممکن است قبلا سطری با S11=S# وجود داشته باشد فاقد شرط تعریف View و طبعا خارج از دید کاربر V1 و این درج منجر به بروز مقدار تکراری برای S# می شود و اگر سیستم مفهوم کلید اصلی را داشته باشد اساسا این درج ناممکن است نکته : حتی دیدهای چمبرلینی هم مشکلاتی دارند ، به شرحی که دیدیم. CREATE VIEW V3 AS SELECT S* ,P* FROM S, P WHERE S.CITY=P.CITY ; CREATE VIEW V1 AS SELECT S# , CITY FROM S; CREATE VIEW V3 [DISTINCT] AS SELECT STATUS , CITY FROM S;

CREATE VIEW V4 AS SELECT P#,SUM(QTY) FROM SP GROUP BY P#; عملیات در V 1 صرف نظر از مشکلات ظریف دیده شده ، مشکلی ندارد.اصطلاحا می گوییم V1 دید حافظ کلید است . ( آقای چمبرلین اصلا این ایده را مطرح نکرد ، زیرا مفهوم کلید اصلا در سیستم (R) نبوده است . S# کلید S است و در VIEW وجود دارد.دیدهای حافظ کليد قطعا چمبرلینی هستند. جمع بندی : VIEW های حافظ کلید مشکل اساسی ندارند(عملیات تغییر امکان پذیر است) Key Preserving دید V2 غیر حافظ کلید Non Key Preserving است . در بهنگام سازی مشکل دارد ،زیرا در حالت کلی سیستم نمی داند عمل درخواست شده روی یک سطر View در کدام شطر از جدول مبنا اعمال می شود

Shahriar Pirnia pirnia