数据库概念6th 教材相关表定义及数据插入

记录一下教材上用到的表，需要的可粘贴复制，数据库中运行

DROP database IF EXISTS test;

CREATE database test;

use test;

CREATE TABLE department(
    dept_name varchar(20),
    building varchar(15),
    budget numeric(12, 2),
    primary key(dept_name)
);

CREATE TABLE course(
    course_id varchar(7),
    title varchar(50),
    dept_name varchar(20),
    credits numeric(2, 0),
    primary key(course_id),
    foreign key(dept_name) references department(dept_name)
);

CREATE TABLE instructor(
    ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    salary numeric(8, 2),
    primary key(ID),
    foreign key(dept_name) references department(dept_name)
);

CREATE TABLE section(
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(8),
    year numeric(4, 0),
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    primary key(course_id, sec_id, semester, year),
    foreign key(course_id) references course(course_id)
);

CREATE TABLE teaches(
    ID varchar(5),
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4, 0),
    primary key(ID, course_id, sec_id, year),
    foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year),
    foreign key(ID) references instructor(ID)
);

INSERT INTO
    department(dept_name, building, budget)
values
    ("Comp.Sci.", "Taylor", 100000),
    ("Biology", "Watson", 90000),
    ("Elec.Eng.", "Taylor", 85000),
    ("Music", "Packard", 80000),
    ("Finance", "Painter", 120000),
    ("History", "Painter", 50000),
    ("Physics", "Watson", 70000);

INSERT INTO
    instructor(ID, name, dept_name, salary)
values
    ("10101", "Srinivasan", "Comp.Sci.", 65000),
    ("12121", "Wu", "Finance", 90000),
    ("15151", "Mozart", "Music", 40000),
    ("22222", "Einstein", "Physics", 95000),
    ("32343", "El.Said", "History", 60000),
    ("33456", "Gold", "Physics", 87000),
    ("45565", "Katz", "Comp.Sci.", 75000),
    ("58583", "Califieri", "History", 62000),
    ("76543", "Singh", "Finance", 80000),
    ("76766", "Crick", "Biology", 72000),
    ("83821", "Brandt", "Comp.Sci.", 92000),
    ("98345", "Kim", "Elec.Eng.", 80000);

INSERT INTO
    course(course_id, title, dept_name, credits)
values
    ("BIO-101", "Intro.to_Biology", "Biology", 4),
    ("BIO-301", "Genetics", "Biology", 4),
    ("BIO-399", "Computational_Biology", "Biology", 3),
    (
        "CS-101",
        "Intro.to_Computer_Science",
        "Comp.SCi.",
        4
    ),
    ("CS-190", "Game_Design", "Comp.SCi.", 4),
    ("CS-315", "Robotics", "Comp.SCi.", 3),
    ("CS-319", "Image_Processing", "Comp.SCi.", 3),
    (
        "CS-347",
        "Database_System_Concepts",
        "Comp.SCi.",
        3
    ),
    (
        "EE-181",
        "Intro.to_Digital_System",
        "Elec.Eng.",
        3
    ),
    ("FIN-201", "Investment_Banking", "Finance", 3),
    ("HIS-351", "World_History", "History", 3),
    ("MU-199", "Music_Video_Production", "Music", 3),
    ("PHY-101", "Physical_Principles", "Physics", 4);

INSERT INTO
    section(
        course_id,
        sec_id,
        semester,
        year,
        building,
        room_number,
        time_slot_id
    )
values
    (
        "BIO-101",
        1,
        "Summer",
        2009,
        "Painter",
        514,
        "B"
    ),
    (
        "BIO-301",
        1,
        "Summer",
        2010,
        "Painter",
        514,
        "A"
    ),
    ("CS-101", 1, "Fall", 2009, "Packard", 101, "H"),
    ("CS-101", 1, "Spring", 2010, "Packard", 101, "F"),
    ("CS-190", 1, "Spring", 2009, "Taylor", 3128, "E"),
    ("CS-190", 2, "Spring", 2009, "Taylor", 3128, "A"),
    ("CS-315", 1, "Spring", 2010, "Watson", 120, "D"),
    ("CS-319", 1, "Spring", 2010, "Watson", 100, "B"),
    ("CS-319", 2, "Spring", 2010, "Taylor", 3128, "C"),
    ("CS-347", 1, "Fall", 2009, "Taylor", 3128, "A"),
    ("EE-181", 1, "Spring", 2009, "Taylor", 3128, "C"),
    (
        "FIN-201",
        1,
        "Spring",
        2010,
        "Packard",
        101,
        "B"
    ),
    (
        "HIS-351",
        1,
        "Spring",
        2010,
        "Painter",
        514,
        "C"
    ),
    ("MU-199", 1, "Spring", 2010, "Packard", 101, "D"),
    ("PHY-101", 1, "Fall", 2009, "Watson", 100, "A");

INSERT INTO
    teaches(ID, course_id, sec_id, semester, year)
values
    (10101, "CS-101", 1, "Fall", 2009),
    (10101, "CS-315", 1, "Spring", 2010),
    (10101, "CS-347", 1, "Fall", 2009),
    (12121, "FIN-201", 1, "Spring", 2010),
    (15151, "MU-199", 1, "Spring", 2010),
    (22222, "PHY-101", 1, "Fall", 2009),
    (32343, "HIS-351", 1, "Spring", 2010),
    (45565, "CS-101", 1, "Spring", 2010),
    (45565, "CS-319", 1, "Spring", 2010),
    (76766, "BIO-101", 1, "Summer", 2009),
    (76766, "BIO-301", 1, "Summer", 2010),
    (83821, "CS-190", 1, "Spring", 2009),
    (83821, "CS-190", 2, "Spring", 2009),
    (83821, "CS-319", 2, "Spring", 2010),
    (98345, "EE-181", 1, "Spring", 2009);

department 表

course 表

 instructor表

 section表

 taches表