فصل دوم: کار با پایگاه های داده SQL

ساختار دیتابیس ها

هدف اصلی دیتابیس این است که تنها به اندازه اطلاعات مورد نیاز کاربر، داده ها را در اختیار کاربر قرار دهد. این بدان معنی است که سیستم تمام جزئیات داده ها را فاش نمی کند، بلکه برخی از جزئیات نحوه ذخیره و نگهداری داده ها را پنهان می کند. پیچیدگی دیتابیس ها از دید آنها پنهان است که در صورت لزوم از طریق سطوح مختلف انتزاع برای تسهیل تعامل آنها با سیستم مرتب می شوند.

سطوح مختلف دیتابیس از طریق سه لایه پیاده سازی می شوند:

• سطح داخلی (سطح فیزیکی) / Internal Level: پایین ترین سطح انتزاع، سطح داخلی، نزدیکترین سطح به ذخیره سازی فیزیکی است. توضیح می دهد که چگونه داده ها به طور مشخص در رسانه ذخیره سازی ذخیره می شوند.
• سطح مفهومی / Conceptual Level: این سطح از انتزاع توصیف می کند که چه داده هایی به طور مشخص در دیتابیس ذخیره می شوند. همچنین روابطی را که بین داده ها وجود دارد، توصیف می کند.
• سطح خارجی (سطح مشاهده) / External Level: نزدیکترین سطح به کاربران است و به نحوه مشاهده داده ها توسط کاربران فردی مربوط می شود.

از آنجایی که یک دیتابیس را می توان از طریق سه سطح انتزاعی مشاهده کرد، هر تغییری در یک سطح می تواند بر برنامه های سطوح دیگر تاثیر بگذارد. همانطور که دیتابیس ها همچنان در حال رشد هستند، ممکن است گاهی اوقات نیاز به تغییرات در سطوح مختلف داشته باشند.

برای ذخیره و مدیریت موثر داده ها در دیتابیس، اجازه دهید برخی از اصطلاحات کلیدی را درک کنیم:

1. داده / Data: این اطلاعاتی است که می خواهید ذخیره کنید، مانند هر چیزی از نام و آدرس گرفته تا جزئیات محصول یا حتی اطلاعات کتابخانه.
2. جداول / Tables: اینها مانند پوشه های کابینت بایگانی هستند. هر جدول داده هایی را در مورد یک موضوع خاص نگه می دارد.
3. ردیف ها و ستون ها / Rows and Columns: جداول از سطرها و ستون ها تشکیل شده اند. ردیف ها را به عنوان ورودی های جداگانه در پوشه و ستون ها را به عنوان برچسب روی پوشه ها در نظر بگیرید.
4. طرحواره پایگاه داده / Database Schema: طرحی از پایگاه داده است که برای نمایش ساختار استفاده می شود.
5. محدودیت های داده / Data Constraints: محدودیت هایی که روی جدول قرار می دهیم تا مشخص کنیم چه نوع داده ای را می توان در ستون ها ذخیره کرد.
6. دیکشنری داده / Data Dictionary: فراداده به عنوان داده مربوط به داده ها شناخته می شود و توسط DBMS ذخیره می شود.
7. نمونه پایگاه داده / Database Instance: مجموعه ای از ساختارهای حافظه و فرآیندهای پس زمینه است که برای دسترسی به فایل های دیتابیس استفاده می شود.
8. Query: در یک دیتابیس، از یک پرس و جو برای دسترسی به داده های دیتابیس استفاده می شود.
9. دستکاری داده ها / Data Manipulation: با استفاده از سه عملیات اصلی Insertion، Deletion و Updation می توانیم داده ها را دستکاری کنیم.
10. موتور داده / Data Engine: یک جزء زیربنایی است که برای ایجاد و مدیریت پرس و جوهای مختلف دیتابیس استفاده می شود.

انواع دیتابیس ها

انواع مختلفی از دیتابیس وجود دارد، از جمله:

