Given the following narrative description for Cheerful Cherubs Daycare Center:
a. Create a business process level REA model in either grammar or diagram format, for Cheerful Cherubs' sales/collection process. Be sure to include all relevant entities, relationships, attributes, and cardinalities (minimum and maximum).
b. Convert your business process level model into a set of minimal relational database tables. Be sure to identify primary and foreign keys in the relational tables.
Cheerful Cherubs (CC) is a daycare center that provides services to parents. (Note -- the parents are the customers, although the children are important to track, they are not the customers). CC is financed via debt. CC acquired the building in which it operates along with all the furniture and equipment. CC regularly purchases food and teaching supplies for its everyday use. CC provides day care for children from 3 weeks old to 5 years old. CC offers three types of service: Infant Care, Toddler Care, and Preschool. Each week of child care represents a separate "Service Engagement" and is documented via a service engagement invoice. Each invoice can include multiple types of service, since parents may have multiple children enrolled at different levels at CC. Types of service need to be input into the database before any child care is actually provided. CC's teachers are responsible for the child care services; at least one teacher is reflected on each invoice. Each child is assigned to a group; in rare circumstances, or when a child changes age categories, the child may be re-assigned to a different group. A group may consist of either 4 infants or 6 toddlers, or 10 preschool children. Each teacher is assigned to work with the same group each day, to provide consistency and stability for the children. If a teacher is ill or is on vacation, a substitute teacher cares for the absent teacher's group. A teacher may be re-assigned to a different group if circumstances dictate, and CC must keep track of all groups to which that teacher has been assigned. Teachers are entered into the database as soon as they are hired, before they have been assigned to a group and before they have provided any child care services. Children and their parents are entered into the database as soon as they come in for a school visit and express an interest in utilizing CC's services. If no age-appropriate group has an open slot for a child, the child is placed on a waiting list until an opening arises, or until enough additional children of that age are waiting (to warrant adding another teacher).
Parents are identified by their social security numbers. Since some children have parents that live at different addresses and have different work phone numbers, each parent is included separately in the database. Note: a child's legal guardian is considered a "parent" for purposes of CC. Children are assigned unique identification numbers, since they do not all have social security numbers. Parents are required to pay for services in advance. For example, on Friday of each week, parents must turn in written estimates of the number of hours for each specific day of the following week that they expect to leave their children, and they must pay for that number of hours. If parents need to leave children for more hours than estimated, they must pay for the extra hours along with their payment for the following week. Parents do not receive credit for unused hours, unless a child's illness requires the child to be absent for more than one day. In such cases, parents receive credit for each consecutive day of illness after the first day. For example, parents of a child scheduled for attendance Monday through Friday for 9 hours each day who misses Wednesday, Thursday and Friday due to illness, receive credit for 18 hours. Credit is deducted from the amount the parents pay for the next scheduled week.
Fees vary by type of service. Infant care costs $4.00 per hour, toddler care $3.50 per hour, and preschool $3.00 per hour. Each cash receipt (including those from loans and other sources) is processed by one of several CC cashiers. Cashiers are entered into the database as soon as they are hired, before they have processed any transactions. All parents associated with a child for whom payment is made for a service engagement invoice receive credit for that payment. For example, if Allison's mom pays for the child care for a particular week, the payment is considered to have come from both Allison's mom and from her dad. All cash received on a particular day is deposited into whichever one of CC's three bank accounts has the lowest balance; cash receipts cannot be recorded in the database without being associated with a valid bank account. Although bank accounts cannot be established without immediately depositing one or more cash receipts into them, CC must be able to enter the account information into the database before entering the related cash receipts.
In preparing your model and relational database tables, you must account for all of the following attributes. You may use the abbreviations (in parentheses) in your tables to save writing time. Do not add any attributes.
Parent Social Security Number (P-SSN)
Remittance Advice Number (RA#)
Service Engagement Invoice Number (Inv#)
Parent Name (P-Name)
Child Birthdate (C-BDay)
Cash Account Balance (Cash-Bal)
Service Type Code (S-Code)
Teacher Telephone Number (T-Ph#)
Child Name (C-Name)
Parent Home Phone Number (P-Home-Ph)
Parent Work Phone Number (P-Work-Ph)
Amount of Remittance advice applied to a particular invoice (RA-Amt-Applied)
Child Toilet Training Status (C-TT-Status)
Child ID Number (Child#)
Cashier Social Security Number (Csr-SSN)
Cash Account Number (Cash-Acct#)
Remittance Total Amount (RA-Amt)
Cashier Name (Csr-Name)
Teacher(s) providing care on a particular invoice (Teach-care)
Parent's Address (Par-Addr)
Cash Account Location (Cash-Acct-Loc)
Service Engagement Total Cost (SE-Tot$)
Date a Child was assigned to a particular Group (Date-Child-Assigned)
Service Type Name (ST-Name)
Number of Hours of Care for each type of service on a service engagement invoice (#Hours)
Address at which a particular Child lives (Child-Addr)
Service Type Hourly Rate (ST-Rate)
Service Engagement Line Item Cost (SE-Line$)
Teacher Social Security Number (T-SSN)
Group Number (Grp#)
Teacher Name (T-Name)
Number of Children in a Group (#Children)
Level of the Children in a Group, e.g. Infant, Toddler, Preschool (G-Level)
Date a Teacher was assigned to a particular Group (Date-Teach-Assigned)
Cashier Telephone Number (Csr-Ph#)

Question

Given the following narrative description for Cheerful Cherubs Daycare Center:
a. Create a business process level REA model in either grammar or diagram format, for Cheerful Cherubs' sales/collection process. Be sure to include all relevant entities, relationships, attributes, and cardinalities (minimum and maximum).
b. Convert your business process level model into a set of minimal relational database tables. Be sure to identify primary and foreign keys in the relational tables.
Cheerful Cherubs (CC) is a daycare center that provides services to parents. (Note -- the parents are the customers, although the children are important to track, they are not the customers). CC is financed via debt. CC acquired the building in which it operates along with all the furniture and equipment. CC regularly purchases food and teaching supplies for its everyday use. CC provides day care for children from 3 weeks old to 5 years old. CC offers three types of service: Infant Care, Toddler Care, and Preschool. Each week of child care represents a separate "Service Engagement" and is documented via a service engagement invoice. Each invoice can include multiple types of service, since parents may have multiple children enrolled at different levels at CC. Types of service need to be input into the database before any child care is actually provided. CC's teachers are responsible for the child care services; at least one teacher is reflected on each invoice. Each child is assigned to a group; in rare circumstances, or when a child changes age categories, the child may be re-assigned to a different group. A group may consist of either 4 infants or 6 toddlers, or 10 preschool children. Each teacher is assigned to work with the same group each day, to provide consistency and stability for the children. If a teacher is ill or is on vacation, a substitute teacher cares for the absent teacher's group. A teacher may be re-assigned to a different group if circumstances dictate, and CC must keep track of all groups to which that teacher has been assigned. Teachers are entered into the database as soon as they are hired, before they have been assigned to a group and before they have provided any child care services. Children and their parents are entered into the database as soon as they come in for a school visit and express an interest in utilizing CC's services. If no age-appropriate group has an open slot for a child, the child is placed on a waiting list until an opening arises, or until enough additional children of that age are waiting (to warrant adding another teacher). 
Parents are identified by their social security numbers. Since some children have parents that live at different addresses and have different work phone numbers, each parent is included separately in the database. Note: a child's legal guardian is considered a "parent" for purposes of CC. Children are assigned unique identification numbers, since they do not all have social security numbers. Parents are required to pay for services in advance. For example, on Friday of each week, parents must turn in written estimates of the number of hours for each specific day of the following week that they expect to leave their children, and they must pay for that number of hours. If parents need to leave children for more hours than estimated, they must pay for the extra hours along with their payment for the following week. Parents do not receive credit for unused hours, unless a child's illness requires the child to be absent for more than one day. In such cases, parents receive credit for each consecutive day of illness after the first day. For example, parents of a child scheduled for attendance Monday through Friday for 9 hours each day who misses Wednesday, Thursday and Friday due to illness, receive credit for 18 hours. Credit is deducted from the amount the parents pay for the next scheduled week. 
Fees vary by type of service. Infant care costs $4.00 per hour, toddler care $3.50 per hour, and preschool $3.00 per hour. Each cash receipt (including those from loans and other sources) is processed by one of several CC cashiers. Cashiers are entered into the database as soon as they are hired, before they have processed any transactions. All parents associated with a child for whom payment is made for a service engagement invoice receive credit for that payment. For example, if Allison's mom pays for the child care for a particular week, the payment is considered to have come from both Allison's mom and from her dad. All cash received on a particular day is deposited into whichever one of CC's three bank accounts has the lowest balance; cash receipts cannot be recorded in the database without being associated with a valid bank account. Although bank accounts cannot be established without immediately depositing one or more cash receipts into them, CC must be able to enter the account information into the database before entering the related cash receipts. 
In preparing your model and relational database tables, you must account for all of the following attributes. You may use the abbreviations (in parentheses) in your tables to save writing time. Do not add any attributes.
Parent Social Security Number (P-SSN)
Remittance Advice Number (RA#)
Service Engagement Invoice Number (Inv#)
Parent Name (P-Name)
Child Birthdate (C-BDay)
Cash Account Balance (Cash-Bal)
Service Type Code (S-Code)
Teacher Telephone Number (T-Ph#)
Child Name (C-Name)
Parent Home Phone Number (P-Home-Ph)
Parent Work Phone Number (P-Work-Ph)
Amount of Remittance advice applied to a particular invoice (RA-Amt-Applied)
Child Toilet Training Status (C-TT-Status)
Child ID Number (Child#)
Cashier Social Security Number (Csr-SSN)
Cash Account Number (Cash-Acct#)
Remittance Total Amount (RA-Amt)
Cashier Name (Csr-Name)
Teacher(s) providing care on a particular invoice (Teach-care)
Parent's Address (Par-Addr)
Cash Account Location (Cash-Acct-Loc)
Service Engagement Total Cost (SE-Tot$)
Date a Child was assigned to a particular Group (Date-Child-Assigned)
Service Type Name (ST-Name)
Number of Hours of Care for each type of service on a service engagement invoice (#Hours)
Address at which a particular Child lives (Child-Addr)
Service Type Hourly Rate (ST-Rate)
Service Engagement Line Item Cost (SE-Line$)
Teacher Social Security Number (T-SSN)
Group Number (Grp#)
Teacher Name (T-Name)
Number of Children in a Group (#Children)
Level of the Children in a Group, e.g. Infant, Toddler, Preschool (G-Level)
Date a Teacher was assigned to a particular Group (Date-Teach-Assigned)
Cashier Telephone Number (Csr-Ph#)

Quizplus · Accepted Answer

The Answer of Given the following narrative description for Cheerful Cherubs Daycare Center:
a. Create a business process level REA model in either grammar or diagram format, for Cheerful Cherubs' sales/collection process. Be sure to include all relevant entities, relationships, attributes, and cardinalities (minimum and maximum).
b. Convert your business process level model into a set of minimal relational database tables. Be sure to identify primary and foreign keys in the relational tables.
Cheerful Cherubs (CC) is a daycare center that provides services to parents. (Note -- the parents are the customers, although the children are important to track, they are not the customers). CC is financed via debt. CC acquired the building in which it operates along with all the furniture and equipment. CC regularly purchases food and teaching supplies for its everyday use. CC provides day care for children from 3 weeks old to 5 years old. CC offers three types of service: Infant Care, Toddler Care, and Preschool. Each week of child care represents a separate "Service Engagement" and is documented via a service engagement invoice. Each invoice can include multiple types of service, since parents may have multiple children enrolled at different levels at CC. Types of service need to be input into the database before any child care is actually provided. CC's teachers are responsible for the child care services; at least one teacher is reflected on each invoice. Each child is assigned to a group; in rare circumstances, or when a child changes age categories, the child may be re-assigned to a different group. A group may consist of either 4 infants or 6 toddlers, or 10 preschool children. Each teacher is assigned to work with the same group each day, to provide consistency and stability for the children. If a teacher is ill or is on vacation, a substitute teacher cares for the absent teacher's group. A teacher may be re-assigned to a different group if circumstances dictate, and CC must keep track of all groups to which that teacher has been assigned. Teachers are entered into the database as soon as they are hired, before they have been assigned to a group and before they have provided any child care services. Children and their parents are entered into the database as soon as they come in for a school visit and express an interest in utilizing CC's services. If no age-appropriate group has an open slot for a child, the child is placed on a waiting list until an opening arises, or until enough additional children of that age are waiting (to warrant adding another teacher). 
Parents are identified by their social security numbers. Since some children have parents that live at different addresses and have different work phone numbers, each parent is included separately in the database. Note: a child's legal guardian is considered a "parent" for purposes of CC. Children are assigned unique identification numbers, since they do not all have social security numbers. Parents are required to pay for services in advance. For example, on Friday of each week, parents must turn in written estimates of the number of hours for each specific day of the following week that they expect to leave their children, and they must pay for that number of hours. If parents need to leave children for more hours than estimated, they must pay for the extra hours along with their payment for the following week. Parents do not receive credit for unused hours, unless a child's illness requires the child to be absent for more than one day. In such cases, parents receive credit for each consecutive day of illness after the first day. For example, parents of a child scheduled for attendance Monday through Friday for 9 hours each day who misses Wednesday, Thursday and Friday due to illness, receive credit for 18 hours. Credit is deducted from the amount the parents pay for the next scheduled week. 
Fees vary by type of service. Infant care costs $4.00 per hour, toddler care $3.50 per hour, and preschool $3.00 per hour. Each cash receipt (including those from loans and other sources) is processed by one of several CC cashiers. Cashiers are entered into the database as soon as they are hired, before they have processed any transactions. All parents associated with a child for whom payment is made for a service engagement invoice receive credit for that payment. For example, if Allison's mom pays for the child care for a particular week, the payment is considered to have come from both Allison's mom and from her dad. All cash received on a particular day is deposited into whichever one of CC's three bank accounts has the lowest balance; cash receipts cannot be recorded in the database without being associated with a valid bank account. Although bank accounts cannot be established without immediately depositing one or more cash receipts into them, CC must be able to enter the account information into the database before entering the related cash receipts. 
In preparing your model and relational database tables, you must account for all of the following attributes. You may use the abbreviations (in parentheses) in your tables to save writing time. Do not add any attributes.
Parent Social Security Number (P-SSN)
Remittance Advice Number (RA#)
Service Engagement Invoice Number (Inv#)
Parent Name (P-Name)
Child Birthdate (C-BDay)
Cash Account Balance (Cash-Bal)
Service Type Code (S-Code)
Teacher Telephone Number (T-Ph#)
Child Name (C-Name)
Parent Home Phone Number (P-Home-Ph)
Parent Work Phone Number (P-Work-Ph)
Amount of Remittance advice applied to a particular invoice (RA-Amt-Applied)
Child Toilet Training Status (C-TT-Status)
Child ID Number (Child#)
Cashier Social Security Number (Csr-SSN)
Cash Account Number (Cash-Acct#)
Remittance Total Amount (RA-Amt)
Cashier Name (Csr-Name)
Teacher(s) providing care on a particular invoice (Teach-care)
Parent's Address (Par-Addr)
Cash Account Location (Cash-Acct-Loc)
Service Engagement Total Cost (SE-Tot$)
Date a Child was assigned to a particular Group (Date-Child-Assigned)
Service Type Name (ST-Name)
Number of Hours of Care for each type of service on a service engagement invoice (#Hours)
Address at which a particular Child lives (Child-Addr)
Service Type Hourly Rate (ST-Rate)
Service Engagement Line Item Cost (SE-Line$)
Teacher Social Security Number (T-SSN)
Group Number (Grp#)
Teacher Name (T-Name)
Number of Children in a Group (#Children)
Level of the Children in a Group, e.g. Infant, Toddler, Preschool (G-Level)
Date a Teacher was assigned to a particular Group (Date-Teach-Assigned)
Cashier Telephone Number (Csr-Ph#)

Given the Following Narrative Description for Cheerful Cherubs Daycare Center