数据库系统 第4节 关系模型的基本概念案例

1. 关系模型的基本概念

表 (Table)

• 定义: 在关系数据库中，数据被组织成一张或多张表。每张表都有一个唯一的名称，用于区分不同的数据集。
• 案例: 在我们的图书销售网站的例子中，我们有以下几张表：
  • Books: 用于存储书籍的信息。
  • Customers: 用于存储客户的信息。
  • Orders: 用于存储订单的信息。
  • OrderDetails: 用于存储订单详情的信息。

行 (Row)

• 定义: 表中的每一行代表一条记录或实体。每个实体都对应着表中的一个具体实例。
• 案例: 每张表中的行表示如下：
  • Books 表中的每一行代表一本书的信息。
  • Customers 表中的每一行代表一个客户的信息。
  • Orders 表中的每一行代表一个订单的信息。
  • OrderDetails 表中的每一行代表一个订单中的书籍详情。

列 (Column)

• 定义: 列定义了表中的字段或属性。每个列都有一个特定的数据类型，比如整数、字符串或日期等。
• 案例: 每张表中的列如下所示：
  • Books 表:
    • BookID: 书籍的唯一标识符，数据类型为整数。
    • Title: 书籍的标题，数据类型为字符串。
    • Author: 书籍的作者，数据类型为字符串。
    • Publisher: 出版社名称，数据类型为字符串。
    • PublicationDate: 出版日期，数据类型为日期。
    • Price: 书籍的价格，数据类型为浮点数。
  • Customers 表:
    • CustomerID: 客户的唯一标识符，数据类型为整数。
    • Name: 客户的姓名，数据类型为字符串。
    • Address: 客户的地址，数据类型为字符串。
    • Phone: 客户的电话号码，数据类型为字符串。
  • Orders 表:
    • OrderID: 订单的唯一标识符，数据类型为整数。
    • CustomerID: 订单所属客户的唯一标识符，数据类型为整数。
    • OrderDate: 订单日期，数据类型为日期。
    • TotalAmount: 订单总价，数据类型为浮点数。
  • OrderDetails 表:
    • OrderID: 订单的唯一标识符，数据类型为整数。
    • BookID: 书籍的唯一标识符，数据类型为整数。
    • Quantity: 购买的书籍数量，数据类型为整数。
    • UnitPrice: 书籍单价，数据类型为浮点数。

键 (Key)

• 定义: 键是一组一个或多个列的组合，用于唯一标识表中的每一行。主键是一种特殊的键，用来唯一标识表中的每条记录。
• 案例: 每张表中的键如下所示：
  • Books 表:
    • 主键: BookID。
  • Customers 表:
    • 主键: CustomerID。
  • Orders 表:
    • 主键: OrderID。
  • OrderDetails 表:
    • 主键: 组合键 (OrderID, BookID)。

外键 (Foreign Key)

• 定义: 外键是用于建立不同表之间关联的键。一个表的外键通常是另一个表的主键。
• 案例: 外键关系如下所示：
  • Orders 表中的 CustomerID 是外键，与 Customers 表中的主键 CustomerID 相关联。
  • OrderDetails 表中的 OrderID 和 BookID 是外键，分别与 Orders 表中的主键 OrderID 和 Books 表中的主键 BookID 相关联。

通过这些基本概念，我们可以构建起一个关系数据库模型，用于描述图书销售网站的数据结构。这些概念是关系数据库设计的基础，也是理解和操作数据库数据的关键。如果你有任何具体的问题或需要进一步的解释，请随时告诉我！

这次我们将创建一个简单的在线电影票务系统的数据库模型。

案例：在线电影票务系统

实体和属性

• 电影 (Movies)

  • 电影ID (MovieID)
  • 电影名称 (Title)
  • 导演 (Director)
  • 上映日期 (ReleaseDate)
  • 时长 (Duration)

• 影院 (Cinemas)

  • 影院ID (CinemaID)
  • 名称 (Name)
  • 地址 (Address)
  • 电话 (Phone)

• 放映场次 (Showtimes)

  • 放映场次ID (ShowtimeID)
  • 电影ID (MovieID)
  • 影院ID (CinemaID)
  • 开始时间 (StartTime)
  • 结束时间 (EndTime)

• 座位 (Seats)

  • 座位ID (SeatID)
  • 影院ID (CinemaID)
  • 排号 (RowNumber)
  • 座号 (SeatNumber)

