テーブルスキーマの設計とテーブルの作成 - ApsaraDB RDS

ApsaraDB RDS for PostgreSQLは、高度なオープンソースのリレーショナルデータベースであるPostgreSQLに基づいて開発されています。 ApsaraDB RDS for PostgreSQLは、SQL構文との互換性が高く、複数のデータ型とさまざまなコミュニティ拡張機能をサポートしています。このトピックでは、ApsaraDB RDS for PostgreSQLインスタンスでテーブルスキーマを設計し、データベースとテーブルを作成する方法の例を示します。このトピックでは、コース選択システムを使用します。

説明

オンラインコースの選択とスコア統計の収集を容易にするために、学校はコース選択システムを確立することを計画しています。システムのバックエンドはRDSインスタンスを使用します。このシナリオでは、テーブルスキーマを設計し、学生のID、コース、およびスコアに関する情報をテーブルに保存する必要があります。

デザインテーブルのスキーマ

ビジネス要件に基づいて、学生、コース、および登録テーブルのスキーマを設計します。次の図は、エンティティ関係 (ER) ダイアグラムを示しています。

このセクションでは、テーブルスキーマについて説明します。

学生テーブル

テーブルには、学生に関するID情報が格納されます。このテーブルでは、各学生のレコードが格納され、stu_id列は学生をコーススコアと照合するために使用されます。

フィールド	フィールドタイプ	説明
stu_id	シリアル	学生のID。
stu_name	VARCHAR (50)	学生の名前。
stu_gender	VARCHAR (10)	学生の性別。
stu_birth	日付	学生の生年月日。
stu_dept	VARCHAR (50)	学生の学科。

コーステーブル

テーブルには、学生のコース情報が格納されます。このテーブルには、コースごとにレコードが格納されている。各コースには、cou_idフィールドで指定される一意のIDがあります。

フィールド	フィールドタイプ	説明
cou_id	シリアル	コースのID。
cou_name	VARCHAR(100)	コースの名前。
cou_teacher	VARCHAR (50)	コースの先生。
cou_credit	INTEGER	コースのスコア。

登録テーブル

フィールド	フィールドタイプ	説明
en_id	シリアル	コース選択レコードのID。
stu_id	INTEGER	学生のID。これは、学生テーブルのstu_idフィールドと一致するために使用されます。
cou_id	INTEGER	コースのID。これは、コーステーブルのcou_idフィールドと一致するために使用されます。
グレード	VARCHAR(2)	スコア。

RDSインスタンスでのテーブルの作成

RDSインスタンスを作成します。詳細については、「ApsaraDB RDS for PostgreSQL インスタンスの作成」をご参照ください。
データベースとアカウントを作成します。詳細については、「ApsaraDB RDS For PostgreSQLインスタンスでのデータベースとアカウントの作成」をご参照ください。
ホワイトリストを設定します。詳細については、「IPアドレスホワイトリストの設定」をご参照ください。
RDSインスタンスに接続します。詳細については、「ApsaraDB RDS For PostgreSQLインスタンスへの接続」をご参照ください。

設計されたテーブルスキーマに基づいてテーブルを作成します。

学生テーブル

CREATE TABLE students (
  stu_id SERIAL PRIMARY KEY,
  stu_name VARCHAR(50),
  stu_gender VARCHAR(10),
  stu_birth DATE,
  stu_dept VARCHAR(50)
);

コーステーブル

CREATE TABLE courses (
  cou_id SERIAL PRIMARY KEY,
  cou_name VARCHAR(100),
  cou_teacher VARCHAR(50),
  cou_credit INTEGER
);

登録テーブル

CREATE TABLE enrollments (
  en_id SERIAL PRIMARY KEY,
  stu_id INTEGER,
  cou_id INTEGER,
  grade VARCHAR(2),
  FOREIGN KEY (stu_id) REFERENCES students(stu_id),
  FOREIGN KEY (cou_id) REFERENCES courses(cou_id)
);

学生がフロントエンドプログラムからコースを選択し、教師が学生のストアに入り、関連データがバックエンドデータベースに送り返されると仮定します。テストデータをデータベースに挿入します。

