locked
Help with database for library RRS feed

  • Question

  • Hello people, I'm new here so I hope that I selected right category. 

    I have problem following problem: 
    I have to create database which will contain informations about all the books and "Faculty of Political Science Library". Each book is classified into one of the following categories: Technical literature, philosophic literature and dictionaries. For each book, database have to contain informations about the Author, ISBN code and publishing year. For each author, database have to contain Name, Surname and date of birth.

    Bold text is part which I don't understand. Can anyone explain me how to implement those three categories to sql database so I can join book with one of the those three categories?

    I suppose this isn't something complicated but I'm still newbie so I would be very grateful if you help me by any way. Thanks! 
    Saturday, December 3, 2016 7:34 PM

Answers

  • You'll need to tables:

    Books:

    ID (Primary key, Identity, Int)

    Title (varchar(50))

    Author (varchar(50))

    ISBN (varchar(50))

    Year (Int)

    CategoryID (Int)

    Categories:

    ID (Primary key, Identity, Int)

    Category (varchar(50))

    You then insert the following records into your Categories table:

    1 Technical Literature

    2 Philosophic literature

    3 Dictionaries

    Your books records will look like this:

    1 BookTitle BookAuthor BookISBN BookYear 1

    2 BookTitle2 BookAuthor2 BookISBN2 BookYear2 2

    3 BookTitle3 BookAuthor3 BookISBN3 BookYear3 2

    4 BookTitle4 BookAuthor4 BookISBN4 BookYear4 3

    The last column (CategoryID) refers to your Category-table, so if you want to retrieve all books that is categorized as 'Philosophic literature' use the following SQL:

    SELECT * FROM Books WHERE CategoryID = 2


    Saturday, December 3, 2016 9:15 PM
  • Hi zemo323,

    According to your description 'Can anyone explain me how to implement those three categories to sql database so I can join book with one of the those three categories?' We can create one table for each categorie, we can create three tables: technical_literature, philosophic_literature, dictionaries. We can add records to each table. When we want to select books from these tables, we can use select *** from table_name from these categories.

    Then we can create a view to show all books of the three categories.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 7, 2016 10:18 AM

All replies

  • You simply create a new table called Categories (CategoryId int, Description varchar(100))

    And then your Books table will have CategoryId column that will link it with the Categories table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Saturday, December 3, 2016 8:59 PM
    Answerer
  • You'll need to tables:

    Books:

    ID (Primary key, Identity, Int)

    Title (varchar(50))

    Author (varchar(50))

    ISBN (varchar(50))

    Year (Int)

    CategoryID (Int)

    Categories:

    ID (Primary key, Identity, Int)

    Category (varchar(50))

    You then insert the following records into your Categories table:

    1 Technical Literature

    2 Philosophic literature

    3 Dictionaries

    Your books records will look like this:

    1 BookTitle BookAuthor BookISBN BookYear 1

    2 BookTitle2 BookAuthor2 BookISBN2 BookYear2 2

    3 BookTitle3 BookAuthor3 BookISBN3 BookYear3 2

    4 BookTitle4 BookAuthor4 BookISBN4 BookYear4 3

    The last column (CategoryID) refers to your Category-table, so if you want to retrieve all books that is categorized as 'Philosophic literature' use the following SQL:

    SELECT * FROM Books WHERE CategoryID = 2


    Saturday, December 3, 2016 9:15 PM
  • Hi zemo323,

    According to your description 'Can anyone explain me how to implement those three categories to sql database so I can join book with one of the those three categories?' We can create one table for each categorie, we can create three tables: technical_literature, philosophic_literature, dictionaries. We can add records to each table. When we want to select books from these tables, we can use select *** from table_name from these categories.

    Then we can create a view to show all books of the three categories.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 7, 2016 10:18 AM