Analysis and Design Report
Design Databases
Created by
UPTOWN IT
For
<<CUSTOMER>>
YOUR NAME:
YOUR STUDENT NUMBER:
PROJECT REFERENCE:
DATE:
Contents
Task 1 – Meeting with client 2
Task 2 – Technical requirements 2
Task 5 – Business rules and constraints 5
Remove all blue text once your report is completed. The blue text is intended at giving you some pointers for the report content.
Please ensure that you re-submit your ICTICT523 AT2 document with this assignment.
Task 1 – Meeting with client
NOTE: Already completed in ICTICT523 – Gather data to identify business requirements
This part consists of group work. Group size will depend on class numbers. Around four (4) people per group is an appropriate number. As a group, review the given scenario and collaboratively create a list of around 60 questions. Questions should be categorised as follow:
Hardware
Software
Data requirements
People – roles, access, etc.
Processes/business activities
Business rules
Security
Provide evidence of communication between the student group/developer and the client/teacher. Send an email to the client/teacher requesting a mutually suitable time for a meeting. In a second email, once the meeting time has been agreed to, send the meeting agenda prior to the meeting.
Each group will have a meeting with the teacher/facilitator who will be playing the role of the client – a non-technical business executive. The meeting will either be recorded or documented as an observation checklist (by the teacher) as evidence to demonstrate that you have:
met with the client to conduct the user-needs analysis
negotiated and understood the client’s requirements for the functionality of the website
used active listening, communicating in plain English, and summarising the client’s key points to understand their requirements.
A meeting is a good way of identifying user needs. List and explain other four (4) methods that can be used to identify user needs. Outline the advantages and disadvantages of each method.
Task 2 – Technical requirements
NOTE: Already completed in ICTICT523 – Gather data to identify business requirements
Based on the answers received as a result of the client meeting, recommend specific technical requirements for the database/website project.
You will also be required to research the technical requirements, and include references to where you sourced your information from.
Document the technical requirements (500-800 words). The document must include:
technical requirements negotiated with the client/teacher during the meeting
referenced sources of technical information you have obtained in preparing the technical requirements.
Task 3 – Conceptual model
NOTE: Already completed in ICTICT523 – Gather data to identify business requirements
Design a conceptual model using Visual Paradigm (or similar). The conceptual model will take the form of an entity relationship diagram (ERD).
The conceptual model submitted with your evidence must include:
the completed conceptual model for the database – first draft ERD
feedback from the client, incorporated as amendments in the model – provide evidence of communication between you and the client/teacher (exchange of e-mails)
content written in plain English language which a non-technical client would understand.
Task 4 – Logical data model
Perform normalisation process to at least third normal form (3NF) showing the results for each step. (Hint: Normalisation to be completed on each of the forms provided for the scenario. Reference: 03a_SomeBackgroundOnNormalisation.doc.)
Update the ERD after normalisation. Ensure that the cardinality ratio is displayed. (Hint: Based on your initial version prepared in ICTICT523 – updated as required.)
Develop a data dictionary that clearly indicates the data types and precision parameters for each field (size, range and nullability), primary and foreign keys for tables, data validation and business constraints (checks or triggers). Template provided. (Hint: Export a data dictionary from Visual Paradigm (or equivalent))
NOTE: The logical data model submitted with your evidence must include:
normalisation documentation (step-by-step)
final ERD
data dictionary
approval of the logical data model to the client (exchange of e-mails).
To: Client
From: Me…
CC:
BCC:
Date: 19/07/2023
Subject: Logical database model
————————————————————-
Hi <Client>,
…
Regards,
<Systems Analyst>
———————————————-
<Systems Analyst>
———————————————–
Task 5 – Business rules and constraints
Create a database – Create a database in-line with your ERD and normalisation within Microsoft Access, LibreOffice Base, Microsoft SQL Server or MySQL. Add the required tables and relationships within this database.
Add Sample Data – Add at least 2 records of sample data to each table of your database.
Database Data Dictionary – Generate a data dictionary (or equivalent) from your database. (Note: this is a second data dictionary, in addition to the one you have generated from your ERD.)
Business rules – Identify a minimum of five (5) business rules that apply to the database/website project. (Hint: These would be based on requirements noted by your client in your initial project requirements meeting for ICTICT523.)
Integrity constraints – Primary and foreign keys have been identified. Also identified in the data dictionary. (Hint: As per your updated ERD and Data Dictionary in Task4, and noted in the table below.)
Referential integrity constraints – Identify and set all referential integrity constraints. Use the table below to document the referential integrity constraints by indicating the rules and actions that apply to each foreign key. (Hint: As per your updated ERD and Data Dictionary in Task4, and noted in the table below.)
-
TABLE and FOREIGN KEY (FK)
TABLE & PRIMARY KEY it REFEENCES (PK)
REFERENTIAL INTEGRITY CONSTRAINTS
REFERENTIAL ACTIONS
RULE 1
FK has the same declaration (data type and domain) that the PK it references
RULE 2
FK value is equal to value in PK or NULL
Table_name
(FK)_Col_name
Referenced_tbl_name
(PK)Col_name
Yes
Yes
ON DELETE CASCADE,
ON UPDATE CASCADE
Add lines as required
Semantic and other constraints – These can take the form of validation rules, check constraints and triggers. These need to have been included in the data dictionary. Review and update this section the data dictionary until you are sure that all semantic constraints have been identified. (Hint: These would be based on requests from the client from your initial project requirements meeting for ICTICT523. Examples of these might include: phone numbers or email addresses having a correct format, a product code having the correct number of numbers and characters, a customer name having being entered, a date being in an appropriate date range, a quantity ordered having been entered, and being in an appropriate value range, etc.)
Indexes – Identify which table column will benefit from indexing and create the indexes. You can use the table below to record the indexing. (Hint: While many RDBMS add their own indices, additional indices might be added to improve search efficiency. For instance, indices could be added to customer names, or product codes and/or product names, etc, where searching would be more based on words rather than just IDs.)
-
INDEX NAME
COLUMN NAME
TABLE
INDEX TYPE
(e.g. HASH or B-Tree)
SEARCH BENEFITS
Add rows as required
Provide an estimation of the approximate size of the completed database. Explain how you have reached the estimate. (Hint: See sample spreadsheet provided)
Task 6 – User interface
Using inVision1, MockFlow2 or similar program (Prototyper, Microsoft Visual Studio, etc) design a user interface for the required database/website project including menus, input forms, and reports. The number of screens and reports depends on the case study or scenario used for the project.
The queries necessary to display the required reports will be completed in Task 7.
Task 7a – Physical design
Create a database prototype following the blueprint or structure outlined in the data dictionary created in Task 4. Export or dump the database structure as an SQL file and/or data dictionary file. Submit the database or SQL file, and your exported data dictionary as evidence of database implementation. Apply the constraints identified in Task 5 to the database and provide evidence of the implementation of these.
Compare conceptual model and technical requirement with the actual database created and list and explain the differences. Make sure to make changes to the database, as necessary, while performing this review.
Populate the database with appropriate testing data – sufficient to run the queries.
Create and run the necessary queries to display the reports outlined in the case study or scenario project requirements. Provide screenshots of your queries and results. Remember that these queries are run on the database prototype and their purpose is to ascertain that the database structure is sufficient to produce the desired results.
Task 7b and 8 – Access and security
Outline a plan to backup and restore the database remembering that at this stage it is just a prototype.
Review the business security requirements for the case study/scenario. The points below may help you clarify these requirements.
How the users will gain access.
How the password will be stored securely.
Who will be authorised to access the database.
What permissions will be given
The different permission levels.
At this stage, you are ready to document the access and security requirements (500-800 words). The document must ensure that:
Security design meets the requirements of the business security requirements.
The password access system for the database including encryption/hashing details.
The different user groups and their access requirements and privileges. (Role-Based Access Control, RBAC)
User access profiles.
Task 9 – Client receipt
Create a checklist to seek feedback from the client/teacher, change according to given feedback.
Write an email to the client/teacher to obtain sign off via communicating your intention to submit the completed database design documentation. Include this email with your submission with sign off sheet.
Upload your completed database portfolio (zipped folder) to Connect which include:
Assessment document (this word file)
Database and any associated SQL files of your database
UI Design documents and Report samples– if separate from this report
Excel file with data dictionary
Any other additional files prepared for this assessment that have not been included in this dot-point list.
Example Sign-off:
| DATABASE DESIGN REPORT SIGNOFF
Signing off on this document signifies that the Analysis Report complies with the Client Business requirements. |
|
| Project Manager
Signature: Date: |
Systems Analyst
Signature: Date: |
| DATABASE DESIGN REPORT NOT APPROVED
Please provide feedback on the changes needed. |
|