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

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

فعالیت هایی از قبیل ایجاد یک عضو کتابخانه جدید ، قرض دادن کتاب به یک عضو جدید ، حفظ جزئیات مربوط به تمام آیتم هایی که در کتاب  موجود است  و ... را پیاده سازی خواهیم کرد. این سیستم با فراهم کردن اطلاعاتی درباره هر کتاب ، جزئیات و دسته بندی آنها  به کتابدار کمک میکند .

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

 Book_Details

Binding_Details

Category_Details

Borrower_Details

Student_Details

Staff_Details

Shelf_Details

Book_Details

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

ISBN یک Id یونیک برای هر کتاب است. ممکن است کتابهای زیادی با یک عنوان وجود داشته باشند اما هر کدام ISBN خاص خود را دارند.

Book_Title نام :کتاب را مشخص میکند.

Publication_year :سال نشر کتاب را مشخص میکند.

Language   :شامل زبان هایی که در آن کتاب انتشار یافته اند

Category_Type  :این ستون شامل  CategoryID هایی می شود که می توانند از جدول  Category_master  واکشی شوند.  CategoryID یک عدد منحصر بفرد است که به هر  category  یا دسته بندی اختصاص می یابد.

Binding_id  :این ستون شامل  binding_id هایی می شود که جزئیات آنها می تواند از جدول Binding_Details واکشی شود. Binding_id  یک عدد منحصر بفرد است که به هر نوع از  Binding  داده می شود.

No_Of_Copies_Actual   : این ستون شامل تعداد چاپ هر کتاب در ابتدا می باشد.

No_Of_Copies_Current   : این ستون شامل تعداد چاپ های موجود در حال حاضر است.

Binding_Detailes

این جدول نوع  Binding  را مشخص میکند ،  شامل  ID و نام Binding می شود. Binding_id  به عنوان کلید اصلی انتخاب می شود.

Binding_id  :این ستون شامل عددی منحصر بفرد است که به هر نوع از  binding اختصاص می یابد.

Binding_Name  :نام انواع  Binding  را مشخص میکند.

Category_Details

این جدول نیز شامل  id  و نام می شود.  Category_id  به عنوان کلید اصلی انتخاب می شود.

category_ID  : شامل یک عدد منحصر بفرد که به هر نوع از  category اختصاص می یابد.

Category_Name  : این ستون نام هر دسته بندی را مشخص میکند.

Borrower_Details

این جدول شامل همه کسانی می شود که از کتابخانه ، کتاب قرض گرفته اند. هر دانش آموز یک  Borrower_ID مخصوص را خواهد گرفت . همه کارهای مربوط به یک شخص خاص بر اساس  Borrower _ID ضبط می شود. این جدول برای دنبال کردن رکوردهای کسانی که کتاب قرض میگیرند استفاده می شود.  Borrower_ID کلید اصلی تعریف می شود .

Borrower_ID  : به هر شخصی که کتاب قرض می گیرد یک   ID منحصر بفرد اختصاص می دهد.

Book_ID  :این ستون شامل  Id کتابهایی که قرض گرفته شده اند.

Borrowed_From_Date  : روزی که کتاب به یک شخص خاص قرض داده شده است.

 Borrowed_To_Date  : روزی که قرار است کتاب به کتابخانه برگردد یا تمدید شود.

Actual_Return_Date  :  روزی که قرض گیرنده ، کتاب را به کتابخانه باز میگرداند.

Issused_By  : شامل  ID کسی که کتاب را قرض می دهد.

Staff_Details

این جدول شامل اطلاعات کارکنان کتابخانه است. هر عضو یک  ID منحصر بفرد User_ID دارد که به عنوان کلید اصلی تعریف شده است.

User_ID  : شامل  Id منحصر بفردی که به هر کارمند کتابخانه داده می شود.

User_Name   : نام کارمندان را شامل می شود.

Is_Admin  : فقط برای چک کردن آنکه یک کارمند ، ادمین است یا نه .

Designation  : نقش هر عضو از کارمندان کتابخانه .

Student_Details 

