Scribd
Upload
4 views12 pages

Culinary Database Schema and Queries

Uploaded by

687.21 İnformasiya texnologiyaları
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
4 views12 pages

Culinary Database Schema and Queries

Uploaded by

687.21 İnformasiya texnologiyaları
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd

-- Create the database

CREATE DATABASE CulinaryBook;

GO

USE CulinaryBook;

GO

-- Create the Recipes table

CREATE TABLE Recipes (

RecipeID INT PRIMARY KEY IDENTITY(1,1),

Title NVARCHAR(255) NOT NULL,

Cuisine NVARCHAR(100),

PrepTime INT,

CookTime INT,

Servings INT,

Instructions NVARCHAR(MAX),

Difficulty NVARCHAR(50),

Calories INT

);

GO

-- Create the Ingredients table

CREATE TABLE Ingredients (


IngredientID INT PRIMARY KEY IDENTITY(1,1),

Name NVARCHAR(100) NOT NULL,

Quantity DECIMAL(10, 2),

Measurement NVARCHAR(50),

Temperature INT -- Temperature in degrees (optional)

);

GO

-- Create the RecipeIngredients table (junction table)

CREATE TABLE RecipeIngredients (

RecipeIngredientID INT PRIMARY KEY IDENTITY(1,1),

RecipeID INT NOT NULL,

IngredientID INT NOT NULL,

FOREIGN KEY (RecipeID) REFERENCES Recipes(RecipeID),

FOREIGN KEY (IngredientID) REFERENCES Ingredients(IngredientID)

);

GO

-- Create the Authors table

CREATE TABLE Authors (

AuthorID INT PRIMARY KEY IDENTITY(1,1),

Name NVARCHAR(100) NOT NULL,

Surname NVARCHAR(100),

Bio NVARCHAR(MAX),

YouTubeChannel NVARCHAR(255)
);

GO

-- Create the RecipeAuthors table (junction table)

CREATE TABLE RecipeAuthors (

RecipeAuthorID INT PRIMARY KEY IDENTITY(1,1),

RecipeID INT NOT NULL,

AuthorID INT NOT NULL,

FOREIGN KEY (RecipeID) REFERENCES Recipes(RecipeID),

FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)

);

GO,

INSERT

-- Inserting sample data into Ingredients table

INSERT INTO Ingredients (Name, Quantity, Measurement, Temperature)

VALUES

('Flour', 2.5, 'cups', NULL),

('Sugar', 1.0, 'cup', NULL),

('Butter', 200.00, 'grams', 21), -- Room temperature

('Eggs', 3.00, 'pieces', 21), -- Room temperature

('Milk', 1.5, 'cups', NULL),


('Vanilla Extract', 2.00, 'teaspoons', NULL),

('Salt', 0.5, 'teaspoon', NULL),

('Chocolate Chips', 100.00, 'grams', NULL),

('Garlic', 2.00, 'cloves', NULL),

('Olive Oil', 3.00, 'tablespoons', NULL);

-- Inserting sample data into Authors table

INSERT INTO Authors (Name, Surname, Bio, YouTubeChannel)

VALUES

