Description

دانشگاه تهران
پردیس دانشکدههای فنی دانشکده برق و کامپیوتر

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

مهلت تحویل:
۱۴۰۰/۱۰/۰۱

مجتبی بنائی
آنچه خواھید آموخت
هدف اصلی از این تمرین، آشنایی عملی با سه مفهوم زیر در بانک های اطلاعاتی رابط های است:

توابع : مشابه با مفهوم تابع در زبا نهای برنام هنویسی، بسته به ورودی ها، خروجی مناسب را تولی د میکند . این خروجی میتواند هر مقدار مجاز در SQL از جمله یک جدول (خروجی select) یا ی ک مقدار عددی باشد. مثلا در دیتابیس Northwind میتوانیم تابعی در پستگرس بنویسیم که شماره یک فاکتور ر ا گرفته، کل اجناس و یا قیمت پرداخت شده مشتری به ازای آنرا برگرداند .
تریگ ر : با انجام هر تغییری در دیتابیس، میتوانیم تریگری را فعال کنیم و کار بخصو صی را انجام دهیم. مث لا فرض کنید به ازای هر تراکنش مالی (برداشت یا واریز به حساب)، نیاز داریم به دلیل مسائل امنیتی، مقدار قبل ی حساب را در یک جدول جداگانه به نام، transaction_history به صورت خودکار ذخیره کنیم (عملیات Audit
. ( Log
این کا ر به راحتی توسط تریگرها قابل انجام اس ت و کافی است قبل از عملیات به روز رسانی حساب کاربر، تابعی که برای این منظور نوشت هایم را به کمک تریگری که روی این عملیات، تنظی م میکنیم، فراخوانی کنی م .
نکته : در دنیای واقعی تولید نر مافزار، تا حدامکان از نوشتن مستقیم منطق کسب و کار در دیتابیس اجتناب م یکنیمچون این امر، تست و تغییر برنام هها را بسیار مشکل م یکند. در صورت نیاز به این کار هم معمولاً با استفاده از ORM(کتابخان ههای واسط برای کار با دیتابیسهای رابط های) این مسایل را مدیریت م یکنیم که اگر نیاز به تغییر خود دیتابیسداشتیم و یا منطق برنامه در بخشی از کار، نیاز به تغییر داشت، مجبور به تغییر مستقیم کدهای نوشته شده در دیتابی سهاکه خارج از سیستم گیت و مدیریت پروژه و … است، نشویم .

توابع پنجرهای : تاکنون با توابع تجمعی مانند میانگین و ماکزیمم و بخصوص count آشنا شد هاید که ب ر روی مجموع های از سطرها کار کرده، یک مقدار را به ازای هر گروه برمیگردانند. اما در بسیاری از کاربردها ی امروزی، نیاز داریم که به ازای هر رکورد، مقداری را بر اساس گروهبندی مرتبط با آن رک ورد، محاسبه کنیم مثلا در
جدول نمرات ی ک دانشگاه، بتوانیم رتبه دانشجو در یک کلا س خاص، اختلاف نمره هر دانش آموز از نمره اول آن کلاس، میانگین نمره دانشجویان هم ورودی و هم جنسیت هر دانشجو در آن کلاس و .… را محاسبه کنیم. این کارها را با توابع تجمعی عادی نم یتوانیم م حاسبه کنیم و برای این منظور نیاز به توابع پنجر هایWindow Function داری م.
با توجه به اینکه هدف از این تمرین، آشنایی عمومی با این مفاهیم است، ای ن مطال ب را در قالب دو آموزش تک صفح های و انجام عملی دستورات آنها، فراخواهید گرفت که زمان زیادی را از شما نخواهد گرفت.

دستورالعمل اجرایی
تواب عوتریگرھا
برای آشنایی با دو مفهوم توابع و تریگرها در پستگرس، به آدرس زیر مراجعه کنید:
https://severalnines.com/database-blog/postgresql-triggers-and-stored-
function-basics
ابتدا کل آموزش را مرور کنید تا دستتان بیاید که قرار است چه کارهایی انجام دهید. سپس دیتابیس جدیدی د ر پستگرس ساخته و تک تک دستورات این آموزش را (با شروع از دستور CREATE TABLE person) انجام دهید.
به ازای هر دستور و خروجی آن، از صفحه اسکرین شات گرفته، یک توضیح کوتاه در مورد کاری که انجام میدهد در کنار این تصویر در گزارش این جلسه، بنویسید.
تواب عپنجرهای
برای آشنایی با مفهوم توابع پنجره ای که امروزه نقش مهمی در تولید گزار شهای تحلیلی برعهده دارن د، به این دو منبع آموزشی مراجعه کنید :
( دو بخش اول که رایگان است) https://learnsql.com/course/window-functions/ • yun.ir/ohbik5 •
سپس به دلخواه، سه دستور sql برای دیتابیس Northwind (موضوع تمرین چهارم) با استفاده از توابع پنجر های
بنویسید.
بخش امتیازی :
دو سوال زیر، بیست درصد به نمره این دستور کار اضافه خواهد کرد که توصیه م یکنم بخاطر بار آموزشی آنها، حتم ا انجام شو د .
س اول اول :
فرض کنید دیتابیس زیر را برای ذخیره کلیک های کاربران در یک سایت فروشگاهی طراحی کرده ای م و داد ههای زیر را هم درج نمود هایم (یک دیتابیس در پستگرس ایجاد کرده، این دستورات را اجرا کنید تا جدول مربوطه ایجاد شود) :
drop table if exists clickstream; create table clickstream ( eventId varchar(40), userId int, sessionId int, actionType varchar(8),
datetimeCreated timestamp
);
insert into clickstream(eventId, userId, sessionId, actionTy pe, datetimeCreated ) values
(‘6e598ae5-3fb1-476d-9787-175c34dcfeff’,1 ,1000,’click’,’202 0-11-25 12:40:00′),
(‘0c66cf8c-0c00-495b-9386-28bc103364da’,1 ,1000,’login’,’202 0-11-25 12:00:00′),
(’58c021ad-fcc8-4284-a079-8df0d51601a5′,1 ,1000,’click’,’202
0-11-25 12:10:00′),
(’85eef2be-1701-4f7c-a4f0-7fa7808eaad1′,1 ,1001,’buy’, ‘202 0-11-22 18:00:00′),
(’08dd0940-177c-450a-8b3b-58d645b8993c’,3 ,1010,’buy’, ‘202 0-11-20 01:00:00’),
(‘db839363-960d-4319-860d-2c9b34558994′,10,1120,’click’,’202 0-11-01 13:10:03′),
(‘2c85e01d-1ed4-4ec6-a372-8ad85170a3c1′,10,1121,’login’,’202 0-11-03 18:00:00′),
(’51eec51c-7d97-47fa-8cb3-057af05d69ac’,8 ,6, ‘click’,’202 0-11-10 10:45:53′),
(‘5bbcbc71-da7a-4d75-98a9-2e9bfdb6f925′,3 ,3002,’login’,’202 0-11-14 10:00:00′),
(‘f3ee0c19-a8f9-4153-b34e-b631ba383fad’,1 ,90, ‘buy’, ‘202 0-11-17 07:00:00’),
(‘f458653c-0dca-4a59-b423-dc2af92548b0′,2 ,2000,’buy’, ‘202 0-11-20 01:00:00’),
(‘fd03f14d-d580-4fad-a6f1-447b8f19b689′,2 ,2000,’click’,’202 0-11-20 00:00:00′);

حال توضیح دهید خروجی دستور SQL زیر چیست ؟ نقش دستور Case در آنرا بیان کنید .
with purchasingUsers as ( select userId, sum( case
when actionType = ‘buy’ then 1 else 0 end
) as numPurchases from clickstream group by userId
having numPurchases >= 1
),
movingUsers as ( select userId from geolocation group by userId
having count(distinct zipCode) > 1

دستورکار شماره ۵ دستورکار آزمایشگاه پایگاهداده
), userSessionMetrics as ( select userId, sessionId, sum( case
when actionType = ‘click’ then 1 else 0 end
) as numclicks, sum( case
when actionType = ‘login’ then 1 else 0 end
) as numlogins, sum( case
when actionType = ‘buy’ then 1 else 0 end
) as numPurchases from clickstream group by userId, sessionId
)
select usm.*
from userSessionMetrics as usm
join movingUsers as mu on usm.userId = mu.userId
join purchasingUsers as pu on usm.userId = pu.userId;
س اول دوم :
فرض کنید جدول زیر موجود باشد :

5