Reference no: EM131477576

Database Design Assignment

General Information -

The purpose of this assignment is to provide you with experience in analyzing, designing and implementing a solution for a community garden center. Your solution should be implemented as a program for a database system using Microsoft Access. This assignment will help you to understand the nature and purpose of database analysis, design and implementation. It offers you experience in managing a technical database project.

This assignment is to be attempted by groups of 4 to 5 students. Each group is collectively responsible for both the submission and the outcome. Individual efforts will not be marked. There are no restrictions on the use of word processors or similar tools for the production of submissions for this assignment.

Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for non-completion of this assignment.

Submit your assignment to the Moodle website of this subject. Marked assignments will be available from Moodle website of this subject.

Problem Description -

The Canberra Community Garden (CCG) was established recently. It has several offices in Canberra. CCG requires your team to design a database system for the CCG. CCG database will record and store all data about each of its customers including their first and last name, postal address and home address (street number, street name, suburb, post-code, city), gender and type/s of plants a customer has and the types of plants a customer is interested to grow. The name (first and last name), contact telephone number and address (street number, street name, suburb, post-code, city, state) of customer's next-of-kin are also stored in CCG database.

CCG offers several types of plants. Plants are managed under CCG plant section. There exist several types of plants that CCG grows. Details of all plants are stored in CCG database and these details include: plant name, plant number, plant price. When a customer joins CCG, he or she is assigned a customer number and his/her details are recorded. Every customer is provided with a customer number and a sheet of paper describing the rules relating to membership and monthly charges and access to CCG support materials.

CCG has several staff members. For each staff member the following data is stored in the database system: staff first and last name, staff number, position, gender, date of birth, name of the section he/she works in, internal telephone number and office number. CCG consists of few sections. These are: information section, plant section, customer support section lost-stolen card section. The information about each section is: section name, location. Each section has several telephone and fax numbers.

CCG offers several courses. To take a course a customer makes an appointment with one of the staff in customer support section. The customer can also browse CCG website to find out the details of all course that are offered each month.

A customer can enrol in several types of courses. However a customer is limited to a maximum of 6 courses at any particular time. Customer's identity is established with their card number and their customer number. The customer's card number and their customer number are used to access customer records.

Customers can enrol in courses or purchase plants from CCG. An invoice is issued for customers who enrol in a course or purchase a plant. Each invoice has a unique invoice number. The data stored on each invoice includes the invoice number, course number or plant number and plant name, course duration (start date and end date), payment due date, amount to be paid, client's full name and name of staff who issued the invoice.

Each customer is required to make a payment for his/her invoice. The method of payment can be cash or credit cards. If there are any outstanding invoices for a customer then that customer will be sent a notice to pay his/her outstanding invoice. The details of each payment (payment number, customer number, invoice number, amount paid, date of paid) is stored in CCG database. For each payment a receipt that lists details of the payment made is provided to the customer and it is recorded in the database. Receipt details include: customer number, receipt number, date of payment amount paid, invoice number.

CCG provides small community gardens to people interested in having a small garden to plant herbs or flowers but they live in areas that has no gardens (such as apartments). CCG provides an appointment reservation facility where customers can make an appointment to consult with staff members about their garden needs and garden designs. For appointment reservation a customer enters his/her name (first and last name), contact details (telephone number and postal address), branch number and preferred time and date of appointment. An appointment is then made for the customer at that particular time with a staff member. Each CCG customer can rent a small garden. The details of all gardens rented by CCG customer are stored its database. These details are: Customer number, garden number, garden location, garden type, rental fees per month. An invoice is issued for customers who rent a garden. The data stored on each invoice includes the invoice number, garden number, payment due date, amount to be paid, customer full name and name of staff who issued the invoice. Each customer is required to make a payment for his/her invoice. The details of each payment (payment number, customer number, invoice number, amount paid, date of paid) is stored in CCG database. For each payment a receipt that lists details of the payment made is provided to the customer and it is recorded in the database. Receipt details include: customer number, receipt number, date of payment amount paid, invoice number.

For students enrolled in Database Design G, you are required to have the following details into your design and implementation of your assignment 2:

 

CCG has two seminar rooms. Customers and staff can book seminar rooms. Details of CCG clients that book seminar rooms are stored in CCG database. These details are: customer number, customer first and last name, seminar room number and date and time for which a seminar room is booked.

Details of staff that book a seminar room for a customer is also recorded in CCG database. These details are: staff number, staff first and last name, position of staff, date and time for which a seminar room is booked. Hiring of seminar rooms costs $130 per day for customers. Customers need to make a payment for any seminar room that they book. Details of all seminar booking and payments are stored in CCG database.

Requirements -

CCG database program developed by your team should have well designed screens that are easy to use, understand and follow by novice staff and customers of CCG. A novice user should be able to use the database application (program) by reading instruction on the computer screen of your program. For security reasons the views of customers and staff should be different.

CCG database program should provide the following facilities:

1. Two types of computer-generated reports are produced by CCG database system for staff.

The first report shows all payments notices that are send to customers. This report is ordered by customer's name and customer number. It is used to contact the customers with overdue payments. Customers are reminded to make a payment for the specified overdue payment and of the late charges that is incurred. If a payment is not made after a period of two months from the due date legal actions is usually taken. This report will provide the following information: first and last name of customer, postal address (street number, street name, suburb, post-code, and city), contact telephone number and all records of overdue payments as well as total amount of overdue charges.

The second report shows the details of all payments made by customers at each branch.

CCG has 3000 customers and 5 branches with 15 full time and 4 part time staff members.

2. CCG staff should be able to use the CCG database system to:

• Enter the details of new customers,
• Enter the details of new products (plants etc..)
• Check the total number of existing customers,
• Check the details of staff members in a branch or a section
• Check the details of all customers that current rent a garden.

3. The customers should be able to use the CCG database to:

• Make an appointment to consult with a staff,
• Check the location and details of all branches of CCG
• Check total number of staff at each branch.
• Check the telephone number of each branch ordered by branch number.

For the scenario above:

(a) Identify entity types and their attributes, including the primary keys and any foreign keys for each entity.

(b) Identify the relationship types and their multiplicity.

(c) Develop a data dictionary,

(d) Compile the E-R diagram of the system (state all assumptions that you have made).