DAT536 ASSESSMENT 1 – V.4.2 1New Zealand Diploma in Information TechnologyTechnical Support Level 5Assessment 1: Case Study ResearchDAT536: Database Development and DesignVersion 4.2, Level 5, Credits 20 SubmissionFER1FER2ResultDateAssessor Signature Student NameStudent IDStudent SignatureAssessor Name DAT536 ASSESSMENT 1 – V.4.2 2Assessor’s Feedback SubmissionFER1FER2 DAT536 ASSESSMENT 1 – V.4.2 3Instructions and Guidelines for SubmissionTask completion Assignments will be judged based on completeness, correctness and clarity.Complete ALL tasks.Warning: All media must be virus free! Media containing viruses or media that cannotbe run directly will result in a FAIL grade. Format The submission must have a title page with your name, class and ID number clearlyprinted.All written work must be done in size 12 font with 1.5 line spacing.All printouts or soft copies must include your student ID (or group IDs) in the footer(printed).You may need to attach the task cover sheet found on Canvas to the front of the finalsubmission of your task (please confirm with your tutor). Submission The completed research must be handed to the tutor at the beginning of the class on thedue date.Submit a printed and bound copy of your report or a soft copy. Your tutor will inform youon how to submit the soft copy. Plagiarism By submitting your assignment, you agree to Aspire2 International’s policy on ‘AcademicDishonesty and Plagiarism’. Assignments completed using unfair means or plagiarisedmaterial will receive a FAIL grade.All tasks must be done in your own words and referenced. Competency-based assessment FER and Re-enrolment Students will have an opportunity to provide further evidence twice.To be eligible for a FER the student must make a reasonable attempt at all questions/tasksin the assessment.Reasonable is to be defined as a response that at the very least attempts to address thequestion/task.Where the student does not make a reasonable attempt at ALL questions/tasks they willnot achieve and will be required to re-enrol in the paper.A fee of $250 is payable for the second FER for international students. This does notapply to domestic students.The need for further evidence from the students will be noted on the assessment as “FER”and dated. DAT536 ASSESSMENT 1 – V.4.2 4 When the further evidence is deemed adequate the “Achieved” notation will be written,initialled and dated.If a student is still deemed not yet competent after the second FER, they will be requiredto re-enrol in the paper. A fee will apply for re-enrolment and details will be available in the Programme Handbook, and students will be informed of them at the start of theprogramme. Additional FERs are at the discretion of the Head of Faculty.Reconsideration of Assessments and Appeals Students have the right to a reconsideration of assessment or appeal if they believe anassessment has been incorrectly marked or graded.The request for reconsideration must be made in writing to the Head of Faculty withinfive (5) working days of the return of the assessment. A fee of $40 per assessment appliesfor international students.Students must be informed that, as a result of the reconsideration of assessment, theirresult may be unchanged, raised or lowered.The reconsidered result will be recorded as the Final – result. Students retain the right to appeal this result.Plagiarism By submitting your assignment, you agree to Aspire2’s policy on ‘Academic Dishonestyand Plagiarism’. Assignments completed using unfair means or plagiarised material willreceive a FAIL grade.All tasks must be done in your own words and referenced. Feedback You may request feedback from your lecturer to verify the accuracy of your marks.Student Undertaking:I have read and understood the instructions for assessment given above. I understand that I willbe assigned zero marks and may be reported to the disciplinary committee if I am found cheatingor engaging in any unfair practice.Student Name: _______________________________________________________Student Signature: ___________________________________________________Date:_______________________________________________________________DAT536 ASSESSMENT 1 – V.4.2 5 Learning Outcomes Covered1. Apply a broad operational knowledge of database administration tomeet typical organizational data storage and retrieval requirements. Mapping of Tasks with Learning Outcomes (LO) & Conditions Associated with the LOs TaskLO (Conditions)1.DBMS Optimisation2.DBMS Optimisation3.DBMS Optimisation4.DBMS Optimisation5.DBMS Backups6.DBMS Security7.DBMS Security, Query language8.Query Language9.DBMS Backups DAT536 ASSESSMENT 1 – V.4.2 6InstructionsRead the case study scenario below and complete the tasks that follows.The UniPharma chain of pharmacies has offered to give you a free lifetime supply of medicine if youdesign its database. UniPharma is a small medium enterprise (SME) in terms of company structureand has four (4) pharmacies in the Auckland region. Being an SME, they would like to keep their costsdown but also need an efficient solution to manage their back-office administration across all thesites. Here is some more information to help you out with the case study: Patients are identified by a Social Security Number (SSN), and their names, addresses,and ages must be recorded. • Doctors are identified by an SSN. For each doctor, the name, specialty, and years ofexperience must be recorded.• Each pharmaceutical company is identified by name and has a phone number.• For each drug, the trade name and formula must be recorded. Each drug is sold by a givenpharmaceutical company, and the trade name identifies a drug uniquely from among theproducts of that company. If a pharmaceutical company is deleted, you need not keeptrack of its products any longer.• Each pharmacy has a name, address, and phone number.• Every patient has a primary physician. Every doctor has at least one patient.• Each pharmacy sells several drugs and has a price for each. A drug could be sold atseveral pharmacies, and the price could vary from one pharmacy to another.• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs forseveral patients, and a patient could obtain prescriptions from several doctors. Eachprescription has a date and a quantity associated with it. You can assume that, if a doctorprescribes the same drug for the same patient more than once, only the last suchprescription needs to be stored.• Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceuticalcompany can contract with several pharmacies, and a pharmacy can contract with severalpharmaceutical companies. For each contract, you have to store a start date, an end date,and the text of the contract.• Pharmacies appoint a supervisor for each contract. There must always be a supervisor foreach contract, but the contract supervisor can change over the lifetime of the contract.DAT536 ASSESSMENT 1 – V.4.1Task InstructionsTask 1. Draw an ER diagram that captures the requirements.Task 2. As the Database Administrator (DBA), identify at least five (5) relational schemas in thisdatabase. Identify the data type and primary key for each relational schema.Task 3. How would your design change if each drug must be sold at a fixed price by all pharmacies?Present your answer in a complete ER diagram.Task 4. As a part of the UniPharma change management plan the design requirements:“If a doctor prescribes the same drug to the same patient more than once, several ofthese prescriptions will have to be stored”.Provide a complete ER diagram for the changes made.Task 5. UniPharma would like to back up their data on weekly basis to prevent data being lost.– Explain three (3) types of data backup techniques that could be used byUniPharma for their weekly back up process.– You are required to prepare a weekly data backup plan for UniPharma. Thebackup plan needs to include different types of data backup techniques; justifyyour choices.Each of the four (4) UniPharma pharmacies have five (5) employee types. They are a pharmacymanager, a data entry operator, an accountant, 2 pharmacists and database administrator.Keeping this company hierarchy in mind, complete the following tasks.Task 6. a) Describe at least THREE access privileges level of the five (5) employee types of UniPharmain the database.b) Who the person deciding the access privilege levels should be, and why?Task 7. Being the DBA, write three (3) SQL commands that you should use to grant the accessprivileges described in Task 6 for THREE account type. Consider the Database name as“Unipharmadb” and username as account type from Task 6.Task 8. Recently 100 tables were updated in the database after new products came in. As a DBA, youmust ensure the consistency of database and improve the database performance. Discuss two(2) SQL commands that you may use to check for data consistency and improve the databaseperformance and its importance.DAT536 ASSESSMENT 1 – V.4.28Task 9. Database Management System (DBMS) is a highly complex system with hundreds oftransactions executed every second. UniPharma is concerned that if the database fails orcrashes in the middle of transactions, they may lose data. It is expected therefore that thesystem would have some techniques to recover lost data. Discuss three (3) categories offailures and at least TWO methods to recover the data.DAT536 ASSESSMENT 1 – V.4.1 9Evidence and Judgement Statements TaskEvidence CriteriaJudgementSubmissionFER1FER21. Draw an ER diagramthat captures therequirements.Identify anyconstraints notcaptured by the ERdiagram. The student presents a clear ER diagram. The student logically creates and identify allentities, attributes, primary keys and includesthem in the diagram. The student correctly creates all relationshipsamong the entities and includes them in thediagram. The student correctly identifies all thecardinalities and includes them in the diagram. The student correctly identifies any constraints.The student’s answers may vary but must coverall the concepts listed in the evidence criteria.The student hasdrawn a completeand correct ERdiagram.2. As the DatabaseAdministrator (DBA),identify at least five(5) relational schemasof the database . The student shows at least five (5) relationalschemas relevant to the case study. The student identifies data types for each fieldand the primary key for each of the relationalschema. The student’s answers may vary but must coverall the concepts listed in the evidence criteriaThe student hasidentified at leastfive (5) correct andcomplete relationalschemas of the givencompany’s database . DAT536 ASSESSMENT 1 – V.4.1 10 3. How would yourdesign change ifeach drug must besold at a fixed priceby all pharmacies?Present your answerin a complete ERDiagram. The student analyses the design change request. The student adds or deletes the correctattributes. The student presents the answer in a clear ERdiagram. The student logically creates all entities,attributes, primary key (if any) and includesthem in the diagram. The student correctly creates all relationshipsamong the entities and includes them in thediagram. The student correctly identifies all thecardinalities. The student’s answers may vary but must coverall the concepts listed in the evidence criteria.The student hascorrectly shown in acomplete ERdiagram how thedesign wouldchange if each drugmust be sold at afixed price by allpharmacies.4. As a part of theUniPharma changemanagement plandesign requirements:If a doctor prescribes thesame drug to the samepatient more than once,several of theseprescriptions will have tobe stored. The student creates the correct entity sets withidentified relationships. The student presents a complete ERD diagramwith the requested changes. The complete ER diagram includes involvedentities, attributes, primary key, relationships,and cardinalities. The student’s answers may vary but must coverall the concepts listed in the evidence criteria.The student hasdescribed theprocess of changerequired and hascorrectly shown thechanges in acomplete ERdiagram DAT536 ASSESSMENT 1 – V.4.1 11 Provide a complete ERdiagram for thechanges made.5. Explain three (3)types of data backuptechniques thatcould be used byUniPharma.Prepare a weeklydata backup planwith different typesof data backuptechniques.Justify your choices. The student identifies and explains three (3)appropriate backup techniques. The student provides a weekly data backup plan. The data plan includes appropriate backuptechniques. The student provides valid justification for thechoice of backup techniques. The student’s answers may vary but must coverall the concepts listed in the evidence criteria.The student hascorrectly identifiedand explained three(3) data backuptechniques andcompleted a weeklybackup plan withclear justification ofdata included.6. Describe accessprivileges level of thefive (5) employeetypes of UniPharma inthe database.Explain who should bethe person decidingthe access privilegelevels and why.a) The student assigns at least TWO (2)appropriate access privilege levels to each ofthe five (5) employee types.The student describes the access privilegelevels assigned to each of the five (5)employee types.b) The student identifies the correct access leveldecision maker.The student provides valid justification for thechoice of decision maker.The student hascorrectly describedthe access privilegelevel of the fiveemployee types.And had alsoidentified thecorrect decisionmaker, giving validreasons for thechoice. DAT536 ASSESSMENT 1 – V.4.1 12 The student’s answers may vary but must coverall the concepts listed in the evidence criteria7. Being the DBA, writethree (3) SQL commandsthat you should use togrant the accessprivileges described inTask 6 for 3 account type(DBA cannot beselected). Consider theDatabase name isUnipharmadb andusername is the accounttype from Task 6. The student lists at least three (3) correct SQLcommands for 3 employee type. Command format is correctly. The command must include Database name,Username, and the privileges type. Student could grant multiple privileges in asingle expression. Student did not select DBA.The student hasgrant three (3)correct privilegesusing SQLcommand/s for 3employee typeaccording to theiraccess level.8. Discuss two (2) SQLcommands that youmay use to check forthe data consistencyand improve thedatabase performanceand its importance.a) The student identifies and discusses two (2)SQL commands to check for data consistencyand to improve the database performance.b) Student discusses each command with itsfunctionality and importance.The student hasidentified andclearly explainedtwo (2) correct SQLstatements thatcould be used tocheck dataconsistency. DAT536 ASSESSMENT 1 – V.4.1 13 9. Discuss three (3)types of databasefailures andmethods to recoverthe data. The student correctly identifies anddiscusses three (3) types of databasefailures. The student suggests at least TWO validrecovery method for database failure. The student’s answers may vary but mustcover all the concepts listed in the evidencecriteriaThe student hasdiscussed three (3)categories ofdatabase failuresand methods torecover the data.
- 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