COM5005 Database Implementation Design Specification

[1548] Page 1 of 9

Date for Submission: Please refer to the timetable on ilearn

(The submission portal on ilearn will close at 14.00 UK time on the date of submission)

Page 2 of 9 [1548]

Assignment Brief

As part of the formal assessment for the programme you are required to submit a Database Implementation assignment. Please refer to your Student Handbook for full details of the programme assessment scheme and general information on preparing and submitting assignments.

Learning Outcomes:

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.

All learning outcomes must be met to pass the module.

Maximum word count: 4000 words

Please note that exceeding the word count by over 10% will result in a reduction in grade by the same percentage that the word count is exceeded.

You must not include your name in your submission because Arden University operates anonymous marking, which means that markers should not be aware of the identity of the student. However, please do not forget to include your STU number.

Page 3 of 9 [1548]

SCENARIO

Global Intelligence lead the world in market research for the business and government sectors. They are based in London but with regional offices across the United Kingdom plus foreign cities including Paris, Berlin, New York, Los Angeles, Beijing and Delhi.

Working alongside their blue chip clients, they use a wide variety of techniques and communication channels to explore, gather, aggregate, analyse and present public opinion and consumer sentiment to those clients. The methods they use include:

• Surveys and Questionnaires – these can be issued in a number of ways. Traditional paper-based surveys still have a role to play in market research because it offers the surveyor the opportunity to fine-tune the precise time, location and member of the public to be targeted. It is however very timeconsuming and not highly scalable. By contrast, online surveys can handle a mass market and are highly cost-effective. An intermediate method is telephone surveys. Participants are not directly financially rewarded but entered into a competition where they have the opportunity to win a product or service provided by the commissioning client – so if the client was BMW, they could win a car and if the client was British Airways they could win free travel. The client will contact the winner directly.

• In-depth Interviews – while surveys offer a generalized picture across a large group, it is only in-depth one-on-one interviews that can provide high-quality insights and feedback. These interviews normally last 1-2 hours and are held at one of the company’s regional offices. They are run by a senior interviewer who then writes a report for the commissioning client. Interviewees are paid an attendance fee plus expenses. The actual fee is agreed with the client and can range from £50 to £200. Expenses include travel, subsistence and very occasionally accommodation. Global Intelligence pays these fees and expenses and then reclaims them from the client later.

• Focus Groups – often, the best quality market research comes from the interaction between a group of people discussing a topic in a relaxed but structured setting. This is where focus groups come in. They are led by an experienced market researcher from the company and each focus group normally consists of 10-12 pre-selected members of the public, based on the specific parameters (age, gender, socio-economic level etc.) specified by the client. Each meeting will last ½ day to 1 full day. Again, each participant is paid an attendance fee plus expenses. The actual fee is agreed with the client and can range from £150 to £250 for a ½ day to £250-£400 for a full day. Expenses include travel, subsistence and very occasionally accommodation. Global Intelligence pays these fees and expenses and then reclaims them from the client later.

Page 4 of 9 [1548]

• Social Media Scraping – this is a more recent development but obviously a very important one. For a market research organization like Global Intelligence, platforms like Facebook and Twitter offer almost unlimited data on people’s opinions, sentiments and actions and the application of big data analytics is changing the whole market research world. Global Intelligence pays a flat monthly fee to these large social media companies in return for plugging their IT systems into these vast data pools. Global Intelligence pays these fees and expenses and then reclaims them from the client later.

• Secondary Research & Data Harvesting – This often involves paying to

acquire existing research and/or data sets from third-party organizations – if it

directly relates to the main campaign Global Intelligence is running. Data

harvesting is a common technique whereby those third-party organizations

that routinely gather massive data sets on issues like voting habits, spending

habits, leisure activities, travel-to-work data etc. can be paid a fee – normally a

few pennies per data item – and those millions of data items are then

aggregated and analysed alongside the main market research work. Global

Intelligence pays these fees and expenses and then reclaims them from the

client later.

Clients are billed at the end of a campaign and have 90 days to settle their invoice.

Final Point

The organisation needs a database to run all the above operations. Your job is to design and build this database. The above 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.

Page 5 of 9 [1548]

Assignment Task

Question 1 (Covers LO 2)

