CO7401 SQL Database Design and Build Assignment

Tasks

1. 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.

2. 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.

3. 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.

4. 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

5. 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.

6. Conclusion:

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

Learning Outcomes Assessed

1. Demonstrate designing, implementing, and managing relational (SQL) databases.
2. Effectively apply advanced concepts in databases, showcasing expertise in query optimisation, performance tuning,
   and utilising sophisticated features during the creation and tuning process.
3. 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