University of Canberra
Faculty of Science and Technology Semester 1, 2019
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 11:55pm
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 Seminar Organization (CSO) is a professional organization that organizes and manages seminars. CSO wants to streamline their seminar operations and improve the efficiency and performance of sharing data and information with their three offices in Canberra. In consultation with users and its staff the following information is obtained during the fact finding about Canberra-Seminar.
Seminars are organised for the purpose of disseminating information about cutting edge computer technology being conducted for government, and private industry. Computer professionals come together at seminars to learn about new technologies.
CSO database designed and created by your group will maintain a record of all seminars that CSO organises in each of CSO office. Some seminars are organised every 6 months while others are organised every 12 months.
A seminar can be held at almost anytime during the year. Each seminar is advertised by its title, start date, end date, topic of the seminar, its website address and location of the seminar. The location includes the name of the venue (e.g. Canberra Convention Centre) and the physical address of the venue (street, city, state, country).
No two seminars (managed by CSO) are held in the same location (i.e. city) beginning on the same date. The duration of each seminar will be 1 to 3 days. The purchase price to be charged will be in Australian dollars per day per attendee (e.g. $350 per day).
Approximately two months before a seminar is to be organised, CSO sends an e-mail and an invitation letter to prospective people interested in seminars. For example, for a seminar to be held on 1st of June 2019, the advertising begins on 1st April 2019.
A prospective seminar attendee submits a completed form for a seminar from the CSO website. The details required to be completed in the form are: title, first name, last name, postal address, e-mail address, name of the seminar, the number of days that the person wishes to attend the seminar, the credit card number and the expiry date of the credit card.
When a form is submitted to CSO the form is assigned a unique attendee number and the details provided on the form is required to be stored in a database for CSO. CSO records the details of each form and date received. A form can contain only the details of one person.
CSO organises trade shows during some of their seminars. Traders fill in a proposal form to express their interest in attending a trade show and representing their software products. A prospective trader submits a filled form for attending and presenting their products at a seminar. The details required in the form are: title, first name, last name, postal address, e-mail address, the name of the seminar, credit card number and the expiry date of the credit card and the details of all products they wish to present at the seminar.
When a trader form is submitted to CSO, the form is assigned a unique trader number. CSO records the details of each form and date received. A form can contain only the details of one trader and their products details. A trader may have many products at each seminar.
CSO maintains a database of people who will be reviewing the product proposals of each trader. CSO adds new reviewer details each year to their list of reviewers.
The data stored about reviewers are: title, first name, surname, e-mail address and the topics that the reviewer is interested in (each reviewer selects three to five topics that they are interested in from a list of topics). Each topic has a topic number and a topic name. The list of topics is available at the CSO website. A reviewer chooses the topics that the reviewer is familiar with. The lists of topics that can be chosen by each reviewer are: Computer Architecture, Database Systems, Software Engineering, System Testing, User Interface Design, Data Structures, System Maintenance, System Documentation, Computer Security.
CSO automatically assigns a reviewer number as a unique identifier for each reviewer. Two reviewers review each trader’s product proposal. Once a product proposal is reviewed by a reviewer the reviewer will fill in a form from CSO website to inform the seminar organiser about their review. A reviewer submits one completed assessment form for each product proposal that is reviewed.
Each product proposal is then given an assessment result by each reviewer. The assessment result is either to accept or reject the reviewed product proposal. The reviewer’s assessment results are stored in CSO database with reviewer’s comments.
Traders are sent an e-mail confirming the acceptance or rejection of their proposal by one of the CSO staff. This e-mail includes the comments made by the reviewers about the product proposal reviewed. Details of confirmation letters emailed to all traders are stored in CSO database. These details are: Trader number, product details, results, date and time of email and staff number of the staff who has sent this email.
An invoice is sent to each attendee and each trader that intends to attend a seminar. Invoice details are stored in CSO database. Invoice details for an attendee include the following data: Invoice number, attendee number, due date and amount to be paid. Invoice details for a trader includes the following data: Invoice number, trader number, due date and amount to be paid.
Staff details are recorded in CSO database. CSO has 45 staff members in all their offices. All staff members will be able to access the CSO database that you will design and implement. The staff details are: title, first name, last name, postal address, e-mail address, office number and office address.
CSO uses travel agents for the monitoring of the accommodation booking for the seminar attendees and traders. The data stored about each travel agent in CSO database include: full name of the travel agency, location, travel agent first and last name, travel agent number, position, gender, date of birth, internal telephone numbers, e-mail address of the travel agent and the website of the travel agency. Each travel agent can have up to four telephone numbers.
Travel agents can book single or double bedroom accommodation in several hotel chains used by CSO. CSO stores full details of all hotel accommodations. For hotel accommodation CSO database stores the hotel number, room number, room type and daily rental price and hotel address. A seminar attendee or a trader may rent a room in a hotel. Rental agreements are issued at the start of each rental period with minimum rental period of one day. Each individual agreement between CSO and its clients is uniquely identified using a rental number. The data stored for each rental for each attendee include: rental number, duration (start and end date), attendee name and accommodation details and travel agent number. The data stored for each rental for each trader include: rental number, duration (start and end date), trader name and accommodation details and travel agent number.
At the start of each rental an invoice for the rental period is provided. Each invoice has a unique invoice number. The data stored about each invoice for each attendee’s accommodation includes the invoice number, rental number, duration (start date and end date), payment due date, amount to be paid, attendee full name and accommodation details and name of staff who issued the invoice.
The data stored about each invoice for each trader’s accommodation includes the invoice number, rental number, duration (start date and end date), payment due date, amount to be paid, attendee full name and accommodation details and name of staff who issued the invoice.
Each attend and trader are required to make a payment for each of their accommodation bookings. The method of payment can be cash, check or credit cards.
The data stored about each attendee’s payment includes the payment number, invoice number, rental number, duration (start date and end date), date of payment, amount paid, attendee’s full name and accommodation details. The data stored about each trader’s payment includes the payment number, invoice number, rental number, duration (start date and end date), date of payment, amount paid, trader’s full name and accommodation details.
CSO has a loyalty program for its conference attendees. Conference attendees of CSO can join the CSO loyalty program and receive one point for every dollar that they spend on CSO seminars. Each conference attendees of CSO that joins the CSO loyalty program receives a loyalty program number and their CSO loyalty points are stored with CSO loyalty number in CSO database. These details are: attendees number, total amount spent by an attendee for hotel accommodation, date, attendee loyalty program number, total loyalty points received. These details are: attendees number, total amount spent by a trader for hotel accommodation, date, trader loyalty program number, total loyalty points received. The loyalty points earned will be used to receive discounts to attend seminars.
3. Requirements
CSO 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 CSO. For security reasons the views of customers and staff should be different.
1. CSO staff should be able to use the CSO database system to:
Enter details of new customers,
Display details of hotels booked by each attendee and trader,
Display all details of attendees for each seminar,
Display all details of all payments received for each seminar,
Display all details of all traders.
Display all details of all unpaid invoices.
2. The customers should be able to use the CSO database to:
Check all detail of all available seminars,
Check all details of each staff of CSO order by staff number and office number.
4. Assignment submission
For the scenario above:
(a) Identify entity types and their attributes, including the primary keys and any foreign keys of 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 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 CSO 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 (Normalization to 3NF) 25
E-R diagram (based on entities and attributes you identified) 10
IMPLEMENTATION (Soft copy)
Views, Tables and data 5
Queries 5
Reports 5
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.