(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.

(15 Marks)

(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).

(5 Marks)

Question 2 (Covers LO 3)

(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.

(10 Marks)

(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.

(10 Marks)

(5 Marks)

Question 3 (Covers LO 1)

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.

(10 Marks)

Page 6 of 9 [1548]

Question 4 (Covers LO 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.

(10 Marks)

(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.

(5 Marks)

(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.

(20 Marks)

Page 7 of 9 [1548]

Question 5 (Covers LO 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.

(10 Marks)

Page 8 of 9 [1548]

Formative Feedback

You have the opportunity to submit a single draft of your report to receive formative feedback.

The feedback is designed to help you develop areas of your work and it helps you develop your skills as an independent learner.

If you are a distance learning student, you should submit your work, by email, to your tutor, no later than 2 weeks before the actual submission deadline. If you are a blended learning student, your tutor will give you a deadline for formative feedback and further details.

Formative feedback will not be given to work submitted after the above date or the date specified by your tutor – if a blended learning student.

Guidelines

You MUST underpin your analysis and evaluation of the key issues with appropriate and wide ranging academic research and ensure this is referenced using the AU Harvard system.

The My Study Skills Area on iLearn contains useful resources relating to referencing.

Additional notes:

Students are required to indicate the exact word count on the title page of the assessment.

The word count excludes the title page, tables, figures, diagrams, footnotes, reference list and appendices. Where assessment questions have been reprinted from the assessment brief these will also be excluded from the word count. ALL other printed words ARE included in the word count. See ‘Word Count Policy’ on the homepage of this module for more information.

Submission Guidance

Assignments submitted late will not be accepted and will be marked as a 0% fail.

Your assessment should be submitted as a single Word (MS Word) or PDF file. For more information please see the “Guide to Submitting an Assignment” document available on the module page on iLearn.

You must ensure that the submitted assignment is all your own work and that all sources used are correctly attributed. Penalties apply to assignments which show evidence of academic unfair practice. (See the Student Handbook which is on the homepage of your module and also in the Induction Area).

[1548] Page 9 of 9

Level 5 reflects the continuing development in knowledge, understanding and skills from Level 4. At Level 5, students are not expected to be fully autonomous but are able to take responsibility for their own learning with appropriate guidance and direction. Students are expected to further develop their theoretical knowledge within a more intellectual context and to demonstrate this through more complex forms of expression which move beyond the descriptive or imitative domain. Students are expected to demonstrate skills of analysis in both problem-solving and resolution.
Grade	MarkBands	Generic Assessment Criteria
First (1)	80%+	An exceptional information base exploring and analysing the discipline, its theory and any associated ethical considerations. There is sophisticated use and management of learning resources and a high degree of autonomy is demonstrated. Writing is exceptionally well structured and accurately referenced throughout.Where appropriate, outstanding professional skills are demonstrated. The work is original and with some additional effort could be considered for internal publication.
70-79%	An excellent knowledge base within which the discipline is explored and analysed. There is a good degree of originality in the approach. The work demonstrates confidence and autonomy and extends to consider ethical issues. Learning resources have been managed confidently. Writing is exceptionally well structured and accurately referenced throughout. Where appropriate, an excellent level of professional skills are demonstrated and the work demonstrates a high level of intellectual and academic skills.
Upper second (2:1)	60-69%	A very good knowledge base which explores and analyses the discipline, its theory and any associated ethical issues. There is evidence of some originality and independence of thought. A very good range of learning resources underpin the work and there is evidence of growing confidence and self-direction. The work demonstrates the ability to analyse the subject and apply theory with good academic and intellectual skills. Academic writing skills are good, expression is accurate overall and the work is consistently referenced throughout.
Lower second (2:2)	50-59%	A satisfactory understanding of the discipline which begins to analyse the subject and apply some underpinning theory. There may be reference to some of the ethical considerations. The work shows a sound level of competence in managing basic sources and materials. Academic writing skills are good and accurate overall and the work is planned and structured with some though. Professional skills are satisfactory (where appropriate). The work lacks original thought but academic and intellectual skills are moving into the critical domain. The work is referenced throughout.
Third (3)	40-49%	Basic level of performance in which there are some omissions in understanding the subject, its underpinning theory and ethical considerations. There is little evidence of independent thought and the work shows a basic use of sources and materials. Academic and intellectual skills are limited. The work may lack structure overall. There are some difficulties in developing professional skills (where appropriate). There is an attempt to reference the work.
MarginalFail	30-39%	A limited piece of work in which there are clear gaps in understanding the subject, its underpinning theory and ethical considerations. The work shows a limited use of sources and materials. Academic and intellectual skills are weak and there are errors in expression and the work may lack structure overall. There are difficulties in developing professional skills (where appropriate). The work lacks original thought and is largely imitative.
29% and below	A poor performance in which there are substantial gaps in knowledge and understanding, underpinning theory and ethical considerations. The work shows little evidence in the use of appropriate sources and materials. Academic writing skills are very weak and there are numerous errors in expression. The work lacks structureoverall. Professional skills (where appropriate) are not developed. The work is imitative.

Database_Implementation_-_COM5005D_paper_[1548]