Advanced Database Development

Advanced Database Development 1. Produce an Entity-Relationship Model for the scenario 2.Design a set of relations conforming to Boyce-Codd Normal Form 3.Implement your final database design 4.Query your database 5.Optimize your Database 6.Secure your Database The coursework does not necessarily need to be perfect or excellent. It supposes to have more schematic diagrams rather than plenty of words. Essentially I just want to see how you would structure and write this coursework. The last task #7 Building a Web Interface of Assessment Brief may be omitted. You may check the module units here http://www.derbyonlinelearning.co.uk/content1.asp?MenuID=6814&courseId=Adv2299 Assessment Brief Advanced Database Development 6CC519 September 2015 University of Derby Online Learning www.derby.ac.uk/online 2 of 12 Module Overview: This module covers the design and deployment of relational databases in multi-user and web-based environments. It also explores alternative technologies that are available in the database arena along with scripting languages utilised. The key issues of database security, legal and ethical issues and security of information and the incorporation of non-traditional data structures are also introduced. Learning Outcomes: On successful completion of this module, you will be able to: On successful completion of this module, you will be able to: 1. Model, design and implement a relational database with a web-based interface for a specific given case study 2. Review and critically evaluate database technologies. Assessment Formative Activities & Assessment Formative Activities and Assessments are opportunities for you to apply, practice and make sense of the learning materials and content that you have encountered. These may take the form of individual tasks, such as reading some text or watching a video and documenting your reaction to it, responding to some discussion points on the discussion forum, considering a case study, or participating in a live online classroom session. The main aim of formative activities is for you to receive feedback on your contribution or performance that will help you prepare for and complete your final module assessment. Summative Assessment Summative assessments are the pieces of coursework that you complete which contribute towards your final grade in this module. You should take the feedback that you receive from the completion of coursework in this module and use it to help you improve your performance in future assessments. Summative assessment in this module is two pieces of submitted coursework. You will be expected to submit your summative assignments via the Turnitin assessment points on the Assessment page in My Learning section of your module. University of Derby Online Learning www.derby.ac.uk/online 3 of 12 Coursework 1 Assessment weighting: 50% of the module grade Word count: 2000 words equivalence Submission Deadline Date/Time: Friday 20th November at 9pm GMT. Title: Animal Tracking Systems (ATS) This is an individual assessment worth 50% of the module. Case Study Animal Tracking Systems (ATS) is a developer and supplier of specialist software products to individuals, businesses and government organizations working in the wildlife and veterinary sectors. Their software allows individual animals and birds to be remotely monitored and tracked. Examples include anti-poaching work in Africa, bird migration pattern analysis across Europe, canine tracking by law enforcement and the military or simply a private individual attaching a chip to a pet cat’s collar to avoid it getting lost. As part of their operations and after-sales package, ATS provides a help desk for clients who have questions about software purchased from the company. The range of software is extensive and offers a wide range of functionality at various price points. Some functions offered include location identification, migration and travel pattern analysis, health data capture, eating and drinking data capture, sleeping and resting pattern analysis and so on. When a call comes in, an operator enquires about the nature of the call. For calls that are not truly help desk functions, the operator redirects the call to another unit of the company (such as Order Processing or Billing). Since many customer questions require in-depth knowledge of a product, help desk consultants are organized by product. The operator directs the call to a consultant skilled on the software that the caller needs help with. Since a consultant is not always immediately available, some calls must be put into a queue for the next available consultant. Once a consultant answers the call, he determines if this is the first call from this customer about this problem. If so, he creates a new call report to keep track of all information about the problem. If not, he asks the customer for a call report number, and retrieves the open call report to determine the status of the inquiry. If the caller does not know the call report number, the consultant collects other identifying information such as the caller's name, the software involved, or the name of the consultant who has handled the previous calls on the problem in order to conduct a search for the appropriate call report. If a resolution of the customer's problem has been found, the consultant informs the client what that resolution is, indicates on the report that the customer has been notified, and closes the report. If resolution has not been discovered, the consultant finds out if the consultant handling this problem is on duty. If so, he transfers the call to the other consultant (or puts the call into the queue of calls waiting to be handled by that consultant). Once the proper consultant receives the call, he records any new details the customer may have. University of Derby Online Learning www.derby.ac.uk/online 4 of 12 For continuing problems and for new call reports, the consultant tries to discover an answer to the problem by using the relevant software and looking up information in reference manuals. If he can now resolve the problem, he tells the customer how to deal with the problem, and closes the call report. Otherwise, the consultant files the report for continued research and tells the customer that someone at ATS will get back to him, or if the customer discovers new information about the problem, to call back identifying the problem with a specified call report number. A database is required to manage the data for the above scenario. It should also be possible to query the data and produce various reports for management as and when required. Your job is to deliver that database by correctly identifying what data must be captured and how it is related. NOTE: The above case study is simply an outline of the company and you will need to make your own assumptions and interpret or even extend the scenario as you go. Use your imagination as you see fit but you must clearly document all assumptions and extensions. University of Derby Online Learning www.derby.ac.uk/online 5 of 12 Your Tasks 1. Produce an Entity-Relationship Model for the scenario described above (15 marks) Develop a top-down design of the data in the form of an entity-relationship diagram. You should note all assumptions you make about the data and the reasoning behind your design choices. Also include any appropriate constraints and a list of entity types showing their attributes and identifiers. 2. Design a set of relations conforming to Boyce-Codd Normal Form (BCNF). (15 marks) Once you are satisfied that the ER diagram is a good representation of the data, produce a logical design by mapping the E-R diagram to a set of (normalized) relations. Clearly show all intermediate steps. 3. Implement your final database design. (15 marks) Take each of the relations from your relational model and implement them as SQL tables. You must include all primary and foreign keys as well as any other table or column constraints you feel are appropriate. Then, using appropriate sample data and your own imagination, populate your finished tables. 4. Query your database. (20 marks) Using SQL, write a set of realistic sample queries based on the above scenario (use your imagination for details of each query) but they should include the following SQL query techniques: ? Joins (using two, three or more tables) ? Set operations (UNION, INTERSECT and MINUS) ? Ordering ? Grouping ? Aggregate functions (MIN, MAX, AVG, COUNT, SUM) ? Table aliases ? Renaming columns ? Sub-queries (nested queries) You should aim to write at least ten sample queries – ranging from basic SELECT…FROM…WHERE queries to more advanced ones using the above techniques. 5. Optimize your Database (10 Marks) You should now optimize your database: ? Apply suitable database optimization techniques to your final set of tables. ? Aim to implement a range of indexes. ? Run a suite of queries that will invoke those indexes. University of Derby Online Learning www.derby.ac.uk/online 6 of 12 ? Aim to demonstrate some index suppression techniques. 6. Secure your Database (10 Marks) Your optimized database should now be secured. To demonstrate this: ? Create three new users on your database ? Allocate them different security privileges and roles. ? Create a suite of views over your existing base tables. ? Issue appropriate privileges so that at least one user can use those views. ? Clearly demonstrate what actions each user can/cannot perform. 7. Building a Web Interface (15 Marks) You now have a fully working, secure and optimized database. Your final task is to design and build a web-based interface to that database such that it can support the following operations: ? Insert new rows of data ? Update existing rows of data ? Delete existing rows of data ? Query existing rows of data Please read the following guidance… Important Notes and FAQ’s. ? Make sure your full name and student ID are on the front page of the assessment ? Clearly label all tasks and take care to explain and discuss your technical work ? For the modelling and design work you must supply clearly annotated diagrams ? For the SQL work you must supply full code listings of the inputted code and screenshots of the outputted results – even if no rows were returned. Each screenshot must include some identifying feature – such as a username or user ID – that proves beyond doubt that it is your own work. ? If you need to research, then fully reference all such sources using the Harvard notation The detailed grading criteria are below… University of Derby Online Learning www.derby.ac.uk/online 7 of 12 Grading Criteria – Coursework 1 70- 100% (Excellent/Outstanding) ? Fully complete and accurate ER Model that captures all/most semantic aspects of the case study ? A fully normalized set of BCNF relations with all intermediate steps fully annotated ? A fully populated set of tables that encapsulates all primary & foreign keys plus all other constraints ? A minimum of ten complex SQL queries that employ joins, grouping and other advanced techniques ? A rich set of database optimization techniques to include both indexing and clustering ? A rich set of database security techniques to include all privileges, roles, profiles and views ? A well-designed web interface that incorporates HCI design principles and various components ? A minimum of two insert, update and delete operations (each) via this web interface ? A minimum of ten queries (of varying complexity) via this web interface ? All work to be fully evidenced proving beyond doubt who the author of that work is ? All work to be fully annotated, well laid out and easy to follow with suitable headings ? Any external sources are fully referenced by strict adherence to the Harvard citation standards 60- 69% (Very Good/Commendable) ? Fully complete and accurate ER Model that captures most semantic aspects of the case study ? A fully normalized set of BCNF relations with most intermediate steps fully annotated ? A fully populated set of tables that encapsulates all primary & foreign keys plus other constraints ? A minimum of eight complex SQL queries that employ joins, grouping & other advanced techniques ? A rich set of database optimization techniques to include both indexing and clustering ? A rich set of database security techniques to include many privileges, roles, profiles and views ? A well-designed web interface that incorporates HCI design principles and various components ? A minimum of two insert, update and delete operations (each) via this web interface ? A minimum of eight queries (of varying complexity) via this web interface ? All work to be fully evidenced proving beyond doubt who the author of that work is ? All work to be fully annotated, well laid out and easy to follow with suitable headings ? Any external sources are fully referenced by strict adherence to the Harvard citation standards 50- 59% (Good/Highly Competent) ? Mostly complete and accurate ER Model that captures many semantic aspects of the case study ? A largely normalized set of BCNF relations with many intermediate steps fully annotated ? A fully populated set of tables that encapsulates some primary & foreign keys and other constraints ? A minimum of five complex SQL queries that employ joins, grouping and other advanced techniques ? A rich set of database optimization techniques to include either Indexing or clustering ? A rich set of database security techniques to include a few privileges, roles, profiles and views University of Derby Online Learning www.derby.ac.uk/online 8 of 12 ? A basic web interface that incorporates some HCI design principles & perhaps various components ? A minimum of one insert, update and delete operations (each) via this web interface ? A minimum of five queries (of varying complexity) via this web interface ? All work to be fully evidenced proving beyond doubt who the author of that work is ? Most work to be fully annotated, well laid out and easy to follow with suitable headings ? Any external sources are fully referenced by strict adherence to the Harvard citation standards 40- 49% (Satisfactory/Competent) ? A very basic ER Model that captures only some semantic aspects of the case study ? A partly normalized set of BCNF relations with no/little annotation ? A barely populated set of tables that encapsulates only primary and/or foreign keys ? A minimum of three simple SQL queries that employ no advanced techniques ? A very basic set of database optimization techniques – perhaps only simple indexing ? A very basic set of database security techniques to include any aspect ? A very basic web interface that incorporates no serious HCI design principles ? A minimum of one insert, update and delete operations (each) via this web interface ? A minimum of three simple queries via this web interface ? All work to be fully evidenced proving beyond doubt who the author of that work is ? Work is presented in a very basic (but readable) state ? Most external sources are fully referenced by adequate adherence to the Harvard citation standards 35- 39% (Unsatisfactory) ? A very basic ER Model that captures only small semantic aspects of the case study ? A very basic set of relations with no/little annotation or serious attempt at normalization ? A barely populated set of tables that encapsulates only primary and/or foreign keys ? A minimum of three simple SQL queries that employ no advanced techniques ? A very basic set of database optimization techniques – perhaps only simple indexing ? A very basic set of database security techniques to include any aspect ? A very basic web interface that incorporates no serious HCI design principles ? A minimum of one insert, update and delete operations (each) via this web interface ? A minimum of one simple query via this web interface ? Some work to be fully evidenced proving beyond doubt who the author of that work is ? Work is presented in a very basic (but readable) state ? Many external sources are NOT referenced by any adherence to the Harvard citation standards 1-34% (Very Poor/Nothing of Merit) ? A very basic (or absent) ER Model that captures only small semantic aspects of the case study ? A very basic (or absent) set of relations with no /little annotation or serious attempt at normalization ? A barely populated set of tables that encapsulates only primary and/or foreign keys ? A minimum of three simple SQL queries that employ no advanced techniques ? No database optimization or security work ? No web interface at all ? No hard evidence proving beyond doubt who the author of that work is ? Work is presented in a very basic (but readable) state ? No attempt is made to reference any cited sources End of Assessment 1 University of Derby Online Learning www.derby.ac.uk/online 9 of 12 Coursework 2 Assessment weighting: 50% of the module grade Word count: 2000 words Submission Deadline Date/Time: Wednesday 9th December at 9pm GMT. Assessment This is an individual assessment worth 50% of the module. Your Tasks Write a 2000-word report on alternative and complementary technologies to be used in the deployment of the database implementation from Assessment 1, in order to fully exploit the corporate data assets. Security, ethical and legal issues should also be considered. Specifically, you should address the following technical areas: 1. Data Warehousing and the difference between operational and strategic data sets 2. Data Mining and OLAP (On-Line Analytical Processing) compared with OLTP Systems 3. The rise of ‘Big Data’ and its applications 4. ‘NoSQL’ databases as compared with ‘ACID-compliant’ databases 5. The impact of the ‘Open Data’ movement Important Notes and FAQ’s ? Make sure your full name and student ID are on the front page of the assessment ? Clearly label and number all sections and sub-sections of your report ? Have an accurate contents page at the front and a full reference list at the rear ? Place a word count at the end of the report ? Try and incorporate good-quality diagrams into your discussion ? Fully reference all sources using the Harvard notation ? Each technical area carries equal weight – 20 marks each ? For each technical area, cover the following points: ? Describe the essential concepts involved in that technology ? Explain the key advantages and benefits of that technology ? Describe specific real-world applications and users of that technology ? Explain any disadvantages or situations were that technology would be unsuitable The detailed grading criteria are over the page… University of Derby Online Learning www.derby.ac.uk/online 10 of 12 Grading Criteria 70-100% (Excellent/Outstanding) ? All five technology areas are fully explained and analysed with many excellent examples/applications ? Many high-quality diagrams are employed to support the key concepts discussed in the report ? The data security, ethical and legal aspects are fully explained, analysed and placed within context ? The report is presented to a very high standard, logically sequenced with no spelling or other errors ? At least 15 quality external sources are cited within the report ? All external sources are fully cited and referenced by strict adherence to the Harvard standard 60-69% (Very Good/Commendable) ? All five technology areas are well explained and analysed with some excellent examples/applications ? Some high-quality diagrams are employed to support the key concepts discussed in the report ? The data security, ethical and legal aspects are well explained, analysed and placed within context ? The report is presented to a high standard, logically sequenced with no spelling or other errors ? At least 12 quality external sources are cited within the report ? All external sources are fully cited and referenced by strict adherence to the Harvard standard 50-59% (Good/Highly Competent) ? Most technology areas are well explained and analysed with some excellent examples/applications ? Some high-quality diagrams are employed to support the key concepts discussed in the report ? The data security, ethical and legal aspects are adequately explained and analysed ? The report is presented to a good standard, logically sequenced with few spelling or other errors ? At least 10 quality external sources are cited within the report ? All external sources are fully cited and referenced by strict adherence to the Harvard standard 40-49% (Satisfactory/Competent) ? Most technology areas are explained and analysed to some degree ? Possibly one or more diagrams are employed to support the key concepts discussed in the report ? The data security, ethical and legal aspects are explained and analysed in a very superficial manner ? The report is presented to a basic (but readable) standard ? At least 7 quality external sources are cited within the report ? All external sources are fully cited and referenced by reasonable adherence to the Harvard standard University of Derby Online Learning www.derby.ac.uk/online 11 of 12 35-39% (Unsatisfactory) ? Most technology areas are NOT explained and analysed to any serious degree ? No diagrams are employed to support the key concepts discussed in the report ? The data security, ethical and legal aspects are very poorly explained and analysed (if at all) ? The report is presented to a very basic (but readable) standard, possibly with spelling errors ? Fewer than 5 quality external sources are cited within the report ? Few external sources are cited and referenced by any adherence to the Harvard standard 1-34% (Very Poor/Nothing of Merit) ? Most technology areas are NOT explained and analysed to any meaningful degree ? No diagrams are employed to support the key concepts discussed in the report ? The data security, ethical and legal aspects are very poorly explained and analysed (if at all) ? The report is presented to a very basic (but readable) standard, with many errors and mistakes ? Fewer than 3 quality external sources are cited within the report ? There is no use of the Harvard system Assessment Presentation When marking this assignment the academics will also be looking for the following criteria: ? Clear legible presentation. ? Good use of spelling, grammar and language throughout. ? Appropriate focus, meeting learning outcomes/assignment criteria. ? Logical progression and structure of arguments. ? (Normally) an introduction, a well-developed discussion and a conclusion summarising the work. ? The introduction will include an exploration of the focus of the assignment and discuss the way the assignment has been approached. ? Evidence of a range of relevant supporting reading. ? Use of accurate, evidence based information to support the arguments made. ? Follow normal Academic Regulations in terms of Academic Offences, style and language. ? Use the Harvard system of referencing and may include a bibliography that lists all resources referenced. University of Derby Online Learning www.derby.ac.uk/online 12 of 12 ? A declaration statement which says you have checked your Turnitin originality report and certain that the work is your own (and has never been submitted for marking before by you, or anyone else) ? Maintain the confidentiality of clients/customers and persons associated with them, colleagues and organisations This module uses a grading scale applicable to Levels 4, 5 and 6 in the University Credit Framework.