数据科学导论第二次作业

 ER1

CREATE TABLE player(
    player_ID VARCHAR(20),
    name VARCHAR(20),
    competition_ID VARCHAR(5),
    team_ID VARCHAR(5),
    
    PRIMARY KEY(player_ID),
    
    FOREIGN KEY(competition_ID) REFERENCES competition(competition_ID),
    FOREIGN KEY(team_ID) REFERENCES team(team_ID)
);

CREATE TABLE team(
    team_ID VARCHAR(5),
    name VARCHAR(20),
    player_ID VARCHAR(20),
    competition_ID VARCHAR(5),

    PRIMARY KEY(team_ID),

    FOREIGN KEY(player_ID) REFERENCES player(player_ID),
    FOREIGN KEY(competition_ID) REFERENCES competition(competition_ID)
);

CREATE TABLE competition(
    competition_ID VARCHAR(5),
    name VARCHAR(20),
    questions VARCHAR(100),
    start_time DATETIME,
    end_time DATETIME,
    player_ID VARCHAR(20),
    team_ID VARCHAR(5),
    orgniser_ID VARCHAR(5),

    PRIMARY KEY(competition_ID),

    FOREIGN KEY(player_ID) REFERENCES player(player_ID),
    FOREIGN KEY(team_ID) REFERENCES team(team_ID),
    FOREIGN KEY(orgniser_ID) REFERENCES orgniser(orgniser_ID)
);

CREATE TABLE orgniser(
    orgniser_ID VARCHAR(5),
    name VARCHAR(5),

    PRIMARY KEY(orgniser_ID)
);

 

ER2

CREATE TABLE customer(
    customer_ID VARCHAR(5),
    name VARCHAR(5),

    PRIMARY KEY(customer_ID)
);

CREATE TABLE staff(
    staff_ID VARCHAR(5),
    name VARCHAR(5),
    department_ID VARCHAR(5),

    PRIMARY KEY(staff_ID),

    FOREIGN KEY(department_ID) REFERENCES department(department_ID)
);

CREATE TABLE chat(
    chat_ID VARCHAR(5),
    time DATETIME,
    customer_ID VARCHAR(5),
    staff_ID VARCHAR(5),

    PRIMARY KEY(chat_ID),

    FOREIGN KEY(customer_ID) REFERENCES customer(customer_ID),
    FOREIGN KEY(staff_ID) REFERENCES staff(staff_ID)
);

CREATE TABLE department(
    name VARCHAR(5),
    department_ID VARCHAR(5),
    staff_ID VARCHAR(5),
    admin_ID VARCHAR(5),

    PRIMARY KEY(department_ID),

    FOREIGN KEY(staff_ID) REFERENCES staff(staff_ID),
    FOREIGN KEY(admin_ID) REFERENCES administrator(admin_ID)
);

CREATE TABLE administrator(
	admin_ID VARCHAR(5),
	name VARCHAR(5),
	department_ID VARCHAR(5),

	PRIMARY KEY(admin_ID),

	FOREIGN KEY(department_ID) REFERENCES department(department_ID)
);

 

2

/* 2.1 */

--1 Find the names of workers in the CSG group.
SELECT name
FROM m_worker
WHERE group_name = 'CSG';

--2 Find the IDs of all workers who are in team Infra and 
--  get salary more than 3500; make sure there are no duplicates 
--  in the result. 
SELECT DISTINCT ID
FROM m_worker
WHERE team_name = 'Infra' AND salary > 3500;

--3 Find the highest salary of all workers who have joined 
--  project AliyunAlbum. 
SELECT MAX(salary)
FROM m_worker
WHERE team_name IN (
    SELECT team_name
    FROM team_project
    WHERE project_id IN (
        SELECT project_id
        FROM m_project
        WHERE titile = 'AliyunAlbum'
    )
);

--4 Find all of those projects which have more than 3 different Groups.
SELECT * 
FROM m_project
WHERE project_id IN (
    SELECT project_id
    FROM team_project NATURAL JOIN m_team
    GROUP BY team_name
    HAVING COUNT(group_name) > 3
    )
);

--5 Find the project has the maximum workers. 
SELECT *
FROM m_project
WHERE project_id IN (
    SELECT project_id
    FROM team_project NATURAL JOIN m_worker
    GROUP BY project_id
    HAVING COUNT(m_worker) = (
        SELECT MAX(COUNT)
        FROM(
            SELECT COUNT(m_worker)
            FROM team_project NATURAL JOIN m_worker
            GROUP BY project_id
        )
    )
);

--6 Find the season and year of all quaters which earned more profit 
--  than the average of all quaters'.
SELECT season, year
FROM m_quarter
WHERE profit > (
        SELECT AVG(profit)
        FROM m_quarter
    )
);


/* 2.2 */

--1 Increase the salary of each worker in the CSG group by 10%.
UPDATE m_worker
SET salary = salary * (1 + 0.1)
WHERE group_name = 'CSG';

--2 Delete all teams that have never joined any project
DELETE FROM m_team
WHERE team_name NOT IN (
    SELECT team_name
    FROM team_project
)

--3 Decrease the profit to 10% of the origin of all quarters involved
--  with project AliyunAlbum
UPDATE m_quarter
SET profit = profit * 0.1
WHERE qua_id IN (
    SELECT qua_id
    FROM quarter_project
    WHERE project_id IN (
        SELECT project_id
        FROM m_project
        WHERE title = 'AliyunAlbum'
    )
)