Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: Need help creating tables

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first thing to do is to break down your problme into bite size chunks

    ,..just wondering if you need to go through first (fnf) & second (snf) normal forms to get to your final table design?

    Id suggest you read up on normalistaion... Paul Litwin's text in Rudy's site is as good as any others I've seen... well worth a read through.

    So I dsuggest you have a read through thaet, and any other referneces on design and then have a stab at it..... You will learn far more form doing than just been shown or provided a solution

  3. #3
    Join Date
    Dec 2007
    Posts
    3
    right this si what i have so far please any advice would be appreciated

    bookid
    title
    author
    yr published

    [Ujournalid[/U]
    title
    author
    yr published

    technocalreportid
    title
    author
    yr published

    studentid(librycardid)
    fname
    sname
    type

    staff(librycardid)
    fname
    sname
    type

    schoolid
    schoolname

    courseid
    courseunit
    schoolid


    wh t do u guys reckon

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  5. #5
    Join Date
    Dec 2007
    Posts
    3
    New table 1: ItemTypesTable
    Fields: ID, Description
    Example data:
    1, 'Book'
    2, 'Journal'
    3, 'Technical Report'

    New table 2: ItemsTable
    Fields: ID, ItemTypeID, name, author, year published
    Example data:
    1, 3, 'Tech Report 1', 'J Bloggs', 2001

    ItemTypeID would be linked to your ItemTypesTable.

    this is how i have now done the book/jpirnal techical report table, does this maek sense??

    how can i link staff and students together without creating null entries? i would then link that tablke to the tables i have created so people can take out books.
    Last edited by marcusps; 12-03-07 at 17:28.

Posting Permissions

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