Relational Database Design, Implementation and Maintenance / Recovery
Assessment Brief SCENARIO – STUDENT RECORDS SYSTEM The Unseen University are an open-access alternative to the Open University that have recently been awarded Taught Degree Awarding Powers. They require the development of a system to manage students, courses, enrolments, staff and users. The database needs to support the following features:
• Students should be linked to courses through enrolments; the enrolment should include when the course starts, ends, and what the current academic level is
• Courses are grouped by a learning aim; therefore, when a degree gets revalidated, it will have a new course record but still be for the same learning aim
• There are a limited number of departments; learning aims must be linked to a single department
• Students and staff have user accounts which must hold encrypted passwords The database does not include the following in this project scope:
• Fees / payments / funding
• Tutorials / assessments / tracking
• Registers / attendance
• Any HR or finance functions Any suitable clarifications or additions to the model should be agreed with your tutor.
TASK 1 – DESIGN AND RATIONALE [35% OF OVERALL MARKS] – LO: 4 Based on the above scenario, you must provide a full database design with decisions for design choices made fully justified. All entities, relationships and data types must be defined and related to the scenario, including business rules and assumptions. You are also to investigate normalisation and prove that your design conforms to 3NF. Finally, you must investigate security concerns related to storing user account details. DELIVERABLES
• Detailed ERD o A Visio document diagramming Entities and their Relationships o Attributes showing keys must be included
• Supplementary Documentation o Data Dictionary with full list of attributes by entity, keys, data types and descriptions o Entity and Relationship definitions including precise language o Business Rules and Assumptions clearly documented o A paragraph justifying design decisions made in relation to the scenario
• A referenced short report (approx. 400-600 words) proving your design conforms to 3NF by ensuring the principles of Normalisation are communicated o This should include the rules for each stage of normalisation and what anomalies are prevented through adhering to these rules o Include examples from your data model to show that your database meets these rule o All references should be referenced in the Harvard format according to the college’s guidelines
Please refer to the grade descriptors for how this task will be marked. If you are unsure of the format for providing the design documents, then please consult with your tutor.
GRADE DESCRIPTION: Excellent The ERD is produced clearly with identification of suitable keys and consistent naming throughout. The relationships are clear and cardinality / optionality is clearly displayed, meeting the principles of relational modelling to remove anomalies. The attributes selected are suitable for each entity and the document is titled. Creative problem solving is evident. The Data Dictionary is fully comprehensive, consistent with the ERD and selects suitable data types with additional notes disambiguating where appropriate. The business rules / assumptions listed to constrain the model are justified well and entity / relationship definitions are clear and tightly worded. A strong rationale is given for the design decisions made in creating the model. Overall, the documentation is fully suitable for a developer to create from. The principles of normalisation up to 3NF are documented and interpreted well with clear links to how the data model produced meets the criteria for 3NF. Harvard referencing is used flawlessly.
TASK 2 – IMPLEMENTATION AND MAINTENANCE [65% OF OVERALL MARKS] – LO: 3 & 5. You are now to implemented the database and perform tasks that would aid in the maintenance, backup and recovery of the database. After which you are to consider the security around database and its contents and its legal implications.
• Create and implement a database in your workplace using a suitable DBMS o Evidence will be in the form of screenshots with accompanying descriptions o Suitable test data should be entered o An SQL backup file should be produced o Admin user privileges should be customised rather than left default for security
• Create a log of queries should be provided with evidence of testing to include: o Evidence of JOINs o Evidence of LIKE statements / searching / discovery ability o Statistics using functions, such as number of records etc. o Reporting should include the ability to search for students / courses o Reporting should also be able to determine which students are enrolled to which courses
• Apply maintenance tasks such as scheduling regular backups and using stored procedures o Evidence will be in the form of screenshots with accompanying descriptions
• Implement replicas across multiple machines, setup replication management, configure and test o Testing should include taking the ‘master’ offline and setting one of the replicas / ‘slaves’ as the new master o Evidence will be in the form of screenshots with accompanying descriptions
• Upon completion of the database you should now look to add to the existing report (created in the first task) around 300-600 words which consider the legal liabilities for storing data securely and security mechanisms to meet obligations o Include the Data Protection Act 1998 and investigate implications of breaches / theft of sensitive user data o Discuss one-way hashing, issues with its usage and recommended approaches (e.g. salting) o All references should be referenced in the Harvard format according to the college’s guidelines Please refer to the grade descriptors for how this task will be marked. If you are unsure of the format for providing the evidence documents, then please consult with your tutor.
Grade Descriptors (Derived from Lancaster University Primary Descriptors) The descriptions below provide an indication of the requirements for each grade boundary for this assessment Excellent The database has been fully implemented, configured securely, populated with test data and backed up; this is evidenced with screenshots and a detailed rationale. Full set of working queries is produced with evidence provided in screenshots. Scheduled backup and a broad range of stored procedures are implemented and evidenced with screenshots and a detailed rationale. Multiple replicas have been configured securely across multiple machines and fully tested with comprehensive evidence. Legal issues surrounding storage of user data have been investigated and solutions including one-way hashing, use of salts and best practices have been discussed concisely and in-depth. Harvard referencing is used flawlessly