این جدول شامل اطلاعات همه دانش آموزانی که واجد شرایط قرض گرفتن کتاب هستند. هر دانش آموز یک Student_ID منحصر بفرد و یک Borrower_ID  منحصر بفرد دارد . Student_ID کلید اصلی تعریف می شود. البته  Borrower_ID و  Phone_No هم منحصر بفرد می باشند.

Student_ID  : شامل  id  منحصر بفردی که به هر دانش آموز داده می شود. 

Student_Name : نام دانش آموز

 Sex  : جنسیت دانش آموز را مشخص میکند.

 Date_Of_Birth  :  تاریخ تولد دانش آموز

Borrower_ID : شامل  ID قرض گیرنده که به هر دانش آموز انتساب داده می شود.

Department  : شامل دپارتمان دانش آموز 

Contact_Number : شماره تماس دانش آموز

Shelf_Details 

این جدول موقعیت کتاب در کتابخانه را نشان می دهد . به عبارت دیگر در کدام راهرو و قفسه قرار دارد .

Shelf_ID  : شامل شماره قفسه

Floor  : شامل راهروی درنظر گرفته شده برای هر کتاب

جداول را می توانید با استفاده از دستورات زیر ایجاد کنید.

 ایجاد جدول  Book_Details  :

    CREATE TABLE Book_Details  
    (  
        ISBN_Code int PRIMARY KEY,  
        Book_Title Nvarchar(100),  
        Language Nvarchar(10),  
        Binding_Id int,  
        No_Copies_Actual int,  
        No_Copies_Current int,  
        Category_id int,  
        Publication_year int  
    )  

وارد کردن داده به آن  :

INSERT INTO Book_details  
VALUES('0006','Programming Concept','English',2,20,15,2,2006);

