Database project assignment

DATABASE PROJECT

I need to prepare a report with 2 requirements

1) A carefully developed class diagram to show those aspects of the world that the database will store data about

2) A normalised data model that serves as the design that you will implement in software

The class diagram is the results of analysis work - studying the world. The data model , which leads on from the class diagram , is the result design work - taking the class diagram as ts starting point. If the data model is well excuted, with its entries identified, relations clearly expressed and attribute specified, and then the rest of the project - its implementation using the soft - will follow smoothly. In preparing the data model , you must show evidence that they have explicitly considered issues of normalisation.

Aim of Assignment

To demosrate an understanding of the basics of analysis and design for databases as well as to provide evidence of the use of the main feature of a database package.

You will be expected to demoestrate the following below, through the analysis, design and construction of a small database application:

· Selection of a suitable problem to be solved by a database application

· production of a class diagram sing UML notation - this is a logical database design that reflects the aspects of the world that you store data about

Production of a set of normalised relations - a physical database design

· Design of a data input screen or screens

· Design of a query screen

· Design of a report for use on screen and/or printing on paper.

You must choose your own database problems from the world around you. Examples are;

1) College,

2) Local business, or

3) Something associated with some hobby or pastime.

Suitable problems are those that require the recording of data on three to five related classes of things and allow the production of a number of contrasting reports.

Examiners will give low marks to any student who submits a database project that is just based on the customer-order model.

How to Prepare the Report for the Database Project

When preparing your report for the database assignment, you are asked to include the following items.

· A description of the database problem tackled.

· A class diagram of the application, showing the various classes identified and their associations. You must use UML notation.

· The normalised relations that you will implement in the software, showing the attributes and keys together with their field type and ‘picture’ (for example, the type of data that is held – text, a date, a number, etc).

· A sample table of the basic relations set up in the database software together with a small amount of data.

· Designs for data input screens and reports and queries produced.

· Very brief description of how the system is operated and the commands used to undertake each task. (Note: it is assumed that this is done by using interactive commands of the database package, not by any programming.)

· Examples of the reports produced

The report must be produced with the aid of a word processor and you are expected to insert relevant diagrams or screen shots into the text. Diagrams should either be prepared using a computer package, or perhaps done by hand and scanned in to the document.

The total report should be about six to eight pages of carefully laid out text, figures, diagrams and all examples of printouts or other necessary computer-generated reports.

Simple Examples (Please don’t use these examples)

Consider this example. Develop a database that will allow a person to review all the films that are on in London this week and discover at which cinemas they are showing. The aim is to help people plan their entertainment and book tickets.

At first sight this suggests two classes of things about which a system will store data – various films and various cinemas – and of course the association between them (an association is the name we use for the link between things of one class and things of another. This usage comes formula. Sometimes we express the same idea as a ‘relationship’). 2001: A Space Odyssey – a classic film from 1968 by Stanley Kubrick and in part about computers – is showing at five particular cinemas. A user of the database would want to know this to answer their query about where the film is showing. But, just knowing where is not enough. They will wanton know when. This will lead us to add another class – another class of relevant thing in the world – which we might call a showing or screening.

We will then need to reflect in our class diagram these three classes. Below are two simple examples of such class diagrams with the second one showing some of the attributes (data items) that we would want to store for items of each of the three classes.

Figure 2.1: A simple class diagram for films and cinemas.

The label 0..* at each end of the association in Figure 2.1 means that there can be zero, 1 or more films showing at a particular cinema (the cinema may be closed this week for redecoration), and that there can be zero, 1 or more cinemas showing a particular film. The key to database analysis is to be able to think about such associations and how they are expressed accurately in the class diagram. The ‘many to many’ relationship in Figure2.1 above, which is how the world looks at first sight, becomes resolved into the idea of a new class called ‘Showing’ which allows us to specify a particular film being shown at a particular cinema at a particular time–hence the simple 1 at one end of the associations shown in Figure 2.2below.

As an exercise explain what change you would make to the diagram in Figure 2.2 if a single showing could include up to four separate films. To get to the full answer to this question will require that you have studied Chapter 8, but even if this is your first read through the subject guide, you should be able to take the first steps to allow for this detail to be faithfully recorded in the class diagram.

Figure 2.2: A class diagram for films, showings and cinemas.

Example 1

A database for the Human Resources department of a company to hold information on employees and the department they work for. Data to be held include the employee’s:

• Family and first names

• Age

• Sex

• Address of residence

• Date of joining the company

• Department (administration, distribution, manufacturing)

• Job title (assistant, technician, specialist, consultant, manager)

• Head of their department (another employee)

• Line manager to whom they report

• Qualifications held

• Training courses attended.

The system should have an input screen to allow new employees to be added to the database and a screen to allow employees who leave to be deleted.

Similarly it should be possible to add or delete departments (this is an organisation that likes to reorganise itself) and to record when an employee moves from one department to another or from one job title to another (for example, a move or a promotion).

The system should produce the following reports on screen and on paper:

• A report that lists all female employees with an MSc.

• A report that shows, for each department, the employees sorted by family name.

• A report that shows all employees who joined the company before a given date in date order.

• A query to show an employee’s line manager.

Example 2

A database is to hold information on students, the courses they take and the teachers who teach them. Data to be held will include a student’s:

• Name

• Sex

• Age

• address

• Courses taken.

Each course has a name and meets up to three times during the week (for example, Tuesday 10–11, Wednesday 4–6). A course can have one or more teachers. The details of the teachers to be stored are:

• Name

• Telephone number

• Qualification.

The system will allow a teacher to record homework marks for students.

The system should have input screens to allow new students to be added to the database and a screen to allow students who leave to be deleted. Similarly, it should be possible to add or delete courses and teachers as well as to record a change in who is teaching or taking which courses.

The system must produce, on screen and on paper, a report that shows:

• A query of all the people who teach a certain student

• A report of all students who have done 60 per cent or less of their homework assignments

• A report, by course, of the students enrolled sorted by family name (for example, a register)

• A query as to all teachers who are teaching more than two courses

• A list of all students who should be in class at a given time (say, Friday between 9.00 am and 2.00 pm).

4