Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Exclamation Database Design help!!!

    I am currently working on a conceptual design for a database and i am trying to identify what tables i will need and what relationships there will be between the tables. The only problem i am having is with the fees and that fact they vary depending on the number of people on any specific course, and i dont know how i would implement this in the database, can any1 please help? the spec is below.

    Marmac Ltd wishes to create a database of its professional Training courses. The team delivers a number of training courses. Each course is delivered by one member of staff at the Faculty Training Suite; each staff is allocated a number. The policy of the faculty is to record the start date of each staff, the first and last name, the date of birth, home address, telephone number for home , mobile and work, sex, National Insurance Number and the specified salary. There are different courses offered by the Enterprise team. The fees vary for each course and on the number of delegates a company sends. For example, if a company sends one person, the charge may be 1200. If the company sends two people, the first may be charged 1000, but the second may be charged 800. The course can be attended by a number of delegates, subject to a limit for each course. A start and end date is assigned for each course with the start and end time. A delegate can register as an individual or through his or her company for one course or more after making sure of code, title and the description of the course. An invoice is sent either to the delegate or to his or her company. The invoice clarifies to the client or/and the company the date that the fee is paid, credit card number and method of payment (one full payment, or a deposit then completing the payment).

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so what have you got so far
    what entities have you identified to date
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2008
    Posts
    6

    Exclamation

    I have identified these entities and the attributes linked to them:

    Staff - staff no, start date, end date, first name, last name, DOB, home address, home tel, mobile tel, work tel, sex, NI Number and salery.

    Course - course no, cost, max number, start date, start time, end date, end time.

    Invoice - invoice no, fee date, card number, payment method, total cost.

    Delegate - delegate no, status(company/individual), course no.

    This is what i have up to now but I am not sure where I will factor in the varying price dependant on amount of delagates signed to the specific course. Should I seperate the cost of the course into a seperate entity(table)??

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Your design is OK as a 1st attempt but you're missing a number of things. I suggest you re-read your coursework (or at least the bit you gave us) carefully and check each little requirement and see how it fit's into your table design. The requirements here are quite easy to spot as they're separated by fullstops and commas.

    The bit about one price for the first person and a different price for the second person etc is quite hard to put into a table design. It would however be much easier to have a set of prices for each course, a price if just 1 person comes, another price if 2 people come etc. To be fair I don't understand why your tutor specified it the way he did - if a company is sending two people on a course then what does the company care that the 1st guy is being charged different to the second guy - they just want to know the total price or the price per person.

    Mike
    Last edited by mike_bike_kite; 10-27-08 at 05:10.

  5. #5
    Join Date
    Oct 2008
    Posts
    6

    Exclamation

    I have worked out my entities i think, now i have to move on to Entity-Relationship diagram. I have designed databases before for various project however i dont usually go through the proper development process so i have not done ER diagrams before. I was asked to:

    create a single Entity-Relationship (ER) diagram to represent the data requirements of the database (Conceptual Database Design). To simplify the diagram, only show entities, relationships, primary and foreign key attributes. State any assumptions (if necessary) to support your design.

    this ER is in relation to the data i have already provided. I have posted a image of what i believe the ER diagram to look like however it doesnt seem right to me, can anyone provide any feedback or help?? thanks


    http://img60.imageshack.us/my.php?image=erdiagrw5.jpg

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Personally I find it easier to read your tables in the text format you supplied earlier ie Staff - staff no, start date, ... this also makes it easy to see where the changes are.

  7. #7
    Join Date
    Oct 2008
    Posts
    6
    I would also prefer to do it that way but as its part of the spec i would be interested if someone with experience in designing ER diagrams could tell me if im doing it right or give me some pointers? also thanks for your advice mike_bike_kite if you can give me anymore advice I would appreciate it thanks.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by anixan
    ... if you can give me anymore advice I would appreciate it thanks.
    My general advice would be to listen to those who are trying to help you but never expect them to do your work for you. Our general policy on the forum is to help people to help themselves rather than to simply do their coursework. If you do get a spare 2 minutes to make your current design a bit easier to read then I'll be happy to spend a half hour looking over it.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anixan
    create a single Entity-Relationship (ER) diagram to represent the data requirements of the database (Conceptual Database Design). To simplify the diagram, only show entities, relationships, primary and foreign key attributes.
    you may want to do a bit more research into what an ER diagram looks like

    for instance, take this portion (attached)

    what does the double-headed arrow signify?

    it appears that you want Staff to be the entity, and StaffNo to be the PK, but what is CourseNo in this context? an attribute of the Staff entity? if so, it would have to be a FK to Course, yes? because you were to show only entities, relationships, PKs, and FKs

    and FKs only manifest themselves through relationships, which is what i think you were trying to do with the triangles

    the most common ER diagramming technique uses rectangles for entities, with the attributes listed inside them, and the rectangles connected by lines representing the relationships
    Attached Thumbnails Attached Thumbnails staff.gif  
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2008
    Posts
    6
    Hi i just need some help identifying the many to many relationships in my database. I have finished my ER diagram but I cant spot any many to many relationships is there any1 who can tell me if I am right or if there are some many to many relationships? I have put my relationship explainations below to help as well as my ER diagram.

    Run This is the relationship between the Staff and the Course. The relation is a one to one relationship as one member of staff runs one course and each course is run by one member of staff.

    Teach This is the relationship between the Course and the Delegate. The relation is a one to many relationship as one course teaches many delegates and many delegates are taught on one course.

    Receive This is the relationship between the Delegate and the Invoice. The relation is a one to many relationship as many invoices can be linked to one delegate and one delegate can be linked to many invoices.
    Attached Thumbnails Attached Thumbnails er diag.jpg  

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is it possible that one member of staff might run more than one course?

    or is every staff member restricted to only one course?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2008
    Posts
    6
    It is possible that they could teach more than one course as the spec only states that "Each course is delivered by one member of staff at the Faculty Training Suite". So i might change that to a one to many relation. is there any other problems or potential many to many relations that I have missed? thanks for the help

Posting Permissions

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