You are asked to design a database that could be used by a relationship counselling service to
manage data on their clients, their staff, and their sessions. Client information includes data on
individuals (adults and children) and the relationships between them. Session includes the time and
room that a counselling session took place in or is going to take place in. Here is what the service
Our services include relationship counselling for individuals and couples, family counselling,
counselling for children and young people, and divorce mediation. We can help you if you're single
or in a relationship, straight or gay, young or old. Whatever your circumstances we're dedicated to
helping you get the most out of your relationships, no matter how big or small the issue.
1. Data model
Create a data model for the counselling service. You will probably work through several design
iterations. You do not need to include early designs in your documentation. Use the approach and
notation we learned in class. Fully document your design process in a manner you would use if you
were a professional database administrator - you may lose marks for the inclusion of extraneous
information and unclear designs.
Note that you have not been given any attribute lists, nor have you been given a full list of entities.
You should therefore feel free to include anything that you think will be useful, but check what data
you will need to fulfil the queries that you are being asked to create in Part 3.
2a. Write SQL statements to implement your design. 2b. Implement your design using a suitable technology. Input enough data into your tables (however
much you decide although you will need a minimum of 30 adult women in the dataset for Part 4) so
that you can fully test your design and the queries you will write for Part 3.
Managers might ask for the following information. Write an SQL statement to answer each using
your test data to demonstrate that they work.
a. Which couple has been our client the longest?
b. Of the people who have had more than three sessions with us, what is the average number of
sessions they've had? (Three is the minimum that people get when they first attend the service, so
everyone gets three - here we are interested in those who stayed longer.)
c. Is workload spread about the counsellors evenly?
d. Can we see a time table for tomorrow? What clients are coming to see us, what rooms are they
booked into and at what times?
Answer rating: 100% (QA)
Designing a database for a relationship counseling service involves identifying the entities their attributes and their relationships Below is a step by step approach to creating a data model for theView the full answer