Advanced Database Concepts
U21278
Coursework 2016 – 17
Lecturer: Dr Penny Ross
Submission Information:
This Unit has a 2 Stage Submission. Plus SQL Sign Offs in the Labs.
Submission 1 consists of. Q1, Q2 & Q3
Submission 2 consists of Q4, Q5 & Q6
Each written submission must be handed in on the due date to the CAM Office -remember the office closes at 4.00pm any work submitted after 4.00pm will be marked as LATE and capped to 40%
Each submission should consist of a printed, typed document of your answers plus (for submission 2) either a CD / USB or Electronic Access to the files that make up your database. If you do not provide a printed document you marks will be automatically reduced by 20%.
Submission Dates:
Lab Activities 4 & 5 MUST be signed off prior to the Christmas Break
Submission 1 – Friday 20th January 2017
Lab Activities 7 & 8 MUST be signed off prior to 27th March 2017
Submission 2 – Monday 27th March 2017
SQL Sign Offs in Lab Sessions (copies of these will need to be included as an appendix in Submission 2)
Feedback to help you improve your work will be given during Lab and Seminar Sessions throughout the year prior to formal submission
Marks & Feedback on submitted work will be given within 20 WORKING days of submission
Additional Information:
This is an individual piece of coursework.
This work is worth 100% of your overall unit mark.
Aim of the Assignment: The aim of this coursework is for you to conduct in depth study on the design and creation of a database system relating to real world concepts.
Learning Outcomes:
This coursework covers the following unit Learning Outcomes:
- Critically review application systems in order to optimise performance and assess their potential to be scaled
- Evaluate distributed environments with regard to performance issues.
- Recommend hardware and software solutions where database applications are to be scaled.
- Review the role of database application in strategically supporting business
The word limit GUIDELINE is 4,500 words +/- 10%. You should divide this between all the questions using the % of the question mark as a guide to how much is expected per section.
However, this is only a guide, as marks are allocated for good research, clarity of expression and pertinent facts. If you write repetitive or irrelevant information you will not get good marks! Reports of excessive length may be penalised.
The total Marks are 200 for the coursework – this will be converted into a percentage to give the overall unit mark. Each submission is worth 90 marks — plus a total of 20 Marks for SQL Sign Offs (5 marks for each lab sign off). These will be added together, divided and a percentage calculated.
Plagiarism and Poor Scholarship
This piece of work requires you to conduct research on your chosen topic. You are expected to understand how to reference using the Harvard APA 6 system. Therefore, marks will be deducted for poor scholarship and NO marks will be awarded for the entire assignment if ANY part of it is found to be directly copied from printed / published work or another student.
Use respected web sites for your information gathering.
To avoid plagiarism rewrite using your own words, but ensure where you have used the ideas of others you acknowledge the source within the text and provide the full source in the references at the end of the document (using Harvard APA format). The majority of your document will need be referenced, unless the idea is an original one of your own. If you are not sure how to reference use the information provided by the Library.
Presentation
Proof read you work for spelling, grammar and English prior to submission as clarity of expression is an important part of a research coursework.
Please include page numbers and you student ID in the footer of the document (on every page).
Anonymity
All coursework should be anonymous, therefore do NOT put your name on your work. Only identify your work by putting your Student ID number in the footer of the document.
The analysis you perform and the suggestions you propose are limited by major constraints.
Information – The amount of information you can gather for this work is limited. Remember, real world decisions usually have to be based on incomplete information.
Assumptions – The number of assumptions you make will affect your analysis. Good rational assumptions are a key ingredient in good solutions, since there is never enough information available. You should identify and explain your rationale for any assumptions that you make.
Knowledge – The amount of knowledge you have about the business and the information systems is limited. As you conduct your research you should further improve your understanding.
Case Study: (Details attached)
All your answers should not be generalistic but specifically relate to the case study.
This coursework follows the lifecycle of database implementation. The first stage is to try and identify the data and the organisational / linkage of the data for the system. This we represent in an EERD.
Questions
(Submission 1)
Q1
Using the information given in the case study, draw an Enhanced Entity Relationship Diagram for the system. On the diagram include the Primary and any Foreign Keys in each of the tables.
Do NOT include any other attributes on the EERD. The EERD should be printed (e.g. not hand drawn) on ONE side of A4 of paper.
This diagram is to explain the system design to the people you are working with. It may not be what you eventually end up building. But we need to clarify business objectives with the organisation using the EERD before we start development. In addition to what is shown on the EERD you WILL have to make some assumptions about how you interpret the system. Clearly state any assumptions you have made about the system. These assumptions should only relate to the design of the system. You WILL have to make assumptions as the case study does not give you enough information to complete an accurate EERD that will be exactly what the client requires. You cannot ask the client so you have to make assumptions.
10 marks for the EERD and 5 marks for the assumptions. Question Total 15 Marks
After you have completed the logical design, you have to consider the physical design of a system. Having an idea of the tables (from the EERD) is a start but now we have real world issues to contend with.
Q2
Discuss the design decisions you will have to make to ensure successful implementation of the database on ONE Site. (i.e. A centralised database architecture, not a web database)
These decisions include, but are not limited to, how to obtain the exact requirements (clarification of assumptions), selection of RDBMS, file systems, indexes and underlying hardware decisions. Many of these decisions will be based on a Transaction Analysis which you need to show only for the KEY tables.
You should try to include all issues that you would need to address to ensure that you are successful when implementing the database system for the client.
50 Marks
Database security should be considered early on, during the design process so that it is an integral part of the system, not just an add-on later.
Q3
Critically evaluate the threats the Database System faces. Discuss the security measures you will take to protect the System. Your discussion should focus on what you consider to be the FOUR most important issues to a CENTRALIZED database (not a web based database) and only address database security i.e. Not Network Security etc. Your answer must specifically address the coursework database and NOT just general security issues.
25 Marks
Lab Activity 4 & 5 MUST be signed off before the Christmas Break to Achieve the SQL Lab marks.
(Submission Two)
Once you database has been developed and is working there may be performance issues, at this point you have more of an idea how the database is being used and any problems will start to show.
Q4
Critically evaluate how you would monitor, tune and optimise the Database. Your answer MUST focus on the case study and NOT just general optimization issues.
20 Marks
Q5
There are always options to the architecture of database systems; they may be Web Databases, Distributed Databases and Mobile Databases
Extend your design for ONE of the following scenarios.
Scenario One:
The database is going to cover a MULTI site company and you should extend your design to a Distributed Database System.
Discuss any design issues you previously identified and change anything you consider needs changing. You should also include the additional design requirements for a Distributed Database System including, but not limited to, data allocation, data fragmentation, data replication, optimization, security, backup and recovery strategies, distributed query processing and underlying infrastructure changes from the centralized system discussed in Q1.
50 Marks
OR
Scenario Two: The Company would like to use MOBILE database technology. In this scenario the company would like to give some of their key workers Tablet PCs
Discuss any design issues you previously identified and change anything you consider needs changing. You should also include the additional design requirements for a Mobile Database System This discussion should include, but is not limited to, local subsets of the database schema, query and update in real time, required updates between the central server and the employee’s local database, what needs to be synchronised and how often, Contingency if damage were to occur to the tablet, Specific security threats introduced by mobile databases, optimization, underlying infrastructure changes from the centralized system discussed in Q1. This question is NOT about mobile databases that reside on phones (mini databases – SQL-Lite), it is about mobile access to a centralized database
50 Marks
Q6
Write Four SQL statements that reflect the needs of the business. These statements should include some of the more complex SQL syntax you have learned this year. For example: Unions, Minus, Inner or Outer Joins. These queries may be written for the CENTRALIZED scenario – do NOT try to write queries for a Distributed Database System! The FOUR submitted queries should based on the CASE STUDY and not those marked in the Lab Sessions.
Marks will be awarded for the level of complexity, the query being of use to the business, good input and output formatting.
Please state for each query what the query will extract and why you believe the query is of use to the business.
You should create the tables you require for your queries (not the whole database) and enter approximately 10 rows of data into each table. You should use a RDBMS to run the query and output the results from the RDBMS (e.g. by cutting and pasting or screenshot from the system). Queries not presented in this format (e.g. typed by word processor) will have marks deducted.
Include your create table & insert statements in the appendices.
20 Marks
Marking Scheme
First Submission
Q1
EERD
Entities 5 Marks
Logic 2 marks
Relationships 3 Marks
Naming 2 Marks
Use of Enhanced Concepts 3 Marks
e.g Composition, generalisation, specialisation etc
assumptions 5 marks
Total Q1 – 15 Marks
Q2
Discuss the design decisions you will have to make to ensure successful implementation of the database on ONE Site. (i.e. A centralised database architecture)
Total 50 Marks
35 – 50 Marks – Work in this range will demonstrate a clear understanding of the key issues relating to the design decisions required for the system. Appropriate selection of file types/ indices/data structures/ hardware and requirements will be shown for all tables and the system. With clear justification of selection. Students may support their work with specific examples, quantitative data, implementation or any additional material that supports their discussion. The argument will indicate an understanding of the underlying issues relating to transaction management, concurrency, performance, and recovery. Work will have relevant references and show critical insight into database design. Work will evidence that you have widely researched outside the unit material supplied.
20 – 35 Marks – Work in this range will demonstrate that you understand key issues relating to database design and the decision that have to be made. A good selection and explanation of the file types/ indices/data structures/ hardware and requirements will be shown for all tables and the system, which will have a justification. Students may support their work with specific examples, quantitative data, implementation or any additional material that supports their discussion. The argument will indicate an understanding of the underlying issues relating to transaction management, concurrency, performance, and recovery. Work will have relevant references and show insight into database design. Work will evidence that you have researched outside the unit material supplied.
0 -20 Marks – Work in this range may demonstrate that you have attempted to address the issues of database design. There will be information on file types/ indices/data structures/ hardware and requirements for all tables, this should relate to the case study. There may be little support of the arguments. Work should be referenced. Material used will not stray much further than that provided in the unit material or text book.
Q3
Critically evaluate the threats the Database System faces. Discuss the security measures you will take to protect the System. Your discussion should focus on database security.
Total 25 Marks
A good answer will focus on database security issues and not network or internet security. The discussion will be in-depth and detailed showing an understanding of the technical issues of implementing security, the strengths and weakness of each security solution, and how it applies to the scenario. It may also cover the issues with the users and how they may affect security. Again technically orientated showing you understand not only the concept but how it works, the benefits and disadvantages. The answer will relate to the scenario and will not be a general discussion on the issues.
12 – 25 Marks
The answer will focus on database security issues and not network or internet security. The discussion will show an understanding of the technical issues of implementing security, the strengths and weakness of each security issue. It will show an understanding but a less technical explanation may be given. The work will be related the Scenario. Work will be well research, well referenced and may contain information beyond that provided in the course material.
0 -12 Marks
The answer may not entirely relate to database security and contain other security issues. The discussion may show some mis-understanding of the topic and not be able to explain any advantages or disadvantages. There may be no technical information given. The discussion may not relate to the scenario. Little evidence of research outside the unit material.
(Total Marks for Submission 1 – 90 marks)
Submission Two
Q4
Critically evaluate how you would monitor, tune and optimise the Database.
Total 20 Marks
10 – 20 Marks
A clear discussion on the need for, strategies for and techniques for, good optimisation. A range of methods and issues should be discussed. At the top level Students will show they understand the concepts of optimization. There will be mention of SQL tuning and the heuristics for optimizing queries. Mention of RAT graphs. Also issues such as denormalisation, file types and indexing will be referred to and the contribution to performance that they make. Students should show they understand how queries can be optimized through the use of and possibly examples showing Relational Algebra and / or Relational Calculus.
0 – 10 Marks
Any mention of the topics above but discussion may be weak or have inaccuracies. GBD for mentioning optimization strategies. Answer will show an understanding of the concepts but may be a bit vague on the technicalities and a poor answer will show that you do not really understand the issues and reasons for the importance of this topic.
Q5a
Extend your design for ONE of the following scenarios.
Scenario One:
The company is a MULTI site company and you should extend your design to a Distributed Database System.
Total 50 Marks
35 – 50 Marks
A good answer will show that you understand the architecture of the DDBMS and understand the complexity of design of a DDBMS.
It should cover issues such as fragmentation, distribution, replication, locality of reference. It will show an understanding of the issues involves in concurrency, and recovery. Discussion of performance and security will show the advantages and disadvantages of this type of database design. You will demonstrate an understanding of the complexity and the technicalities. This discussion will relate to how the database may become or be designed as a DDBMS. (Note when mentioning security you either refer back to this question or just mention the threat, it does not need to be discussed). Good research & referencing and work may go beyond the unit material.
20 – 35 Marks
An answer in this range will show that you understand the architecture of the DDBMS. Data storage strategies will be discussed – perhaps not as well as the above. There should be a discussion on performance and security. The answer may not include the level of discussion at a technical level and may draw more on the concepts about how the DBMS handles being distributed. There may be some attempt at relating the discussion specifically to the case study. Good research & referencing and work may possibly go beyond the unit material.
0 – 20 Marks
An answer in this range will show that you understand the architecture of the DDBMS. However there may be errors of fact, mis-understanding of how the DDBMS works, a lack of insight into security and performance issues. You might not be able to explain any advantages or disadvantages. There may be no technical information given. There may be flaws in the discussion of how it would apply to the company.
For all of this question a very good answer will be technically oriented showing that you understand not only the concept but how these things work. The topic needs to be related to the case study to show that you understand some of the issues and implications of having a DDBMS. A reasonable answer will show an understanding of the concepts but may be a bit vague on the technicalities and a poor answer will show that you do not really understand the issues and reasons for the importance of this topic.
Q5b
Scenario Two: The company would like to use MOBILE database technology. In this scenario the company would like to give some of their key workers Tablet PCs.
Total 50 Marks
35 – 50 Marks
A good answer will show that you understand the architecture of the Mobile Database System. It should cover issues such as fragmentation, distribution, replication, subsets of data, and update in real time, required updates between the central server and the employee’s local database, what needs to be synchronised and how often. Demonstration of the importance of the network architecture and performance. It will show an understanding of the issues involved transaction management & in concurrency, and recovery. Discussion of performance and security will show the advantages and disadvantages of this type of database design. You will demonstrate an understanding of the complexity and the technicalities. This discussion will relate to how the database may become or be designed as a Mobile Database. (Note when mentioning security you either refer back to this question or just mention the threat, it does not need to be discussed). Good research & referencing and work may go beyond the unit material.
20 -35 Marks
An answer in this range will show that you understand the architecture of the Mobile Database. Data storage strategies will be discussed – perhaps not as well as the above. There should be a discussion on performance and security. The answer may not include the level of discussion at a technical level and may draw more on the concepts about how the Mobile database handles being distributed between devices. There may be some attempt at relating the discussion specifically to the case study. Good research & referencing and work may possibly go beyond the unit material.
0 – 20 Marks
An answer in this range will show that you understand the architecture of the Mobile Database. However there may be errors of fact, mis-understanding of how the Mobile Database works or a lack of insight into security and performance issues. You might not be able to explain any advantages or disadvantages. There may be no technical information given. There may be flaws in the discussion of how it would apply to the case study.
For all of this question a very good answer will be technically oriented showing that you understand not only the concept but how these things work. The topic needs to be related to the case study to show that you understand some of the issues and implications of having a Mobile Database. A reasonable answer will show an understanding of the concepts but may be a bit vague on the technicalities and a poor answer will show that you do not really understand the issues and reasons for the importance of this topic.
Q6
SQL Total 20 Marks
For each query:
Calibre and use of advanced SQL statements 3 Marks
Relation of the queries to the business 1 Marks
Explanation of the query 1 Mark
(Total Marks for Submission 2 – 90 Marks)
Coursework Total: 200 Marks – % will be calculated
Max Word Count: 4500