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).

so what have you got so far
what entities have you identified to date

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)??

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

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

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.

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.

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.

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

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.

is it possible that one member of staff might run more than one course?

or is every staff member restricted to only one course?

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