头歌-数据模型

第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 ##########