You are asked to design a database that could be used by a relationship counselling service...

 

Transcribed Image Text:

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 offers: 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. 2. Implementation 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. 3. Queries 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? Activate 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? 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 offers: 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. 2. Implementation 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. 3. Queries 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? Activate 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 stepbystep approach to creating a data model for the co... View the full answer