the practical aspects of the module. It is divided into two parts:

CC5002 Databases and Web-based Information Systems Coursework Assignment 2017-18 The coursework assignment is an individual assessment weighted 60% of the marks for the module. It is designed mainly to assess the practical aspects of the module. It is divided into two parts: database design and web-based information system development. It requires the student to analyse, design and implement a web-based information system based on a given business case study. You are asked to provide a software solution as well as appropriate documentation detailing the design and implementation of the system. Case study – Pets Care Pets Care is a medium sized veterinary surgeon based in north London founded and run by John King. Although Pets Care had traditionally dealt with dogs and cats, they have started to treat more exotic species especially as these become more popular as pets. Recently, they have employed Lee Smith a vet from Australia who specialises in snakes and lizards. With the success of the business, John wants a web-based database system developed to handle the records of patients, prescriptions and referrals. At the first stage, the system will be used for the staff only. Pets Care requires that the system is able to keep records of their patients, patient’s owners and the type of animal they are. For appointments, they need to know with which vet the appointment is as well as the time and date. The outcome of the appointment might be a prescription with one or more drugs on it. Repeat prescriptions are seen as being the outcome of a single appointment. An appointment might also lead to a referral or number of referrals for a particular treatment. He Pets Care referrals are for treatments carried out at only one particular treatment centre each. You have been chosen to build a web-based information system Pets Care using Oracle database and This assignment includes various materials gathered during the course of an analysis from which you can design and build the system. Further information 1. Transcript of an interview with John King “Things have been expanding in recent years. That’s why we’ve taken on Lee. So now we have myself, Lee Dave, Adrian Cooper, Suleman Faizi and Emma Goldman. The real substance of our work is the appointment where people bring in their pets. Owners who have more than one pet tend to use us for all of them. We charge for each appointment and for each prescription made out depending on the cost of the drug. Any treatment carried out by treatment centre is billed by them and has nothing to do with us. “ 2. Manual Pet and owner card 2 3 3. Example of a manual appointment record Pets Care 60 Holloway Road N7 1BB Name: Tiddles Owner: Mrs Jones Appointment Date: 01/Jan/2018 2.00pm Details: I examined Tiddles (cat) and found he had a severe in-growing toenail. Prescribed some diazipan to get him through it. Mrs Jones is bearing up. Peter. Cost of appointment £10 Prescription number: 0010 Drug Cost Diazipan £3.50 Aspirin £0.5 Total Cost £14.00 Recommendations: 1. Laser treatment at Harris’s Animal Hospital Middlesex. (3 sessions) 2. Series of training sessions at Woodhouse. 4 5. Example of a prescription sheet Pets Care 60 Holloway Road N7 1BB Prescription PRESCRIPTION NUMBER: 0023 PATIENT NAME: Rolph PATIENT TYPE: Alsatian Dog OWNER: Dave Stewart CUSTOMER ADDRESS: 11 Victoria Street, N1 PRESCRIPTION BY: Adrian Cooper (Vet) Drug Dosage Period Length of Course Cost Zoratin 1ml 4 hours 2 weeks 2.00 Pheno Barbatine 2 ml 6 hours 1 week 10.00 Aspirin 1ml 4 hours 2 days 0.50 Signed: Adrian Cooper 5 Detailed Specifications Study the case study carefully and then submit proper documentation for the design and implementation of the system that meets the requirements specified below: Section A. Design (20 marks) 1. Normalise Pets Care appointment record and prescription sheet to 3 rd NF with facts found from the case study. Provide the individual steps you took in the normalisation process. [5 marks] 2. Consolidate the entities by eliminating redundancies and merging entities where are applicable. Provide a final 3 rd NF and an ERD model for Health Pets. [5 marks] 3. Provide a Data Dictionary for the whole system. The data dictionary should include primary key and foreign keys, names, data types and descriptions of each attribute for each entity in the entity relationship model. [10 marks] Section B. Oracle database implementation (20 marks) Use the University’s Oracle SQLPlus as tools to implement the database. 1. Create all the database tables needed for implementing the in Oracle. Show table-creating scripts for each table with clear PK and FK definition. [10 marks] 2. Populate the tables with appropriate data using INSERT command and list its content using SELECT command. Data included in the each table should meet the requirements specified below: ï‚· minimum 4 rows of information for each table. [5 marks] ï‚· yourself as an owner. [3 marks] ï‚· enough data for each table to illustrate one to many relationships. [2 marks] Section C. Web application development (50 marks) Use Microsoft Visual Studio with ASP.NET C# as well as HTML and CSS as development tools to build a web enabled database application with the following functions to support Pets Care’s daily business operations. 1. A home page (web form) with menu to access other web forms [8 marks] 2. A pet’s owner form to allow the input, amendment and querying of owner details [7 marks] 3. A vet form to allow the input, amendment and querying of vet details [7 marks] 4. A pet form to allow the input, amendment and querying of pet details. Pet’s owner ID should be allow to select from an owner list. 6 [8 marks] 5. A master-details form to allow the querying of a owner and its appointment details. [10 marks] 6. A master-details form to allow the querying of a pet and its prescription details. [10 marks] Note: the system is for internal staff use only so that customer access is beyond to scope of the CW. Section D. System Testing and evaluation (10 marks) 1. Testing the completed system with testing plan and results. [6 marks] 2. Evaluate the completed system and reflect your development process. [4 marks] Coursework Submission The completion of the coursework is delivered in two stages. ï‚· Interim submission on Friday Week 12 Development work assessment is an ongoing assessment based on the progress you have made. There will be one interim submission on Friday week 12. Feedback on the database design work will be given to help you improve your work. By week 12, you should have completed section A- database design. ï‚· Final submission The final coursework assessment is due on Friday Week 27. Coursework will be due on Friday week 27.The submission should include CW documentation and a live dynamic web-based information system. ï‚· The document should be contained in a single MS Word document with title and contents pages ï‚· The title page should include module code, module title, URL of the web site, student id and name, and date submitted ï‚· The coursework document should be submitted via Weblearn. ï‚· The dynamic web-based information system should be uploaded to the university’s web server. 7 Documentation template CC5002 Databases and Web-based Information Systems Coursework 2017-18 Website URL: Your name Your ID DATE 8 Table of Contents 1 Section A – Design..………………………………..……………….…..…..……XXX 1.1 3 rd NF and ERD of the Health Pets appointment record..………..……… ………………..…XXX List of 3 rd NF entities and their attributes ERD 1.2 3 rd NF and ERD of the Health Pets prescription sheet.……….…..……………………..…XXX List of 3 rd NF entities and their attributes ERD 1.3 Consolidated 3 rd NF and ERD for Health Pets …
..…………………..………………………XXX List of 3 rd NF entities and their attributes ERD 1.4 Data Dictionary …………………………………..…………………..………………………….…XXX entity name, primary key and foreign keys, names, data types and descriptions of each attribute for each entity 2 Section B – Oracle database implementation…….……………………….…….xxx

Leave a Reply

Your email address will not be published.