The aim of this assignment is to undertake a range of tasks involved in analysis, design and build a database structure and applications for a database system for "Give & Take House" (GTH).
GTH is a non-political and non-profit organisation that co-ordinates people's donations of various types around the world and it is based in the UK (at present). It does not discriminate among various ethnic groups neither is it influenced by any geographical factors.
The collected money and any other donations are distributed among various organisations and voluntary bodies on request.
You are to undertake this assignment individually, although you may discuss ideas with your fellow students. However, the final submission must be your own work.
Where information is not available you should make reasonable assumptions. Make sure that you include all business constraints that have been captured during the analysis part.
Specification
The Director of GTH requires you to design a database system to assist with the administration of the office. The requirements collection and analysis phase of the database design process, which is based on the Manager's view, has provided the following requirements specification for the GTH database system.
The donations are collected in two ways from people. Hence, we classified the people according to their donations. For the purpose of this coursework, the following definitions apply:
1. A "Donator", refers to a person who donates money or equivalent items (e.g.gold, shares, properties, etc).
2. A "Volunteer" refers to unpaid helper who voluntary provide his/her service free of charge (e.g. professional people such as Physicians, Engineers,Lecturers, etc.) or assist in certain activities.
3. A "Contributor" refers to a person who can be a Donator, Volunteer or both.
Each month a record of the actual total hours spent by a Volunteer is recorded.
Labour hours are evaluated later in money by multiplying the monthly total hours by a suitable rate depending on the Volunteer profession.
A record of each Contributor's donation preferences must be kept. This is used to direct the donation to the favourite charity or institute at the Contributors' wish.
These preferences are restricted by the following categories: Religious Assistance,Natural Disaster, Health Care, Family Care, Humanitarian Aid and Cultural Care. A Contributor can have at most one corresponding address when it is different from
his/her usual address.
Comp1302 Cswk_DBD_G&T_Charity_May 11_V1.docx Page 3 of 7 printed on 18-Jan-11 Money contribution from a Donator must be all paid in one method only at any one time. However, a Donator can change his method of payment from one time to another. It can be Cash, Cheque, Bank Draft, Standing Order, or others. Non money payment by a Donator can be Shares, Property, Food, Clothes, Medicine and Bedding.
One of the most important activities of a Volunteer is to persuade candidate Donators to give their donations. A Volunteer is responsible for communicating with many Donators that are allocated to him. No other Volunteer is allowed to communicate unless he is doing it on behalf of the main allocated Volunteer. Changing the allocated Volunteer is allowed but must be recorded. A Donator can only be
approached by one Volunteer at one time. A record of the date of the last Donator contact and with whom it was made must be stored in the DB.
Initially there was a plan to keep details of all founders of the GTH, but recently it has realised that the list of Governors of GTH would be more useful. The information should include each governor's starting and finishing dates.
The term Administrator is used to anyone works at the GTH (voluntary or paid).
Some details on each administrator are to be kept. This includes name, address,contact numbers, type of contract, salary or hourly rate, as well as the position each occupies (e.g. a member, president), etc.
A person can be a Founder, Governor, Administrator or any combination of the above.
There is a high chance that any of these people become Donator (donates money or others) or Volunteer (labour participant in voluntary activities).
Contributions (money or otherwise) are to be delivered to Beneficiary Institutes.
Information is to be kept on each institute such as the name of the institute, contact name and address, name of any sponsors or caretakers, last annual spending budget (usually of the previous financial year). The DB also should hold information or brief
descriptions of any future activities that a beneficiary institute is planning to engage in and awaiting sponsorship. The category of each activity is also classified in the same way as the donators' preference categories above.
A sponsor or a caretaker of a beneficiary institute can be of one type. These include Religious Authority, Government Organisation, International Organisation,professional Society, Personal or Family. A beneficiary institute can have at most one corresponding address when it is different from its usual address. An institute can share an address, phone and fax with another institute.
End of further requirements specification Comp1302 Cswk_DBD_G&T_Charity_May 11_V1.docx Page 4 of 7 printed on 18-Jan-11
Sample Applications Initially, the following applications for the system are planned, but please note the list given below is only a sample:
A1. List all Volunteers who are willing to contribute in a specific given activity.
Produce the SQL code only (i.e. no form or report).
A2. For all beneficiary institutes, list the contact name, phone numbers as well as all sponsoring organisations or caretakers of that institute (if any). Produce the SQL code only (i.e. no form or report).
A3. For any given category of Contributors (i.e. Donator, Volunteer or both) find the total number of Contributors under that category. The user should be prompted to enter the category type on the screen. The user should be prompted to enter the category type at run-time.
A4. For all types of beneficiary institutes list the type and the total number of institutes under each type. Produce the SQL code only (i.e. no form or report).
A5. For a given Volunteer, list the names of all allocated Donators. The user should be prompted to pick a Volunteer name from a pull down menu (e.g. a combo box in MS Access) down menu). Produce the output on a report.
A6. For a given Contributor, list all donations, its type, date of donation, value of donation and the Contributor's favourite charity or institute category. The output is to be produced in a master/detail form. The user should be prompted to pick a Contributor name from a pull down menu.
Comp1302 Cswk_DBD_G&T_Charity_May 11_V1.docx Page 5 of 7 printed on 18-Jan-11 Deliverables:
D1. One A4 page, state clearly any assumptions (i.e. Enterprise or Business rules) that you make about the data; in particular, noting any information that you believe should be included, but is not mentioned in the outline specification.
D2. One A4 page containing the conceptual data model diagram (i.e. an Entity Relationship Diagram, using the Chen notation) for the system. Your diagram should show:
· Relevant Entity Types,
· You only need to show the following attributes in the conceptual model: a Primary Key for each entity; any multi-valued or derived attributes; Derived attributes & Relationships attributes1.
· Relationship Types with a role name (plus relationship attributes if any),
· Structural constraints for each relationship (both cardinality and participation).
Note: if you show attributes other than the Primary Keys (e.g. Foreign Keys) then you will be penalised.
D3. For the above model, produce a relational schema2 (i.e. transforming the conceptual data model into a logical relational schema) on one A4 page - DO NOT show the mapping steps.
Your Relational Schema should show:
· The relational tables.
· For each table identify the primary key (underlined) and any necessary foreign keys and all other attributes.
· Show the links between the tables by drawing for each FK an arrow pointing back to the original candidate key3.
D4. Normalisation check: You need to check your produced Relational Schema above for 3rd NF.
If it satisfies 3NF criteria then you ONLY need to include the statement "The Relational Schema satisfies 3NF criteria". If your schema does not satisfy the criteria of 3NF then you need to reproduce your schema in 3NF. You DO NOT need to show the steps (i.e. process) of
Normalisation.
D5. Create a DB for the above schema in an appropriate relational DBMS and populate each table with typical records to clearly demonstrate the application results. You DO NOT need to produce a snapshot of the tables. Please note that all students within a cohort must use the same DBMS as directed by your tutor.
1 To avoid cluttering the Conceptual Data Model with many attributes and to improve clarity, other attributes can be shown in the logical model (i.e. or listed in the relational schema). Any extra attributes which are not required to be shown on the CDM (as indicated above) will be ignored. Please note that presenting Foreign Keys at the CDM diagram is wrong and students will be panelised if they do so.
2 Please note that the choice of the DBMS is to be decided by your tutor. However, all students within the same cohort MUST use the same DBMS. No additional software (e.g. VB, Java, etc.) should be used to develop any of the applications. Instead, the tools (i.e. forms and report tools) provided by the chosen DBMS should be used.
3 You can show the links between Primary and Foreign keys on your relational schema as arrows starting from the FK and ending at the PK, as shown in the example below.
Doctor (DocNo, DocName, BirthDate, Address, City, PostCode, Tel_H);
Doctor_Qualification (DocNo, QualName, QualDesc, DateAwarded); /*DocNo referencing Doctor.DocNo */
Comp1302 Cswk_DBD_G&T_Charity_May 11_V1.docx Page 6 of 7 printed on 18-Jan-11
D6. Write the SQL code only (i.e. no form or report) for applications A1-A4 above. -DO NOT USE the QUERY BUILDER or any tools available with your DBMS. Code produced automatically by the wizard (i.e. tools) will be awarded ZERO.
D7. Using the appropriate tool from the chosen DBMS, create a form for registering a Contributor. This should have two buttons on it- one to commit a new Contributor to the Database and one to exit the form. A screen dump of this application at run-time is required.
For D8-D10, No additional software (e.g. VB, Java, etc.) or scripting language should be used to develop any of the applications. Use the tools (e.g. Form Builder) provided by the chosen DBMS.
D8. Using the appropriate tool from the chosen DBMS, produce a report for application A5 above.
D9. This is for application A6 above. Using the appropriate tool from the chosen DBMS create a master/detail form to perform this application.
D10. You are required to implement all above applications as well as submitting a one A4 page containing the SQL statements only required by the sample applications above. You need to test all your queries, forms and reports with sample data and make sure that all your applications produce some answers. You will be asked by your tutor, during your demo, to run some/all of the applications.
Your electronic submission should be in two files:
ONE PDF document consists of followings:
1. One A4 page for any assumptions and business rules. Ref. D1
2. One A4 page for Conceptual Model (CDM). Ref. D2
3. One A4 page for the mapped Relational Schema (i.e. Logical Data Model) showing links between Foreign Keys & Primary Keys. Ref. D3
4. One A4 page for Normalisation declaration (or a reproduction of the relational schema in Third Normal Form). Ref . D4
5. One A4 page for presenting SQL code for the required applications. Ref. D6 & D10
6. Screen dumps for all required applications in D6 and One Screen dump for each of deliverables D7-D9.
ONE Zip file containing all DB files (e.g. MS Access MDB file) or any relational DBMS4 such as SQL Server files, Visual studio source file, etc. Ref. D5