('Jamie', 'Oliver', 'Celebrity chef known for his focus on healthy eating and easy-to-make recipes.',
'[Link]

('Gordon', 'Ramsay', 'Chef, restaurateur, and television personality known for his fiery temper and high
standards in the kitchen.', '[Link]

('Nigella', 'Lawson', 'Food writer and broadcaster known for her comforting home cooking style.'),

('Rachael', 'Ray', 'Television personality and celebrity chef who hosts a popular talk show and cooking
program.', '[Link]

-- Inserting sample data into Recipes table

INSERT INTO Recipes (Title, Cuisine, PrepTime, CookTime, Servings, Instructions, Difficulty, Calories)

VALUES

('Chocolate Chip Cookies', 'American', 15, 12, 24, 'Preheat oven to 350°F. Mix flour, sugar, and butter.
Add eggs, vanilla extract, and chocolate chips. Bake for 12 minutes.', 'Easy', 250),

('Spaghetti Aglio e Olio', 'Italian', 10, 10, 2, 'Cook spaghetti. Sauté garlic in olive oil, toss with spaghetti,
and garnish with parsley.', 'Easy', 350),

('Beef Wellington', 'British', 45, 60, 4, 'Prepare beef with mushroom duxelles, wrap in puff pastry, and
bake at 400°F for 25-30 minutes.', 'Hard', 800),

('Chicken Alfredo', 'Italian', 20, 20, 4, 'Cook chicken, make alfredo sauce with butter, cream, and
Parmesan cheese. Toss with cooked pasta.', 'Medium', 600);
-- Inserting sample data into RecipeIngredients table (linking recipes and ingredients)

INSERT INTO RecipeIngredients (RecipeID, IngredientID)

VALUES

(1, 1), -- Chocolate Chip Cookies: Flour

(1, 2), -- Chocolate Chip Cookies: Sugar

(1, 3), -- Chocolate Chip Cookies: Butter

(1, 4), -- Chocolate Chip Cookies: Eggs

(1, 5), -- Chocolate Chip Cookies: Milk

(1, 6), -- Chocolate Chip Cookies: Vanilla Extract

(1, 7), -- Chocolate Chip Cookies: Salt

(1, 8), -- Chocolate Chip Cookies: Chocolate Chips

(2, 1), -- Spaghetti Aglio e Olio: Flour (for spaghetti)

(2, 9), -- Spaghetti Aglio e Olio: Garlic

(2, 10), -- Spaghetti Aglio e Olio: Olive Oil

(3, 1), -- Beef Wellington: Flour (for pastry)

(3, 3), -- Beef Wellington: Butter

(3, 4), -- Beef Wellington: Eggs

(3, 10), -- Beef Wellington: Olive Oil

(4, 3), -- Chicken Alfredo: Butter

(4, 5), -- Chicken Alfredo: Milk

(4, 6), -- Chicken Alfredo: Vanilla Extract (optional for the sauce, though it could be used sparingly)

(4, 7); -- Chicken Alfredo: Salt

-- Inserting sample data into RecipeAuthors table (linking recipes to authors)


INSERT INTO RecipeAuthors (RecipeID, AuthorID)

VALUES

(1, 1), -- Chocolate Chip Cookies: Jamie Oliver

(2, 2), -- Spaghetti Aglio e Olio: Gordon Ramsay

(3, 3), -- Beef Wellington: Nigella Lawson

(4, 4); -- Chicken Alfredo: Rachael Ray

QUERIES

1)Authors without youtube channel

SELECT Name, Surname

FROM Authors

WHERE YouTubeChannel IS NULL;

2)Easy making recipes

SELECT Title, Cuisine, PrepTime, CookTime, Servings

FROM Recipes

WHERE Difficulty = 'Easy';

3)More than 400 calories recipes

SELECT Title, Cuisine, Calories


FROM Recipes

WHERE Calories > 400;

4)[Link] of X author

SELECT Name, Surname, YouTubeChannel

FROM Authors

WHERE Name = 'Jamie' AND Surname = 'Oliver';

5)More than 15 minutes prep time recipes

SELECT Title, Cuisine, PrepTime

FROM Recipes

WHERE PrepTime > 15;

LAB 5

1)

SELECT [Link] AS RecipeTitle, [Link] AS AuthorName

FROM Recipes r

JOIN RecipeAuthors ra ON [Link] = [Link]

JOIN Authors a ON [Link] = [Link];

2)
SELECT [Link] AS RecipeTitle, [Link] AS IngredientName, [Link], [Link]

FROM Recipes r

JOIN RecipeIngredients ri ON [Link] = [Link]

JOIN Ingredients i ON [Link] = [Link];

3)

SELECT [Link] AS AuthorName, COUNT([Link]) AS RecipeCount

FROM Authors a

LEFT JOIN RecipeAuthors ra ON [Link] = [Link]

