数据库 - 第一次实验

英雄联盟

CREATE TABLE Teams (  
    team_id INT PRIMARY KEY,  
    team_name VARCHAR(255),  
    found_date DATE  
);  
  
CREATE TABLE Players (  
    player_id INT PRIMARY KEY,  
    name VARCHAR(255),  
    birthday DATE,  
    gender VARCHAR(10),  
    position VARCHAR(10),  
    team_id INT,  
    FOREIGN KEY (team_id) REFERENCES Teams(team_id)  
);  
  
CREATE TABLE teamsjoin (  
    winningTeamID INT,  
    losingTeamID INT,   
    FOREIGN KEY (winningTeamID) REFERENCES Teams(team_id),  
    FOREIGN KEY (losingTeamID) REFERENCES Teams(team_id)  
);  
  
CREATE TABLE Matches (  
    match_id INT PRIMARY KEY,  
    location VARCHAR(255),  
    beginning_time DATE,  
    duration INT
);

CREATE TABLE Heroes (  
    hero_id INT PRIMARY KEY,  
    hero_Name VARCHAR(255),  
    description TEXT,  
    battle_value INT,  
    health_value INT,  
    defense_value INT  
);

CREATE TABLE KillEvents (  
    kill_time DATE,  
    killer_id INT,  
    killed_id INT, 
    match_id INT,
    PRIMARY KEY (kill_time,killer_id,killed_id,match_id),    
    FOREIGN KEY (match_id) REFERENCES Matches(match_id),  
    FOREIGN KEY (killer_id) REFERENCES Players(player_id),
    FOREIGN KEY (killed_id) REFERENCES Players(player_id)  
);

CREATE TABLE DamageEvents (  
    damage_time DATE,  
    attacker_id INT,  
    attacked_id INT, 
    match_id INT,
    PRIMARY KEY (damage_time,attacker_id,attacked_id,match_id),    
    FOREIGN KEY (match_id) REFERENCES Matches(match_id),  
    FOREIGN KEY (attacker_id) REFERENCES Players(player_id),
    FOREIGN KEY (attacked_id) REFERENCES Players(player_id)   
);

CREATE TABLE MoneyEvents (  
    get_time DATE,  
    money INT, 
    match_id INT,
    player_id INT,
    PRIMARY KEY (get_time,money,player_id),  
    FOREIGN KEY (match_id) REFERENCES Matches(match_id),  
    FOREIGN KEY (player_id) REFERENCES Players(player_id)  
);

CREATE TABLE Pick (   
    pick_order INT,  
    match_id INT,
    player_id INT,
    hero_id INT,
    FOREIGN KEY (match_id) REFERENCES Matches(match_id),  
    FOREIGN KEY (player_id) REFERENCES Players(player_id), 
    FOREIGN KEY (hero_id) REFERENCES Heroes(hero_id)  
);

CREATE TABLE Ban (
	ban_order INT,
	match_id INT,
    player_id INT,
    hero_id INT,
    FOREIGN KEY (match_id) REFERENCES Matches(match_id),  
    FOREIGN KEY (player_id) REFERENCES Players(player_id),  
    FOREIGN KEY (hero_id) REFERENCES Heroes(hero_id)  
);

微信

CREATE TABLE user (  
  uid INT PRIMARY KEY,  
  nickname VARCHAR(255),  
  longitude FLOAT,  
  latitude FLOAT  
);

CREATE TABLE friend (  
  uid1 INT,  
  uid2 INT,  
  connect_time DATETIME,  
  type VARCHAR(255),  
  note VARCHAR(255),  
  PRIMARY KEY (uid1, uid2),  
  FOREIGN KEY (uid1) REFERENCES User(uid),  
  FOREIGN KEY (uid2) REFERENCES User(uid)  
);

CREATE TABLE send (  
  mid INT,
  receiver_uid INT,
  sender_uid INT,
  sent_time DATETIME,
  FOREIGN KEY (mid) REFERENCES Message(mid),
  FOREIGN KEY (sender_uid, receiver_uid) REFERENCES Friend(uid1, uid2)
);

CREATE TABLE message (  
  mid INT PRIMARY KEY,  
  content TEXT,  
  shared_count INT,    
  status VARCHAR(25)    
);

CREATE TABLE groups (  
  gid INT PRIMARY KEY,  
  gname VARCHAR(255),  
  create_time DATETIME,   
  uid_owner INT,  
  FOREIGN KEY (uid_owner) REFERENCES User(uid)  
);

CREATE TABLE moment (
	moid INT PRIMARY KEY,
	type VARCHAR(255),
	post_time DATETIME,
	uid INT,
	FOREIGN KEY (uid) REFERENCES User(uid)
);

CREATE TABLE group_send (
	sent_time DATETIME, 
	mid INT,
	gid INT,
	uid INT,
	FOREIGN KEY (uid) REFERENCES User(uid),
	FOREIGN KEY (mid) REFERENCES User(message),
	FOREIGN KEY (gid) REFERENCES groups(gid)
);

CREATE TABLE changename (
	nickname VARCHAR(255),
	uid INT,
	gid INT,
	FOREIGN KEY (uid) REFERENCES User(uid),
	FOREIGN KEY (gid) REFERENCES groups(gid)
);

CREATE TABLE join_group (
	join_time VARCHAR(255),
	uid INT,
	gid INT,
	FOREIGN KEY (uid) REFERENCES User(uid),
	FOREIGN KEY (gid) REFERENCES groups(gid)
);