طراحی و پیاده سازی
مدیریت
1) Design and Implementation
2) Administrator
بانکهای اطلاعاتی به دو دسته تقسیم بندی می شوند که عبارتند از:
1) Desktop Access-paradox, foxpro-Dbase
2) client/server sql-oracl/
مدل Desktop برای کامپیوتهای single پیش بینی شده است. به این شکل که برای یک مجموعه کوچک که با حجم زیادی از اطلاعات روبرو نیست می تواند نیاز ما را برطرف کند.
بانکهایی مانند Access شاخص ترین عضو خانواده Desktop به حساب می آید. در این نوع از بانکهای اطلاعاتی، بانک اطلاعاتی ساخته شده بر روی یک hard قرار گرفته و برای دیگران قابل دسترسی میباشد. اگر بخواهیم برای کاربران مختلف که در این سیستم کار می‌کنند محدودیت های دسترسی تعریف کنیم با مشکل مواجه خواهیم شد چرا که گزینه های امنیتی در آن نسبت به مدل client/server بسیار بسیار ضعیف می باشد. البته در صورتیکه یک محیط شبکه داشته باشیم می توانیم فایل ساخته شده Access خود را در داخل فایل server قرار دهیم و client ها به آن دسترسی داشته باشیم.
اگر بخواهیم از این بانک که نگهدارنده اطلاعات کارگزینی کارمندان و اطلاعات حقوق و دستمزد آنها باشد. گزارشی به شکل زیر تهیه کنیم لیست کلیه کارمندان را نمایش دهید که در دپارتمان 1 مشغول کار هستند و می خواهیم جمع حقوق کارمندان این بخش را مشخص کند.
حداکثر سرعت در شکبه
این مشکلات می تواند از زوایای مختلف بوجود بیاید:
1- تمام پردازش ها در داخل client انجام می شود.
2- در این حالت نیازمند سخت افزار قوی برای client ها و وجود یک خطوط ارتباطی پرسرعت.
3- از آنجایی که هارد server قابل دسترس برای کلیه client ها می باشد امنیت اطلاعات server از بین خواهد رفت.
4- اگر در این مدل نیاز به up date کردن برخی از رکوردها داشته باشیم بعد از آنکه رکوردها به client آمدند و تعدادی از آنها up date شدند باید بر روی هارد server ذخیره گردد.
اگر در حین انجام عمل save سیستم با قطع برق مواجه گردد و یا خطوط ارتباطی قطع شوند برخی از رکوردها در فایل بانک در server ، up date شده و برخی دیگر بدون تغییر باقی خواهد ماند. هیچ راهی برای شناسایی اینکه کدام یک تغییر یافته اند وجود ندارد و نمی توان وضعیت بانک را به حالت اولیه آن برگرداند.
Select sum (Salary) From Employees
Where Dep=1
در قالب نوع client/server یکپارچگی اطلاعات تضمین می شود به این شکل که در هنگام عمل up date چنانچه برق قطع شود با روشن شدن مجدد server بررسی می شود که آیا مراحل قبلی با موفقیت به پایان رسیده است یا خیر.
برای حفظ یکپارچگی، اطلاعات sql server قبل از ثبت قطعی اطلاعات کلیه اعمال را در فایل log خود انجام می دهد.
Sql server دارای دو نوع فایل می باشد:
فایل داده با پسوند Mdf . 1) Date file
فایل تراکنش با پسوند Ldf . 2) log file
فایل های تراکنش Transaction log file
زمانیکه سیستم روشن می شود ابتدا log file مورد ارزیابی قرار می گیرد. اگر در فایل log با دستور commit مواجه شود یعنی کلیه اعمال قبلی به شکل کامل انجام شده اند و آماده اند برای آنکه بر روی date file نوشته شوند. در این حالت log file بر روی data file ثبت خواهد شد.
اگر با دستور commit مواجه نشود عمل roll Back اتفاق می افتد حال می توانیم با این شرایط وضعیت سیستم را به شرایط قبلی بازگردانیم. تمام حالتهای انجام شده به وضعیت سابق بر میگردد و بانک ثبات خود را حفظ خواهد کرد یکپارچه باقی می ماند.
انواع sql های قابل نصب:
1) sql server enterprise
روی ویندوز 2000 و 2003 نصب می شود یعنی روی ویندوزهای 32 بیتی و 64 بیتی قابل نصب هست.
بهترین خاصیت آن امکان داخلی آن clustering می باشد یعنی 4 یا 8 کامپیوتر را می توان بهم وصل کرد.
4- نحوه
ویرایش استاندارد 2) Standard Edition
روی ویندوز 2000 و 2003 نصب می شود و تفاوتش با sql اولی این است که امکان clustering را ندارد.
Sql 2000 service pack 3a
Win 2000 service pack 4
Win 2003 service pack 1
3) sql server personal edition
روی ویندوز xp نصب می شود.
Spl server , client tools
روی ویندوز CE کامپیوترهای جیبی (packet pc) نصب می شود.
نصب sql :
بعد از نصب sql server و فعال شدن service manager به سراغ interprise manager می رویم تا بتوانیم به کمک آن به table ها دسترسی یابیم. اگر اثری از sql نصب شده در sql server group وجود نداشت بر روی آن right click کرده گزینه new registration را انتخاب می کنیم. با این کار sql server شناسایی خواهد شد. سپس آن را باز کرده و لیست data base ها از folder data base کلیه بانکهای موجود را مشاهده خواهیم کرد. حال بانکهای tempdb-msdb-model-master به عنوان بانکهای سیستیم و pubs , northwind مشاهده می شود که این دو data base (بانک) نمونه‌هایی هستند که ماکروسافت آنها را برای آشنایی بهتر ترویج کرده است.
برای غیر فعال کردن بانکهای سیستمی وtable های سیستمی که در هر بانکی (data base های) وجود دارند می توانند بر روی نام data base ، کلیک راست کرده و گزینه edit sql server registration را انتخاب کنیم و در این پنجره گزینه show system … را غیر فعال کنیم تا هیچ یک از موارد سیستمی به نمایش در نیاید.
از این بانکهای سیستمی sql بانک master اصلی ترین بانک sql می باشد این بانک در پشت صحنه کلیه عملیات sql را مدیریت می‌نماید و هر نوع آسیب به این data base باعث از کارافتادن sql خواهد شد.
بانک model db بانکی است که از روی آن تمام data base های جدید ساخته می شود و در واقع طرح و الگویی جهت سایر data base هاست.
بانک temp db به ما کمک می کند که بر روی انجام عملیات موقت از این بانک اطلاعاتی استفاده نماییم زمانیکه مطمئن به انجام قطعی عملیات نیستیم می توانیم از بانک temp db استفاده کنیم هر بار sql فعال می شود temp db خالی خواهد شد.
Msdb بانکی است که وظیفه انجام کارهایی را که تحت عنوان job شناخته می شوند را بر عهده دارد. این بانک با سرویس sql server agent کار می کند.
نکته:
اگر sql در حالت stop باشد یعنی sql سرویس دهی نمی کند و اگر در حالت pause باشد باعث می شود که هر کسی که قبلاً وارد سیستم شده و در حال کار با sql است بینام خطا دریافت نکرده ولی فرد جدید نمی تواند وارد سیستم شده و کاری انجام دهد.
Stop یعنی توقف بطور کامل و sql سرویس دهی می کند ولی pause یعنی توقف لحظه ای.
اگر بخواهیم به هر دلیلی به عنوان admin بخواهیم تغییری در sql بدهیم باید sql را از حالت multi user خارج کرده و به حالت single user درآوریم و برای اجرای مجدد start را می زنیم و سرویس دهی sql دیگران قرار می گیرد.
توسط interpriser ابزارهای مدیریتی خواهیم داشت.
روی sql server کلیک می کنیم و آن را باز می کنیم.
Sql server\ service manager \ interprise manager \ registration \ wizard\ register sql server wizard
اگر اسم sql را ندیدیم باید آن را ثبت کنیم. مثلاً pcl
Right click \ new sql server registration
Right click \ edit … \ registed sql server properties
Sql هایی که سیستمی بودند پنهان شدند.
نکته:
اگر master آسیب ببیند دیگر sql کار نمی کند.
نحوه ایجاد data base :
بر روی data bases ، کلیک راست کرده گزینه new data base را انتخاب می کنیم. اسم data base را وارد می کنیم.
در قسمت collation name برای انتخاب زبان code page می باشد. اگر در هنگام نصب گزینه collation به درستی تنظیم شده باشد در این جا بر روی انتخاب server default تنظیم خود را قرار می دهیم. در غیر اینصورت می توانیم از موارد موجود Arabic CS-AI برای زبان فارسی انتخاب نمائیم.
اگر data base ای را حذف کنیم دیگر آن data base قابل برگشت نیست.
مثال:
اسم کامل فیزیکی Employces-Data.MDF
با حجم 1mG بر روی hard ساخته می شود.
تحقیق:
Group file چیست؟ برای چه فایل ها را دسته بندی می کنیم؟ آیا می‌تواند مقداری غیر از primary بگیرد؟
دسته:
فقط اولین فایل داده ساخته شده پسوند MDF دارد و فایل دوم به بعد NDF پوندش می باشد.
در قسمت properties یک قسمت automatic … وجود دارد که اگر تیک نداشته باشد گزینه های آن غیر فعال خواهد بود و اگر تیک داشته باشد می توان حجم و موارد دیگر را به دلخواه تغییر داد.
برای مثال باشد اگر
بعد از پر شدن ، مگ مگ اضافه می شود.
نامحدود unrestricted
محدود restricted
در قسمت maximum file size می توان محدودیت قرارداد می توان محدودیت قرار دارد.
قسمت transaction فایل ساخته شده با پسوند LDF است.
تحقیق:
در این قسمت log file (با پسوند LDF) چرا group file ندارد؟
در زمان ساخت data base در tab دوم فایل data را تنظیم می کنیم و در tab سوم فایل log را تنظیم می کنیم. در قسمت پایین می توانیم نحوه رشد فایل log file , data را مشخص سازیم.
نحوه رشد می تواند برحسب mB یا percent (درصد) باشد. در قسمت restricted حجم نهایی فایل مشخص می شود. اگر unrestricted انتخاب شود برای حجم فایل محدودیتی قائل نخواهیم شد.
هر بانک اطلاعاتی دارای یک log file است که محل نگهداری آن همان محل فایل data می باشد. log file تاریخچه تغییرات اطلاعاتی است که در سیستم روی می دهد. هر data ای که بخواهیم تعویض شود در log file نوشته می شود هر عمل حذف data و یا تغییر data باعث می‌شود در log file ذخیره گردد بطور کلی log file همواره در حالت بزرگ شدن می باشد.
فوائد log file :
1- عمل پیگیری تراکنش ها (Transaction Tracking) برای پیگیری تراکنش ها مورد استفاده قرار می گیرد. یعنی در هر واحد عملیاتی در data base که به آن تراکنش می گوییم می توانیم به کمک log file در صورت انصراف از تغییرات انجام شده وضعیت بانک را به حالت قبل از تراکنش برگردانیم.
2- امکان data base recovery یا بازیابی data base :
اگر data base به هر دلیلی دچار مشکل گردد و فایل MDF آن آسیب ببیند می توانیم به کمک Log عمل recovery را انجام دهیم.
3- restore to the point of crash :
عمل بازیابی اطلاعات یعنی restore قابل انجام خواهد بود به شرط آنکه از log file ، back up گرفته شده باشد.
در شرایطی که حتی server دچار مشکلات جدی گردد و یا بطور فیزیکی از بین برود اگر محل نگهداری log در جای دیگری خارج از آن server پیش بینی شده باشد به راحتی می توانیم، اطلاعات از بین رفته را برگردانیم.
عمل back up در sql به دو صورت قابل انجام است:
1) complete back up
2) log back up
لبه option :
Restricted access برای محدود کردن دسترسی می باشد که با انتخاب آن گزینه های زیر فعال می شود.
1) members of db-owner , db creator or …
Db-owner مالک بانک اطلاعاتی
میدان بانک اطلاعاتی
نکته:
کاربر معمولی جزء این گروه (میدان بانک اطلاعاتی) نمی باشد.
محدود به یک کاربر 2) single user
Single user برای مواقعی است که می خواهیم تنها به شکل single (منفرد- تک) با این data base کار کنیم. گاهی مواقع لازم است که دسترسی به بانک از حالت چند کاربره خارج شود و به حالت تک کاربره درآید.
با انتخاب Read only کل بانک اطلاعاتی حالت فقط خواندنی پیدا می‌کند. مثلاً اگر بخواهیم بانک اطلاعاتی مربوط به حسابداری برای سال قبل را به شکل فقط خواندنی درآوریم تا جلوی هر تغییری در آن گرفته شود آن بانک را می توان Read only کرد.
Recovery :
Model سه حالت دارد: Bulk log-simple-full
اگر ویندوز Xp باشد در حالت simple خواهد بود و اگر ویندوز 2000 یا 2003 باشد در حالت full قرار خواهد گرفت.
حالت full :
در این حالت همه تغییرات در فایل log ذخیره می شود و تا وقتی که از log ، back up گرفته نشود فایل log حذف نمی گردد.
حالت bulk :
شبیه full است با این تفاوت که برخی از دستورات در sql مانند دستور BCP/DTS دستوراتی هستند که اطلاعات پرحجم تولید می‌کنند. کسانی که جابجایی اطلاعات یعنی از بانکی مانند oracle ، access و یا حتی sql و ... اطلاعات را به محل دیگر و یا به داخل خود sql منتقل می نمایند باید روی گزینه bulk loge انتخاب نمایند.
حالت simple :
در این حالت تمام تغییرات در log نوشته می شود اما با هر بار عمل commit یا roll back از log حذف می گردد در persona نوع پیش فرض هستند در این روش log file بزرگ نمی شود.
قسمت setting :
Ansi null default :
این گزینه رفتار ایجاد جدول در sql را تغییر می دهد. برای فیلدهای هر جدول می توانیم تعیین کنیم که مقدار آن null باشد یا خیر.
با انتخاب این گزینه فیلدها بطور اتوماتیک null خواهد شد. وضعیت پیش فرض هر فیلد بدون استفاده از این گزینه not null خواهد بود.
Auto close :
Data base بطور خودکار بسته می شود.
Recursive triggers :
یعنی در حالت عادی trigger ها بازگشتی نمی باشند. با این گزینه می‌توانیم trigger از نوع بازگشتی ایجاد کنیم:
از بین بردن فضای خالی
Auto shrink :
باعث می شود فضای خالی در data base از بین برود. Auto shrink زمانی کار می کند که حجم خالی به میزان 25% کل فضای data base رسیده باشد.
Auto shrink برای data base هایی لازم است که admin نداشته باشد.
: Auto create statistics , auto update statistics
اطلاعات آماری مربوط به update کردن
به کمک این گزینه ها آمار فراوانی index های sql مشخص می شود و بطور اتوماتیک با هر بار اضافه کردن record تعداد کلیدهای index مورد شمارش واقع می شود.
این آمار به این دلیل لازم و مهم است که sql تشخیص دهد که گزارش خود را بر حسب index انجام دهد یا خیر.
: Torn page detection صفحات پاره پاره پاره Torn
وسط این گزینه وجود صفحات پاره مورد شناسایی قرار می گیرد. واحد خواندن و نوشتن در page , sq می باشد که حجم آن 8kB است.
اگر در هنگام کار در زمان نوشتن اطلاعات sql دچار مشکل شود یا برق قطع گردد صفحات پاره بوجود می آیند که با run شدن مجدد sql با فعال بودن این گزینه torn page ها مورد شناسایی قرار خواهند گرفت.
: Use quoted identifiers
در حالت معمولی رشته ها (مجموعه ای از کاراکترها) در sql در داخل ‘ ‘ و یا “ “ گذاشته می شوند.
“Ali” یا ‘Ali’ : مثال
در صورت فعال بودن این گزینه عبارت را در “ “ هم می توان قرار داد.
نکته:
اگر گزینه تیک دار باشد از هر ‘ ‘ و “ “ می توان استفاده کرد ولی اگر تیک دار نباشد فقط از ‘ ‘ استفاده می کنیم.
اگر جدولی تعریف شده باشد که نام آن شامل space باشد مانند:
Order – details حتماً باید در داخل “ “ قرار گیرد.
نکته:
در صورتیکه این گزینه تیک نداشته باشد برای مورد خطاب قرار دادن جدول های space دار از [ ] استفاده می شود.
: Compatibility
برای سازگاری version های مختلف sql (قدیمی) با 2000
نکته:
برای دسته بندی فایل ها از file group استفاده می کنیم.
مثل آدرس tel- محل خدمت و ...
کمتر دچار تغییرات می شود (صدا- تصویر)
برای binary می توان از یک هارد مستقل از نوع TDE استفاده کنیم و برای text می توان از هارد SCSI که گران قیمت است استفاده کنیم با این کار از سرعت بالایی برخوردار خواهیم بود. ولی هارد TDE ارزای قیمت است.
File group زمانی قابل استفاده است که در سیستم server بیش از یک هارد فیزیکی داشته باشیم.
File group برای partion های گوناگون کاربردی ندارد.
اگر ما دو تا هارد داشته باشیم و آنها را در یک file group قرار دهیم باعث می شود که عمل نوشتن و خواندن بر روی هر دو هارد بطور همزمان انجام شود یعنی موازی خواندن و موازی نوشتن.
Page (8 kB) واحد نوشتن
ولی اگر دو هارد داشته باشیم سرعت بالاتر می رود چون اطلاعات در هر دو hard نوشته می شود. دو page بطور همزمان نوشته و خوانده و پر می شود.
ولی اگر در یک file group باشد و یک hard داشته باشیم یک page که دارد پر می شود باید صبر کرد که در page بعدی پر شود.
File group و کاربرد آن:
کاربرد اول file group امکان موازی نوشتن و موازی خواندن اطلاعات بر روی دو هارد می باشد.
از آنجایی که اطلاعات بصورت page ذخیره می شود و page ها ظرفیت 8kB دارند داده ها ذره ذره بر روی هارد نوشته نمی شوند بلکه page ها باید پر شوند آنگاه دستور نوشتن از RAM بر روی هارد صادر خواهد شد که به این عمل Check point می گویند.
اگر دو هارد فیزیکی در یک file group قرار داشته باشد می توانیم موازی خواندن و موازی نوشتن بر روی آنها را بطور همزمان انجام دهیم.
File gioup زمانی قابل پیاده سازی است که چندین هارد فیزیکی در اختیار داشته باشیم.
کاربرد بعدی file group جدا کردن انواع داده ها از یکدیگر میباشد به این شکل که می توان log file را در یک محل دیگر بر روی هارد دیگر قرار دهیم تا اگر اطلاعات data دچار مشکل شد عمل بازیابی راحت تر صورت گیرد و دیگر اینکه می توانیم داده های text را از صدا و تصویر تفکیک نموده و جهت صرفه جویی در هارد مصرفی به جای هارد SCSI برای تصاویر و اطلاعات Binary از هارد TDE استفاده کنیم.
: Query analyzar
واحد برحسب مگابایت MG Syntax
Create database database-name
[on
[< file spec > [,…n]]
[, < file group > [,…n]]
]
[log on {< file spec > [,... n ]}]
[collate collation name ]
[for load I for ATTach]
< file spec > :: =
[primary ]
([Name = logical file name,]
File name = ‘ on file name]
[, size = size]
[, MAX size = {max size | unlimited}]
[, file growth = growth increment ]) [,… n]
< file group > :: =
File group file group name < file spec > [, …n]
تحقیق:
دستورات data base که گفته شد هر کدام چه کاربردی دارند؟
Primary گفته شد. دستورات بالای primary تحقیق شود.
برای تغییر نام:
Create data base new data base
alter data base new data base
modify name = my data base
با استفاده از دستور alter data base را می توانیم نام data base را تغییر دهیم.
هر دو دستور را با هم می نویسیم برای تغییر نام و بعد کلید F5 برای اجرای دستورات استفاده می شود.
ERD:
Entity relationship diagram
هر موجودیت در یک table قرار می گیرد. تشخیص موجودیت ها یکی از مشکل ترین کارها در data base است چون یکسری از موجودیت ها آشکار و بعضی از موجودیت ها پنهان می باشند.
انواع ارتباطات:
یک به یک one to one
یک به چند one to many
چند به چند many to many
نکته:
ارتباط بین دو موجودیت تنها یکی از سه حالت ارتباط می تواند باشد.
ارتباط چند به چند
و انتخاب اینکه رابطه بین موجودیت ها کدامیک از سه حالت ارتباطات بستگی به محیط عملیاتی که در آن قرار داریم.
اگر تشخیص دادیم که یک ارتباط بین دو موجودیت چند به چند است آن قابل پیاده سازی نمی باشد.
باید ارتباط چند به چند را تبدیل به ارتباطات یک به چند کنیم تا بتوان آنها را پیاده سازی کرد.
برای این کار از یک table ثانویه استفاده می کنیم برای تبدیل ارتباط چند به چند به چند ارتباط یک به چند
تشخیص موجودیت ها یکی از مهمترین موضوعاتی است که در یک بانک اطلاعاتی قابل انجام می باشد. هر آنچه که بتوان در داخل یک جدول قرار گیرد به عنوان یک موجودیت تعریف می شود.
مثلاً خدمات آموزشی دانشگاه می تواند شامل موجودیت هایی مانند:
1- دانشجو 2- درس 3- استاد 4- ترم 5- کلاس 6- رشته تحصیلی و ... باشد.
و یا در سیستم فروش می توانیم موجودیت هایی به شرح زیر داشته باشیم:
1- کالا 2- مشتری 3- فروشنده 4- انواع فروش 5- انواع تخفیفات 6- شرکت حمل و نقل 7- انبار 8- حواله 9- فاکتور و ...
بین موجودیت ها ارتباط برقرار می شود که شامل: یک به یک، یک به چند و چند به چند است.
اولین کار در هر data base ای تشخیص موجودیت های آن و سپس تشکیل شده است نگهداری کرد.
در این حالت از آنجایی که در data base های نوع relational نمی‌توان رکوردهای تکراری درج کرد معمولاً یکی از فیلدها کلید اصلی تعریف می شود. (primary key)
کلید اصلی می تواند به شکل ذاتی یا تصنعی ساخته شود. ذاتی بودن کلید مانند شماره دانشجویی – کد پستی – شماره کارمندی و ... می باشد. این کدها اصطلاحاً غیر ماشینی تعریف می شوند.
اما کلیدهای تصنعی کدهای بوجود آمده توسط ماشین برای هر رکورد خواهد بود.
مانند شماره ردیف – اعدادی که بصورت auto number در جدول تولید می شوند.
دو حالت دارد:
Primary key
Auto number در access را در sql به نام indentity می‌شناسیم.
ویژگی primary key :
1- یکتا بودن
2- not null بودن
همواره primany key باید این ویژگی را داشته باشد.
کلید خارجی
F.K forien key
کد پرسنلی 1
کد پرسنلی p.k
کد فرزند نام
نام فرزند نام خانوادگی
نام خانوادگی سال استخدام
جنسیت تاریخ تولد
سال تولد .
.
.
.
.
.
نکته:
در ارتباط فیلدهای مربوط در دو جدول باید از نظر نوع و اندازه یکسان باشند بهتر است نام آنها نیز یکی باشد اما این قانون قطعی نیست.
هم نوع فیلد و هم اندازه فیلد یکسان باشند.
: Forien key
کلید خارجی فیلد نظیر primary key در جدول سمت بی نهایت میباشد و به ما اجازه می دهد که ما بتوانیم ارتباط این دو جدول را برقرار کنیم.
همواره forein key در قسمت بی نهایت و primary key در سمت یک تراز خواهد گرفت.
کلید اصلی P.k برای یکتا یا unipue کردن رکورد در سمت 1 در جدول MASTER
کلید خارجی F.K در جدول Details (جزئیات) برای ایجاد ارتباط با جدول MASTER هم نوع و هم سایز با کلید اصلی جدول MASTER
که پرسنلی غیر قابل تکرار است.
ارتباط حلقه ای
ارتباط چند به چند
این حلقه باید شکسته و بهینه شود.
نکته:
در این جدول مشخص نمی توان کرد که کدام فروشنده به مشتری کدام کالا را فروخته و یا مشتری مورد نظر ما از کدام فروشنده کالا خرید کرده است.
مثال: در صورت وجود داشتن 3 موجودیت دانشکده، گروه و استاد ارتباطات مابین را تشخیص دهید.
نکته:
در صورت چند به چند بودن باید شکسته شود.
قوانین نرمال سازی:
قانون اول:
قانون 1 نرمال از درج فیلدهای تکراری در جدول خودداری شود.
مشخصات ثابت مشخصات متغیر
کد فاکتور 1
کد فاکتور
کد فروشنده کد کالا
کد مشتری مقدار
تاریخ فاکتور قیمت
.
.
. تخفیف
قانون دوم:
قانون 2 نرمال می گوید مقام فیلدهای موجود در یک جدول باید با P.K آن جدول در ارتباط باشد.
نرمال نیست و باید نرمال شود.
3 تا جدول باید بر این درست کنیم.
کد فاکتور
کد مشتری
کد فروشنده
نام مشتری
آدرس مشتری
تلفن مشتری
نام فروشنده
فامیل فروشنده
.
.
.
قانون سوم:
همان قانون 2 نرمال می باشد ولی به شکل گسترش یافته. می گوید که اگر جدولی دارای فیلد P.K مرکب باشد تمامی فیلدهای موجود در آن جدول باید فیلدهای P.K در ارتباط باشند.
جدول کلاس جدول درس
کد درس
کد کلاس کد درس
عنوان درس
P.Kکد استاد واحد عملی
کد مرکز آموزش
شماره کلاس
روز تشکیل کلاس
P.Kساعت تشکیل
P.Kتاریخ تشکیل واحد نظری
تمرین:
آموزشگاهی که کار خدماتی آموزشی کامپیوتر انجام می دهد می‌خواهد کلیه امور خود را مکانیزه کلیه جدول ها شناسایی شود و ارتباطات بین آنها شناسایی شود. نمودار ER
تمرین:
قوانین چهارم و پنجم نرمال سازی تحقیق شود.
نکته:
از کلیه فیلدهای محاسباتی که از انجام عملیاتی بر روی سایر فیلدها به دست می آیند خودداری نمایید.
کد کالا
قیمت کالا
تعداد
قیمت کل کالا
Select KALA name , price , unit , price , price * unit
As total price from KALA
انواع داده ها در : SOL
Field name Data type Exact Numeric
اعداد دقیق:
1) bigint -2^63 الی 8byte
2) int -2^31 الی 4byte
3) small int -2^13 الی 2byte
4) tiny int 0-255
00000000
11111111
الی 1byte
5) bit یا 1
00000000
00000001 1byte
Decimal and Numeric
برای مدل و نمره های درس بکار می رود. 1) Decimal
هر دو به یک معنا هستند و فرقی ندارند. 2) Numeric
زوج اعداد سیال:
^38-1 1 تا ^38+1 -1
نکته:
تا 38 رقم دقت می تواند داشته باشد.
Decimal (p,s)
مثال: Decimal (20,5)
ظرفیت Precision
5 1-9
9 10-19
13 20-29
17 30-38
DATA TYPE
Money and small money پولی – مای
1) money 8 byte
2) small money 4byte
نکته:
برای پول ایرانی نمی توانیم از این استفاده کنیم و باید از integer استفاده شود.
اعداد تقریبی: Approximate numeric
1) float -1.79E+308 (-1.79 10308)
2) Real 1.79E+308-1 (1.79 10308)-1
n digit byte
Float (n)
1-24 7 4 read
25-53 15 8 flaot
جدول سازی
محل تعریف فیلدها و نوع
Field properties
ویژگی ها
Data time:
از اول ژانویه 1753 تا آخر دسامبر 9999
Small date time:
از اول ژانویه 1900 تا جون 2076
تاریخ 8 بایت است بدون در نظر گرفتن علامت
برای تاریخ شمسی کاراکتری
تابع
دو روش برای مقادیر رشته ای (text) در کامپیوتر پیاده سازی شده است.
1) ANSI
روش کدگذاری ASCII می باشد یعنی برای هر کاراکتر یک بایت در نظر گرفته می شود.
علائم کوچک و بزرگ
2) Unicode
برای هر کاراکتر دو بایت استفاده می شود.
65535 تا
Books online\index\data type char\data types
در SQL دو دسته داریم:
طول ثابت 1) char
از کدگذاری ASCII استفاده می شود. طول تغییر Varchar text
2) n char
از کدگذاری Unicode استفاده می شود. n varchar
n text
n char طول ثابت با Unicode
n varchar طول متغیر با Unicode
تفاوت varchar , char
طول ثابت fixed length ثابت char
طول متغیر variable length متغیر varchar
Other data types:
مراحل ساخت data base :
وارد قسمت data base می شویم و برای مثال employee و سپس کلیک راست می کنیم و گزینه new data base را انتخاب می کنیم.
در این پنجره 4 قسمت داریم:
ستون اول که نام فیلد را در آن می نویسیم.
در data type نوع فیلد را مشخص می کنیم.
در قسمت length مشخص می شود چه حجم این ستون اشغال شده.
در قسمت allow nulls آیا اجباری در ورود اطلاعات هست یا خیر.
سپس کلیک کرده و به ردیف پایین می رویم.
را برمیداریم Varcar l0 allow null N Name
Emp fomily Varcar 15
Department name char 15
Address char 30
Telephon int 4
بعد از انتخاب آنها کلید p.k را انتخاب کرده و بعد save می کنیم و برای table نام انتخاب می کنیم.
سپس برای table دوم راست کلیک کرده و مثل قبل برایش مشخصات وارد می کنیم و برای برقرار شدن این ارتباط بین دو جدول باید فیلد مشترک داشته باشیم.
اگر قرار باشد در جدول فیلد جدیدی بسازیم روی جدولی که ساختیم راست کلیک کرده گزینه design table را انتخاب کرده و فیلد مورد نظر را بوجود می‌آوریم.
طراحی اولیه جدول design table
Open table:
Return all rows برای ورود اطلاعات استفاده می کنیم.
1 Ali ahmady mail 1988/12/06 Tehran tel
نکته:
اگر برای شماره tel که قبل از آن داشت نباید آن را عدد تعریف کنیم چون قبل از عدد اعتبار ندارد باید char تعریف شود.
در اینجا تغییرات بطور اتوماتیک save و ثبت می شود.
رنگ سبز پررنگ نشان دهنده فعال بودن آن است.
SCI\SQL CLASS
روی این راست کلیک کرده گزینه Edit SQL … را انتخاب می کنیم گزینه show system را تیکش را برداشته تا روی data base راست کلیک کرده New … را باز کرده یک پنجره باز می شود نام را وارد می کنیم:
Employee
روی + employee را باز کرده گزینه table را انتخاب می کنیم گزینه new table را انتخاب کرده و فیلدها را می سازیم.
مالک data base owner data … مخفف dbo
روی table ساخته شده را کلیک می کنیم و بعد گزینه properties را انتخاب می کینم تا بتوانیم اطلاعات را ببینیم.
برای ساخت کلید اصلی p.k در جدول سمت چپ راست کلیک می کنیم گزینه اول را انتخاب می کنیم.
زیر employee که ساختیم گزینه diagram راست کلیک می کنیم.
نکته:
روی diagram راست کلیک می کنیم گزینه new diagram و سپس diagram wizard سپس next را زده یک پنجره باز می شود.
در قسمت available table هر دو را add می کنیم و next را زده و سپس finish را می زنیم.
اتصال دو جدول:
روی emp ID جدول employee انتخاب کرده می گیریم و می کشیم و می‌بریم روی empID جدول chid می اندازیم.
نکته:
پنجره creat relationship ایجاد می شود که ارتباط این دو جدول را ممکن می سازد.
FK-child-employee
اگر Ok این پنجره را بزنیم و از ما خطا بگیرد یعنی data type و سایز با هم متفاوت است و باید برویم در table مشکل را رفع و save کرده سپس اتصال دو جدول برقرار می شود.
ارتباط چند کلید ارتباط 1
نکته:
برای table ساخته شده می توانیم حالت های مختلفی داشته باشیم.
Employee Right click \table view\ standard
نکته :
اگر خواستیم هر دو جدول با هم در یک حالت باشند هر دو جدول را select می کنیم و سپس در قسمت آبی نام جدول right click کرده سپس table view و حالت های مختلف را می توانیم داشته باشیم.
در data base علامت North wind + را باز می کنیم قسمت table را نگاه می کنیم. به عنوان مثال روی table که نامش product است راست کلیک کرده گزینه open table را زده و return all rows را زده یک پنجره باز می شود کالاها را می بینیم.
SQL Query Analyzer
هنگام باز شدن این قسمت یک پنجره باز می شود که مشخصات را از ما می‌خواهد.
Connect to SQL Server
دو حالت می توان انتخاب کرد که بر اساس زمان نصب می باشد.
Windows authentication
در این پنجره ای که بعد از Ok کردن باز می شود کدنویسی می کنیم.
نکته:
در قسمت بالای آن combo را باز می کنیم و گزینه north wind را انتخاب می کنیم.
حال Books online را باز کرده در قسمت index می توانیم تایپ کنیم. Select close و می توانیم از این قسمت کمک بگیریم.
دستور کلی select در این قسمت نوشته شده است.
در قسمت index تایپ می کنیم select , from و رویش click می کنیم و دستور select به این ترتیب می باشد.
Select select-list
[INTO new-table]
FROM table-source
[WHERE search-condition]
[GROUP by group-by-expression]
[MAVING search-condition]
[ORDER by order-expression [ASC|DESC]]
لیست فیلد select
نام جدول FROM
مثال:
Select first name , last name , employee from employees
این دستورات را در پنجره SQL Query analyzer می نویسیم سپس کلید FS را زده تا اجرا شود.
در قسمت پایین صفحه اطلاعات در جدول به نمایش در می آید.
Select predicted , product name , unit price From products
کلید F5 را زده تا اجرا شود این دستورات چون پشت سرهم نوشته می‌شوند بعد از زدن F5 هر دو دستور اجرا می شود.
نکته:
ولی اگر بخواهیم فقط یکی از دستوراتتان اجرا شود باید آن را انتخاب و select کرده و بعد F5 را بزنیم.
تمرین:
گزارشی تهیه کنید و در آن در کنار مشخصات کالا، یک فیلد محاسباتی به نام مالیات Tax که برای هر کالایی 10% مالیات محاسبه نماید را نمایش دهید.
فیلد محاسباتی:
یعنی فیلدی که با انجام محاسبات از روی یک فیلد دیگر ساخته می شود.
Select protected , product name , unit price ,
Unit price * 0.1
From products
بر اساس این دستورات فیلد به وجود می آید ولی بدون نام. حال برای داشتن اسم مستعار و جدید دستورات زیر را می نویسیم:
Select protected, product name , unit price , unit price * 0.1 AS TAX
با استفاده از دستور AS حال نام فیلدها TAX خواهد بود.
مثال:
Select protected , left (product name , 10) , unit price from products
نکته:
در فرمان select می توانیم دامنه نمایش رکورد را (predicate) تغییر دهیم که این کار توسط دستوراتی انجام می شود.
این دستورات عبارتند از:
این دستورات بعد از select می آیند. نمایش کلیه رکوردها ALL
فقط موارد غیر تکراری را نمایش می دهد Distinct
عدد صحیح Top n
15% کل رکوردها را میدهد Top percent
مثال:
Select all protected , product name from products
مثال:
Select city
From customers
حال با دستور distinct می نویسیم تا شهرهای تکراری نمایش ندهد.
Select distinct city
From customers
از دستور Top n استفاده می کنیم یعنی n رکورد از جدول را از بالا می‌گرداند.
Select Top 10 products , product name from products
دستور top n percent را استفاده می کنیم.
Select top 10 percent prodectid , product name from products
ORDER BY:
برای منظم کردن اطلاعات به شکل صعودی و نزولی بعد از from استفاده می شود.
لیست فیلدها select
نزولی صعودی نام جدول from
ASC|DESC لیست فیلد یا نام فیلد ORDER BY از Z به A از A به Z
Select productid , product name , unit price
From products
ORDER By product name ASC
مثال:
Select productid , product name , unit price
Form products
ORDER By product name desc , unit price ASC
نکته:
برای هر فیلدی حتماً باید جداگانه صعودی و نزولی نوشته شود.
ولی اگر نوشته شود به صورت پیش فرض به صورت صعودی مرتب می‌شود.
مثال:
گزارشی تهیه کنید که گران ترین کالای موجود در جدول products را نمایش دهد.
Select top 1 unit price , proedectid , product name
From products
ORDER BY unit price desc
مثال:
گزارشی تهیه کنید که سه کالای گران تر را نمایش دهد.
Select top 3 unit price , productid , product name
From products ‘
ORDER BY unit price desc
در این فرمان اول select انجام می شود جواب در server قرار میگیرد سپس 3 رکورد اول به client ارسال خواهد شد.
نکته:
اگر در select بخواهیم از جدولی غیر از جدول data base جاری رکورد استخراج نماییم باید در جلوی from از فرمول زیر استفاده نماییم.
لیست فیلدها select
From server name . data base name . owner . table name
مثال:
Select *
From pubs. dbo. Authors
روی data base وفقه باز کرده گزینه pubs را باز می کنیم چند تا table دارد. برای یکی از آنها مثلاً authors این دستور بالا را نوشته و اجرا می کنیم.
Select *
From pubs. . authors
نکته:
به این صورت نیز جواب می دهد یعنی اسم data base به صورت رزرو را می پذیرد.
مثال:
Select top 3 *
From pubs . . authors
لیست فیلدها select
نام جدول from
> عبارت منطقی where <
Boolean expression
نکته:
Where برای بررسی شرط مورد استفاده قرار می گیرد.
عملگرهای مقایسه ای
Relative operators {> , < , > = , <= , <> , = }
عملگرهای منطقی
Logical operators {not , or , and , is , in , Between}
کالاهایی را لیست کنید که قیمت آنها بالای 20 دلاره
مثال:
Select productid , product name , unit price
From products
Where unit price > 20
مثال:
Select custom rid , company name
From customers
Where city = ‘ London’
مثال:
Select custom rid , company name
From customers
Where city = ‘London’ or city = ‘Paris’
مثال:
کالاهایی را لیست کنید که از خانواده یا از نوع 4 و 5 و 7 می باشد.
Select productid , product name , categoryid
From products
Where categoryid = 4 or categoryid = 5 or categoryid = 7
می توانیم به جای خط آخر دستور زیر را هم بنویسیم:
Where catgoryid TN (4,5,7)
نکته:
دستور IS '
اگر فیلدی دارای مقداری null باشد در هنگام بررسی در شرط باید از دستور is استفاده کنیم.
Where city is null
از مساوی استفاده نمی کنیم.
Select *
From customers
Where region is null
is not null
نکته:
اگر بنویسیم region = null هیچ error نمی دهد ولی دستور غلط است هرگز از مساوی استفاده نکنیم.
کالاهایی را نمایش دهید که قیمتشان بین 10 تا 20 دلار می باشد.
Select *
From products
Where unit price between 10 and 20
کالاهایی که غیر از 10 تا 20 دلار قیمت دارند.
Select *
From products
Where unit price not between 10 and 20
دستور Like :
رکوردهایی را نمایش دهید که نام شرکت مشتری با حرف S شروع شود.
Select *
From customers
Where company name like ‘S%’
Select *
From customers
Where company name like ‘%S%’
نمونه گیری از Like :
حرف اول آنها در زنج A تا D باشد:
Select *
From customers
Where company name like ‘[A-D]%’
نمونه های دیگر:
Like ‘[A-Z][0-9]%’
حرف اول حرف باشد و حرف دوم یک عدد باشد.
علامت نقیض (نباشد):
Like ‘^[A-D]%’
برخی از موارد like در هنگام انجام کار اجرا نمی شود برای انجام این موارد حتماً باید سرویس pak 3a بر روی سیستم SQL نصب شده باشد.
1 یا 1 تا 8 حرف *
توابع تجمیعی و استفاده از Grop By
توابعی هستند که بر روی یکسری مقادیر عملی را انجام می دهند. توابع تجمیعی عبارتند از:
SUM-AVG-COUNT-MAX-MIN-STD-VAR
مورد استفاده:
SUM برای جمع کردن مقادیر یک ستون
AVG برای محاسبه میانگین مقادیر یک ستون
COUNT برای شمارش تعداد عناصر یک ستون
MAX برای تعیین بیشترین مقادیر یک ستون
MIN برای تعیین کمترین مقادیر یک ستون
STD برای محاسبه انحراف معیار مقادیر یک ستون
VAR برای محاسبه واریانس یک ستون
مثال:
گزارشی تهیه کنید که تعداد کالاهای گران تر از 10 دلار را نمایش دهد.
Select count (productid)
From products
Where unit price > 10
مثال:
جمع قیمت کالاهای از نوع 5 را بدست آورید.
Select sum (unit price)
From products
Where categoryid =5
نکته:
Count برای شمارش استفاده می شود اما اگر در آن ستون مقدار null نشسته باشد مورد شمارش قرار نمی گیرد به این معنی که توابع تجمیعی از روی مقادیر null می پرند.
نکته:
با دستور زیر کل رکوردها را می شمارد.
Select count (*)
From products
مثال:
گزارشی تهیه کنید که گران ترین و ارزانترین کالای داخل بانک را و مقدار متوسط بهای کالاها را نشان دهد.
Select MAX (unit price) AS MAX price ,
MIN (unit price) AS MIN price ,
AVG (unit price) AS AVG price
From products
استفاده از Group by :
لیست قیمت ها select
نام جدول from
لیست مقادیر group by
مثال:
گزارشی تهیه کنید که در آن در مقابل نام هر کشور تعداد مشتریان آن کشور را نشان دهد.
Select country, count (customerid)
From customers
این دستور با error مواجه می شود. میگوید این ستون توابع تجمیعی را دارا نمی باشد.
بنابراین دستورات به صورت زیر می باشد:
Select country , count (customerid)
From customers
Group by country
نکته:
1- اگر در یک select در مقابل دستور select از توابع تجمیعی استفاده شده باشد نیازی به استفاده از دستور group by نیست.
2- اگر در یک select از فیلدهای غیر تجمیعی استفاده کنیم باز نیازی به استفاده از دستور group by نخواهد بود. اما به کار گرفتن group by در این دو مدل مجاز می باشد.
3- اگر در یک select از توابع تجمیعی و فیلدهای غیر تجمیع شده استفاده نماییم عبارت group by اجباری خواهد بود.
عدم استفاده از آن باعث ایجاد خطا می شود.
نکته:
بر روی فیلدهای group by , in text , text استفاده نمی شود.
نکته:
هرگز از اسامی مستعار در جلوی group by استفاده نمی کنیم.
مثال:
گزارشی تهیه کنید و در آن به تفکیک شماره کارمندی و سال تعداد فاکتورهای هر کارمند و تاریخ آخرین فاکتور را نشان دهد.
Select employee id , year (order date),
Count (order id) , MAX (order date)
From orders
فرمت این مقاله به صورت Word و با قابلیت ویرایش میباشد
تعداد صفحات این مقاله 76 صفحه
پس از پرداخت ، میتوانید مقاله را به صورت انلاین دانلود کنید
دانلود مقاله سیستم های تجاری Sql