-- Insert test data into the students table.
INSERT INTO students (stu_name, stu_gender, stu_birth, stu_dept) VALUES
  ('Zhangsan', 'Female', '2000-01-01', 'Computer Science'),
  ('Lisi', 'Male', '1999-05-10', 'Mathematics'),
  ('Wangwu', 'Male', '2001-08-20', 'Physics'),
  ('Zhaoliu', 'Female', '1998-03-15', 'Chemistry'),
  ('Sunqi', 'Female', '2002-11-30', 'Biology');

-- Insert test data into the courses table.
INSERT INTO courses (cou_name, cou_teacher, cou_credit) VALUES
  ('Database Systems', 'Professor Zhang', 3),
  ('Calculus', 'Professor Wang', 4),
  ('Computer Network', 'Professor Li', 4),
  ('Organic Chemistry', 'Professor Wang', 4),
  ('Biology Basics', 'Professor Zhang', 2);

-- Insert test data into the enrollments table.
INSERT INTO enrollments (stu_id, cou_id, grade) VALUES
  (1, 1,'A'),(1, 3,'B+'),(1, 5,'C'),
  (2, 1,'B+'),(2, 2,'A'),(2, 3,'B-'),
  (3, 2,'A-'),(3, 4,'A'),(3, 5,'B+'),
  (4, 3,'B'),(4, 4,'B-'),
  (5, 4,'C');

例

学期の終わりに、Zhangsanという名前の学生は、コース選択システムの各コースのスコアを照会する必要があります。
```
SELECT courses.cou_name, enrollments.grade
FROM students
JOIN enrollments ON students.stu_id = enrollments.stu_id
JOIN courses ON enrollments.cou_id = courses.cou_id
WHERE students.stu_name = 'Zhangsan';
```
次のコードは、サンプル出力を示しています。
```
     cou_name     | grade
------------------+-------
 Database Systems | A
 Computer Network | B+
 Biology Basics   | C
(3 rows)
```
説明
クエリステートメントでは、JOIN句を使用して、学生、登録、およびコースのテーブルを結合します。 stu_idおよびcou_idフィールドは、登録テーブルを結合するために使用される。 WHERE句は、Zhangsanという名前の学生のレコードをフィルタリングするために使用されます。
Zhang教授という名前の教師は、コース選択システムで教えるすべての生徒のスコアを照会する必要があります。
```
SELECT students.stu_name, courses.cou_name, enrollments.grade
FROM students
JOIN enrollments ON students.stu_id = enrollments.stu_id
JOIN courses ON enrollments.cou_id = courses.cou_id
WHERE courses.cou_teacher = 'Professor Zhang';
```
次のコードは、サンプル出力を示しています。
```
 stu_name |     cou_name     | grade
----------+------------------+-------
 Zhangsan | Database Systems | A
 Zhangsan | Biology Basics   | C
 Lisi     | Database Systems | B+
 Wangwu   | Biology Basics   | B+
(4 rows)
```
説明
クエリステートメントでは、JOIN句を使用して、学生、登録、およびコースのテーブルを結合します。 stu_idおよびcou_idフィールドは、登録テーブルを結合するために使用される。 WHERE句は、Zhang教授という名前の教師の記録をフィルタリングするために使用されます。
学校は優秀な学生を選ぶためにすべての学生のコースクレジットをランク付けする必要があります。トップ3の学生は優秀な学生を称えられます。
```
SELECT students.stu_name, SUM(
CASE
WHEN enrollments.grade = 'C' THEN 0
ELSE courses.cou_credit
END
) AS total_credits
FROM students
JOIN enrollments ON students.stu_id = enrollments.stu_id
JOIN courses ON enrollments.cou_id = courses.cou_id
GROUP BY students.stu_name
ORDER BY total_credits DESC
LIMIT 3;
```
次のコードは、サンプル出力を示しています。
```
 stu_name | total_credits
----------+---------------
 Lisi     |            11
 Wangwu   |            10
 Zhaoliu  |             8
(3 rows)
```
説明
クエリステートメントでは、JOIN句を使用して、学生、登録、およびコースのテーブルを結合します。 stu_idおよびcou_idフィールドは、登録テーブルを結合するために使用される。 GROUP BY句は学生名で学生をグループ化するために使用され、SUM関数は各学生の合計コースクレジットを計算するために使用され、ORDER by句は合計コースクレジットを降順でソートするために使用されます。学生が1つのコースに対してCを取得した場合、学生はコースのコースクレジットを取得できません。 LIMIT 3条項は、コース単位で上位3人の学生を返すために使用されます。