LEFT JOIN Recipes r ON [Link] = [Link]

GROUP BY [Link];

4_)

SELECT [Link], [Link] AS RecipeTitle, [Link] AS IngredientName, [Link], [Link]

FROM Recipes r

JOIN RecipeIngredients ri ON [Link] = [Link]

JOIN Ingredients i ON [Link] = [Link]

ORDER BY [Link];

5)

SELECT [Link] AS RecipeTitle

FROM Recipes r

JOIN RecipeIngredients ri ON [Link] = [Link]

JOIN Ingredients i ON [Link] = [Link]

WHERE [Link] = 'Sugar';

6)

SELECT [Link] AS RecipeTitle, [Link], [Link], [Link] AS AuthorName

FROM Recipes r
JOIN RecipeAuthors ra ON [Link] = [Link]

JOIN Authors a ON [Link] = [Link];

7)

SELECT [Link] AS RecipeTitle, [Link] AS IngredientName, [Link], [Link]

FROM Recipes r

JOIN RecipeAuthors ra ON [Link] = [Link]

JOIN Authors a ON [Link] = [Link]

JOIN RecipeIngredients ri ON [Link] = [Link]

JOIN Ingredients i ON [Link] = [Link]

WHERE [Link] = 'Jamie';

8)

SELECT [Link], [Link] AS RecipeTitle, [Link]

FROM Recipes r

ORDER BY [Link];

9)

SELECT [Link] AS RecipeTitle, [Link], SUM([Link]) AS TotalIngredientQuantity

FROM Recipes r

JOIN RecipeIngredients ri ON [Link] = [Link]

GROUP BY [Link], [Link];

10)

SELECT [Link] AS AuthorName, [Link], [Link] AS RecipeTitle

FROM Authors a

JOIN RecipeAuthors ra ON [Link] = [Link]

JOIN Recipes r ON [Link] = [Link];


LAB 6

Group by queries

1)

SELECT [Link] AS AuthorName, COUNT([Link]) AS TotalRecipes

FROM Authors a

JOIN RecipeAuthors ra ON [Link] = [Link]

JOIN Recipes r ON [Link] = [Link]

GROUP BY [Link];

2)

SELECT [Link], AVG([Link]) AS AveragePrepTime

FROM Recipes r

GROUP BY [Link];

3)

SELECT [Link] AS RecipeTitle, COUNT([Link]) AS TotalIngredients

FROM Recipes r

JOIN RecipeIngredients ri ON [Link] = [Link]

GROUP BY [Link];

4)

SELECT [Link] AS AuthorName, COUNT([Link]) AS TotalRecipes

FROM Authors a

JOIN RecipeAuthors ra ON [Link] = [Link]

JOIN Recipes r ON [Link] = [Link]


GROUP BY [Link]

HAVING COUNT([Link]) > 5;

5)

SELECT [Link], SUM([Link]) AS TotalCalories

FROM Recipes r

GROUP BY [Link];

6)

SELECT [Link], COUNT([Link]) AS RecipeCount

FROM Recipes r

GROUP BY [Link]

WITH ROLLUP;

7)

SELECT [Link], AVG([Link]) AS AverageCookTime

FROM Recipes r

WHERE [Link] > 500

GROUP BY [Link];

8)

SELECT [Link] AS IngredientName, SUM([Link]) AS TotalQuantity

FROM Ingredients i

JOIN RecipeIngredients ri ON [Link] = [Link]

GROUP BY [Link];

9)

SELECT [Link] AS AuthorName, AVG([Link]) AS AverageServings

FROM Authors a

JOIN RecipeAuthors ra ON [Link] = [Link]


JOIN Recipes r ON [Link] = [Link]

GROUP BY [Link];

10)

SELECT [Link], SUM([Link]) AS TotalPrepTime

FROM Recipes r

GROUP BY [Link]

WITH ROLLUP;

Common questions

Powered by AI

