[piston]

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

[r937]

what were your questions? and where's the ER diagram?

[piston]

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

[r937]

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

[piston]

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

[r937]

begin by defining a primary key for every entity

then make sure that all your relationships are defined using proper foreign keys