Hello everyone!
I've just started a module on Database Design & Implementation and was wondering if someone with experience of ERD's could take a quick look for me.
Here is the scenario
Nightingale’s Nursery
Nightingale’s is a private nursery organisation which takes in children from the age of one year old up to the age of five years. Nightingale’s has 5 branches in the North Wales. The Nursery previously had a paper based system for all of its administration and would now like to part-computerise the administrative and personnel side of the business. (For the moment the payroll (holidays, absences and salaries) for staff will not be dealt with).
Each nursery can employ up to 50 staff and will need to have a minimum of 10 staff at each nursery:- these are either Manager, Nursery Room Teachers, administrative staff or auxiliary staff (e.g. cleaners and caretakers). At any one time each member of staff will only work at one branch.
Children enroll on sessions at the nursery. Most children will be enrolled for a whole term at a time. (They may attend either morning or afternoon sessions or both. Also if a child is enrolled in all the morning sessions they may occasionally stay for afternoon sessions.) A register has to be kept for each session listing the children attending the session and the nursery room teachers taking the session.
Only the nursery room teachers will take sessions with the children. There may be more than one nursery room teacher in each session. There are different numbers of children in each session as some children come for a few mornings or afternoons per week and other children are full-time. (Please note that there is no time-tabling system detailing the activities; children arrive for sessions and are then taken for various activities by the nursery room teachers)
For the purposes of the database one of the child’s parents will be regarded as having enrolled the child and will be responsible for the payment of fees. The fees for the nursery are calculated at a session rate. A session is regarded as a morning or an afternoon. Children can attend for any number of sessions in a week, with the maximum being 10 sessions (the nursery is not open on Saturdays and Sundays). At the moment the rate for each session for each child is £20. There are no reductions for having two children from the same family enrolled. Parents pay monthly.
The staff and children will need to have the names, addresses and phone numbers of up to two emergency contact people.
My task is to:
Read the scenario and produce a conceptual data model consisting of an E-R diagram, entity headings, constraints and any assumptions
I have attached my diagram attempt and below are my constraints and any assumptions:
Children must be between 1 and 5 years
At least 10 staff at each nursery with a max of 50
Each member of staff will only work at one branch
Only nursery teachers will take session with children
Each child has to be enrolled by a parent
Maximum of ten sessions per child per week
A register must be taken for each session
Staff + Children must have contact details of at least 2 emergency contacts
Open Mon - Fri
Fees paid at monthly session rate
I can't see many assumptions except if a child isn't booked for the whole term they are enrolled on a session by session basis.
Thank you very much for any input I want to do well at DB Dev and DBA! :)
