Page 1 of 8COM4003Database DesignDesign SpecificationDate for Submission: Please refer to the timetable on ilearn(The submission portal on ilearn will close at 14:00 UK time on the dateof submission)Page 2 of 8Assignment BriefAs part of the formal assessment for the programme you are required to submit a DatabaseDesign assessment. Please refer to your Student Handbook for full details of the programmeassessment scheme and general information on preparing and submitting assignments.Learning Outcomes:After completing the module, you should be able to:1. Demonstrate a knowledge and understanding of databases and data managementsystems.2. Understand key principles of relational database design techniques.3. Transform a logical database design into a physical database design for a targetdatabase management system.4. Apply knowledge and understanding of database design and development todesign, implement, test, document and evaluate a relational database solution fora given scenario.5. Use appropriate problem-solving techniques.Your assignment should include: a title page containing your student number, the modulename, the submission deadline and a word count; the appendices if relevant; and a referencelist in Arden University (AU) Harvard format. You should address all the elements of theassignment task listed below. Please note that tutors will use the assessment criteria set outbelow in assessing your work.Maximum word count: 3,000 wordsPlease note that exceeding the word count by over 10% will result in a reduction in grade by thesame percentage that the word count is exceeded.You must not include your name in your submission because Arden University operatesanonymous marking, which means that markers should not be aware of the identity of thestudent. However, please do not forget to include your STU number.Page 3 of 8You have been engaged as a database consultant for the following organization.Environmental Angels are the go-to company when you have uninvited and unwelcomevisitors! Got bats nesting in the attic? Got a mad mole destroying your prized lawn? How aboutan annoying wasps’ next in the garden shed? Or perhaps a large family of fury, but not socute, rats have taken a fancy to your house? Fear not, Environmental Angels are here! Pestcontrol is just a phone call away.But wait – Environmental Angels do more than serve the needs of stressed-out house-holders.They actually make most of their money from commercial and industrial contracts. Typicalexamples will be local government contracts to deal with vermin in schools, libraries and othercouncil properties; mole removal from up-market private golf courses and tennis courts;protection of underground utility services for the electricity, gas, water and telecomscompanies; removal of vermin from the private sector food-processing and brewing industries(whose core activities are a magnet for rats and mice) and even bird removal from airports(military and commercial) so planes can take off and land safely (nobody wants a bird-strike)– where they employ falconers to scare away unwanted birds like pigeons using a range ofbirds-of-prey like hawks, kestrels, buzzards and falcons. This is much more lucrative thantaking down a wasps’ nest at someone’s home!The latest channel of income-generation is actually in the commercial (freshwater) fisheriessector. Angling is the world’s most popular (participant) hobby – by a huge margin. There are3-4 million anglers in the UK alone. These anglers have to fish somewhere and they expectto catch fish when they go. This represents a huge financial market and means that manycommercial fisheries (primarily owners of lakes, reservoirs and, to a lesser extent, canals) arevery keen to minimize or even completely remove any predatory fish like pike and zander,who can decimate other fish stocks such as roach, bream and trout if left unchecked.Environmental Angels offer a bespoke service whereby they can use small boats with sonarscanning technology to get a pretty good estimate on the population and size of the predatoryfish living in a given water. They can then use electro-stunning technology (which stuns butdoes not kill the fish) along with large nets to remove most, if not all, the predatory fish safely.These extracted fish are not killed or harmed. They are transferred – in the company’s ownspecialized tanker lorries – to third-party fisheries (who may actually want to promote theirown predator fishing) or more commonly to fish farms and specialist breeders. So,Environmental Angels gets paid at both ends – to take these fish out in the first place and thenagain to sell them to someone else. How good is that financial model?As part of all the above activities the company has to work with a wide range of external clients– such as private individuals, large corporations, small companies or various types of localand central government. However, just doing the pest removal is not the whole story. Manyjobs and contracts demand environmental and regulatory oversight. For example, bats areprotected, so if they need to remove them for a client, they must have a local authority ‘batpreservation and protection survey’ carried out. If they are dealing with vermin in a foodprocessing or brewing context then the local authority environmental health officer must attendthe premises and sign-off that the work is satisfactory.Page 4 of 8Even removing fish from one fishery to another has a lot of red tape. The danger of diseasebeing carried from water to water is too dangerous and so again, a government inspector mustsupervise and check the fish being moved. Similar rules, regulations and supervisory oversightapply to most commercial and industrial work. All this activity – scheduling meetings, arrangingvisits, writing reports, actioning reports, issuing of certificates etc. has to be carefully trackedand documented. Otherwise, the company leaves itself open to legal action.Imagine not fully clearing vermin out of a bakery, transferring diseased fish to a new fisheryor maybe, in an extreme case, failing to clear birds from an airport. That leaves you with somedubious bread, a ruined fishery and even a plane crash. Not good.The company also uses many chemicals and poisons to kill wasps, bees etc. as well asspecialist equipment to trap and remove bats, moles and other larger animals – even badgersand ‘urban foxes’. All supplies and equipment must be carefully tracked, from initial purchase,through allocation to each job, through to final decommissioning. Even not cleaning up wasppoison powder fully can kill any dogs or cats living in the home. This is serious and it needs tobe treated as such.The company is based in Birmingham, UK but services the whole United Kingdom. TheBirmingham head office has 15 admin and IT support staff who look after core functions likeHR, payroll, marketing, legal (contracts and compliance), purchasing and central IT supportand website. Out in the field, each job will be staffed by between one and ten ‘pest controlofficers’ – depending on the job. Removing a wasps’ nest from a garden shed needs only one,clearing out a rat infestation from a food factory maybe demands four or five but clearing 100sof pike from a large reservoir will need at least ten staff – maybe more.In terms of vehicles, the company has small vans (domestic work), mid-large vans(commercial and industrial) and specialized items like boats and fish-tankers. These are allpurchased from and maintained by external third-party companies on long-term contracts.Lastly, travel and accommodation is a major issue as the company often has to send staff longdistances to jobs or some jobs demand several day’s work – such as airport, factory and largefishery contracts. In such situations, staff will stay away from home in hotels and guest housesof various types and all incurred expenses must be carefully recorded and authorized.Final PointThe organisation needs a database to run all the above operations. Your job is to design andbuild this database. The above is simply an outline of the company and you will need to makeyour own assumptions and interpret or even extend the scenario as you go. Use yourimagination as you see fit, but you must clearly document all assumptions and extensions.The specific tasks you need to satisfy are listed below…Page 5 of 8Question 1 (Covers LO 1)(a) Summarize the key benefits that the database approach to data management offersover traditional file-based data management. Answer with regards to this case study.(5 Marks)(b) List and briefly describe the major features to be found in a commercial databasemanagement system (DBMS).(5 Marks)Question 2 (Covers LO 2)Develop a semantically rich data model that captures the above scenario in the form of anentity-relationship diagram (ERD). You should note (and number) all assumptions you makeabout the data and the reasoning behind your design choices. Also, include (and number) anyappropriate constraints and a list of entity types showing their attributes and identifiers.(20 Marks)Question 3 (Covers LO 2)Once you are satisfied that the ER diagram is a good representation of the organisation’s datarequirements, produce a logical design by mapping the ER diagram to a set of normalizedrelations – to third normal form (3NF). You should annotate and explain this process.(15 Marks)Question 4 (Covers LO 3)Take each of the normalized relations and implement them as SQL tables using an appropriateDBMS – such as Oracle, MySQL or another one you have access to – and a series of CREATETABLE statements. You must include all primary and foreign keys as well as any other tableor column constraints you feel are appropriate such as NOT NULL, CHECK, UNIQUE andDEFAULT. Provide screenshots of the working code. Ensure your user name or some otherdistinguishing aspect is included in the screenshot to verify it is your code.(15 Marks)Question 5 (Covers LO 4)Using appropriate sample data and your own imagination based on this case study, populateyour finished tables with at least 10 rows of data in each table. Provide screenshots of theworking code. When done, display the full contents of each populated table to screen and takescreenshots. Ensure your user name or some other distinguishing aspect is included in thescreenshot to verify it is your code.(10 Marks)Page 6 of 8Question 6 (Covers LO 5)To demonstrate that your final database is useful, write a set of realistic sample SQL queriesbased on the above scenario (use your imagination for details of each query) but they shouldinclude 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 basicSELECT…FROM…WHERE queries to more advanced ones using the above techniques. Besure to fully evidence all SQL work by taking screenshots of the inputted code and theoutputted results. Ensure your user name or some other distinguishing aspect is included inthe screenshot to verify it is your code.(30 Marks)Page 7 of 8Formative FeedbackYou 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 developyour skills as an independent learner.If you are a distance learning student, you should submit your work, by email, to your tutor, no laterthan 2 weeks before the actual submission deadline. If you are a blended learning student, yourtutor 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 specifiedby your tutor – if a blended learning studentGuidelinesYou MUST underpin your analysis and evaluation of the key issues with appropriate and wideranging 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, executive summary, tables, figures, diagrams,footnotes, reference list and appendices. Where assessment questions have beenreprinted from the assessment brief these will also be excluded from the word count. ALLother printed words ARE included in the word count. See ‘Word Count Policy’ on thehomepage 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 moreinformation please see the “Guide to Submitting an Assignment” document available on themodule page on iLearn.You must ensure that the submitted assignment is all your own work and that all sources usedare correctly attributed. Penalties apply to assignments which show evidence of academicunfair practice. (See the Student Handbook which is on the homepage of your module andalso in the Induction Area).Page 8 of 8Assessment Criteria (Learning objectives covered – all) Level 4 is the first stage on the student journey into undergraduate study. At Level 4 students will be developing their knowledge and understanding of the discipline and will be expected to demonstrate some of those skillsand competences. Student are expected to express their ideas clearly and to structure and develop academic arguments in their work. Students will begin to apply the theory which underpins the subject and will start toexplore how this relates to other areas of their learning and any ethical considerations as appropriate. Students will begin to develop self-awareness of their own academic and professional development.GradeMarkBandsGeneric Assessment CriteriaFirst(1)80%+Outstanding performance which demonstrates the ability to analyse the subject area and to confidently apply theory whilst showing awareness of any relevant ethical considerations.The work shows an excellent level of competence and confidence in managing appropriate sources and materials, initiative and excellent academic writing skills and professional skills (whereappropriate). The work shows originality of thought.70-79%Excellent performance which demonstrates the ability to analyse the subject and apply theory whilst showing some awareness of any relevant ethical considerations.The work shows a high level of competence in managing sources and materials, initiative and very good academic writing skills and professional skills (where appropriate). The work showsoriginality of thought.Uppersecond(2:1)60-69%Very good performance which demonstrates the ability to analyse the subject and apply some theory.The work shows a good level of competence in managing sources and materials and some initiative. Academic writing skills are good and expression remains accurate overall. Good professionalskills (where appropriate). The work shows some original thought.Lowersecond(2:2)50-59%A satisfactory to good performance which begins to analyse the subject and apply some underpinning theory.The work shows a sound level of competence in managing basic sources and materials. Academic writing skills are satisfactory and expression remains accurate overall although the piece maylack structure. Satisfactory professional skills (where appropriate). The work lacks some original thought.Third(3)40-49%Basic level of performance in which there are some omissions in understanding the subject, its underpinning theory and ethical considerations.The work shows a basic use of sources and materials. Academic writing skills are limited and there are some errors in expression and the work may lack structure overall. There are somedifficulties in developing professional skills (where appropriate). The work lacks original thought and is largely imitative.Marginalfail30-39%Limited performance in which there are omissions in understanding the subject, its underpinning theory and ethical considerations. The work shows a limited use of sources and materials.Academic writing 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 worklacks original thought and is largely imitative.29%andbelowA 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.
- Assignment status: Already Solved By Our Experts
- (USA, AUS, UK & CA PhD. Writers)
- CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS