This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
"Give and Take House" (GTH) is an organization based in UK that helps to volunteers or donators to donate or contribute to different charity organizations or beneficiary institutes. As required by the management team of GTH, a database system should be created in order to provide a data collector which assist with the administration of the organization.
In this report it is delivered into six main parts. The first part is discussing the enterprise or business rule for producing the database application. The second part is showing the conceptual data model diagram for the database application. The third part is listing the Relational Schema of the database. The fourth part is the normalization check of the database. The fifth part is producing the SQL code for query results. The last part is result screen capture for the forms and reports.
In the first part, the enterprise or business rule for producing the database application will be defined. The assumption we used to define about the data will be discussed. The information that is not listed out in the sample application requirement or the deliverable requirement, and the constraint of the database construction must be included in the business rule.
In the second part, a conceptual data model diagram will be created according to the current situation and the requirement of the application. An entity relationship diagram with the Chen notation technique is used to create the diagram for the database system. The entities of the database, the relationship between each entity, the primary key of each entity, the structural constraints of each relationship will be illustrated in the entity relationship diagram.
In the third part, a relational schema will be produced according to the above conceptual data model and converted into a logical relational schema. The relational tables, the primary key and foreign key of each table, and the link for the connecting each foreign key to the corresponding table's primary key will also be included in this logical relational schema.
In the fourth part, a normalization declaration for the database application is produced. The logical relational schema of the application was created and it is verified that the Relational Schema satisfies the third normal form criteria. A functional dependency diagram for each table is created to confirm the database is in the third normal form.
In the fifth part, the database was created. SQL code is also prepared for query different results in order to fulfill the requirement of the sample application. Those SQL code includes list all volunteer who wishes to contribute a given activity, list all beneficiary institutes and the corresponding information, find out the total number of contributors under which category, and find the number of beneficiary institutes that under which category.
In the last part, the database system will be tested by running the SQL code prepared from the fifth part. The result of the query will be captured for ensure the SQL code is workable. Moreover, Form for creating new contributor, form and report for listing the allocated donated for a given volunteer, and form for listing the donation record for a given donator is also created in the database system. Therefore, it is necessary to run those forms and reports to test its functionality.
Enterprise or Business Rule (Deliverable D1)
The enterprise or business rule for "Give & Take House" (GTH) is determined as below
The number of contributors should be more than the number of donators
The number of contributors should be more than the number of volunteers
The number of contributors should be more than the number of person who is both donator and volunteer.
A person can have more than one role within the organization: Contributor, donator, volunteer, administrator, and governor
A contributor must be a volunteer, donator, or both volunteer and donator.
A Beneficiary Institutes can have more than one sponsor or caretaker
A contributor can donate to more than one activities
A Volunteer can persuade more than one donators
Contributors will not donate to the activity which is not organized by their favorite charity's category
Not all contributors will have additional corresponding address
A donator will not meet more than one volunteer at the same day
Volunteer will have only one total working hour record in each month
The value of address, phone and fax in Beneficiary institutes is not unique
The category of a activity must be the same as the category of the beneficiary institutes
The category of the beneficiary institutes must be the same as the category of the contributors that wish to contribute.
Entity Relationship Diagram (Deliverable D2)
The Entity Relationship Diagram for GTH database system is shown as follows
Relational Schema (Deliverable D3)
Relational Schema for GTH database system is designed as follows.
Normalization declaration (Deliverable D4)
It is verified that the Relational Schema satisfies the third normal form criteria, and the function dependency diagram shows the tables' attributes as below.
SQL Code for the application (Deliverable D6)
Refer to sample application requirement A1, the SQL code is designed to list all Volunteers who are willing to contribute in a specific given activity. It is assumed that the specific given activity is "Activity 3". The SQL code is listed as below.
SELECT Activities.ActivityDetail AS Activity, Person.Name AS Volunteer FROM Person INNER JOIN ((ActivitiesCategory INNER JOIN (Contributors INNER JOIN Volunteers ON Contributors.ContributorID = Volunteers.ContributorID) ON ActivitiesCategory.ACatID = Contributors.ACatID) INNER JOIN Activities ON ActivitiesCategory.ACatID = Activities.ACatID) ON Person.PersonID = Contributors.PersonID WHERE Activities.ActivityDetail="Activity 3";
According to sample application requirement A2, The SQL statement will be used to list all beneficiary institutes, their contact name, phone numbers, sponsoring organizations or caretakers of that institute. The SQL statement is produced as below.
SELECT BeneficiaryInstitutes.InstituteName AS [Institute Name], BeneficiaryInstitutes.ContactName AS [Contact Person], BeneficiaryInstitutes.PhoneNumber AS [Phone Number], SponsorCaretaker.Name AS [Sponsor or Caretaker] FROM BeneficiaryInstitutes INNER JOIN SponsorCaretaker ON BeneficiaryInstitutes.InstituteID = SponsorCaretaker.InstituteID;
In sample application requirement A3, the SQL code is defined to select a given category of Contributors (i.e. Donator, Volunteer or both) to find the total number of Contributors under that category. SQL code is listed as follows
SELECT Count(Contributors.ContributorID) AS [Number of Person] FROM Contributors INNER JOIN ContributorCategory ON Contributors.ContributorCategoryID = ContributorCategory.ContributorCategoryID HAVING ContributorCategory.ContributorCategory=[Type of Category:];
According to the sample application requirement A4, the SQL statement is constructed to list the type and the total number of beneficiary institutes under each type. SQL statement is described as follows
SELECT ActivitiesCategory.ActCategory AS [Institute Category], Count(BeneficiaryInstitutes.ACatID) AS [Number Of Institutes] FROM ActivitiesCategory INNER JOIN BeneficiaryInstitutes ON ActivitiesCategory.ACatID = BeneficiaryInstitutes.ACatID GROUP BY ActivitiesCategory.ActCategory;
Result Screen capture (Deliverable D10)
SQL statement query test
Firstly, we are going to run the SQL code to list all volunteers who are willing to contribute in a specific given activity, and it is assumed that the specific given activity is "Activity 3". After running query in MS Access by using the SQL code from the previous section, the result list out the volunteer "Reece Brown" is willing to contributor to the activity "Activity 3" (Figure 1).
Figure 1 - Result for running SQL code for requirement A1
Secondly, the SQL statement is used to list all beneficiary institutes, their contact name, phone numbers, sponsoring organizations or caretakers of that institute. After running query in MS Access by using the SQL statement from the previous section, the results are returned as figure 2.
Figure 2 - Result after running SQL code for requirement A2
Thirdly, the SQL code is used to find the total number of Contributors under to the category (Donator, Volunteer, and both donator and volunteer). After running the query in MS Access, A pop up window on the screen is generated as figure 3 for user to enter the category.
Figure 3 - popup windows when running SQL code for requirement A3
After entering the category of the contributor, the screen will list out the total number of person for that category as figure 4.
Figure 4 - Result returned after entering the type of category
Finally, the SQL code is created to find out all types of beneficiary institutes and the total number of institutes under each type. After running the query in MS Access, result is generated as figure 5.
Figure 5 - Result returned after running SQL code for requirement A4
Forms and Reports (Deliverables D7 - D9)
According to the deliverable D7, it is necessary to create a form for registering a new contributor record. In the MS Access database, double click the form "D7 - New Contributor", a form is popped up for creating new contributor record. After entering all the necessary information into the form, click "Create" button to save the record. A pop-up window will also come out on the screen to confirm the new contributor is created.
Figure 6 - Access form for registering a new contributor record
In the sample application requirement A5 (Also refer to the deliverable D8), the application can produce a report for listing out the allocated donated of a given volunteer. In the MS Access database, click the form "A5 - Select Volunteer", a pop-up window comes out and allow user to select the name of volunteer from the pull down menu. After selecting the volunteer, a report will pop up which shows all the allocated donators for the selected volunteer. (Figure 7)
Figure 7 - Access report for listing all allocated donator for a selected volunteer
According to the sample application requirement A6 (refers to Deliverable D9), the application should be able to produce a master/detail form for listing all donations, its type, date of donation, value of donation and the favourite charity or institute category for a given contributor. In the MS Access database, click the form "A6 - Select Volunteer", a pop-up window comes out and allow user to select the name of contributor from the pull down menu. After selecting the contributor, another Access Form will pop up which shows all the donation record for the selected contributor. (Figure 8)
Figure 8 - Access form for listing all donation record for a selected volunteer
After creating this database system, users from GTH can centralize the information into the database system. The database system can improve the operation of the office and assist with office administration. As a result, the requirement for the management team can be satisfied.