• دیتابیس های رابطه ای (Relational Databases): این ها از ساختار مبتنی بر جدول و Structured Query Language یا SQL برای پرس و جو و مدیریت داده ها استفاده می کنند. به عنوان مثال می توان به MySQL، PostgreSQL و دیتابیس Oracle اشاره کرد.
• دیتابیس های NoSQL: این دیتابیس ها که برای داده های بدون ساختار و نیمه ساختار یافته طراحی شده اند، نیازی به طرحواره ثابت ندارند و از نظر مدیریت داده ها انعطاف پذیرتر هستند. به عنوان مثال: MongoDB، Cassandra و Redis.
• پایگاه های اطلاعاتی در حافظه (In-Memory Databases): این دیتابیس ها داده ها را در حافظه اصلی (RAM) ذخیره می کنند تا زمان پاسخگویی سریع تری داشته باشند. برای سیستم های کش ایده آل هستند.
• دیتابیس های گراف (Graph Databases): بهینه شده برای ذخیره و پیمایش روابط، برای شبکه های اجتماعی، موتورهای توصیه و تحلیل شبکه ایده آل هستند. به عنوان مثال: Neo4j و Amazon Neptune.

Relational Database

در یک دیتابیس رابطه ای، هر ردیف در جدول یک رکورد با یک شناسه منحصر به فرد به نام کلید است. ستون‌های جدول ویژگی‌های داده‌ها را نگه می‌دارند و هر رکورد معمولاً مقداری برای هر ویژگی دارد که برقراری روابط بین نقاط داده را آسان می‌کند.

مدل رابطه ای که برای اولین بار توسط ادگار اف. کادر پژوهشگری در IBM، در سال 1970 ارائه شد، یک روش بصری و ساده برای نمایش ساختارمند داده ها و انقلابی اساسی در نحوه مدیریت دیتابیس ها محسوب می شود. یک دیتابیس رابطه از جداول حاوی ستون و ردیف تشکیل شده است. هر دسته جدول مخصوص به خود را ارائه می دهد.

می توانید یک جدول را تا حدودی شبیه به صفحه گسترده در نظر بگیرید. در داخل یک صفحه گسترده، ستون ها و ردیف هایی از داده ها وجود دارد. اما برای دیتابیس هر سطر یک رکورد و هر سلول یک فیلد نامیده می شود. تصور کنید یک دفترچه تلفن دارید. این یک دیتابیس ساده است! دفترچه تلفن اطلاعاتی در مورد افراد دارد که به ترتیب حروف الفبا سازماندهی شده است. هر ورودی یک ردیف است که در یک ستون نام فرد و در ستون دیگر شماره تلفن او قرار دارد.

SQL & DBMS

سیستم های مدیریت پایگاه داده (Database Management Systems یا DBMS) نوعی سیستم مدیریت دیتابیس ها هستند که داده ها را در جداول سازماندهی می کنند. این جداول بر اساس ویژگی های مشترک داده ها به هم مرتبط می شوند. این ساختار بر اساس مدل رابطه ای (Relational Model) شکل گرفته است.

SQL یک زبان برنامه نویسی است که برای تسهیل کار با دیتابیس ها طراحی شده است. قدرت این ابزار را نمی توان دست کم گرفت. با درک قوی از مفاهیم اصلی آن، می توانید تقریباً هر کاری را که می خواهید با داده ها انجام دهید. معمولاً از SQL برای استخراج داده ها، به روزرسانی رکوردها، ایجاد جداول و مدیریت ساختار کلی پایگاه داده استفاده می شود.

ویژگی های اصلی DBMS:

