Lecturer
Problem Solving with Software Tools: Assignment 2
You have been hired by Blantyre City Assembly (BCA) under a project named “Improving Life of
Communities By Providing Modern Facilities” – ILCPMF; Among other things the project donors and the BCA would like to assess townships to check if they have enough public facilities like Passenger Shelter (Bus Stops); Public Toilets, Recreation Parks, Clinics’, Markets, Water
Points, Deep Tanks for animals. They have selected a few townships for the initial pilot phase which include: Bangwe, Chimwankhuda, Machinjiri, Chilomoni, Kameza and Zingwangwa.
Because this will be assessed against population, for each township there will be a need to record the population demography particularly number of women, number of men and number of children regardless of gender.
From the specification above, you have been given the ERD below which should guide you in
a. Creating a database using Ms Access (which should have proper tables, forms, queries and reports) (15 tables, 5 marks forms= 20marks)
b. Queries as follows:
i. List all the locations that are in Machinjiri (showing location number, name and its population )
(4marks)
ii. A query showing the location and the public facilities that it has.
(6marks)
iii. A query showing each location and its demographic population (i.e. number of men , of women and children grouped by location)
(10marks)
iv. A query showing all the location that has no recreation park
(5marks)
c. For each report create a report showing the data as specified in b above
(15marks)
Note: Your sample data must be must be in such a way that it meets all the queries requirements e.g. some other locations must not have the creation parks.
LocationId(PK)
LocationId(PK), FacilityCode(PK)
1
Location
M
Has
1
Has
1
Population
FacilityLocation
,FacilityCode(PK)
M
Has
1
Facility
FacilityCode(PK)
PopulationCodeId(PK)
Submission:
1. To be done in groups of
You have been hired by Blantyre City Assembly (BCA) under a project named “Improving Life of
Communities By Providing Modern Facilities” – ILCPMF; Among other things the project donors and the BCA would like to assess townships to check if they have enough public facilities like Passenger Shelter (Bus Stops); Public Toilets, Recreation Parks, Clinics’, Markets, Water
Points, Deep Tanks for animals. They have selected a few townships for the initial pilot phase which include: Bangwe, Chimwankhuda, Machinjiri, Chilomoni, Kameza and Zingwangwa.
Because this will be assessed against population, for each township there will be a need to record the population demography particularly number of women, number of men and number of children regardless of gender.
From the specification above, you have been given the ERD below which should guide you in
a. Creating a database using Ms Access (which should have proper tables, forms, queries and reports) (15 tables, 5 marks forms= 20marks)
b. Queries as follows:
i. List all the locations that are in Machinjiri (showing location number, name and its population )
(4marks)
ii. A query showing the location and the public facilities that it has.
(6marks)
iii. A query showing each location and its demographic population (i.e. number of men , of women and children grouped by location)
(10marks)
iv. A query showing all the location that has no recreation park
(5marks)
c. For each report create a report showing the data as specified in b above
(15marks)
Note: Your sample data must be must be in such a way that it meets all the queries requirements e.g. some other locations must not have the creation parks.
LocationId(PK)
LocationId(PK), FacilityCode(PK)
1
Location
M
Has
1
Has
1
Population
FacilityLocation
,FacilityCode(PK)
M
Has
1
Facility
FacilityCode(PK)
PopulationCodeId(PK)
Submission:
1. To be done in groups of