Database Management System
Budget: ₹600 – ₹1,500 INR
Case Background
Oakmont & Partners LP has been busy building data models for a number of new clients and Monica is excited to start a new modeling effort for a local non-profit that provides assistance to homeless and others in need. She is keen on applying her UML and ERD modeling skills that she acquired while taking a corporate training class taught by Ars Doceo. She knows that the first step in building a conceptual data model is to conduct requirements analysis. So, she sets up an interview with Kaileen Ormond, the Director of Social Pedagogy who has been with the organization for over 18 years. To make sure she remembers what is being said, she decides to record the interview. The following is a transcription of part of that recording:
"... Let me give you a scenario. So, last month we took in 132 new homeless individuals and families. After we register them and determine their eligibility to shelters, we assign them to a bed in a common dormitory room in a shelter -- if they are by themselves -- or to a small family room if they are a family of five or fewer. If they are larger, then we generally cannot help them and we refer them to social services. Once we assign them to temporary housing (we need to record the date of the intake, as they can only stay a maximum of six months), we have to see if they can qualify for any government assistance programs such as WIC. Those programs are only accessible to US Citizens or permanent residents; they are not open to asylum seekers or anyone who is undocumented or is on a non-immigrant visa."
Monica thinks she has enough to develop an initial data model but is reassigned to a new project where her skills in agile business analysis are needed. So, you are being asked to jump in and build an initial data model. Your task is to develop a conceptual and then a logical data model for the entities and relationships within the context of the above requirements, along with a full definition of all entities. Be sure to list all your assumptions used in the construction of the data models.
Problem 1 : Conceptual Data Model in UML
Express the data model in a UML Class Diagram using LucidChartLinks to an external site.. Label the relationships where useful. Use directionality indicators on the labels (▲▼▶◀). Add key attributes as appropriate with the stereotype «key».
To narrow and focus the scope of the model, consider only the specific requirements below. Those are the ones that the conceptual data model expressed in UML must support -- you may omit any other considerations as this is clearly is very large project. The likely implementation will be a small application, perhaps a web app and this data model will help inform the database design and the user interface.
track the names and key demographic information (birthday, country of birth, citizenship or visa status) of all individuals registered
track immediate familial relationships, e.g., children (son, daughter), parents, grandparents
track the shelter to which they are assigned and the type of housing (bed or family room), including address
know when they were registered and when their permit to reside in-country expires
track eligibility for government assistance: while you do not need to address this, how would you manage eligibility for different programs, e.g., a person might be eligible for one program but not for another
legal representative(s) hired by or assigned to them
If there are unresolved questions from the notes, post your question on Teams and incorporate the new findings into your model. You may discuss the problem and share insights with your peers in the class but you must build and submit your own model. Keep your model to about 6-8 classes/entities.
Problem 2 : Logical Data Model as ERD
After you have built your conceptual data model as a UML Class Diagram, create (in a separate page/tab), an Entity-Relationship Digram (ERD) in the IE (Crow's Feet) Notation of the same entities, i.e., "translate" the UML to and ERD. This may not always be done in practice, but we want you to practice using both notations.
Oakmont & Partners LP has been busy building data models for a number of new clients and Monica is excited to start a new modeling effort for a local non-profit that provides assistance to homeless and others in need. She is keen on applying her UML and ERD modeling skills that she acquired while taking a corporate training class taught by Ars Doceo. She knows that the first step in building a conceptual data model is to conduct requirements analysis. So, she sets up an interview with Kaileen Ormond, the Director of Social Pedagogy who has been with the organization for over 18 years. To make sure she remembers what is being said, she decides to record the interview. The following is a transcription of part of that recording:
"... Let me give you a scenario. So, last month we took in 132 new homeless individuals and families. After we register them and determine their eligibility to shelters, we assign them to a bed in a common dormitory room in a shelter -- if they are by themselves -- or to a small family room if they are a family of five or fewer. If they are larger, then we generally cannot help them and we refer them to social services. Once we assign them to temporary housing (we need to record the date of the intake, as they can only stay a maximum of six months), we have to see if they can qualify for any government assistance programs such as WIC. Those programs are only accessible to US Citizens or permanent residents; they are not open to asylum seekers or anyone who is undocumented or is on a non-immigrant visa."
Monica thinks she has enough to develop an initial data model but is reassigned to a new project where her skills in agile business analysis are needed. So, you are being asked to jump in and build an initial data model. Your task is to develop a conceptual and then a logical data model for the entities and relationships within the context of the above requirements, along with a full definition of all entities. Be sure to list all your assumptions used in the construction of the data models.
Problem 1 : Conceptual Data Model in UML
Express the data model in a UML Class Diagram using LucidChartLinks to an external site.. Label the relationships where useful. Use directionality indicators on the labels (▲▼▶◀). Add key attributes as appropriate with the stereotype «key».
To narrow and focus the scope of the model, consider only the specific requirements below. Those are the ones that the conceptual data model expressed in UML must support -- you may omit any other considerations as this is clearly is very large project. The likely implementation will be a small application, perhaps a web app and this data model will help inform the database design and the user interface.
track the names and key demographic information (birthday, country of birth, citizenship or visa status) of all individuals registered
track immediate familial relationships, e.g., children (son, daughter), parents, grandparents
track the shelter to which they are assigned and the type of housing (bed or family room), including address
know when they were registered and when their permit to reside in-country expires
track eligibility for government assistance: while you do not need to address this, how would you manage eligibility for different programs, e.g., a person might be eligible for one program but not for another
legal representative(s) hired by or assigned to them
If there are unresolved questions from the notes, post your question on Teams and incorporate the new findings into your model. You may discuss the problem and share insights with your peers in the class but you must build and submit your own model. Keep your model to about 6-8 classes/entities.
Problem 2 : Logical Data Model as ERD
After you have built your conceptual data model as a UML Class Diagram, create (in a separate page/tab), an Entity-Relationship Digram (ERD) in the IE (Crow's Feet) Notation of the same entities, i.e., "translate" the UML to and ERD. This may not always be done in practice, but we want you to practice using both notations.