The relational structure of the 'RecipeIngredients' table, which links recipes and ingredients through foreign keys, enhances the management and querying of ingredient data. It simplifies complex queries necessary for identifying all ingredients of a particular recipe or evaluating the usage frequency of specific ingredients across recipes. This structure eliminates redundancy, reduces storage requirements, and streamlines updates, allowing for efficient extraction and manipulation of data across complex recipes .

Including diverse difficulty levels for recipes, such as 'Easy', 'Medium', and 'Hard', allows users to filter and select recipes based on their cooking expertise and time constraints, enhancing user interaction. This stratification caters to different user segments from beginners to advanced cooks, encouraging wider engagement with the database. It also aids in educational purposes by gradually challenging users as they develop their skills .

The 'Recipes' table is structured with specific fields such as RecipeID, Title, Cuisine, PrepTime, CookTime, Servings, Instructions, Difficulty, and Calories that allow efficient categorization and retrieval of recipe data. Primary key 'RecipeID' ensures each recipe is unique for secure referencing, while attributes like 'Cuisine' and 'Difficulty' enable targeted queries like filtering by type or complexity of recipes. This organization facilitates streamlined storage and retrieval, enhancing overall data management efficiency .

Junction tables like 'RecipeIngredients' and 'RecipeAuthors' play a critical role in maintaining data integrity by implementing many-to-many relationships. 'RecipeIngredients' links recipes to multiple ingredients, while 'RecipeAuthors' connects multiple authors to recipes, ensuring that changes in one entity reflect accurately across the related entities without data redundancy. This structure enforces relational integrity and allows complex querying capabilities, such as finding all recipes by a specific author or listing all ingredients required for a recipe .

Using specific data types such as NVARCHAR for textual data like 'Title' and 'Instructions', and INT for numerical data such as 'PrepTime' and 'Servings', ensures appropriate storage and processing efficiency. NVARCHAR handles variable-length strings, optimizing space for names and descriptions, whereas INT allows for efficient numerical calculations and comparisons. This precise definition of data types ensures robust data integrity, storage efficiency, and performance in query execution .

Preparation and cook times vary across cuisines due to cultural variations in cooking styles and complexity. For instance, Italian recipes like 'Spaghetti Aglio e Olio' feature shorter times (10 minutes prep, 10 minutes cook), while British dishes like 'Beef Wellington' require more time due to complex preparations (45 minutes prep, 60 minutes cook). These attributes allow users to plan meals according to available time, enhancing the culinary experience. In data management, these metrics enable performance tracking and optimization of recipe recommendations based on available preparation time .

The inclusion of author metadata like YouTube channels in the 'Authors' table enhances the database's value by integrating multimedia resources. Authors connected through their YouTube channels provide users with direct access to visual tutorials and insights into cooking techniques, enriching the learning and application process. This also boosts user engagement by offering diverse content formats and potentially driving traffic between the database and external platforms like YouTube .

Aggregate functions like SUM and AVG are pivotal in analyzing culinary data by providing insights into total and average values across data sets. For example, using SUM with the quantity of ingredients helps determine total inventory needs, while AVG applied to prep times across cuisines can identify average time investments per cuisine, supporting menu planning and resource allocation. These functions enable performance and efficiency assessments, informing data-driven decisions for optimizing culinary experiences .

Categorizing recipes by 'Cuisine' and 'Calories' provides strategic value by allowing users to filter recipes based on geographical culinary preferences and dietary needs. This enables personalized recommendations and enhances the user experience by catering to diverse tastes and health considerations. From a database functionality perspective, it aids in generating dietary analytics and insights, supporting health-focused objectives and targeted culinary programming .

Linking recipes to authors through the 'RecipeAuthors' table enhances database utility by allowing users to explore recipes from chefs with specific cooking styles or expertise, such as Jamie Oliver's focus on healthy eating. This linkage enriches user experience by fostering discovery based on preferred culinary educators and facilitates engagement with curated content from recognized experts, supporting both educational and entertainment exploration within the culinary domain .

You might also like