CP2236: Information Systems Design
Tutorial Exercises
Schedule:
Week 1:Lab session (unsupervised)
Week 2:Required System Logical DFDs (Tuts 2a & 2b)
Week3:Relational Data Analysis (Tuts 3a &3b)
Week 4:RDA (Tuts 4a & 4b)
Note: Students should also attempt to normalise the two forms given in tutorial exercise 5 before coming to the session next week.
Week 5:RDA (Consolidation and preparation for test)
Week 6:NO TUTORIAL (Time Constrained Test in lecture session)
Week 7: Rationalisation (Tut 5) and Report Design(Tut 7)
(Set up groups for Assessment 1.2)
Week 8:Screen Design (Tut 8) & GROUP SURGERY
Week 9:Program Design (Tut 9) & GROUP SURGERY
Week 10:GROUP SURGERY (Groups report to their assigned tutors)
Week 11:GROUP SURGERY (Tutors allocate timeslots for the group viva. Tutors answer any queries about the assessment.)
Week 12:GROUP SURGERY (Hand in Group report to Tutor)
Week 13:Group viva
WEEK 2: Required System DFDs: Tutorial Exercise 2a
Post-Graduate Admissions System
Wulfruna University processes applications for its post-graduate courses from its own (home) graduates, from graduates from other institutions and also from other applicants who would like to gain a higher qualification.
The Admissions Team records the date on which the application is received. The applications received are sorted into home students and others.
Home students are accepted if they meet the entry requirements. No references are required for home students. The Admission Team requests examination results from the Assessment Unit, as they do not have direct access to the results. The results are recorded by Course Code and Academic Year and the Assessment Unit keeps these.
Graduates from other institutions are rejected if they do not meet the entry requirements. If they meet the entry requirements, references are requested. If the references are satisfactory, they are accepted on the course, and if not, they are rejected.
Applications from other applicants, who are not recent graduates, are processed in the same way as those from graduates from other institutions.
All applicants are notified by letter advising them whether they have been successful or not. The date is recorded on which the notification was sent.
Current problems:
Often, applications received are recorded incorrectly.
References requested and received get lost in the system.
Receiving results from the Assessment Unit takes far too long.
New Requirements:
Automate the process of checking results.
Home applications and others should be recorded correctly.
References requested and received should be recorded.
Partly the analysis was done and the logical DFD (incomplete) is shown below.
Student Tasks:
You are required to produce the following data flow diagrams:
i)The Context Level DFD of the required system
ii)The Level 1 DFD of the required system
WEEK 2: Required System DFDs: Tutorial Exercise 2b
Sales Enquiry System
All telephone sales enquiries are recorded by the sales clerk on a two part (pink) enquiry form. The bottom copy is filed in the sales office and the top copy is sent to the Sales Manager, who allocates the enquiry to the appropriate Product Manager.
The Product Manager pursues the enquiry until
either
- A sale is made
or
- No sale is made and the enquiry is terminated
or
- Currently product is not available and the enquiry will be followed-up at a later date
In the first two cases, the pink form is returned to the Sales Manager with the result of the enquiry. The Sales Manager records these details on to a file held on his PC and the form is filed under ‘Completed Enquiries’.
The Product Manager keeps the outstanding enquiries and sends the results back to the Sales Manager within the agreed deadline.
At the end of each week, the Sales Manager’s secretary goes through the filed copies and prepares a report under the following headings:
Number of enquiries received
Number resulting in a sale
Number not resulting in a sale
Number of outstanding enquiries
The report is given to the Sales Manager. A Monthly Sales Report is generated and sent to the Regional Sales Manager.
If the enquiry results in a sale, confirmation of the order is sent to the customer and the Product Manager records the order details in a file and sends a copy to the Warehouse. The Product Manager updates the product file.
Problems:
- Enquiries are not chased to obtain a result.
- Order details are mislaid.
- As people leave, it is not clear who is responsible for which product line.
Requirements:
- Automate the enquiry recording system – no paper forms to be used.
- When a new product is introduced, the Sales Manager must assign that product to the appropriate Product Manager and also record the product details in the product file.
- The Warehouse should access the order details once the order is confirmed.
A partial analysis was done and the logical DFD (incomplete) is shown below.
Student Tasks:
You are required to produce the following data flow diagrams:
i)The Context Level DFD of the required system
ii)The Level 1 DFD of the required system
WEEK 3 Relational Data Analysis: Tutorial Exercise 3a
Normalisation
Using the data items in the Drug Card below perform normalisation to 3rd normal form. Layout of your answer should conform to the SSADM standard. State any assumptions.
The Hawthorns Veterinary CentreINVOICE
Client No: 9KL112
Mr J Smith Date: 30 June 2002
The Riddings Farm Invoice No: 1226D
Gladebrook Road.
Heaverton BR18 9KL
Treatment / Code / Date / Quantity / Unit Cost / Item Total
Clip Ewes Nails / T4335 / 4/5/02 / 5 / 3.10 / 15.50
Give Heifer drench / T3227 / 4/5/02 / 4 / 5.50 / 22.00
Ziloxyl Drench 250ml / D1276 / 4/5/02 / 4 / 3.375 / 13.50
Deliver Foal / T5534 / 10/6/02 / 1 / 25.00 / 25.00
Epohydrin / D7319 / 10/6/02 / 1 / 8.50 / 8.50
Zenecarp 20mg / D6498 / 10/6/02 / 8 / 0.96 / 7.68
Total for this
invoice / 92.18
Tutorial Exercise 3b
Perform normalisation to 3rd normal form on the report below. The purpose of this report is to indicate which customer will be serviced by which warehouse and where it is located.
HandyTool Ltd.Monthly Salesperson Report
Salesperson No: 1001 Date: May 1999
Salesperson Name: Bloggs, J.
Sales Area: West
Customer No. / Customer name / Warehouse code / Warehouse location / Sales
101 / Smith & Co / 2 / Bilston / 12,950.00
102 / Jones & Son / 4 / Oxley / 21,255.00
103 / Smithsons / 7 / Parklane / 10,199.80
Total Monthly Sales / 44,4404.80
You should make an attempt at Tutorial 4a before next week’s session.
WEEK 4 Relational Data Analysis: Tutorial Exercises 4a & 4b
Normalisation
Using the data items in the Spare Parts Request Form and Drug Card below, perform normalisation to 3rd normal form. Layout of your answers should conform to the SSADM standard. State any assumptions made.
Tutorial 4a
SPARE PARTS REQUEST FORM
REQUEST REFERENCE:Z201-354
NAME: FRED MCFEETIME:14:45
ADDRESS:32, SIX TREE LANE, PENN, WOLVERHAMPTONDATE:11/11/97
PHONE NO:01902 321000
PART No / PART NAME / ITEM CD / ITEM NAME / ITEM DESC / COLOUR / QTY0151 / Right Leg / EX701 / Pine King / Coffee Table / BLACK / 1
0216 / Nut / EX701 / Pine King / Coffee Table / BLACK / 5
0217 / Stool Top / ST401 / Bar High / Bar Stool / RED / 1
.
Tutorial 4b
Drug CardPatient No: 923 Surname: Face Forename: Baby
Ward No: 10 Ward Name: Greenfields
Drugs Prescribed:
Date / Drug Code / Drug Name / Dosage / Length of Treatment
20/5/88 / CO2355P / Cortisone / 2 pills x 3 day after meals / 14 days
20/5/88 / MO3416T / Morphine / Injection every 4 hours / 5 days
25/5/88 / MO3416T / Morphine / Injection every 8 hours / 3 days
26/5/88 / PE8694N / Penicillin / 1 pill 3 x day / 7 days
You should attempt to normalise/apply relational data analysis to the documents in Week 5 tutorial before next week’s session.
WEEKs 5 & 7 Rationalisation: Tutorial Exercise 5
There are NO Tutorials or exercises in week 6.
Apply relational data analysis to the staff allocation sheet and the invoice.
The following are required:
- A normalised set of relations from the analysis of each document.
- Rationalised set of 3NF Tables.
- Develop a data structure diagram to represent the rationalised tables.
- Compare this with the LDS given below and amend this to give an updated LDS.
LDS
Staff Allocation Sheet
This indicates which staff members are allocated to a project. A staff allocation sheet is drawn up for each project as the contract is awarded. The No. of Days column relates to the planned number of days that an employee will spend on the project. A project is assigned a unique code and each project will be carried out for a single customer.
Project Code:3411Project Desc:New Accounts
Cust No:3475Cust Name:British Bakers
Staff No.NameGradeNo. of Days
34BloggsS.Prog12
12JonesAnalyst3
23BrownManager9
Project Code:2356Project Desc:Betting System
Cust No:5134Cust NameBobs Bookies
Staff No.NameGradeNo. of Days
34BloggsS.Prog12
12JonesAnalyst3
23BrownManager9
Invoice
Each active customer is invoiced once a month for the work performed in the previous month. The Start and Finish dates refer to the overall project but the Man-Days column refers only to those worked in that particular month.
Invoice No:3412Date of Invoice:23/12/96
From:Systems Ltd
Wolverhampton
WV1 1SB
Cust No.3475
Cust NameBritish Bakers
AddressBread House
Albert Square
London
E12 6TY
Project CodeProject Desc Start DateFinish DateMan DaysCost
3411 New Accounts 15/10/9620/11/9613£13,000
3411Delivery Sys 3/3/9625/11/9658£42,000
Total Cost£55,000
WEEK 7 REPORT DESIGN: Tutorial Exercise 7
TUTORIAL:
1.The Video club wants to produce a report showing a detailed monthly sales analysis of its products. It is to contain the following details:
For each videoCategory
Video number
Video title
Number of loans of the video during month
Income generated during month
For each categoryTotal of loans during month
Total income generated during month
Grand TotalsLoans during month
Income during month
Design a report layout to provide the above information.
2.Design a pre-printed membership form for the Video Club. It is to contain
Name of member
Address
Membership number
Date of joining
Signature
Suitable logo + conditions of membership
WEEK 8 SCREEN DESIGN: Tutorial Exercise 8
TUTORIAL: SCREEN DESIGN
1.Design a character type screen for the Video club, allowing the user to display the current whereabouts of a selected video.
2.Design a character type enquiry screen for the Video club to display all the titles, and their whereabouts for a selected category. Whereabouts is either in stock or the name and address of the user who has it out on loan.
3.The Video club wants a screen program to allow members to book a video or videos by telephone. The program must firstly allow the member’s details to be verified – the user may not know their membership number, so a partial access by name is required. They may also not know the video number only the title. More than 1 video may also be required.
Design suitable windows type screens for the above program.
WEEK 9 PROGRAM DESIGN: Tutorial Exercise 9
STUDENT REGISTRATION
On joining the University, a student must be added to the computer system as a new student.
The data involved are the student’s number, and the course code for which the student is registered. The tables (files) used are the STUDENT file and the COURSE file.
1.What are the likely data items and key fields for these files, and how are they linked?
2.Write down the processes carried out by the update program that adds a new student to the system. Take care that the correct validation is carried out. Do this by way of a Decision Table and Structured English.
CHANGING A STUDENT’S COURSE
A student moves from one course to another. This must be reflected by updating the computer system.
Write down the processes carried out by the update program that carries out this change. Take care that the correct validation is carried out. Do this by way of a Decision Table and Structured English.
CP2236 ISD: Tutorial ExercisesPage: 1 of 13