旅游景点信息管理数据库 MYSQL8.0 我需要设计6-8张表实现这个数据库设计,我需要完成下述所有功能的mysql8.0代码 1、根据所选题目,运用所学的数据库设计知识,设计E-R模型,详细描述实体的属性和实体之间的联系。 2、根据E-R模型设计关系表,定义主码、外码等(使用CREATE TABLE语句实现)。 3、为了加快查询速度,基于基本表中的某些属性建立索引(使用CREATE INDEX语句实现) 需要建立5个索引。 4、根据需要,在基本表上设计视图(使用CREATE VIEW语句实现) 需要建立三个视图。 5、考虑数据库的安全性,设计角色,为不同的用户定义权限 需要非常详细的备注。 6、考虑数据库的完整性,定义完整性约束,根据需要设计触发器 我需要三个mysql8.0语法的触发器。 7、根据需要设计存储过程(使用CREATE PROCEDURE语句实现) 我需要两个存储过程。 8、在数据库中输入数据,进行增、删、改、查等操作测试,并对数据库安全性、触发器和存储过程进行验证
时间: 2024-04-13 15:28:49 浏览: 74
以下是使用MySQL 8.0代码实现旅游景点信息管理数据库设计的步骤:
1. 设计E-R模型,详细描述实体的属性和实体之间的联系。
```sql
-- 景点表
CREATE TABLE spot (
spot_id INT PRIMARY KEY,
spot_name VARCHAR(100),
city VARCHAR(100),
description TEXT,
rating FLOAT
);
-- 旅游者表
CREATE TABLE traveler (
traveler_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
gender ENUM('Male', 'Female'),
nationality VARCHAR(100),
contact VARCHAR(100)
);
-- 游记表
CREATE TABLE travelogue (
travelogue_id INT PRIMARY KEY,
title VARCHAR(100),
content TEXT,
publish_time DATETIME,
author_id INT,
FOREIGN KEY (author_id) REFERENCES traveler(traveler_id)
);
-- 攻略表
CREATE TABLE strategy (
strategy_id INT PRIMARY KEY,
title VARCHAR(100),
content TEXT,
publish_time DATETIME,
author_id INT,
FOREIGN KEY (author_id) REFERENCES traveler(traveler_id)
);
-- 评论表
CREATE TABLE comment (
comment_id INT PRIMARY KEY,
content TEXT,
comment_time DATETIME,
spot_id INT,
commenter_id INT,
FOREIGN KEY (spot_id) REFERENCES spot(spot_id),
FOREIGN KEY (commenter_id) REFERENCES traveler(traveler_id)
);
-- 收藏夹表
CREATE TABLE collection (
collection_id INT PRIMARY KEY,
collection_name VARCHAR(100),
creator_id INT,
FOREIGN KEY (creator_id) REFERENCES traveler(traveler_id)
);
-- 收藏明细表
CREATE TABLE collection_detail (
detail_id INT PRIMARY KEY,
collection_id INT,
spot_id INT,
FOREIGN KEY (collection_id) REFERENCES collection(collection_id),
FOREIGN KEY (spot_id) REFERENCES spot(spot_id)
);
```
2. 建立索引以加快查询速度
```sql
-- 在spot表上创建索引
CREATE INDEX idx_spot_city ON spot(city);
CREATE INDEX idx_spot_rating ON spot(rating);
-- 在travelogue表上创建索引
CREATE INDEX idx_travelogue_author_id ON travelogue(author_id);
-- 在strategy表上创建索引
CREATE INDEX idx_strategy_author_id ON strategy(author_id);
-- 在comment表上创建索引
CREATE INDEX idx_comment_spot_id ON comment(spot_id);
CREATE INDEX idx_comment_commenter_id ON comment(commenter_id);
```
3. 创建视图
```sql
-- 创建景点评分大于等于4的景点视图
CREATE VIEW high_rated_spots AS
SELECT * FROM spot WHERE rating >= 4;
-- 创建最新发布的游记视图
CREATE VIEW latest_travelogues AS
SELECT * FROM travelogue ORDER BY publish_time DESC LIMIT 10;
-- 创建最新发布的攻略视图
CREATE VIEW latest_strategies AS
SELECT * FROM strategy ORDER BY publish_time DESC LIMIT 10;
```
4. 设计角色和权限
```sql
-- 创建角色并定义权限
CREATE ROLE admin;
GRANT ALL PRIVILEGES ON *.* TO admin;
CREATE ROLE editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON spot TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON travelogue TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON strategy TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON comment TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON collection TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON collection_detail TO editor;
CREATE ROLE viewer;
GRANT SELECT ON spot TO viewer;
GRANT SELECT ON travelogue TO viewer;
GRANT SELECT ON strategy TO viewer;
GRANT SELECT ON comment TO viewer;
GRANT SELECT ON collection TO viewer;
GRANT SELECT ON collection_detail TO viewer;
```
5. 定义完整性约束和触发器
```sql
-- 在travelogue表上定义触发器,限制每个作者只能发布一篇游记
CREATE TRIGGER trg_travelogue_limit BEFORE INSERT ON travelogue
FOR EACH ROW
BEGIN
IF EXISTS (SELECT * FROM travelogue WHERE author_id = NEW.author_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Each author can only publish one travelogue.';
END IF;
END;
-- 在comment表上定义触发器,限制每个旅游者只能发布一条评论
CREATE TRIGGER trg_comment_limit BEFORE INSERT ON comment
FOR EACH ROW
BEGIN
IF EXISTS (SELECT * FROM comment WHERE commenter_id = NEW.commenter_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Each traveler can only post one comment.';
END IF;
END;
-- 在collection表上定义触发器,限制每个收藏夹的名称唯一
CREATE TRIGGER trg_collection_name_unique BEFORE INSERT ON collection
FOR EACH ROW
BEGIN
IF EXISTS (SELECT * FROM collection WHERE collection_name = NEW.collection_name) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Collection name must be unique.';
END IF;
END;
```
6. 创建存储过程
```sql
-- 创建存储过程,根据景点ID查询评论数量
DELIMITER //
CREATE PROCEDURE get_comment_count(IN spotId INT)
BEGIN
SELECT COUNT(*) FROM comment WHERE spot_id = spotId;
END //
DELIMITER ;
-- 创建存储过程,根据旅游者ID查询收藏夹数量
DELIMITER //
CREATE PROCEDURE get_collection_count(IN travelerId INT)
BEGIN
SELECT COUNT(*) FROM collection WHERE creator_id = travelerId;
END //
DELIMITER ;
```
7. 进行增、删、改、查等操作测试并验证数据库的安全性、触发器和存储过程。
你可以使用INSERT、UPDATE、DELETE和SELECT语句来向数据库中插入数据、更新数据、删除数据和查询数据,然后验证数据库的安全性、触发器和存储过程的功能。例如:
```sql
-- 向spot表插入数据
INSERT INTO spot (spot_id, spot_name, city, description, rating)
VALUES (1, '景点A', '城市A', '这是景点A的描述', 4.5);
-- 更新spot表中的评分
UPDATE spot SET rating = 4.8 WHERE spot_id = 1;
-- 删除spot表中的一条记录
DELETE FROM spot WHERE spot_id = 1;
-- 查询high_rated_spots视图
SELECT * FROM high_rated_spots;
-- 查询最新发布的游记
SELECT * FROM latest_travelogues;
-- 查询旅游者ID为1的收藏夹数量
CALL get_collection_count(1);
```
以上是使用MySQL 8.0代码实现旅游景点信息管理数据库设计的步骤,你可以根据需要进行进一步的测试和验证。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![avi](https://img-home.csdnimg.cn/images/20210720083319.png)
![-](https://img-home.csdnimg.cn/images/20210720083327.png)
![-](https://img-home.csdnimg.cn/images/20210720083327.png)
![-](https://img-home.csdnimg.cn/images/20210720083327.png)
![-](https://img-home.csdnimg.cn/images/20210720083327.png)
![-](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)