Database System Skills: Analysis, Design and Development

Undertake Analysis and Clarify Business Rules and Constraints for YAHUAS

Assignment Brief

This module will equip students with the database system skills, knowledge and techniques that will enable them to work effectively in a commercial environment. The module will consider the problems and decisions that face database designers and administrators and provide strategies that will enable their resolution.

Students will develop a detailed knowledge and understanding of database theory which will enable them to synthesise and apply this knowledge to the design and implementation of database solutions.

In your role as a database professional seconded from a job at Software & Data Consultants (SDC), you are expected to take on the duties of database designer, developer, data administrator and database researcher.

You have been assigned to work on a project for Yorkshire & Humberside University Accommodation Scheme (YAHUAS) for the director Peter Urchington.  The YAHUAS scenario is attached below.  You are expected to undertake the analysis, design and development activities highlighted in the following tasks, and with a final presentation of your database solution to the director and your employer.

Couchbase  (2017, online) confirm that as  enterprises shift to the Digital Economy – an economy powered by cloud, mobile, social media, and big data technologies – development and operations teams have to build and maintain web, mobile, and Internet of Things (IoT) applications faster and faster, and at greater scale. Increasingly, these applications are how companies interact with customers, employees, partners, and products’.  Underlying to this trend, you will explore the how these changes have evolved, in the competitive landscape.  You will be expected to explore the database environments trends, and emerging technologies, and furthermore, address the legal, social and ethical issues associated with these

Learning outcomes  1, 3, 5, 6, 7   and 8 

Task 1 (25 marks)

Undertake the appropriate analysis of YAHUAS, ensuring you clarify the business rules, constraints and any assumptions you make.  Construct a use case diagram capturing the requirements for YAHUAS and all the interested parties who have an active role to play. Illustrate using a CASE tool.   

Devise a completed conceptual and logical model using the relational principles, and normalisation,   using MYSQL Workbench Data Modelling tool.  Create a metadata schema, detailing all the formats for all your data items including any verification and validations required for implementation.

Task 2 (60 marks)

Based on your design documentation, build a database solution for YAHUAS using MYSQL Community Server 5.6/5.7 linked to a XAMPP solution within the Eclipse IDE tool.

Construct a simple, effective front end to your database solution to support all users’ needs, choosing an appropriate programming language. As part of this build you should:

• Write SQL scripts to create, amend and insert data for YAHUAS.
• Design, write, and test relevant SQL queries that will provide information that meets YAHUAS’s operational, tactical and strategic needs. 
• Design, create, and test a range of advanced queries (including triggers, functions, and procedures) to further support YAHUAS’s decision making.

Task 3 (15 marks)

Create a video (maximum of 20 minutes) of your completed database solution that demonstrates its functionality to the director of YAHUAS, and the senior database developer at SDC.