You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
79 lines
2.2 KiB
79 lines
2.2 KiB
-- 创建数据表
|
|
CREATE TABLE admin (
|
|
admin_name VARCHAR(255) PRIMARY KEY,
|
|
password VARCHAR(255) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE account (
|
|
account_id SERIAL PRIMARY KEY,
|
|
type VARCHAR(50),
|
|
status VARCHAR(50),
|
|
created_by VARCHAR(255) REFERENCES admin(admin_name)
|
|
);
|
|
|
|
CREATE TABLE venue (
|
|
venue_id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
capacity INT NOT NULL CHECK (capacity > 0)
|
|
);
|
|
|
|
CREATE TABLE student (
|
|
student_id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
personal_info_id INT UNIQUE,
|
|
FOREIGN KEY (personal_info_id) REFERENCES personal_info(info_id)
|
|
);
|
|
|
|
CREATE TABLE coach (
|
|
coach_id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
personal_info_id INT UNIQUE,
|
|
FOREIGN KEY (personal_info_id) REFERENCES personal_info(info_id)
|
|
);
|
|
|
|
CREATE TABLE appointment (
|
|
appointment_id SERIAL PRIMARY KEY,
|
|
student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
|
|
coach_id VARCHAR(255) NOT NULL REFERENCES coach(coach_id),
|
|
venue_id INT NOT NULL REFERENCES venue(venue_id),
|
|
period VARCHAR(50) NOT NULL,
|
|
status VARCHAR(50) NOT NULL,
|
|
feedback_id INT REFERENCES feedback(feedback_id)
|
|
);
|
|
|
|
CREATE TABLE feedback (
|
|
feedback_id SERIAL PRIMARY KEY,
|
|
content TEXT NOT NULL,
|
|
date DATE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE invoice (
|
|
invoice_id SERIAL PRIMARY KEY,
|
|
amount DECIMAL(10, 2) NOT NULL CHECK (amount >= 0),
|
|
date DATE NOT NULL,
|
|
appointment_id INT REFERENCES appointment(appointment_id)
|
|
);
|
|
|
|
CREATE TABLE personal_info (
|
|
info_id SERIAL PRIMARY KEY,
|
|
student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
|
|
phone VARCHAR(20),
|
|
email VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE training_record (
|
|
record_id SERIAL PRIMARY KEY,
|
|
student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
|
|
coach_id VARCHAR(255) NOT NULL REFERENCES coach(coach_id),
|
|
feedback TEXT,
|
|
appointment_id INT REFERENCES appointment(appointment_id)
|
|
);
|
|
|
|
CREATE TABLE booking_slot (
|
|
slot_id SERIAL PRIMARY KEY,
|
|
start_time TIME NOT NULL,
|
|
end_time TIME NOT NULL CHECK (end_time > start_time),
|
|
venue_id INT NOT NULL REFERENCES venue(venue_id)
|
|
); |