【Coursera】University of Colorado ; Database Management Essentials (4)

【module3課題】

Q1. Write a CREATE TABLE statement for the Customer table. Choose data types appropriate for the DBMS used in your course. All columns are required (not null).
Ans.

create table Customer(
custno char(10) PRIMARY KEY,
custname char(30) NOT NULL,
address char(20) NOT NULL,
Internal char(3) NOT NULL,
contact char(30) NOT NULL,
phone int(20) NOT NULL,
city char(20) NOT NULL,
state char(20) NOT NULL,
zip int(10) NOT NULL,
CONSTRAINT Internal_check CHECK( Internal  IN(‘Yes’,’No’)),
);


mysql> create table Customer(
    -> custno char(10) PRIMARY KEY,
    -> custname char(30) NOT NULL,
    -> address char(20) NOT NULL,
    -> Internal char(3) NOT NULL,
    -> contact char(30) NOT NULL,
    -> phone int(20) NOT NULL,
    -> city char(20) NOT NULL,
    -> state char(20) NOT NULL,
    -> zip int(10) NOT NULL,
    -> CONSTRAINT Internal_check CHECK( Internal IN('Yes','No'))
    -> );

mysql> show columns from module3.Customer;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| custno   | char(10) | NO   | PRI | NULL    |       |
| custname | char(30) | NO   |     | NULL    |       |
| address  | char(20) | NO   |     | NULL    |       |
| Internal | char(3)  | NO   |     | NULL    |       |
| contact  | char(30) | NO   |     | NULL    |       |
| phone    | int(20)  | NO   |     | NULL    |       |
| city     | char(20) | NO   |     | NULL    |       |
| state    | char(20) | NO   |     | NULL    |       |
| zip      | int(10)  | NO   |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql>

Q2. Write a CREATE TABLE statement for the Facility table. Choose data types appropriate for the DBMS used in your course. All columns are required (not null).
Ans.

create table Facility(
facno char(10) PRIMARY KEY,
facname char(30) NOT NULL
);


mysql> create table Facility(
    -> facno char(10) PRIMARY KEY,
    -> facname char(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

mysql> show columns from module3.Facility;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| facno   | char(10) | NO   | PRI | NULL    |       |
| facname | char(30) | NO   |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

Q3. Write a CREATE TABLE statement for the Location table. Choose data types appropriate for the DBMS used in your course. LocName column is required (not null).
Ans.

create table Location(
locno char(10) PRIMARY KEY,
facno char(10) NOT NULL,
locname char(20) NOT NULL,
FOREIGN KEY (facno) REFERENCES Facility(facno)
);

mysql> create table Location(
    -> locno char(10) PRIMARY KEY,
    -> facno char(10) NOT NULL,
    -> locname char(20) NOT NULL,
    -> FOREIGN KEY (facno) REFERENCES Facility(facno)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

mysql> show columns from module3.Location;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| locno   | char(10) | NO   | PRI | NULL    |       |
| facno   | char(10) | NO   | MUL | NULL    |       |
| locname | char(20) | NO   |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Q4. Identify the foreign key(s) and 1-M relationship(s) among the Customer, Facility, and Location tables.
For each relationship, identify the parent table and the child table.
Ans.
テーブルFacilityが親テーブルとなり、そのカラムfacnoは、子テーブルのLocationのカラムfacnoの外部キーとなっている。
親テーブルFacilityのカラムfacnoと子テーブルLocationのカラムfacnoは、1:Mの関係となっている。

Q5. Extend your CREATE TABLE statement from problem (3) with referential integrity constraints.
Ans.
Locationテーブル作成時にすでにconstraintとしてFOREIGN KEY (facno) REFERENCES Facility (facno)を明示してクエリーを実行したため、対応不要。

Q6. From examination of the sample data and your common understanding of scheduling and operation of events, are null values allowed for the foreign key in the Location table? Why or why not?
Extend the CREATE TABLE statement in problem (5) to enforce the null value restrictions if any.

Ans.
Not now, but it should be allowed.
Because it is not necessary for facno on table Location to be related primary key on the same table locno.
It is possible to be null on column facno on table Location.

The way I alter table configure is as below.

mysql> ALTER TABLE module3.Location MODIFY COLUMN facno char(10);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from module3.Location;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| locno   | char(10) | NO   | PRI | NULL    |       |
| facno   | char(10) | YES  | MUL | NULL    |       |
| locname | char(20) | NO   |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

以上でカラムfacnoについてnullを許容完了。

Q7. Extend your CREATE TABLE statement for the Facility table (problem 2) with a unique constraint for FacName.
Use an external named constraint clause for the unique constraint.

Ans.
problem 2(Q2.)では以下のクエリを発行。

create table Facility(
facno char(10) PRIMARY KEY,
facname char(30) NOT NULL
);

カラムの状態は以下。

mysql> show columns from Facility;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| facno   | char(10) | NO   | PRI | NULL    |       |
| facname | char(30) | NO   |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

テーブルFacilityのカラムfacnameを変更するには以下のクエリを発行。

mysql> ALTER TABLE Facility ADD UNIQUE(facname);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

UNIQUE constraintを設定するクエリを発行。

mysql> show columns from Facility;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| facno   | char(10) | NO   | PRI | NULL    |       |
| facname | char(30) | NO   | UNI | NULL    |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>