Unit Name: Unit Code: Lecturers: Weighting: Handed Out:
Overall Task
INDADD – Introduction to Database Design and Development U21259
Shelley Usher & Roy Abraham
60% of the final unit mark
Week 1
INDADD Individual and Group C oursework 2016/17
Produce a series of documents and SQL code for a database based on a specific case study.
Group Formation 0%
As soon as you can, get yourself into a group of 2 people from within your tutor group and use the online form in Moodle to register your group. Anyone not in a group by Friday 14th October will be allocated a coursework partner by me.
Coursework Schedule
This assignment is an unsupervised activity. You must work in your own time to meet the various deadlines for each submission. Any work which is handed in late and within 20 working days of the original submission date will be capped at 40% for that task. Submissions after that will receive a mark of 0% for that task.
formative assessment summative assessment
Sub
Description
Submission Date
Week
Where to Submit
Weighting
Type
Marking / Late Submission Deadline
Draft DFD
In your usual practical session
9
n/a
NIL
Group
Sub 1
DFD
Fri 2nd Dec 2016
10
Moodle
8%
Group
Fri 13th Jan 2017
Draft Use Case
In your usual practical session
11
n/a
NIL
Group
Sub 2
Use Case
Fri 16th Dec 2016
12
Moodle
5%
Group
Fri 27th Jan 2017
Draft ERD
In your usual practical session
15
n/a
NIL
Group
Sub 3
ERD
Fri 3rd Feb 2017
16
Moodle
10%
Group
Fri 3rd Mar 2017
Sub 4
SQL
Fri 31st Mar 2017
24
VM
32%
Individual
Fri 12th May 2017
Sub 5
Security
Mon 24th Apr 2017
CAP1
Moodle
5%
Group
Mon 22nd May 2017
1
Word Limit
Each task has been given its own word count. You must submit this number of words ∓10% i.e. if the word count is 100 words then you can submit between 90 and 110 words. Coursework that is more than 10% over the word limit will receive a 10 mark penalty
Format*
Document type: File name: Font:**
Spacing: Header: Footer: Page Title: Order: References:
MS Word or PDF unless otherwise stated
Must match the name shown at the start of each submission
Titles: Arial, bold, size 12 or 14
Text: Arial, size 11 or 12 (including text/labels on diagrams)
1.5 line spacing
Left: Student ID, Right: unit name (INDADD)
Page number
Task number and title, for example “Task 1: L0 DFD”
Each task should be printed on a new page and submitted in task order Harvard APA v6
*Failure to meet any of these criteria will result in a 10 mark penalty being applied to the work **Any work smaller than this will not be marked at all and receive a mark of zero
Plagiarism and Referencing
The coursework submitted should be substantially your own work but we do encourage you to research around topics and use external sources of information but you must state where your information came from by properly referencing or acknowledging the material using the Harvard APA v6 system whether the information is in a book, website, lecture slides etc. This method may be one you are unfamiliar with, so do not assume you can simply do as you did in college. Have a look at http://referencing.port.ac.uk/ for examples on how to correctly reference your work.
If you use a resource but don’t reference it (intentionally or unintentionally) you risk a possible plagiarism charge. Plagiarism is described as:
“The incorporation by a student in work for assessment of material which is not their own, in the sense that all or a substantial part of the work has been copied without any adequate attempt at attribution, or has been incorporated as if it were the student’s own when in fact it is wholly or substantially the work of another person or persons”
If we believe this has happened, you will be asked to attend an interview to discuss your work and may face disciplinary procedures as a result. See Section 21 of: http://www.port.ac.uk/accesstoinformation/policies/academicregistry/filetodownload,10383,en.pdf
If you are unsure that you have referenced your work correctly, please ASK your tutor.
2
Anonymity
All coursework should be anonymous, therefore you must ensure that you do NOT put your name on your work. Work should only be identified by putting your Student ID number on all pages submitted
Marks and Feedback
University regulations state that marks and feedback must be with you within 20 working days (approx 4 weeks) of each submission but this is a big unit (200+ students) so it takes a while to mark and sort as we are also teaching full-time. If we cannot meet the deadline you will be notified via Moodle and advised as to when you can expect your marks back.
Once marked, your marks and feedback will be released to you via Moodle. If you have any questions about your mark (usually “why is my mark so low”), please make sure that you have read the feedback supplied before you contact us as this often answers any questions that you have. If it doesn’t, please do pop in and see us for a chat.
Help
Unfortunately, every year there are students who obtain poor marks on their coursework and when we speak to them about it, they all say the same, “I didn’t understand it”. And when we ask them why they didn’t ask for help, “I didn’t want you to think I was stupid”. Please be assured that we will never think you slow/stupid/thick/useless/annoying etc. We would much rather you ask for help and give us the opportunity to help you understand the work, than you fail the unit. So please do ask for help early. We are always happy to give an opinion of draft work and answer any questions you may have either in class, in our office or via email.
And Finally
The majority of students begin work on an assessment in the week it is due. This is far, far too late and you end up rushing, running out of time and having no opportunity to ask for feedback and comments on draft work. Please start working on your coursework as soon as each topic is discussed in the lecture. You will then have approx 4 weeks to develop and refine your work before hand in.
3
Schlepp-adoo Event Management
Case Study
Three graduates in BSc Event Management have started an event management company to set up walking and racing events and to provide all the logistical support needed. The company’s services are packaged, and contracts are taken out by external clients such as charities who want to raise funds.
Among the activities the company looks after are:
1. The graduates meets with the prospective client to determine their requirements and
establish an event brief.
2. Meeting to discuss ideas, determine a solution to meet the event brief and allocate one of the three graduates to take the event forward, manage the project and produce the event proposal. As the business expands it is hoped to add a new role of ‘Event Planner’ (EP) and employ further staff to fulfil this role.
3. Presenting the event proposal, typically in the format of a presentation to the client’s management board and gaining project approval. Currently done by the graduate allocated above.
4. Creating the event:
a. Setting up named events, which can be repeated year on year. These will include scheduling, planning routes, advertising via a website, registration and payment for participants. Liaising with local authorities.
b. Allocating staff and deploying volunteers to help with specific events. This will include medical staff, with first aid qualifications. (An external organisation is used to supply the required number of trained and qualified staff as requested).
c. Sorting out logistics before the event. This will include:
i. assembling information packs
ii. asking participants health and safety questions, emergency contact details, and accommodating special requirements
iii. Arranging for accommodation (tents), washing facilities, evening meal and entertainment when an event spans more than one day
iv. Arranging for medals and hot meal at destination
v. Setting up waymarks and marshals for the route (with vehicles if necessary)
vi. Agreeing with landowners on route and location of feeding stations
vii. Erecting feeding stations, which will provide food and drink, medical attention, loos, transport if needed, water bowsers (for drinks and for participants to refill their water bottles)
viii. Waste disposal arrangements
d. Running the event on the day, including checking registration details and money raised by participants.
5. Approaching external organisations to discuss possible advertising and sponsorship opportunities during the event.
6. Provide collateral merchandising (tee-shirts, caps etc) and the means to pay for them.
4
7. Monitor the budget and staff / volunteer recruitment.
The company would like to make their operation more efficient by expanding their IS use, and would also like to move into providing for corporate events, such as team building, leadership training, reward days.
Identified Business Rules (Note: There will be many other business rules)
● an Event Planner can manage 0 to many events, an event is managed by only 1 Event Planner
● an event has 1 to many instances
● an event involves 1 to many Local Authorities
● an event can be attended by 0 to many delegates and a delegate can attend 0 to many
events.
● an event involves 1 to many staff and staff can work at 0 to many different events
● a role is carried out by 1 to many staff and staff can have 0 to 3 roles
● a destination has 1 to many tents
● a tent has 1 to 4 spaces
©Penny Hart and Shelley Usher, Sept 2016
5
Submission 1 GROUP: Data Flow Diagrams (8%)
Deadline: Friday 2nd December 2016 (week 10) 10pm
Submission: Via Moodle – One person in your group, submit a single pdf file named
UP654321_DFD.pdf (where 654321 is your student ID number) which contains both of your DFDs
Task 1: DFD L0/Context
Using suitable software (e.g. Visio) and the Gane & Sarson notation as shown below, create a context/level 0 diagram for your system. Hand drawn diagrams will receive a mark of zero.
Guidance:
1) Firstly check that you are using the correct notation or you will lose marks.
[3 Marks]
2) Draw a process box in the centre of your diagram to represent the system and number it
3) Think about what is inside and outside the system?
4) What are the entities (people and/or organisations) that the system interacts with? These will be your external entities. Add them to your diagram
5) How do they connect to the system? I.e. what data goes between the system and them? Draw arrows to demonstrate this.
6) Draw the system boundary
Marking Scheme:
Mark
Criteria
3
A clear concise diagram using correct notation and showing the system boundary, possibly missing an entity or 1-2 pieces of data.
2
A reasonable diagram possibly missing two of the following:
● System boundary
● An entity
● 1-2 pieces of data
1
A diagram with errors or omissions of key entities or data
0
Nil submission, major errors or uses the wrong notation
6
Task 2: DFD L1
Using suitable software (e.g. Visio) and the Gane & Sarson notation (see above), create a level 1 diagram for your system. Hand drawn diagrams will receive a mark of zero.
[5 Marks]
Guidance:
1) Save your level 0 diagram under another name i.e. use this as a start point for your level 1
2) Delete the box in the centre but leave the external entities where they are
3) Now think about all of the processes (tasks) that happen within the system, each one should be represented by a process box.
4) Where will the data be stored that these processes make use of or produce?
5) Move your data arrows (from level 0 diagram) so that they now interact with the correct process
6) Add in arrows to show the data moving between processes and data stores
7) Remember to follow the rules when connecting up all the parts of your system (see lecture slides).
Marking Scheme:
Mark
Criteria
5
A clear concise diagram using correct notation and showing the system boundary, possibly missing an entity or 1-2 pieces of data.
4
A good DFD with most entities, data flows and processes identified or all there but with minor errors
3
Partial answer – what’s there is a good attempt but it’s incomplete
2
A good attempt at the DFD but with several errors in logic and content
1
A poor DFD containing major errors, missing most entities, incorrect processes etc
0
Nil submission or wrong notation
7
Submission 2 GROUP: Use Case Diagram (5%)
Deadline: Friday 16th December 2016(week 12) 10pm
Submission: Via Moodle – One person in your group, submit a single pdf file named
UP654321_UC.pdf (where 654321 is your student ID number) which contains your Use Case diagram
Task 3: Use Case
Using suitable software (e.g. Visio), create a Use Case diagram for the system with all main actors. Hand drawn diagrams will receive a mark of zero.
[5 Marks]
Guidance:
1) Think about the system, who would interact with it? What role do they take? These are your actors. Also look at the external entities you identified in your DFD
2) Once you have identified the actors, in turn, put yourself in their shoes and ask yourself what tasks would they do, or would like the system to do for them.
3) Don’t break things down too far, we’re looking for major tasks here so I wouldn’t expect to see something like ‘enter address line 1’, ‘enter address line 2’ etc
Marking Scheme:
Mark
Criteria
5
Excellent diagram showing a depth of understanding of the system. All actors and use cases identified.
4
Good diagram showing a good understanding of the system. Maybe missing one actor or use case or minor errors or over complex
3
Average diagram showing a good understanding of the system. Maybe missing two actors or a few use cases or minor errors or over complex
2
Poor diagram missing three or four actors and/or use cases. Too little detail
1
Major errors: Poor diagram missing most actors and use cases or major errors in logic.
0
Nil submission
8
Submission 3 GROUP: The ERD (10%)
Deadline: Fri 3rd Feb 2017 (week 16) 10pm
Submission: Via Moodle – One person in your group, submit a single pdf file named
UP654321_ERD.pdf (where 654321 is your student ID number) which contains your ERD
Task 4: ERD
Using suitable software (e.g. Visio) and notation, create an ERD for your system showing:
● entities and their attributes
● primary and foreign keys
● the types of relationships between entities (cardinality)
Guidance:
1) Make sure you are using the correct notation
[10 Marks]
2) Start by thinking about what ‘things’(entities) do you need to store information about such as a publication or a customer.
3) Then think about what properties(attributes) you would need to store about each entity so that you could successfully retrieve information about it. For example, it would be sensible to store the name of a publication so that a customer could search by name. What other attributes would you give it that may be of use; publisher, price? Once you have sorted out the entities and their attributes you can start building your ERD tables.
4) After that, think about connections between entities. For example, a list of purchases might show the name of each buyer but what if we wanted to look up the address of the buyer? Where would you look for this and how can you link it to the purchases?
What are we looking for:
Criteria
Details
Notation
Should be correct with readable content/text
Entities (Tables)
Names
Completeness/Suitability
Attributes
Names
Completeness/Suitability
Primary Keys
Each table identified by a PK or composite key
Foreign Keys
All FK identified
Relationships
Named
Many-to-many relationships resolved
9
Marking Scheme:
Mark
Criteria
Notation & Structure
Entities
Attributes
Keys
Relationships
10
Correct notation. Sensible/logical content and structure.
All entities shown, named in the singular with appropriate names
Complete, suitable attributes, correctly named
All keys identified
Meaningful relationship names, all many-to-many relationships resolved
8-9
Correct notation. Sensible/logical content and structure.
As above but with 1 or 2 minor errors or omissions such as a poor attribute name or missing attributes
6-7
Correct notation. Sensible/logical content and structure.
As above but with a few minor errors or omissions
4-5
As above but with some errors and omissions
2-3
As above but with wrong notation and many errors and omissions
1
As above but with wrong notation and major errors and omissions
0
Nil submission
10
Submission 4 INDIVIDUAL: The Database (32%)
Deadline: Friday 31st March 2017 (week 24)
Submission: Via Moodle – Submit a single pdf file named UP654321_SQL.pdf (where 654321 is
your student ID number) which contains all of your SQL for the database creation and your queries
Note: Prior to beginning work on Submission 4 you are advised to look at the feedback you obtained for submission 1-3 AND to look at the model ERD answer which will be issued 21 working days after the deadline for submission 3. Then do either of the following:
1. Use the model ERD to implement your database (with or without improvements)
2. Use YOUR ERD to implement your database (with or without improvements)
Task 5: Database Creation
1) Within your VM, create a folder called I NDADD cswk
2) In that folder create a database called U P654321db
3) Create all of the tables you will need (based on your ERD – see note above)
4) Populate your database with relevant (where possible) or dummy data. For each table, at
least 10 records/tuples should be inserted, wherever possible. Your submission must include a printout or screen capture of the SQL you use to create and populate your database.
[8 Marks]
Guidance: Your database should be based around your ERD although it is fine to adapt/change it following feedback or more thought. If in doubt, ask your tutor. You will need to consider things such as good attribute names, using the correct data types, limiting the size or range of inputs, declaring primary and foreign keys and any constraints. We will also consider the efficiency and layout of your SQL
Marking Scheme:
Mark
Criteria
7-8
Database successfully created with all relevant attribute names, keys, data types, limits and constraints. Tables populated with 10+ records with relevant/dummy data. Excellent code layout
5-6
Database successfully created with attribute names, keys, data types but with wrong/missing limits and constraints. Tables populated with 7+ records with generally relevant/dummy data. Good code layout
3-4
Database successfully created with errors in attribute names, keys, or data types and missing most limits and constraints or perhaps most there but with errors. Tables populated with generally relevant/dummy data.
1-2
Database attempted but doesn’t work or successfully created but not populated
0
Nil submission or major errors
11
Task 6: SQL Queries
Write 4 queries for your database that support the needs of the business.
● Your queries must be meaningful and include Selection, Projection and Join.
● Your queries must target at least 3 different stakeholders stakeholders and show a variety of SQL commands.
For each query your submission must include:
● A short description of each statement (3 lines max – 1.5 line spacing).
● The query written in Relational Algebra
● The SQL query
● A screenshot of the command being run and the results output by your database.
[12 Marks]
Guidance: What we are looking for is; have you understood the business environment? Can you produce meaningful queries which would produce information of use to stakeholders? For example, a new customer (a new shop) might like to know, “what publications are published by News International?” or, “what publications are published every 3 months and are published by “Gamers Inc and are under £5”. The queries will also be marked according to their depth/quality so a basic query (example 1) will attract a lower mark than one which shows a deeper level of understanding and has multiple levels (example 2).
Example 1
SELECT eventName
FROM Events
WHERE eventManager = “Fred Bloggs”
Example 2
SELECT fName, lname, mobile
FROM Staff
WHERE eventName = “Uni Fun Run” &
Gender = “F” & Role = “medical”
Do not use these examples in your coursework, you will get a mark of zero if you do
Marking Scheme:
For each query allocate marks for each of the following:
Criteria
Mark
Details
Business need /description
1
Clear business need for the information returned by the query
0.5
Weak business need for the information returned by the query
SQL
1
Complex, multi-level query
0.5
Simple query
RA
1
Correct
0.5
Good attempt but minor errors
Note: Marks will be reduced if your queries are repetitive 12
Task 7: SQL Advanced Queries
Write 4 more queries using more advanced SQL statements:
● Your queries must be meaningful and include things such as Ordering, Aggregation functions, Grouping, etc.
● Your queries must target at least 3 different stakeholders and show a variety of SQL commands.
For each query your submission must include:
● The SQL query
● A short description of each statement (3 lines max).
● A screenshot of the command being run and the results output by your database.
[12 Marks]
Guidance: Again, the queries will be marked according to their depth/quality so a basic query will attract a lower mark than one which shows a deeper level of understanding and has multiple levels.
Marking Scheme:
For each query allocate marks for each of the following:
Criteria
Mark
Details
Business need /description
1.5
Excellent business need showing depth of thought and an excellent understanding of the sorts of information businesses need
1
Clear business need showing good understanding of the sorts of information businesses need
0.5
Weak business need showing some understanding of the sorts of information businesses need
SQL
1.5
Complex, multi-level query showing depth of SQL knowledge beyond that taught on the unit
1
Complex, multi-level query
0.5
Simple query
Note: Marks will be reduced if your queries are repetitive
13
Submission 5 GROUP: Security Report (5%)
Deadline: Monday 24th April 2017(cap1) 10pm
Submission: Via Moodle – One person in your group, submit a single pdf file named
UP654321_Security.pdf (where 654321 is your student ID number) which contains your report
Task 8: Security Report
Write a 500 word report advising Schlepp-adoo Event Management how to ensure the security of
their data.
Guidance:
1) This is a report not an essay so be concise, no waffle!
2) There is no specific layout, use sub-headings, bullet-points, lists etc if you wish to
3) Diagrams / images are allowed if they enhance your report
4) Remember to reference your sources, including lecture material
Marking Scheme:
[5 Marks]
Mark
Criteria
5
Correct UK English grammar and spelling. Excellent structure and presentation. Excellent consideration of physical and digital security covering all major risks
4
Correct UK English grammar and spelling. Good structure and presentation. Good consideration of physical and digital security but missing 1-2 major risks
3
Average UK English grammar and spelling. Reasonable structure and presentation. Consideration of physical and digital security but missing a few major risks
2
Average UK English grammar and spelling. Reasonable structure and presentation. Consideration of physical and digital security but missing several physical or digital risks
1
Poor UK English grammar and spelling. Poor structure and presentation. No real consideration of physical and digital security, missing most major risks
0
Nil submission or major errors
14