COM5005: Database Implementation - Computer Science Assessment Answer

Subject Code: COM5005 Internal Code: 1AGEHH

Computer Science Assessment Answer

Assignment Task You have been engaged as a database consultant for the following organization. “Past to Present’ is a very successful specialist company catering to the professional or serious amateur historian. Their mission is to bring history to life and to place the past in context with the present. They offer a range of niche and in-depth services...Digging Deeper - Their research department is based at the company headquarters in London and is staffed by a team of in-house professional genealogists, archivists, historians and researchers. They can provide research reports on any aspect of history, from the family tree of a historically-important individual to the battle plans and strategies of generals in key theatres of war. They have access to a vast array of external archives plus their own internal materials. A ‘Digging Deeper’ report is charged based on the number of hours of time needed to put the report together – which is all documented internally via a time-keeping system. The company charges £40/hour and the client gets two identical (hardbound) copies of the report for free. Subsequent copies are £50 each. War Jaw – These are private lectures – normally run at the London office but occasionally off-site – given by a recognized expert in a specific field of history. The talk itself lasts 60-90 minutes followed by a Q+A session, then refreshments so attendees and the speaker can network and mingle. Past lectures have covered ‘How Wellington’s logistics won Waterloo’, ‘Berlin: The last days in the bunker’ and ‘The untold story of Saxon England’. Tickets are limited to 100 per talk and cost £75 each. The speaker is always an external expert and gets a flat fee of £1,000 plus expenses. The talks run all year round with an average of 2 or 3 per week, all advertised on the company website. History on Location – For those historians who need something more than a lecture, the company can provide the ultimate experience – a customized tour of a specific site(s) by a world-leading authority in that very same time period. For example, if you have a passion for Third Reich history, and the money to indulge that passion, the company can arrange for you to be flown over to Germany and Poland, put up in the best local hotels with full inter-site travel provided so that you can tour – in the company of a Nazi world authority - all the iconic Nazi sites such as the Nuremburg rally site, the site of Hitler’s bunker, the Reichstag and selected concentration camps. Similar past trips have visited the WW1 battlefields, Agincourt and other key battle sites of the middle ages, WW2 Pacific theatres of war and so on. Prices are based on many factors (distance, duration etc.) and are thus customized for the client. Behind the Scenes – Some clients cannot afford the full ‘History on Location’ package but want to go beyond a lecture – so the company has partnered with several world-leading institutions to allow small groups to be given ‘behind the scenes’ access to see how historical artefacts are restored, curated and preserved. Past examples include a trip to the Royal Armouries in Leeds, UK, to see how medieval armour and swords are restored and researched, a visit to Bletchley Park to see how the code-breaking machines were rebuilt, and a guided tour of the medieval Battle of Towton in Yorkshire, UK, to see how longbows and their arrows were gathered, plotted and then reconstructed to establish battle lines. Some clients, for whatever reason, cannot get to lectures or engage in trips and tours, but still have a burning passion for history. To satisfy these clients – as well as all their other clients – the company offers two remote online services. The History Store – This is an online bookstore – like Amazon – but only selling history books. Books can be new or second-hand. They are sourced from many places. New books come direct from publishers around the world while used books are donated from university and college libraries, other historical institutions, private individuals and occasionally entire private libraries in the wills of history buffs who have sadly died. Each book is priced according to condition and rarity. Postage is added and the book(s) mailed out to the client. History Today Magazine – Finally, there is a monthly magazine which for a £5/month subscription providers its readers with both a hardcopy high-gloss version posted to the homes, as well as a PDF e-copy version, emailed direct to their inbox. Question 1 (a) Develop a semantically rich data model that captures the above scenario in the form of an entity relationship diagram (ERD). You should note (and number) all assumptions you make about the data and the reasoning behind your design choices. Also, include (and number) any appropriate constraints and a list of entity types showing their attributes and identifiers. (b) Once your ERD is complete, you should look for opportunities to use the entity sub-typing concept and thus develop an EERD (Enhanced ERD). Question 2 (a) Once you are satisfied that the EER diagram is a good representation of the organisation’s data requirements, produce a logical design by mapping the EER diagram to a set of relations, showing all primary and foreign keys clearly. (b) Now produce a normalized relational model by checking each relation is in third normal form (3NF). You should clearly annotate and explain this process. (c) Under what circumstances would it be necessary to de-normalize this model? Question 3 Name and briefly discuss three Database Management Systems (DBMS) that could be used to implement a solution to your proposed database design. Decide on an appropriate DBMS that you wish to use for this project. Provide an argument for using this system rather than others that could be used and remember to relate your reasons to the actual scenario. Be sure to fully reference any external sources used. Question 4 (a) Take each of the normalized relations and implement them as SQL tables using your chosen DBMS from the previous task with a series of CREATE TABLE statements. You must include all primary and foreign keys as well as any other table or column constraints you feel are appropriate such as NOT NULL, CHECK, UNIQUE and DEFAULT. Provide screenshots of the working code. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code. (b) Using appropriate sample data and your own imagination based on this case study, populate your finished tables with at least 10 rows of data in each table. Provide screenshots of the working code. When done, display the full contents of each populated table to screen and take screenshots. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code. (c) To demonstrate that your final database is useful, write a set of realistic sample SQL queries based on the above scenario (use your imagination for details of each query) but they should include the following techniques: • SELECT...FROM...WHERE... • Joins (using two, three or more tables) • Ordering output (ORDER BY) • Grouping output (GROUP BY) • Aggregate functions (MIN, MAX, AVG, COUNT, SUM) You should aim to write a minimum of ten sample queries – ranging from basic SELECT...FROM...WHERE queries to more advanced ones using the above techniques. Be sure to fully evidence all SQL work by taking screenshots of the inputted code and the outputted results. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code. Question 5 Describe and explain how the following database application techniques may be employed to enhance and extend the performance of your database: • Indexes • Triggers • Stored Procedures Ideally, you should attempt to implement (and evidence via screenshots) these coding techniques but if that is not possible, then at least explain what they do and what benefits they bring PLUS supplying the SQL code you would have executed. After completing the module, you should be able to: 1. Demonstrate a critical understanding of the tools and techniques to support database development. 2. Justify the design and development of the application and critically evaluate the implementation and approach. 3. Design and implement a database system meeting the needs of both the user and the client. 4. Develop appropriate SQL statements in order to efficiently create, manage and interrogate data within the database application. 5. Use appropriate database development techniques to solve loosely defined problems.  

This Computer Science Assessment has been solved by our Computer Science experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.