|
Case Scenario
You have got a database design and management company called Info-Solution Database Ltd. Your client Dollis Hill GP Surgery wants your company to create and maintain their appointment management database to maintain details of its doctors, patients, appointments, test prescribed, nurses etc. According to the Surgery’s requirement:
Each doctor can be identified by unique doctor id, doctor’s name, doctor speciality.
Each patient can be identified by unique patient id, patient name, patient phone number, patient date of birth.
Each nurse can be identified by unique nurse id, nurse’s name.
Details of doctor’s appointment should show which doctor is treating which patient on which date and time, and medical problem of the patient being diagnosed.
Details of prescribed tests should show which doctor ordered for the test for which patient, and which nurse will administer the test on which date and time. It will show what type of test it is.
Unit 4 : Database Design & Development
|
Student Name/ID
|
|
|
Unit Title
|
Database Design & Development
|
|
Assignment Number
|
Assignment 01
|
Assessor
|
|
|
Submission Date
|
22/03/2022
|
Date Received 1st submission
|
|
|
Re-submission Date
|
|
Date Received 2nd submission
|
|
|
Assessor Feedback:
|
|
Grade:
|
Assessor Signature:
|
Date:
|
|
Resubmission Feedback:
|
|
Grade:
|
Assessor Signature:
|
Date:
|
|
Internal Verifier’s Comments:
|
|
Signature & Date:
|
You as an employee of Info-Solution Database Ltd are required to provide the design and implement the same design to produce a fully functional database system to help Dollis Hill GP Surgery to carry out its tasks efficiently.
Task 1 a)-(LO1)
i. Make a list of user requirements and system requirements based on the given scenario
ii. Provide ERD made based on the user requirements and system requirements(in MS-Visio) for 3NF with explanation.
Task 1 b)-(LO1)
- Based on the given scenario describe the process of Normalisation (with diagrams in MS-Visio) up to 3NF (i.e. UNF,1NF,2NF,3NF)
- Design an appropriate database for the given scenario. The design documentation must include:
- An entity relationship diagram (3NF stage only) with correct cardinality in MS-ACCESS.
- Design of the tables, input forms based on ERD
- Designs of at least four complex database queries and four database reports that could be used by the organisation as described by the scenario. All reports must be created from complex database queries.
Task 1 c)- (LO1) Evaluate the effectiveness of the ERD in terms of user and system requirements using a requirements traceability matrix .
Task 2 a) – (LO2)
Implement the design and provide evidence of the implementation via annotated screenshots. Create all the required tables, forms and reports.
Task 2 b)- (LO2)
- i. Make ‘USER-ID’ and ‘PASSWORD’ mechanism to secure the implemented database.
- ii. Provide annotated screenshot as an evidence of implemented database maintenance.
Task 2 c)-(LO2)
Create all the queries using SQL code and provide evidence of the implementation via annotated screenshots and source code.
Task 2 d)—(LO2) Include query validation methods where as per the management’s requirement (e.g. number of appointments done by a doctor per day.)
Task 3 a) – (LO3) Test the system using an appropriate test plan. Amongst other things testing must demonstrate that entity integrity, referential integrity and data integrity has been enforced, buttons are working properly, user interface matches the user requirement.
Task 3 b)- (LO3) Provide evidence of successful testing by producing a test report based on the test plan (in TASK 3a) including an explanation of the choice of test data used.
Task 3 c ) - (LO2 & LO3) Critically evaluate the strengths and weaknesses of the database created in the previous tasks and suggest methods for improvement if required.
Task 4 a ) (LO4)
i. Produce a user-manual for the implemented database.
ii. Produce a data dictionary for the implemented database.
Task 4b) (LO4)
Produce a data flow diagram and a flow-chart for the implemented database with proper explanation of each.
Task 4 c) (LO4)
Justify, how the future improvements that have been suggested in Task 3c) are helpful in overcoming the weaknesses of the database system Unit 4 : Database Design & Development
|