1. زبان پرس و جوی ساخت یافته (SQL): در DBMS از SQL برای ساخت، بازیابی، به روز رسانی و مدیریت اطلاعات استفاده میشود.
2. جداول / Tables: داده ها در جدول هایی ذخیره می شوند که از سطرها و ستون ها تشکیل شده اند. هر سطر نشان دهنده یک رکورد منحصر به فرد است.
3. کلیدها / Keys: کلیدها امکان شناسایی منحصر به فرد را برای همه ردیف‌های جدول فراهم می‌کنند.
4. نرمال سازی (Normalization): این فرآیند داده ها را برای کاهش افزونگی و بهبود یکپارچگی داده ها سازماندهی می کند.
5. ویژگی های ACID: سیستم های DBMS از ویژگی های ACID (اتمی بودن، سازگاری، جداسازی، دوام) پیروی می کنند.
6. کنترل همزمانی / Concurrency Control: ابزارهایی برای مدیریت همزمان چند کاربر فراهم میکند.
7. امنیت / Security: ویژگی های امنیتی قدرتمندی از جمله احراز هویت، مجوزدهی و رمزگذاری ارائه می دهد.
8. روابط / Relationships: پیوند بین جداول مختلف که به خوانایی و بهینه سازی فضا کمک می کند.

مزایای DBMS:

• یکپارچگی داده ها: DBMS با اعمال انواع داده ها، محدودیت ها و روابط، سطوح بالایی از یکپارچگی و دقت داده ها را تضمین می کند.
• انعطاف پذیری: SQL امکان پرس و جوهای پیچیده و دستکاری داده ها را فراهم می کند.
• مقیاس پذیری (Scalability): بسیاری از راه حل های DBMS برای مقیاس پذیری کارآمد طراحی شده اند.
• قابلیت نگهداری: ابزارها و رابط های ارائه شده توسط فروشندگان DBMS، کارهای نگهداری را قابل مدیریت تر می کند.

نمونه های رایج DBMS:

• Oracle Database: به دلیل پلتفرم غنی از ویژگی های خود شناخته شده و در محیط های سازمانی استفاده می شود.
• MySQL: یک DBMS منبع باز محبوب که برای برنامه های وب مناسب است. بخشی از پشته LAMP است.
• Microsoft SQL Server: محصولی از مایکروسافت با تجزیه و تحلیل داده ها، یکپارچه سازی ابری و ویژگی های پیشرفته.
• PostgreSQL: یک DBMS منبع باز پیشرفته با انطباق با استانداردها و پشتیبانی از انواع داده های پیشرفته.

دو نوع کلید وجود دارد که باید بدانید: کلید اصلی و کلید خارجی.

• کلیدهای اصلی / Primary Key: هر جدول در یک DBMS یک کلید اصلی دارد که یک شناسه منحصر به فرد برای هر رکورد در جدول است.
• کلیدهای خارجی / Foreign Key: از کلیدهای خارجی برای برقراری ارتباط بین جداول استفاده می شود. یک کلید خارجی در یک جدول به یک کلید اصلی در جدول دیگر اشاره می کند.

مثال: شرکت کوچکی را تصور کنید که بخش‌های مختلفی برای کارمندان خود دارد، مانند صندوق بیمه، مهدکودک، ثبت حضور الکترونیکی. اگرچه همه این اطلاعات ممکن است مفید باشد، خواندن همه آنها با هم مفید نخواهد بود. اگر بخش منابع انسانی بخواهد اطلاعات بیمه یک کارمند را ببیند، نیازی نیست اطلاعات مهدکودک را نیز ببیند.

برای مقابله با این، روابط بین جداول مختلف پیاده سازی می شود. روابط اساساً امکان تقسیم اطلاعات به اجزای مفید را فراهم می کند که بر خوانایی و کارایی تأکید دارند. این قسمتی است که پیوند بین جداول مختلف شروع به توسعه می کند.

تفکیک اطلاعات مهم

در مثال شرکت کوچک بالا، بهترین راه برای ایجاد روابط، داشتن 4 جدول است. هر کدام به نمایندگی از کارمندان، صندوق بیمه، مهدکودک و ثبت نام حضور و غیاب. اکنون، اطلاعات به خوبی تقسیم شده است. گام بعدی شناسایی اطلاعاتی است که در بیشتر مناطق وجود دارد.

در این مورد، هر چیزی که ما تقسیم می کنیم توسط کارمندان استفاده می شود، بنابراین منطقی است که آن را به عنوان جدول اصلی خود در نظر بگیریم. هر یک از کارکنان ستون های اصلی نام، آدرس، تلفن، ایمیل و سن را به همراه چیزی به نام شماره شناسایی دارد. این شماره شناسه برای هر کارمند منحصر به فرد است و به عنوان کلید اصلی جدول کارمند عمل می کند.

