CO7401 SQL Database Design and Build Assignment

Tasks

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.

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.

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.

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

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.

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

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