Custom-Written, AI-Free & Plagiarism-Free Academic Work by Assignment Experts

Assignment Experts UK is a trading name of AKOSZ TEC LTD (Company No. 11483120). View on Companies House

Demonstrate designing, implementing, and managing relational (SQL) databases.

CO7401 Databases – SQL and NoSQL

University of Chester - Postgraduate Programmes Assignment Specification

Faculty of Science, Business & Enterprise Department of Computer Science

Module No

CO7401

Module Title

Databases – SQL and NoSQL

Assessment No

1

Weighting

50%

Submission Date

Feedback due by

Assignment Title

SQL Database Design and Build Assignment

Learning Outcomes Assessed

1. Demonstrate designing, implementing, and managing relational (SQL) databases.

3. Effectively apply advanced concepts in databases, showcasing expertise in query optimisation, performance tuning, and utilising sophisticated features during the creation and tuning process.

4. Critically evaluate and select appropriate database solutions based on specific data management requirements, demonstrating an understanding of the strengths and weaknesses of SQL and NoSQL approaches.

Submission Information

The TurnItIn submission box will have multiple parts. You must submit to the appropriate part

  • A PDF file with your assignment document, all programming code from your project (in a monospace font), and a reference list in APA format
  • A ZIP file containing the project – 1) code in text file format that can be executed, and 2) any data you have used

Both files must be named with your assessment (J number), e.g. J123456_assignment.pdf and J123456_code_data.zip. Please use the assignment template provided for your document.

Files submitted in an incorrect format will usually be marked as zero. All components must be submitted to avoid receiving a mark of zero.

Extensions

A 7-day submission window is available on this assessment with the final date for submission as specified above. No extensions will be granted.

Academic Conduct

The material you submit must be your own work. Please avoid colluding with peers on your work. The penalties for breaching the academic conduct policy are severe. The minimum penalty is usually zero for that piece of work. Further information is available at Portal > Support Departments > Academic Services > Academic Standards > Academic Conduct > Information for Students > Academic Conduct

Generative AI

The use of generative AI tools where not permitted will be treated as a breach of the academic integrity policy.

This assignment does not permit the use of any generative AI tools, including but not limited to ChatGPT, Bard, Copilot, Midjourney, and others.

Referencing code

Code adapted from third parties must be clearly referenced using comments to denote the start and end of the adapted code. You must also include an APA format reference in the PDF file.

Example of referenced code

//code adapted from Thomson, 2012

if (someCharacter == `z` || someCharacter == `Z`) { someCharacter -= 25;

} else {

someCharacter += 1;

}

//end of adapted code

Example of reference entry in PDF file

Thomson, C. (2012). Rot-13 function in Java?. Stackoverflow. Retrieved October 25, 2021, from http://stackoverflow.com/questions/8981296/rot-13-function-in-java

Assignment Brief

Scenario:

You are tasked with designing a SQL database system for a new organisation. You should choose one organisation from the list below and design a database with at least 8 domain specific tables to store transactions for this organisation.

Domain/Industry

Example Entities

E-commerce

User, Products, Shopping Basket, Orders

Hospital Management

Patients, Appointments, Doctors, Locations

University Courses

Students, Lectures, Courses, Grades

Hotel Bookings

Guests, Hotels, Bookings, Extras

Library Book Loans

Book, Member, Loan, Fine

Real Estate Management

Property, Lease, Payment, Tenant

Gym

Member, Trainer, Class, Attendance

Event Management

Event, Attendee, Venue, Schedule

Objective:

You are required to design, populate and document a SQL database schema suitable for managing transactions at this new organization.

Tasks:

Organisation overview

  • The name of the organisation and it’s function e.g. Corner CafĂ© is a cafĂ© serving delicious meals and refreshments to busy shoppers in Chester.
  • A description of the organisation e.g. The cafe is located on the busy high street in Chester and caters to customers between 8am and 6pm. It has 15 tables with a seating capacity for 40 customers at any one time. Corner Coffee offers a diverse menu of meals and snacks to satisfy the needs of hungry shoppers of all ages.

Data Modelling:

  • Identify the main Tables involved in the transaction system (e.g., Customer, Order, Menu Item).
  • Define the Columns and data types for each Table, considering the specific needs of the organisation.
    • For example, a "Customer" Table might include attributes like name, contact information, and order preferences (e.g., milk preference for coffee if the organisation is a cafe).
    • Include additional schema design information such as null/not null constraints, primary keys, foreign keys and indexes.
    • Discuss why a SQL database is a suitable choice for this scenario.

Sample Data:

  • In your SQL*Server environment, create the database, tables and columns for this organisation.
    • Create a sample set of data for your database, demonstrating the structure and potential variations within the data.
    • Build and populate your database with this sample data using a SQL script.

Queries & Operations:

  • Describe how typical operations in the transaction system would be achieved using SQL queries
  • Provide examples of queries that might be used to query the database e.g.:
    • Retrieve a specific customer`s order history.
    • Find all orders for a particular product during a specific time period.
    • Identify the most popular products
    • Summarise sales volumes and value for each day
    • Any other queries you think would be relevant and useful

 

Scalability & Performance:

  • Create Indexes to optimise your queries and aggregates. Document the performance gains you have seen with these indexes.
  • Discuss how your proposed SQL database schema would cater to the potential growth of the organisation (e.g., increased customers, expanding product lines, additional premises).
  • Briefly explain how SQL databases typically scale to handle larger datasets and higher volumes of queries.

Conclusion:

  • Summarize the key aspects of your SQL database design.
  • Briefly discuss any limitations or considerations for using a SQL database in this scenario.

Deliverables:

A well-structured report of up to 1200 words (excluding appendices) documenting

  • Your design decisions and justifications.
  • Diagrams and screenshots to illustrate your data model and sample data.
  • SQL queries with examples of results.
  • Discussion and explanation of your design, queries and results.
  • Your code and its output as an appendix.

SQL code that you have used to create, populate and query your database as a separate *.sql file

Notes

  • You code must run as a single SQL file (*.sql) in Enterprise Manager or Azure Data Studio. You must hand in this *.sql file otherwise you will score 0.
  • You are expected to have comments in the code.
  • You must use APA referencing for any significant blocks of code that are from tutorials etc.
  • All results from your code must be included in your document

Assessment Criteria

70%+ will be awarded for:

  • Demonstrating in-depth knowledge
  • Showing excellent knowledge of the topic area
  • Excellent command and understanding of areas covered
  • A very sophisticated critical analyses and new insights informing situations.

60-70% will be awarded for:

  • Demonstrating extensive knowledge
  • Showing good knowledge of the topic area
  • Sound command, understanding, and usage of relevant tools
  • A sound critical analyses and new insights informing situations

50-60% will be awarded for:

  • Demonstrating some knowledge
  • Showing relevant knowledge of the topic area
  • Showing good command, understanding of areas covered
  • A critical analysis, and some insights informing situations

Answers that fall below the criteria for a pass will receive a failure mark.

100% Plagiarism Free & Custom Written,
tailored to your instructions