ایجاد روابط با سایر جداول از طریق کلیدهای خارجی

ستون Employee ID از آن جدول به عنوان کلید اصلی برای سایر جداول استفاده خواهد شد. هنگامی که جداول دیگر به این طریق پیوند داده می شوند، گفته می شود که دارای کلید خارجی هستند. این کلید خارجی به ما امکان می دهد تمام اطلاعات دیگر را به هر کارگر مرتبط کنیم.

در حالی که بسیاری از مسائل فنی نحوه ایجاد روابط در سیستم های پیچیده را تعیین می کنند، این یکی از ساده ترین نمونه هایی است که می توانید در هر کتاب دیتابیس رابطه ای پیدا کنید.

نمودار ER یک شرکت / ER Diagram of a Company

نمودار ER با نام Entity-Relationship Diagram شناخته می شود و برای تجزیه و تحلیل ساختار دیتابیس ها استفاده می شود. روابط بین موجودیت ها و ویژگی های آنها را نشان می دهد. مدل ER وسیله ارتباطی را فراهم می کند.

نمودار ER شرکت دارای توضیحات زیر است:

• این شرکت دارای چندین بخش است.
• هر بخش ممکن است چندین مکان داشته باشد.
• بخش ها با نام، D_no، مکان شناسایی می شوند.
• یک مدیر بخش خاصی را کنترل می کند.
• هر بخش با تعدادی پروژه مرتبط است.
• کارمندان با نام، شناسه، آدرس، dob و date_of_joining شناسایی می شوند.
• یک کارمند فقط در یک بخش کار می کند اما می تواند روی چندین پروژه کار کند.
• ما همچنین تعداد ساعات کار یک کارمند در یک پروژه را پیگیری می کنیم.
• هر کارمند وابسته ای دارد.
• وابسته دارای D_name، جنسیت و رابطه است.

نمودار ER یک شرکت اطلاعات کلیدی در مورد شرکت، از جمله نهادهایی مانند کارمند، بخش، پروژه و وابسته را نشان می دهد و این اجازه را می دهد تا روابط بین موجودات را درک کنید.

موجودیت / Entity

یک چیز – مانند یک خانه، شی، مفهوم یا رویداد – که می تواند داده های مربوط به آن را ذخیره کند. مثال: مشتری، دانش آموز، خودرو یا محصول. معمولاً به صورت مستطیل نشان داده می شود.

ارتباط / Relationships

چگونه موجودیت ها بر روی یکدیگر عمل می کنند یا با یکدیگر مرتبط هستند. به عنوان مثال، دانشجوی نامبرده ممکن است برای یک دوره ثبت نام کند. دو نهاد دانشجو و دوره خواهند بود، و رابطه نشان داده شده عمل ثبت نام است که این دو نهاد را به این طریق به هم متصل می کند. معمولاً به صورت الماس یا برچسب مستقیماً روی خطوط اتصال نشان داده می شود.

صفت / Attribute

یک ویژگی یا خاصیت یک موجودیت. اغلب به صورت بیضی یا دایره نشان داده می شود.

کاردینالیتی

کاردینالیتی ویژگی های عددی رابطه بین دو موجودیت یا مجموعه موجودیت را تعریف می کند. انواع مختلف روابط اصلی عبارتند از:

• روابط یک به یک (1:1)
• روابط یک به چند (1:N)
• روابط چند به یک (N:1)
• روابط چند به چند (N:N)

مدل‌سازی ER به شما کمک می‌کند تا نیازهای داده‌ها را به طور سیستماتیک تجزیه و تحلیل کنید تا یک دیتابیس با طراحی خوب تولید کنید. تکمیل مدل‌سازی ER قبل از پیاده‌سازی دیتابیس، بهترین عمل در نظر گرفته می‌شود.

نرمال سازی پایگاه داده

