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