Database Design And Implementation Give Take House Computer Science Essay

Published:

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.

to.

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

Conclusion

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.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.