نرمال سازی یک فرآیند اساسی در توسعه پایگاه داده است که هدف آن بهینه سازی ساختار یک پایگاه داده برای به حداقل رساندن افزونگی و اطمینان از یکپارچگی داده ها است. هدف ایجاد یک ساختار داده منعطف و کارآمد است که نگهداری آن آسان باشد.

بیایید با یک مثال ساده شروع کنیم: فرض کنید یک جدول پایگاه داده داریم که در آن اطلاعات کتاب ها و نویسندگان آنها را ذخیره می کنیم. این جدول شامل ستون هایی برای شناسه کتاب، عنوان کتاب، نام نویسنده و ایمیل نویسنده است. مشکلات متعددی ممکن است ایجاد شود، مانند تکرار اطلاعات نویسنده زمانی که نویسنده چندین کتاب نوشته است.

اولین فرم طبیعی (1NF)

اولین شکل نرمال مستلزم آن است که هر ستون در هر جدول حاوی مقادیر اتمی باشد، یعنی مقادیری که نمی توان بیشتر به واحدهای کوچکتر تقسیم کرد. علاوه بر این، هر مقدار در یک ستون باید از یک نوع باشد و هر ستون باید منحصراً قابل شناسایی باشد. در مثال ما، این بدان معناست که اگر یک کتاب چندین نویسنده داشته باشد، ساختار داده باید تغییر کند تا هر ردیف دارای یک ترکیب کتاب و نویسنده منحصر به فرد باشد.

فرم طبیعی دوم (2NF)

برای تبدیل یک پایگاه داده به فرم دوم عادی، ابتدا باید فرم اول عادی را برآورده کند. علاوه بر این، هر ستون غیر کلیدی باید به طور کامل به کلید اصلی وابسته باشد. در مثال ما، اگر اطلاعات نویسندگان به طور مستقیم به کتاب وابسته نباشد، این اطلاعات باید در یک جدول جداگانه جدا شود. بنابراین می‌توانیم یک جدول نویسنده‌ها و یک جدول کتاب‌ها ایجاد کنیم که از طریق یک جدول پیوند به هم مرتبط شده‌اند.

فرم سوم عادی (3NF)

یک پایگاه داده در حالت سوم عادی است اگر فرم عادی دوم را برآورده کند و علاوه بر این هیچ وابستگی گذرای ستون های غیر کلیدی روی کلید اصلی وجود نداشته باشد. یک وابستگی گذرا زمانی رخ می دهد که یک ستون غیر کلیدی به ستون غیر کلیدی دیگری وابسته باشد. به عنوان مثال، اگر جدول نویسندگان شامل ستونی برای کشور نویسنده باشد، این اطلاعات می تواند به یک جدول جداگانه منتقل شود.

فرم های معمولی تر

همچنین فرم چهارم (4NF) و پنجمین فرم طبیعی (5NF) وجود دارد که برای جلوگیری از انواع پیچیده‌تر افزونگی‌ها و ناهنجاری‌ها، الزامات خاص تری نیز دارند. چهارمین فرم عادی وابستگی‌های چند ارزشی را مدیریت می‌کند، و پنجمین فرم عادی با هدف از بین بردن هرگونه ناهنجاری باقی‌مانده انجام می‌شود. نرمال سازی یک عمل متعادل کننده بین بهینه سازی ساختار داده و عملکرد پایگاه داده است.

مروری بر دستورات مختلف SQL

SQL یک زبان برای برقراری ارتباط با پایگاه داده است. این زبان در برقراری ارتباط با پایگاه داده‌های رابطه‌ای برای بازیابی و مدیریت اطلاعات کاربردهای زیادی دارد. دستورات SQL به دسته‌های مختلفی تقسیم می‌شوند که در ادامه به شرح آن‌ها می‌پردازیم.

در اینجا پنج نوع پرس و جوی پرکاربرد آورده شده است:

• زبان تعریف داده (DDL) – Data Definition Language
• زبان دستکاری داده ها (DML) – Data Manipulation Language
• زبان کنترل داده (DCL) – Data Control Language
• زبان کنترل تراکنش (TCL) – Transaction Control Language
• زبان جستجوی داده (DQL) – Data Query Language

DDL – زبان تعریف داده

