12 35 d fleet01 xythos content blackboardcdn com south london universi
Search for question
Question
12:35
...d-fleet01-xythos.content.blackboardcdn.com
South London University (SLU) Lecture and Placements Administration
System
Case study for Information Systems and Databases
South London University's Computing school wishes to set up a database to manage its external
speaker and placements processes. This is all part of developing links with local businesses and
giving students the opportunity to get some industry experience.
At present, individual members of the teaching team organise guest lectures, but this is all done in a
rather random fashion. They might use their personal contacts or meet an interesting speaker at an
external event and invite him/her to give a lecture or run a workshop for their students. If the
speaker agrees, then the lecturer will contact the timetabling unit to arrange a suitable date, time
and room for the lecture and then announce details of the event through the Blackboard
announcement system.
With the increasing emphasis on giving students 'real world' experiences, more and more of these
events are being run and the school wishes to make the most of the contacts they have and which
topics these contacts have expressed an interest in talking about. Unfortunately, these contacts and
speakers are not stored centrally, which leads to problems, for example:
There have been occasions where multiple guest lectures have been arranged in one week
for a particular cohort of students, as each of the teaching staff concerned had no idea of
the other lecturers' arrangements.
• A number of guest lectures were arranged on the same topic, thereby duplicating the
content of a previous lecture.
• Staff have independently invited speakers from the same company.
•
Staff members have contacted an organisation to ask if it would consider offering
placement/workshop/guest lecture etc, to find that the contact has already been
approached and has turned down the request - so is annoyed that he/she is being targeted
again.
Staff members wanted to re-run specific guest lectures in a subsequent year for a new
cohort of students, but the staff member who had arranged the original lecture had left the
university without leaving details of the contact.
⚫ Students have turned up to the staff room wanting to know where a lecture was being held.
etc.. etc..
The school is very keen to set up a database as it feels that if everything is centrally stored it will be
able eradicate such problems and to use the contacts to greater advantage, for example to see if a
speaker (or someone within their organisation) would be interested in providing placements/job
experience or live projects for students, as well as guest lectures. The Head of School hopes that a
sensible timetable can be put together showing the guest lectures for the whole of a semester which
students and teaching staff could access. He would also like to monitor attendance at each of the
lectures/workshops and also to collect any positive or negative feedback from students to see how
popular the talk has been, which might indicate whether to re-run a particular talk the following
year.
4
The Head of Computing has suggested that, with a new system, teaching staff could put in requests
for a guest lecture on a particular topic, and the system administrator might then be able to suggest
a suitable external speaker for that topic from information held in the database.
A small number of companies are already supplying 'live' projects to the department and the Head
of School hopes that a lot more can be found. The details of these also need to be stored centrally,
together with the member of staff who is running the live project. At present nobody knows who is
doing what. The marketing manager for the school would also like to know about these live projects
and their outcomes- if completed successfully, they could raise some good publicity for the
university. The new system should have some mechanism for informing the marketing manager that
the project has completed so that timely publicity materials can be produced. 12:35
...d-fleet01-xythos.content.blackboardcdn.com
5 of 11
The Head of Computing has suggested that, with a new system, teaching staff could put in requests
for a guest lecture on a particular topic, and the system administrator might then be able to suggest
a suitable external speaker for that topic from information held in the database.
A small number of companies are already supplying 'live' projects to the department and the Head
of School hopes that a lot more can be found. The details of these also need to be stored centrally,
together with the member of staff who is running the live project. At present nobody knows who is
doing what. The marketing manager for the school would also like to know about these live projects
and their outcomes - if completed successfully, they could raise some good publicity for the
university. The new system should have some mechanism for informing the marketing manager that
the project has completed so that timely publicity materials can be produced.
The Head of School would also like there to be a designated staff member for each organisation,
responsible for nurturing and developing links with the organisation. Anyone wishing to contact a
company would need to liaise with the designated staff contact person. Members of staff should
have easy access to this information. The system should also be able to store a history of the
contacts made with each company and the reason for that contact, which teaching staff could refer
to.
The placement officer for the school also wants to maintain a database of placement or job
experience opportunities provided by organisations, and which students have been successful in
getting these placements, so that these students could be invited to talk to the student cohort on
their return from placement - again this is all part of getting students ready for the world of work.
School administrators will also benefit from this as currently they often unaware of which students
are doing placements. The new system will be able to clearly show which students are on placement
and who their designated supervisor is at their placement company (which may be different for each
student).
The Head of School and administrator think that useful reports could be generated from the
proposed database, for example to show how the school is interacting with businesses and the local
community, which lecturer has made the most effort at developing links with industry (and who
hasn't bothered at all...), and many other outputs.
All in all, the current system is a mess and you have been called in to sort it out. The above
description is just a basic outline of some of the issues and problems. You can make any sensible
assumptions to fill in any gaps. Good luck!
PORTFOLIO ELEMENT 2: MARKING RUBRIC
Element
Criteria
Fail
5
Satisfactory
Good
Excellent
Exceptional 12:37
...d-fleet01-xythos.content.blackboardcdn.com
Element 3 - Your ER diagram
• Your ER diagram should fit onto one
page and must use 'crows foot'
notation, as shown.
• It must clearly show primary and
foreign keys see example:
• Suggestion is to use draw.io or similar
tool.
• This must be incorporated into the
report, not submitted as a separate
file.
CUSTOMER
places
chane
thane
ORD
COMPLAINT
Element 4 - The Entity Specification Forms:
• An entity specification form is a table which ELEMENTS:
describes each entity on your diagram in
more detail: eg:
ENTITY NAME: COMPLAINT
Entity Description: stores all complaints made by customers
Attribute
complaintID
custid
complaintDate
reason
Data type
and width
Status
NUMBER(4)
NUMBER(5) fk
DATE
VARCHAR2(2) nn
pk
nn
Validation
Input limited to
PR (poor product)
SV (poor service)
DE (delivery issues)
Example of input and any other
relevant info
3000
10001
10-FEB-2022
PR
Entity Name: must match the name you have on
your ER diagram-stick to singular (CUSTOMER,
rather than CUSTOMERS). Don't have cryptic
names, try to make them as descriptive as possible.
Entity description: a short description of the entity,
what it's for, what it stores
Attribute: all attributes must match the names you
have on your ER diagram - as descriptive as
possible without making them too long
Data type and maximum width of the attribute
(date type does not need a width)
Here, provide Oracle data types-varchar2 (for
variable characters), char (fixed character length),
date, number-with maximum widths. Don't just
use defaults, providing a realistic max width is a
type of integrity constraint. Note that numbers can
have precision - eg NUMBER(6,2) allows for 6 digits
in total, 2 of which are after the decimal place
(allowing for a maximum value of 9999.99 to be
inserted into the column)
Element 4 - The Entity Specification Forms/cont
An entity specification form is a table which
ELEMENTS continued:
describes each entity on your diagram in more Status: indicate whether the attribute has any special
detail: eg:
Validation
ENTITY NAME: COMPLAINT
Entity Description: stores all complaints made by customers
Attribute
complaintID
custid
complaintDate
reason
Data type
Status
and width
Example of input and any other
relevant info
NUMBER(4) pk
NUMBER(5) fk
DATE
VARCHAR2(2)
nn
3000
10001
10-FEB-2022
nn
Input limited to
PR
1
PR (poor product)
SV (poor service)
DE (delivery issues)
status, eg:
Pk-primary key
Fk - foreign key
nn - not null - ie a value must be inserted into this field
when you insert a record
I-indexed field - this field contains an index - to be
covered in future lecture
Note that all these will need to be implemented using
appropriate constraints when you set up your tables
Validation: Here you need to provide details of any
validation you need to add to the attribute, eg coding,
restricting values to certain ranges etc. Again, these will
be implemented using appropriate constraints when you
set up your tables (where feasible, without resorting to
programming)
Example of input and any other relevant info:
Supply an example of input into this field (even if it's a
simple number) and any further info that will clarify what
this attribute about, if not immediately obvious 6
Element 5-SQL table creation scripts
• Provide table creation scripts for all the entities shown in your ER diagram
(so first set each table up in APEX to make sure it runs without errors, then
paste the code into your report).
• Note: the script should corresponu exactly to your entity specification forms, 12:44
...d-fleet01-xythos.content.blackboardcdn.com
2) Salesmen's commissions are all calculated at 2% of the order total, so don't need a separate table.
3) The complaints that customers make need to be recorded in the database, along with the salesman
that deals with the complaint. Only one salesman will deal with each complaint.
4) The salesman dealing with the complaint is not necessarily the same as the salesman that looks after
that customer's account.
5) Each complaint needs to be categorised according to the nature of the complaint (ie whether due to
poor service, delivery issues, or poor product issues)
3) ER DIAGRAM: (for your complete Entity Relationship Diagram) must fit onto one page, and must be legible.
(This example clearly just shows a snippet of the whole model)
CUSTOMER
PK custid
chame
drame
etc.
ORD
PK ordid
places
orderdate
custid (FK)
etc
COMPLAINT
raises
PK complaint D
custid (FK)
complaintDate
reason
00
8
4) ENTITY SPECIFICATION FORMS (for all entities shown on your ERD) Note: in 10 point font. Add
further rows to each entity spec, as necessary.
ENTITY NAME: CUSTOMER
Entity Description: stores details of all active customers
Attribute
Data type and
width
Status
pk/fk/nn
I
custid
NUMBER(5)
pk
Title
VARCHAR2(5)
cfname
VARCHAR2(15)
clname
VARCHAR2(15)
custType
VARCHAR2(1) nn
3333
nn
Etc.....
Validation
Example of input and any other
relevant info
Must be either P
(private) or B
(business)
ENTITY NAME: COMPLAINT
Entity Description: stores all complaints made by customers
10001
MR
JOHN
SMITH
P
Attribute
Data type
Status*
Validation
and width
Example of input and any other
relevant info
complaintID
custid
NUMBER(4) pk
NUMBER(5) fk
3000
10001
compDate
DATE
nn
10-FEB-2022
reason
VARCHAR2(2)
nn
Input limited to
PR
I
PR (poor product)
SV (poor service)
DE (delivery issues)
Etc for all entities
5) SQL TABLE CREATION SCRIPTS (10 point font)
CREATE TABLE customer (
custid
NUMBER(5) CONSTRAINT pk_customer_custid PRIMARY KEY,
cfname VARCHAR2(15) NOT NULL,
clname VARCHAR2(15) NOT NULL,
+
CHECK
CO
6