ایجاد جدول  Binding_Details 

    CREATE TABLE Binding_details  
    (  
        Binding_id int PRIMARY KEY,  
        Binding_Name Nvarchar(50)  
    )  

 وارد کردن داده به آن  :

    INSERT INTO Binding_DetailsVALUES(1,'McGraw Hill);  
    INSERT INTO Binding_DetailsVALUES(2,'BPB Publication');  

با دستور زیر می توانید داده های آن را ببینید.

    select *from binding_Details  

ارتباط بین  Book  و  Binding  

ALTER TABLE Book_details  
ADD CONSTRAINT Binding_ID_FK FOREIGN KEY(Binding_Id) REFERENCES Binding_Details(Binding_Id);

برای چک کردن ارتباط از دستور زیر استفاده کنید :

selectb.Book_Title, e.binding_name  
fromBook_Detailsb, Binding_Details e  
whereb.binding_id = e.binding_id; 

ایجاد جدول  Category 

    CREATE TABLE Category_Details  
    (  
        Category_Id int PRIMARY KEY,  
        Category_Name Nvarchar(50)  
    )  

وارد کردن داده در آن  :

INSERT INTO CATEGORY_DETAILS VALUES(1,'Database');  
INSERT INTO CATEGORY_DETAILS VALUES(2,'Programming Language'); 

ایجاد ارتباط بین  Book  و  Category 

    ALTER TABLE Book_details  
    ADD CONSTRAINT Category_Id_FK FOREIGN KEY(Category_Id) REFERENCES Category_Details(Category_Id);  

چک کردن ارتباط :

    selectb.Book_Title,e.Category_Name  
    fromBook_Detailsb,Category_Details e  
    whereb.binding_id = e.Category_id;  

 ایجاد جدول  Borrower 

    CREATE TABLE Borrower_Details  
    (  
        Borrower_Id int PRIMARY KEY,  
        Book_Id int,  
        Borrowed_From datetime,  
        Borrowed_TO datetime,  
        Actual_Return_Date datetime,  
        Issued_by int  
    )  

وارد کردن داده به آن :

    Insert into BORROWER_DETAILS VALUES(1,0004,'01-Aug-2014','7-Aug-2014','7-Aug-2014',1)  
    Insert into BORROWER_DETAILS VALUES(2,6,'02-Aug-2014','8-Aug-2014',NULL,1)  

ارتباط بین  Borrower  و  Book

ALTER TABLE Borrower_details ADD CONSTRAINT Book_Id_FK FOREIGN KEY(Book_Id) REFERENCES Book_Details(ISBN_Code); 

چک کردن ارتباط:

    selectBorrower_Details.Borrower_id,Book_Details.Book_title  
    fromBorrower_Details,Book_Details  
    whereBorrower_Details.book_id=Book_Details.ISBN_Code  

ALTER TABLE Borrower_Details  
ADD CONSTRAINT Issued_by_FK FOREIGN KEY(Issued_by) REFERENCES Staff_Details(Staff_Id);

 ایجاد جدول  Staff_Details

    CREATE TABLE Staff_Details  
    (  
        Staff_Id int PRIMARY KEY,  
        Staff_Name Nvarchar(50),  
        Password Nvarchar(16),  
        Is_Adminbinary float,  
        Designation Nvarchar(20)  
    )  

وارد کردن داده به آن

Insert into STAFF_DETAILS values (1,'Tarek Hossain','1234asd',0,'Lib_mgr');  
Insert into STAFF_DETAILS values (2,'Md.Kishor Morol','iloveyou',0,'Lib_clr'); 

همه داده آن را می توانید با دستور زیر مشاهده کنید:

    select * from staff_details  

ایجاد جدول  Student  :

    Create TABLE Student_Details  
    (  
        Student_Id varchar(10) PRIMARY KEY,  
        Student_Name Nvarchar(50),  
        Sex Varchar(20),  
        Date_Of_Birth datetime,  
        Borrower_Id int,  
        Department Nvarchar(10),  
        contact_Number Nvarchar(11)  
    )  

وارد کردن داده به آن :

    Insert into STUDENT_DETAILS values ('13-23059-1','sara,Alipoor','feMale','05-Oct-1995',1,'CSSE','01681849871');  
    Insert into STUDENT_DETAILS values ('13-23301-1','zahra, ahmadi','feMale','03-Jan-1994',2,'CSE','01723476554');  

تمام داده های این جدول :

select *from student_details 

ارتباط بین جدول Student  و Borrower  :

    ALTER TABLE student_details  
    ADD CONSTRAINT borrower_id_FK FOREIGN KEY(Borrower_Id) REFERENCES Borrower_Details(Borrower_Id);  

چک کردن ارتباط:

    select student.student_id, student.student_name, book.Book_Title, staff.staff_name, b.Borrowed_To  
    fromstudent_Detailsstudent, Staff_Detailsstaff, Borrower_Detailsb, book_details book  
    wherestudent.Borrower_id = b.Borrower_id and book.ISBN_Code = b.book_id and b.Issued_by = staff.Staff_id;  

ایجاد جدول Shelf_Details

    Create Table Shelf_Details  
    (  
        Shelf_id int PRIMARY KEY,  
        Shelf_No int,  
        Floor_No int  
    );  

وارد کردن داده به آن :

    Insert into Shelf_DetailsValues(1, 1, 1);  
    Insert into Shelf_DetailsValues(2, 2, 10001);  
    Insert into Shelf_DetailsValues(3, 1, 10002);  

نمایش داده های این جدول :

select*from Shelf_Details; 

ارتباط بین  Shelf  و  Book 

    ALTER TABLE Book_Details  
    ADD(Shelf_Idint);  
      
    UPDATE Book_Details set Shelf_Id = 1  
    where ISBN_CODE = 4;  
      
    UPDATE Book_Details set Shelf_Id = 2  
    where ISBN_CODE = 6;  
      
    ALTER TABLE Book_Details  
    ADD CONSTRAINT Shelf_Id_FK FOREIGN KEY(Shelf_Id) REFERENCES Shelf_Details(Shelf_Id);  

ترکیب همه روابط به صورت زیر:

select student.student_id, student.student_name, book.Book_Title, staff.staff_name, b.Borrowed_To, shelf.shelf_No  
fromstudent_Detailsstudent, Staff_Detailsstaff, Borrower_Detailsb, book_detailsbook, Shelf_Details shelf  
wherestudent.Borrower_id = b.Borrower_id and book.ISBN_Code = b.book_id and b.Issued_by = staff.Staff_id and book.Shelf_Id = shelf.Shelf_Id; 

آموزش نصب sql server 2017