تمرین‌های حل‌شده‌ی مدل‌سازی داده: رزرو هتل، فروشگاه، شرکت و شبکه‌ی اجتماعی

CREATE TABLE Booking (
  BookingId INT IDENTITY PRIMARY KEY,
  GuestId   INT NOT NULL REFERENCES Guest(GuestId),
  RoomId    INT NOT NULL REFERENCES Room(RoomId),
  CheckIn   DATE NOT NULL,
  CheckOut  DATE NOT NULL,
  CONSTRAINT CK_Booking_Dates CHECK (CheckOut > CheckIn)
);

-- بررسی هم‌پوشانی پیش از ثبت رزرو جدید (@RoomId, @In, @Out)
SELECT 1 FROM Booking
WHERE RoomId = @RoomId AND @In < CheckOut AND @Out > CheckIn;   -- اگر ردیفی آمد، اتاق مشغول است

CREATE TABLE OrderItem (
  OrderId   INT NOT NULL REFERENCES Orders(OrderId),
  ProductId INT NOT NULL REFERENCES Product(ProductId),
  Quantity  INT NOT NULL CHECK (Quantity > 0),
  UnitPrice DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (OrderId, ProductId)
);

-- جمع هر سفارش
SELECT o.OrderId, SUM(i.Quantity * i.UnitPrice) AS Total
FROM Orders o
JOIN OrderItem i ON i.OrderId = o.OrderId
GROUP BY o.OrderId;

-- ساختار سازمانی کامل با CTE بازگشتی
WITH Org AS (
  SELECT EmployeeId, FullName, ManagerId, 0 AS Level
  FROM Employee WHERE ManagerId IS NULL
  UNION ALL
  SELECT e.EmployeeId, e.FullName, e.ManagerId, o.Level + 1
  FROM Employee e JOIN Org o ON e.ManagerId = o.EmployeeId
)
SELECT * FROM Org ORDER BY Level, FullName;

CREATE TABLE AppUser (
  UserId   INT IDENTITY PRIMARY KEY,
  UserName NVARCHAR(50) NOT NULL UNIQUE,
  JoinedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

CREATE TABLE Follow (
  FollowerId INT NOT NULL REFERENCES AppUser(UserId),
  FolloweeId INT NOT NULL REFERENCES AppUser(UserId),
  Since      DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  PRIMARY KEY (FollowerId, FolloweeId),
  CONSTRAINT CK_Follow_NotSelf CHECK (FollowerId <> FolloweeId)
);

CREATE TABLE Post (
  PostId    INT IDENTITY PRIMARY KEY,
  UserId    INT NOT NULL REFERENCES AppUser(UserId),
  Body      NVARCHAR(1000) NOT NULL,
  CreatedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

CREATE TABLE PostLike (
  UserId  INT NOT NULL REFERENCES AppUser(UserId),
  PostId  INT NOT NULL REFERENCES Post(PostId),
  LikedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  PRIMARY KEY (UserId, PostId)
);