Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    13

    university bookstore data modelling assignment (was "Could someone help?")

    Hi I am at my wits end, been trying for a solution for days and I am stuck at two things in my ER Diagram,


    The bookshop gets its supply directly from the publishers and via intermediate distributor, just cant find a way two model a relationship to both from my orderline I have attached my er diagram can anyone help?

    The Bookshop has two main functions that your database must support. Firstly, it supplies textbooks to
    students as required for the course units taught at the University. Secondly, it performs the role of an
    ordinary bookshop, providing a range of books for purchase by the general public.
    To support both functions, the database will need to keep information about the books that it sells. Only
    a limited amount of information about books is to be held locally, as the Bookshop staff have access to
    large online services that provide comprehensive information about all books in print. However, the
    local database will certainly need to contain at least the title, author and ISBN of books, as well as the
    quantity currently in stock.
    The Bookshop obtains its stock of books either directly from publishers or via an intermediate
    distributor. Generally, each of these sources will supply several titles, and a given title may be obtained
    from a number of sources. If there is demand for a book that is not in stock, Bookshop staff will back
    order more copies from one of the suppliers of that book. The database will be used to keep track of
    these back orders.
    As with any bookshop, customers may come in and purchase any of the books available on the shelves.
    In addition, if a customer wishes to buy a book that is out of stock, he/she may order that book through
    the Bookshop. In this case, Bookshop staff must make a note of the customer’s contact information so
    that the customer can be notified when the ordered book arrives. The database will be used to keep
    track of orders from customers.
    The main function of the Bookshop is to supply textbooks to students as required for the course units
    taught at the University. To ensure that adequate supplies of textbooks are available, shortly before the
    start of each semester the Bookshop staff contact the lecturer responsible for organizing each unit that
    is scheduled to run that semester. Each lecturer is asked to submit a book-list for each of the units that
    he/she organizes. Each book on the book-list must be assigned a category, which must be one of “A”
    (essential purchase), “B” (recommended purchase), or “C” (background reading). In addition, lecturers
    are asked to give an estimate of the number of students expected on the unit. This information is then
    used by the Bookshop staff to calculate how many copies of each textbook should be ordered from the supplier
    Attached Files Attached Files
    Last edited by piston; 10-20-07 at 06:15.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what were your questions? and where's the ER diagram?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Posts
    13
    Hi, I have attached my diagram to orig post. My main problem is with the Booklist entity and its relationships. The other problem I have is with the distributor, supplier and backorder, I dont think I have modelled this correctly.

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    something definitely amiss, i see Publisher with a column called OrderID, with a one-to-many relationship to Backorder which has columns OrderNo (which appears not related to OrderLine) and Supplier (which appears not related to Distributor)

    what you need to do, i think, to help yourself along, is to identify the PK for every entity, and ensure that all the relationships have valid FKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Posts
    13

    Help with connecting three entites

    Hi, If it is possible could someone help me connect these three entites in my ER Diagram, BookList, Textbook, and OrderLine.

    Here is my ER Diagram

    Thanks
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    begin by defining a primary key for every entity

    then make sure that all your relationships are defined using proper foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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