Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: How to represent maximum number of books borrowed at the same time through a trigger?

    Hi everybody,

    I have to build a database model, create tables and use triggers or/and procedures on it.
    It's about a library. Books are borrowed by students. They can make a reservation of a book.

    I built the database model so far. My problem is how to represent following tasks:

    - Students can borrow a maximum of 5 books at the same time.
    - bookings/book reservations should be later processed in the order they occurred. That is, who first flagged the book also gets the book/books first

    I have following entities:

    - Book
    - Book exemplar
    - Borrowed by (a table between the entities Book exemplar and Student)
    - Reserved for (a table between the entities Book exemplar and Student)
    - Student

    How would you represent the tasks I mentioned above in terms of triggers or procedures?...

    I would appreciate your help as I am not good or a newbie writing triggers or procedures.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by stud3 View Post
    Hi everybody,

    I have to build a database model, create tables and use triggers or/and procedures on it.
    It's about a library. Books are borrowed by students. They can make a reservation of a book.

    I built the database model so far. My problem is how to represent following tasks:

    - Students can borrow a maximum of 5 books at the same time.
    - bookings/book reservations should be later processed in the order they occurred. That is, who first flagged the book also gets the book/books first

    I have following entities:

    - Book
    - Book exemplar
    - Borrowed by (a table between the entities Book exemplar and Student)
    - Reserved for (a table between the entities Book exemplar and Student)
    - Student

    How would you represent the tasks I mentioned above in terms of triggers or procedures?...

    I would appreciate your help as I am not good or a newbie writing triggers or procedures.
    Try writing select statements first and if you cannot complete this assignment then come back with what you've written so someone can assist.

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    So, for the first task, I have the following statement:

    - Students can borrow a maximum of 5 books at the same time.

    create trigger max_books
    on borrowed_by
    for insert
    as
    begin
    @output int
    set @output = (select count(book_exemplar_id) from borrowed_by where borrowed_date = ? and student_id = ?)
    if output > 5
    raiseerror (It is not allowed to borrow more than 5 books at the same time for the student with student_id ?);
    rollback
    end

    Sorry, I said, I am newbie in triggers and procedures, so if someone could correct my statement here, I would appreciate it.

    I am still thinking how to write my statement for the second task, but I can not get it.
    Help is welcome. Thanks.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    Do you really have only one book and one student, as shown my your table names. Tables model either sets of entities or relationships. You need collective or plural names. I no idea what a “book exemplar” is; each physical unit has an accession number to uniquely identify it.

    But verb phrases are never correct table names.

    CREATE TABLE Stacks ---- not books!
    (accession_nbr INTEGER NOT NULL PRIMARY KEY,
    book_title VARCHAR (25) NOT NULL,
    author_name VARCHAR (25) NOT NULL,
    ddc CHAR(12) NOT NULL,
    isbn CHAR(13) NOT NULL,
    ..);

    CREATE TABLE Students
    (student_id CHAR(10) NOT NULL PRIMARY KEY,
    student_first_name VARCHAR(20) NOT NULL,
    student_last_name VARCHAR(20) NOT NULL,
    ..);

    Why you even think of triggers instead of DDL to control the number of outstanding books per student?

    CREATE TABLE Book_Loans
    (accession_nbr INTEGER NOT NULL
    REFERENCES Stacks(accession_nbr),
    student_id CHAR(10) NOT NULL
    REFERENCES Students(student_id),
    loan_seq SMALLINT DEFAULT 1 NOT NULL
    CHECK (loan_seq BETWEEN 1 AND 5),
    PRIMARY KEY (student_id, loan_seq),
    due_date DATE NOT NULL,
    ..);

    The book reservations are trickier because one of multiple copies can satisfy the request. I have no other business rules, but the skeleton will start with the student:

    CREATE TABLE Book_Reservations
    (student_id CHAR(10) NOT NULL
    REFERENCES Students(student_id),
    .. );

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    Celko, you are right regarding the point that I didn't give details. Thanks for your answer.
    I thought about the CHECK constraint in the creation of a table, but it only says you can borrow maximal 5 books. Suppose the following cases:

    - I go to the library, I borrow 5 books on Monday.
    - If I want to borrow one more book on Tuesday (for example), the CHECK constraint would be violated, but

    1) I didn't borrow books AT THE SAME TIME. I wanted to borrow a total of 6 books on different days, not at the same time, however I can not borrow more than 5 books on different days because the CHECK constraint in the declaration of the CREATE TABLE avoids this action. That is not what the task says, so, putting the CHECK constraint in the loan_seq wouldn't solve the problem...

    I hope I missunderstood your solution, if it is so, then sorry for my ignorance, sorry I am not a genius, I only try to understand...

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I can not borrow more than 5 books on different days
    That is not a problem; extend the constriant to have a date

    CREATE TABLE Book_Loans
    (accession_nbr INTEGER NOT NULL
    REFERENCES Stacks(accession_nbr),
    student_id CHAR(10) NOT NULL
    REFERENCES Students(student_id),
    checkout_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    loan_seq SMALLINT DEFAULT 1 NOT NULL
    CHECK (loan_seq BETWEEN 1 AND 5),
    PRIMARY KEY (student_id, loan_seq, checkout_date),
    ..);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •