DAT536: Database Development and Design

New Zealand Diploma in Information Technology
Technical Support Level 5
Case Study Research (30%)
80 Marks

Candidate Name
Candidate ID
Assessor Name

 

Final Result / Grade
Assessed By / Date

DAT536: Database Development and Design
Version 1.2, Level 5, Credits 20
Instructions and Guidelines for Project Submission
Task completion
• Project will be judged on the basis of completeness, correctness and clarity.
• Complete ALL tasks.
• Warning: All media must be virus free! Media containing viruses or media that
cannot be run directly will result in a fail grade.
Format
• The report must have a title page with your name, class and ID number clearly printed.
• All written work must be done in size 12 font with 1.5 line spacing.
• All printouts must include your student ID (or group IDs) in the footer (printed).
• You may need to attach the task cover sheet found on Moodle to the front of the final
submission 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
the due date.
• Submit a printed and bound copy of your report along with the soft copy. Tutor will
inform you on how to submit the soft copy.
Plagiarism
• By submitting your assignment you agree to Ntec’s policy on ‘Academic Dishonesty and Plagiarism’.
Assignments completed using unfair means or plagiarised material will receive 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.

Learning Outcomes covered:
1. Apply a broad operational knowledge of database administration to
meet typical organisational data storage and retrieval requirements.

Student Undertaking:
I have read and understood the instructions for exam given above. I understand that I will be given
zero marks and will be reported to the disciplinary committee if I am found cheating or engaging in
any unfair practice.
Student Name: _____________________________________________________________
Student Signature: __________________________________________________________
Date: _____________________________________________________________________
Case Study – UniPharma
The UniPharma chain of pharmacies has offered to give you a free lifetime supply of medicine if you
design its database. UniPharma is a small medium enterprise (SME) in terms of company structure
and has four (4) pharmacies in the Auckland region. Being an SME, they would like to keep their
costs down but also to have an efficient solution to manage their back office administration.
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, speciality, and years of
experience 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 given
pharmaceutical company, and the trade name identifies a drug uniquely from among the
products of that company. If a pharmaceutical company is deleted, you need not keep track
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 at several
pharmacies, and the price could vary from one pharmacy to another.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several
patients, and a patient could obtain prescriptions from several doctors. Each prescription
has a date and a quantity associated with it. You can assume that, if a doctor prescribes the
same drug for the same patient more than once, only the last such prescription needs to be
stored.
• Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical
company can contract with several pharmacies, and a pharmacy can contract with several
pharmaceutical 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 for
each contract, but the contract supervisor can change over the lifetime of the contract.
Task 1: (Total 35 Marks)
1. As the Database Administrator (DBA) write down the relational schema for at least
5 tables of the database in terms of the company’s structure (10 marks)
2. Draw an ER diagram that captures the preceding information. Identify any
constraints not captured by the ER diagram. (10 Marks)
3. How would your design change if each drug must be sold at a fixed price by all
pharmacies? (5 Marks)
4. As a part of the UniPharma change management plan how would you administer
changes if the design requirements are as follows:
If a doctor prescribes the same drug for the same patient more than once,
several such prescriptions may have to be stored. (5 Marks)
5. UniPharma is concerned about weekly data backup. Formulate and briefly describe
a data backup plan. (5 marks)
Task 2: (Total 35 Marks)
Each of the 4 UniPharma pharmacies have a Pharmacy Manager, a data entry operator,
an accountant, 2 pharmacists and database administrator. Keeping this company
hierarchy in mind please answer the following questions-
1. Describe access privileges level of the five (5) employee types of UniPharma in the
database? and who is the person deciding the access privilege levels? (10 Marks)
2. Being the DBA you have to provide different access levels to the employees;
mention any three (3) access related. (10 Marks)
3. Recently 100 tables were updated in the database after new products came in. As a
Database Administrator, what tasks will you perform to ensure consistency of the
database? (10 Marks)
4. DBMS is a highly complex system with hundreds of transactions being executed
every second. UniPharma is concerned if it fails or crashes in the middle of
transactions, it is expected that the system would follow some sort of technique to
recover lost data. Discuss categories of failures and methods to formulate a
recovery plan. (5 Marks)

Marking Schedule
Marking Criteria Designated
Marks		 Awarded
Mark		 Comment(s):
Task 1
Task1 – 1
Relational schema of 5 tables mentioned
The 5 tables are applicable to the task and the
entities in it are logical		 5 5
Task1- 2
The ER diagram is developed completely. The
relevant entities are created.
The attributes are logically created. The
relationships between entities are correctly
defined.
The cardinalities are correctly specified and
constraints are identified if any.		 4 3 3
Task1- 3
The correct attribute has been added
The correct attribute has been deleted		 2.5
2.5
Task1 – 4
Deleting the correct attribute
Creating the right entity set
Identify the correct relationships		 1.5
1.5
2
Task1 – 5
The techniques have been identified
The techniques have been explained		 2 3
Total of Question 1>> 35
Task 2
Task2 – 1
Identifies the access levels for the five
employee types
Describes the identified access levels
Correctly identifies and describes the right
decision maker		 3 4 3

 

Task2- 2
Identifies three access related commands
Explains the three identified commands		 5 5
Task2- 3
All MySQL maintenance statements have been
mentioned
All the MySQL maintenance statements have
been explained		 5 5
Task2-4
The failure categories have been identified and
explained
The recovery techniques have been identified
and explained		 2.5
2.5
Total of Question 2>> 35
Professionally formatted report 5
Used APA referencing 5
Total: 80

 



Logo CLICK HERE TO ORDER 100% ORIGINAL PAPERS AT PrimeWritersBay.com GET THIS PAPER COMPLETED FOR YOU FROM THE WRITING EXPERTS Image result for order now NO PLAGIARISM

Comments

Post a Comment

Popular posts from this blog

Explain the communication process that applies to advertising and promotion

Image
Referencing Styles : Harvard Explain the communication process that applies to advertising and promotion Explain the organisation of the advertising and promotions industry Assess how promotion is regulated Examine current trends in advertising and promotion, including the impact of ICT Explain the role of advertising in an integrated promotional strategy for a business or product Explain branding and how it is used to strengthen a business or product Review the creative aspects of advertising 2.4 examine ways of working with advertising agencies   In this task, students need to develop an actual integrated promotion plan by applying the techniques of IMC (Integrated Marketing Communication) and (Promotion), which are taught in classes. Students need to develop an actual integrated promotion plan Firstly, decide the plan for business or product. Secondly, set a budget for your promotion plan by following an appropriate process and discuss how this plan is appropriate for y...
Read more

Text documents, such as long recordings and meeting transcripts, are

Image
2. Assessment Description Text documents, such as long recordings and meeting transcripts, are usually comprised of topically coherent text segments, each of which contains some number of text passages. Within each topically coherent segment, one would expect that the word usage demonstrates more consistent lexical distributions than that across segments. A linear partition of texts into topic segments can be used for text analysis tasks, such as passage retrieval in IR, document summarization, and discourse analysis. In this assessment, you are required to write Python code to preprocess a set of meeting transcripts and convert them into numerical representations suitable for input into topic segmentation algorithms.   This is an individual assignment and worth  30%  of your total mark for FIT5196. The detailed tasks are as follows: Task 1: Reconstruct meeting transcripts with topical boundaries.  The original meeting transcripts are stored in three different t...
Read more

Human resources are of great

Image
Background to the problem Human resources are of great importance for any organization. The success of every business depends upon the effectiveness of its workforce. Companies use different ways to improve the effectiveness of its employees. For example, training programs, employee empowerment, motivation, rewards and recognitions etc. Despite of investing so much in the employees, many companies face high employee turnover rates. A high rate of employee turnover can cost a company in the long run, more than they might realize. Employee turnover occurs when employees leave their jobs voluntarily and must be replaced. (Price 1977, p.15) also defines turnover as “The ratio of the employees of an organisation who left in a particular period of time with the average number of employees in that organisation during the same period of time”. Michael Armstrong (2006) defines Employee Attrition as a normal flow of people out of an organization through retirement, career or job change,...
Read more