Database Design and Queries for Chateau Fine Wines Lite

3. A list of relationships (2”)
4. ER Diagrams (4”)
5. Schemas (2”)
6. Normalization if possible
7. Table creation and data entry (using SQL statements) (8”)
8. Sample run (create at least 4 queries) (4”) 我举得这个例子是关于 Chateau Fine Wines Lite is a small wine store selling high quality wines, primarily from France and Italy. The store has one owner and two salespeople, and all wines are from the store's own inventory; no consignments are accepted. Chateau Fine Wines Lite is a small wine store selling high quality wines, primarily from France and Italy. The store has one owner and two salespeople, and all wines are from the store's own inventory; no consignments are accepted. 请你帮我完成上述陈述

To help you with the tasks related to the Chateau Fine Wines Lite database, I'll guide you through each step, from defining relationships to creating tables and running sample queries. Let's start:

3. A List of Relationships

1. Owner to Store: One-to-One (Each store has one owner)
2. Salespeople to Store: Many-to-One (Multiple salespeople work in one store)
3. Wine to Store: Many-to-One (Multiple wines are sold in one store)
4. Wine to Salespeople: Many-to-Many (Salespeople can sell multiple wines, and each wine can be sold by multiple salespeople)

4. ER Diagrams

To create an ER diagram, you would typically use a tool like Lucidchart or draw.io. Here's a textual representation:

• Entities: Owner, Salesperson, Wine, Store
• Relationships:
  • Owner (1) — (1) Store
  • Salesperson (M) — (1) Store
  • Wine (M) — (1) Store
  • Salesperson (M) — (M) Wine

5. Schemas

1. Owner: OwnerID (PK), Name, ContactInfo
2. Salesperson: SalespersonID (PK), Name, ContactInfo, StoreID (FK)
3. Wine: WineID (PK), Name, Country, Price, StoreID (FK)
4. Store: StoreID (PK), Name, Location
5. Sales: SalesID (PK), SalespersonID (FK), WineID (FK), Date, Quantity

6. Normalization

The schema is already normalized to 3NF:

• Each table has a primary key.
• All non-key attributes are fully functionally dependent on the primary key.
• There are no transitive dependencies.

7. Table Creation and Data Entry (using SQL statements)

CREATE TABLE Store (
    StoreID INT PRIMARY KEY,
    Name VARCHAR(100),
    Location VARCHAR(100)
);

CREATE TABLE Owner (
    OwnerID INT PRIMARY KEY,
    Name VARCHAR(100),
    ContactInfo VARCHAR(100),
    StoreID INT,
    FOREIGN KEY (StoreID) REFERENCES Store(StoreID)
);

CREATE TABLE Salesperson (
    SalespersonID INT PRIMARY KEY,
    Name VARCHAR(100),
    ContactInfo VARCHAR(100),
    StoreID INT,
    FOREIGN KEY (StoreID) REFERENCES Store(StoreID)
);

CREATE TABLE Wine (
    WineID INT PRIMARY KEY,
    Name VARCHAR(100),
    Country VARCHAR(50),
    Price DECIMAL(10, 2),
    StoreID INT,
    FOREIGN KEY (StoreID) REFERENCES Store(StoreID)
);

CREATE TABLE Sales (
    SalesID INT PRIMARY KEY,
    SalespersonID INT,
    WineID INT,
    Date DATE,
    Quantity INT,
    FOREIGN KEY (SalespersonID) REFERENCES Salesperson(SalespersonID),
    FOREIGN KEY (WineID) REFERENCES Wine(WineID)
);

-- Sample Data Entry
INSERT INTO Store (StoreID, Name, Location) VALUES (1, 'Chateau Fine Wines Lite', '123 Wine St');
INSERT INTO Owner (OwnerID, Name, ContactInfo, StoreID) VALUES (1, 'John Doe', '[email protected]', 1);
INSERT INTO Salesperson (SalespersonID, Name, ContactInfo, StoreID) VALUES (1, 'Alice', '[email protected]', 1);
INSERT INTO Salesperson (SalespersonID, Name, ContactInfo, StoreID) VALUES (2, 'Bob', '[email protected]', 1);
INSERT INTO Wine (WineID, Name, Country, Price, StoreID) VALUES (1, 'Chateau Margaux', 'France', 250.00, 1);
INSERT INTO Wine (WineID, Name, Country, Price, StoreID) VALUES (2, 'Barolo', 'Italy', 150.00, 1);
INSERT INTO Sales (SalesID, SalespersonID, WineID, Date, Quantity) VALUES (1, 1, 1, '2023-10-01', 5);
INSERT INTO Sales (SalesID, SalespersonID, WineID, Date, Quantity) VALUES (2, 2, 2, '2023-10-02', 3);

8. Sample Run (Create at least 4 queries)

-- Query 1: List all wines sold by a specific salesperson
SELECT Wine.Name, Wine.Country, Wine.Price
FROM Sales
JOIN Wine ON Sales.WineID = Wine.WineID
WHERE Sales.SalespersonID = 1;

-- Query 2: Total sales for each wine
SELECT Wine.Name, SUM(Sales.Quantity) AS TotalSold
FROM Sales
JOIN Wine ON Sales.WineID = Wine.WineID
GROUP BY Wine.Name;

-- Query 3: List all salespeople and the wines they have sold
SELECT Salesperson.Name AS SalespersonName, Wine.Name AS WineName
FROM Sales
JOIN Salesperson ON Sales.SalespersonID = Salesperson.SalespersonID
JOIN Wine ON Sales.WineID = Wine.WineID;

-- Query 4: Find the total revenue generated by each salesperson
SELECT Salesperson.Name, SUM(Wine.Price * Sales.Quantity) AS TotalRevenue
FROM Sales
JOIN Salesperson ON Sales.SalespersonID = Salesperson.SalespersonID
JOIN Wine ON Sales.WineID = Wine.WineID
GROUP BY Salesperson.Name;

This setup should give you a comprehensive view of the database structure and operations for Chateau Fine