i have this brief and am having troubles creating the correct tables, the scenario basically is..
In university environment there are members of staff and students. SOme postgrad students are also working as research or teaching assistants are are therfore considered as members of staff as wekk, For each stuaff or student they keep name, library card number and degree are the course they are studying. We also keep course units there are studying and the grades they get for each couse unit. Also keep school(s) that are responsible for each course. FOr each research assistant we keep name, library card number and lecturers that they are helping. for each academic member of staff they keep name, library card number and course units they are responisble for. uni has a library for each member of staff or student can borow up to 10 books. items are either books, journals or technical reports. to each book the library assigns a number keeping title volumeand year it was published. for each journal is assigns number, title , volume and year it was published, for each technical report they keep author title and year it was published.
basically i need to create a database for taking out books, what tables should i use? any hepl would be greatly appreciated
a book has what is probably a better primary key.. its ISBN number
a book can have more than one author
a book may have multiple publishing dates
does your proposed design handle this, if not does it need to handle this?
As you have already identified a member of staff may also be a student.. mind you I do know of situations where a student may be registered on more than one course at any one time.
Can the library have multiple versions of the same book, ie can the library have more than one copy of the same book which it lends to students or members of staff. If so do you need to store a tile and author for each version of the same book?
what is the difference between a journal and a book?
how do you plan to handle a Journal which may have multiple articles, by other authors?
how actually do you propose to register who has what copy of what book
do you think your separation of students and staff works in the light of how you propose to register a book has been loaned out?, and how you propose to limit the number of books a borrower may have.
if you have separate staff and student tables then there is a risk, that unless you design out at the application layer that someone who is both a member of staff and a student may take out say 20 books (10 as a member of staff, and 10 as a student)
what are you proposed primary keys.. how do you plan on making each record unique
what are you prosoed foreign keys.. ie how does element a in table 1 realte to element z in table 4