• 订单 (Orders)

  • 订单ID (OrderID)
  • 用户ID (UserID)
  • 放映场次ID (ShowtimeID)
  • 下单时间 (OrderTime)
  • 总金额 (TotalAmount)

• 订单详情 (OrderDetails)

  • 订单ID (OrderID)
  • 座位ID (SeatID)
  • 单价 (UnitPrice)

关系

• 一对多 (One-to-Many)

  • 一个电影可以有多个放映场次。
  • 一个影院可以有多个放映场次。
  • 一个放映场次可以有多个座位。
  • 一个用户可以有多个订单。
  • 一个订单可以有多个座位。

• 多对多 (Many-to-Many)

  • 用户和座位之间的关系是多对多的，因为一个用户可以在多个订单中预订多个座位，而一个座位也可以被不同的用户预订。

关系表 (Tables)

电影表 (Movies)

MovieID	Title	Director	ReleaseDate	Duration
1	Inception	Christopher Nolan	2010-07-16	148
2	Interstellar	Christopher Nolan	2014-11-07	169
…	…	…	…	…

影院表 (Cinemas)

CinemaID	Name	Address	Phone
1	Grand Cinema	123 Movie Blvd, Cityville	(123) 456-7890
2	Starlight	456 Film Ave, Townsville	(987) 654-3210
…	…	…	…

放映场次表 (Showtimes)

ShowtimeID	MovieID	CinemaID	StartTime	EndTime
101	1	1	2024-08-08 14:00	2024-08-08 16:28
102	2	2	2024-08-08 16:00	2024-08-08 19:09
…	…	…	…	…

座位表 (Seats)

SeatID	CinemaID	RowNumber	SeatNumber
1001	1	A	1
1002	1	A	2
2001	2	B	1
2002	2	B	2
…	…	…	…

订单表 (Orders)

OrderID	UserID	ShowtimeID	OrderTime	TotalAmount
3001	1001	101	2024-08-08 12:00	25.00
3002	1002	102	2024-08-08 14:30	30.00
…	…	…	…	…

订单详情表 (OrderDetails)

OrderID	SeatID	UnitPrice
3001	1001	12.50
3001	1002	12.50
3002	2001	15.00
3002	2002	15.00
…	…	…

关系模式 (Relation Schema)

• Movies (MovieID, Title, Director, ReleaseDate, Duration)
• Cinemas (CinemaID, Name, Address, Phone)
• Showtimes (ShowtimeID, MovieID, CinemaID, StartTime, EndTime)
• Seats (SeatID, CinemaID, RowNumber, SeatNumber)
• Orders (OrderID, UserID, ShowtimeID, OrderTime, TotalAmount)
• OrderDetails (OrderID, SeatID, UnitPrice)

关系运算 (Relational Operations)

1. 选择所有时长大于150分钟的电影

SELECT * FROM Movies WHERE Duration > 150;

2. 选择所有在Grand Cinema放映的电影

SELECT M.Title
FROM Movies M
JOIN Showtimes S ON M.MovieID = S.MovieID
JOIN Cinemas C ON S.CinemaID = C.CinemaID
WHERE C.Name = 'Grand Cinema';

3. 获取某个具体用户的订单信息

假设我们要查询用户编号为1001的所有订单信息：

SELECT O.OrderID, O.OrderTime, O.TotalAmount
FROM Orders O
WHERE O.UserID = 1001;

4. 获取某个放映场次的座位详情

如果要获取放映场次编号为101的所有座位详情，可以这样写：

SELECT S.RowNumber, S.SeatNumber, OD.UnitPrice
FROM Seats S
JOIN OrderDetails OD ON S.SeatID = OD.SeatID
JOIN Orders O ON OD.OrderID = O.OrderID
JOIN Showtimes ST ON O.ShowtimeID = ST.ShowtimeID
WHERE ST.ShowtimeID = 101;

5. 计算某个用户所有订单的总金额

为了得到用户1001的所有订单的总金额，我们可以这样写：

SELECT O.UserID, SUM(O.TotalAmount) AS TotalSpent
FROM Orders O
WHERE O.UserID = 1001
GROUP BY O.UserID;

示例结果

1. 时长大于150分钟的电影

MovieID	Title	Director	ReleaseDate	Duration
2	Interstellar	Christopher Nolan	2014-11-07	169