زبان تعریف داده (DDL) به شما کمک می کند تا ساختار بانک اطلاعاتی یا طرحواره (schema) را تعریف کنید. مهم‌ترین دستورات DDL عبارتند از:

CREATE (ایجاد کردن): از این دستور برای تعریف و ساخت ساختار پایگاه داده استفاده می‌شود.

CREATE TABLE table_name (column_name datatype[,….]);

مثال:

CREATE DATABASE university;
CREATE TABLE students;
CREATE VIEW for_students;

DROP (حذف کردن): این دستور برای حذف جداول و پایگاه داده از DBMS استفاده می شود.

DROP TABLE table_name;

مثال:

DROP DATABASE university;
DROP TABLE students;

ALTER (تغییر دادن): برای تغییر ساختار یک دیتابیس می توانید از این دستور استفاده کنید.

ALTER TABLE table_name ADD column_name column_definition;
ALTER TABLE table_name MODIFY (column_definition, … );

مثال:

ALTER TABLE guru99 ADD subject varchar;

TRUNCATE (خالی کردن): با این دستور می‌توانید تمام سطرهای یک جدول را حذف کنید و فضای آن جدول را آزاد نمایید.

TRUNCATE TABLE table_name;

مثال:

TRUNCATE TABLE students;

DML – زبان دستکاری داده‌ها

زبان دستکاری داده ها (DML) به شما این امکان را می دهد که محتوای پایگاه داده را با اضافه کردن، تغییر یا حذف داده‌ها، دستکاری کنید. دستورات اصلی DML عبارتند از:

INSERT (درج کردن): این دستور برای اضافه کردن ردیف‌های جدید به یک جدول استفاده می‌شود.

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

مثال:

INSERT INTO students (RollNo, FirstName, LastName) VALUES ('60', 'Tom', 'Erichsen');

UPDATE (بروزرسانی کردن): از این دستور زمانی استفاده می شود که می‌خواهید داده‌های موجود در جدول را تغییر دهید.

UPDATE table_name SET [column_name1 = value1, … ] [WHERE condition];

مثال:

UPDATE students SET FirstName = 'John', LastName = 'Wick' WHERE StudID = 3;

DELETE (حذف کردن): برای حذف یک یا چند سطر از جدول استفاده می‌شود.

DELETE FROM table_name [WHERE condition];

مثال:

DELETE FROM students WHERE FirstName = 'John';

SELECT (انتخاب): از این دستور برای انتخاب ویژگی ها بر اساس شرطی که با عبارت WHERE مشخص می‌شود، استفاده می‌شود.

SELECT * FROM table_name [WHERE condition];

DCL – زبان کنترل داده

زبان کنترل داده‌ها (DCL) برای کنترل دسترسی به داده‌ها بکار می‌رود و شامل دستوراتی مانند GRANT و REVOKE است. دستورات DCL عبارتند از:

• GRANT (اعطا کردن): برای اعطای دسترسی کاربر به بانک اطلاعاتی از این دستور استفاده می شود.
• REVOKE (باطل کردن): برای پس گرفتن دسترسی کاربر استفاده می‌شود.

TCL – زبان کنترل تراکنش

زبان کنترل تراکنش (TCL) با تراکنش‌های مربوط به پایگاه داده سروکار دارد. از مهم‌ترین دستورات TCL می‌توانیم به موارد زیر اشاره کنیم:

• COMMIT (ثبت کردن): این دستور تمام تراکنش ها را در پایگاه داده ذخیره می کند.
• ROLLBACK (برگرداندن): این دستور برای لغو تراکنش ها قبل از ذخیره شدن در پایگاه داده استفاده می شود.
• SAVEPOINT (ذخیره کردن بخشی از تراکنش): این دستور مشخص می کند تا چه قسمتی از تراکنش را میخواهید ذخیره کنید.
• SELECT (انتخاب – DQL): از این دستور برای انتخاب ویژگی ها بر اساس شرطی که با عبارت WHERE مشخص می‌شود، استفاده می‌شود.

مقایسه دستورات SQL

آزمون / Quiz