University of Canberra

Faculty of Science and Technology                                      Semester 1, 2018

 

Database Design 5915 and Database Design G 6672

Assignment 2

This assignment is worth 50 marks which constituting 25% of the total marks for this unit.

Due date: Friday Week 12 of Semester 1, 2018 at 10:00pm

 

You need to submit all your design documents and your database (MS Access database) with this assignment as described in the assignment specification below to the Canvas website of this subject. Please contact your lecturer or tutor if you have any queries about this assignment.

 

1. General Information

The purpose of this assignment is to provide you with experience in analyzing, designing and implementing a solution for the scenario given below. 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 Canvas website of this subject. Marked assignments will be available from Canvas website of this subject.

 

2. Problem Description

Canberra Work Group (CWG) was established recently. It has several offices in Canberra. CWG requires your team to design a database system for the CWG. CWG 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 work a customer requires to be done at their property. 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 CWG database.

 

CWG offers several types of home repair services. Repair services are managed under CWG repair section. There exist several types of repairs that CWG provides. Details of all repairs services are stored in CWG database and these details include: service name, service number, service type and price. When a customer joins CWG, 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 CWG.

 

CWG has several staff members. For each staff member the following data is stored in CWG database system: staff first and last name, staff number, position, gender, date of birth, salary, position, name of the section he/she works in, internal telephone number and office number. CWG has 3000 customers with 15 full time staff members.

 

CWG consists of few sections. These are: information section, service repair section, customer support section, customer feedback and account section. The information about each section is: section name, email address, location. Each section has several telephone and fax numbers.

 

CWG offers several gardening and home repair courses. To take a course a customer makes an appointment with one of the staff in customer support section. The customer can also browse CWG website to find out about the details of all courses 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 request repair services from CWG. An invoice is issued for customers who enrol in a course or request a repair service. Each invoice has a unique invoice number. The data stored on each invoice includes the invoice number, course number and course name or repair service number and repair service 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.

 

The details of all courses offered by CWG is stored in CWG database. These details are: course number, course name, start date, end date, course fees, instructor number an instructor name.

 

CWG has several instructors who conduct courses at CWG. The details of instructors are stored in CWG database. Instructor details are: Instructor first and last name, instructor number, position, gender, date of birth, salary, internal telephone number and office number.

 

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 CWG database. The details of each notice sent to a customer is also stored in CWG database. These details include: notice number, customer name, invoice number, date of issue of notice.

 

For each payment made by a customer a receipt that lists details of the payment made is provided to the customer and it is recorded in the CWG database. Receipt details include: customer name, receipt number, date of payment, amount paid, invoice number.

 

CWG provides repair services upon a request from its customers. A customer first contact CWG and inform the staff at CWG about the repair services that he/she is required for their property. A job is then logged into the CWG database which includes the following details: job number, customer name, repair number, repair name, date of job lodgement, staff name of the staff who logged the repair job.

 

CWG provides an appointment for the customer where a CWG repair will visit the customer and to provide a quotation for the cost of the repair. For appointment reservation a customer enters his/her name (first and last name), contact details (telephone number and postal address), and preferred time and date of appointment. An appointment is then made for the customer at that particular time with a staff member. A repairer from CWG then will visit the customer at the date and time of the appointment and provides a quotation. The details of all appointments by CWG customers are stored in CWG database. These details are: Customer number, customer property address, staff number, repair description, quotation price. Once the quotation is accepted by the customer a repairer will contact the customer and organise a time to finalise the repair. The repair details are stored in CWG database. The details stored about each repair are: customer name, customer contact details (telephone number and postal address), and time and date of repair and repairer name, repair number.

 

An invoice is for each repair job performed for a customer. The data stored about each invoice includes: invoice number, repair number, payment due date, amount to be paid, property address, 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, invoice number) is stored in CWG 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, payment number, 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:

 

CWG has two seminar rooms. CWG customers can book seminar rooms. Details of CWG customers that book seminar rooms are stored in CWG 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 CWG 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 CWG database.

 

 

3. Requirements

 

CWG 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 CWG. For security reasons the views of customers and staff should be different.

 

CWG database should provide the following facilities for staff:

 

1. Two types of computer-generated reports are produced by CWG 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.

 

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

• Enter details of new customers,
• Enter details of new jobs,
• Check total number of existing customers,
• Check details of staff members in a section
• Check the details of all customers.
• Check details of all invoices that are paid
• Make an appointment for a customer

 

The customers should be able to use the CWG database to:

• Check detail of all available courses,
• Check telephone number of CWG staff members,
• Check the telephone number of each staff ordered by section number.

 

 

4. Assignment submission

 

For the scenario above:

 

(a) Identify entity types and their attributes, including the primary keys and any foreign keys for each entity. (Make sure that all your entities are in 3NF)

 

(b) Compile the E-R diagram of the system based on the entities your identified above (state all assumptions that you have made). Identify on your E-R diagram the relationship types and their multiplicity.

 

(c) Create a database using Microsoft Access based on your database design above. Enter three rows of data in each table you create in your Microsoft Access database you have created.

 

Include with your assignment a cover page containing student number, first and last name of all students in your group, your tutorial day and time, subject name and number as well as the name of your tutor.

 

You need to include with your solution the details of all assumptions that you have made for the CWG database. Only one group member will submit all your design documents with your Microsoft Access database to Canvas website of this subject.

 

Marking Scheme

DOCUMENTATION (Hard copy)

List of entities, attributes and primary keys (Normalisation to 3NF)            20

E-R diagram                                                          10

 

IMPLEMENTATION (Soft copy)

Tables and data                                                             8

Queries                                                                 8

Forms                                                                   2

Reports                                                                 2

TOTAL 50

 

Note:

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. Every group member is advised to retain a full copy of the material handed in for this group assignment. Marked assignments will be available from Canvas website of this subject.