2. 在Grand Cinema放映的电影

Title
Inception

3. 用户1001的订单信息

OrderID	OrderTime	TotalAmount
3001	2024-08-08 12:00	25.00

4. 放映场次101的座位详情

RowNumber	SeatNumber	UnitPrice
A	1	12.50
A	2	12.50

5. 用户1001的总花费

UserID	TotalSpent
1001	25.00

通过这些概念和查询，我们可以看到如何利用关系模型和SQL语言来组织和操作数据。如果你需要更深入的解释或者有其他具体的需求，请随时告诉我！

接下来，我们可以构建另一个案例来进一步说明关系模型的基本概念。这次我们将创建一个简单的在线课程注册系统的数据库模型。

案例：在线课程注册系统

实体和属性

• 课程 (Courses)

  • 课程ID (CourseID)
  • 课程名称 (CourseName)
  • 学分 (Credits)
  • 开课学期 (Semester)
  • 教授 (Instructor)

• 学生 (Students)

  • 学生ID (StudentID)
  • 姓名 (Name)
  • 年级 (Year)
  • 专业 (Major)

• 选课 (Enrollments)

  • 选课ID (EnrollmentID)
  • 学生ID (StudentID)
  • 课程ID (CourseID)
  • 注册日期 (RegistrationDate)
  • 成绩 (Grade)

关系

• 一对多 (One-to-Many)

  • 一个学生可以选修多个课程。
  • 一个课程可以被多个学生选修。

• 多对多 (Many-to-Many)

  • 学生和课程之间的关系是多对多的，因为一个学生可以选择多个课程，而一个课程也可以被多个学生选择。

关系表 (Tables)

课程表 (Courses)

CourseID	CourseName	Credits	Semester	Instructor
1	Database Systems	4	Fall	Dr. Smith
2	Web Development	3	Spring	Dr. Johnson
…	…	…	…	…

学生表 (Students)

StudentID	Name	Year	Major
1001	Alice	2	Computer Science
1002	Bob	3	Information Systems
…	…	…	…

选课表 (Enrollments)

EnrollmentID	StudentID	CourseID	RegistrationDate	Grade
101	1001	1	2024-08-01	A
102	1002	2	2024-08-02	B+
…	…	…	…	…

关系模式 (Relation Schema)

• Courses (CourseID, CourseName, Credits, Semester, Instructor)
• Students (StudentID, Name, Year, Major)
• Enrollments (EnrollmentID, StudentID, CourseID, RegistrationDate, Grade)

关系运算 (Relational Operations)

1. 选择所有学分为4的课程

SELECT * FROM Courses WHERE Credits = 4;

2. 选择所有计算机科学专业的学生

SELECT * FROM Students WHERE Major = 'Computer Science';

3. 获取某个具体学生的选课信息

假设我们要查询学生编号为1001的所有选课信息：

SELECT E.CourseID, C.CourseName, E.Grade
FROM Enrollments E
JOIN Courses C ON E.CourseID = C.CourseID
WHERE E.StudentID = 1001;

4. 获取某个课程的学生名单

如果要获取课程编号为1的所有学生名单，可以这样写：

SELECT S.Name, E.Grade
FROM Students S
JOIN Enrollments E ON S.StudentID = E.StudentID
WHERE E.CourseID = 1;

5. 计算某个学生所选课程的总学分

为了得到学生1001所选课程的总学分，我们可以这样写：

SELECT S.StudentID, SUM(C.Credits) AS TotalCredits
FROM Students S
JOIN Enrollments E ON S.StudentID = E.StudentID
JOIN Courses C ON E.CourseID = C.CourseID
WHERE S.StudentID = 1001
GROUP BY S.StudentID;

示例结果

1. 学分为4的课程

CourseID	CourseName	Credits	Semester	Instructor
1	Database Systems	4	Fall	Dr. Smith

2. 计算机科学专业的学生

StudentID	Name	Year	Major
1001	Alice	2	Computer Science

3. 学生1001的选课信息

CourseID	CourseName	Grade
1	Database Systems	A

4. 课程1的学生名单

Name	Grade
Alice	A

5. 学生1001所选课程的总学分

StudentID	TotalCredits
1001	4

通过这些概念和查询，我们可以看到如何利用关系模型和SQL语言来组织和操作数据。如果你需要更深入的解释或者有其他具体的需求，请随时告诉我！