头歌 数据模型答案

第1关：关系模型

任务描述

本关任务：将 E-R 图 转换为关系模型，并创建这些转换的表。

答案：

#请在此添加实现代码
########## Begin ##########
#在mydb库中创建表
create table t_player(
    p_id int(11) NOT NULL,
    p_name varchar(32) NOT NULL,
    p_sex varchar(32) NOT NULL,
   p_num int(11) NOT NULL,
   p_email varchar(32) NOT NULL,
   PRIMARY KEY (p_id) 
);
create table t_school(
    s_name varchar (32) NOT NULL,
    s_address varchar(32) NOT NULL,
    s_telephone int(11) NOT NULL,
    s_email varchar (32) NOT NULL,
    PRIMARY KEY (s_name)
);
create table t_product(
    pr_id int(11) NOT NULL,
    pr_name varchar(32) NOT NULL,
    pr_kind varchar(32) NOT NULL,
    pr_link varchar(32) NOT NULL,
    pr_size int(11) NOT NULL,
    PRIMARY KEY (pr_id)
);
create table t_player_product(
    p_id int(11) NOT NULL,
    pr_id int(11) NOT NULL,
    pr_grade int(11) NOT NULL,
    grade_rates varchar(11) NOT NULL,
    CONSTRAINT fk_player_product1 FOREIGN KEY t_player_product(p_id) REFERENCES t_player(p_id),
    CONSTRAINT fk_player_product2 FOREIGN KEY t_player_product(pr_id) REFERENCES t_product(pr_id)
);
create table t_school_player(
    s_name varchar(32) NOT NULL,
    p_id int(11) NOT NULL,
   CONSTRAINT fk_school_stu1 FOREIGN KEY t_school_player(s_name) REFERENCES t_school(s_name),
    CONSTRAINT fk_school_stu2 FOREIGN KEY t_school_player(p_id) REFERENCES t_player(p_id)
);
########## End ##########
 

第2关：层次模型

任务描述

本关任务：将层次模型转化为关系模型。

答案：

#请在此添加实现代码
########## Begin ##########
#在mydb库中创建表并插入数据
CREATE TABLE dept (
    dept_id CHAR (3) PRIMARY KEY,
    dept_name VARCHAR (20) NOT NULL,
    addr VARCHAR (100)
);
CREATE TABLE edu (
    edu_id CHAR (3) PRIMARY KEY,
    edu_name VARCHAR (20),
    dept_id CHAR (3),
    FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
);
CREATE TABLE student (
    student_id CHAR (10) PRIMARY KEY,
    student_name VARCHAR (10),
    level_class CHAR (3),
    dept_id CHAR (3),
    FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
);
CREATE TABLE emp (
    emp_id CHAR (10) PRIMARY KEY,
    emp_name VARCHAR (10),
    title VARCHAR (10),
    edu_id CHAR (3),
    dept_id CHAR (3),
    FOREIGN KEY (edu_id) REFERENCES edu (edu_id),
    FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
);
INSERT INTO dept VALUES ('D02', '计算机', 'R1101');
INSERT INTO edu VALUES ('R01', '数据库', 'D02'),
                ('R02', '网络', 'D02'),
                ('R03', '人工智能', 'D02');
INSERT INTO student VALUES ('S0012', '王明','G1','D02'),
                            ('S0020', '郑直','G2', 'D02'),
                   ('S0199', '周密','G3', 'D02');
INSERT INTO emp VALUES ('E1101', '何璧','教授','R01', 'D02'),
                ('E3721', '刘新','讲师','R01', 'D02'),
                ('E1234', '王思','教授','R03', 'D02'),
                ('E3722', '付弈','助教','R03', 'D02');
########## End ##########

第3关：网状模型

任务描述

本关任务：将网状模型转化为关系模型。

答案：

#请在此添加实现代码
########## Begin ##########
CREATE TABLE t_student (
    s_id VARCHAR(32) NOT NULL,
    s_name VARCHAR(32) NOT NULL,
    s_class VARCHAR(32),
    PRIMARY KEY (s_id)
);
CREATE TABLE t_course (
    c_id VARCHAR(32) NOT NULL,
    c_name VARCHAR(32) NOT NULL,
    c_creadit INT(3),
    PRIMARY KEY (c_id)
);
CREATE TABLE t_relation (
    s_id VARCHAR(32) NOT NULL,
    c_id VARCHAR(32) NOT NULL,
    grade VARCHAR(32) NOT NULL,
    CONSTRAINT fk_1 FOREIGN KEY t_relation(s_id) REFERENCES t_student(s_id),
    CONSTRAINT fk_2 FOREIGN KEY t_relation(c_id) REFERENCES t_course(c_id)
);
INSERT INTO t_student VALUES("S1","张乐","大一"),("S2","王冲","大二"),("S3","翠花","大一");
INSERT INTO t_course VALUES("C1","数据库",45),("C2","python",30);
INSERT INTO t_relation VALUES("S1","C1","A"),
                             ("S1","C2","A"),
                             ("S2","C1","B"),
                             ("S2","C2","A-"),
                             ("S3","C1","C");
########## End ##########

点赞收藏加关注

下次再看不迷路