【Coursera】University of Colorado ; Database Management Essentials (5)
【Module 3 追加課題】
Q1. Write a CREATE TABLE statement for the Customer table. Choose data types appropriate for the DBMS used in your course. Note that the CustBal column contains numeric data. The currency symbols are not stored in the database. The CustFirstName and CustLastName columns are required (not null).
Ans.
mysql> create database ex_module3; Query OK, 1 row affected (0.00 sec) mysql> use ex_module3; Database changed mysql> mysql> CREATE TABLE Customer( -> CustNo VARCHAR(8), -> CustFirstName VARCHAR(8) NOT NULL, -> CustLastName VARCHAR(8) NOT NULL, -> CustCity VARCHAR(20), -> CustState VARCHAR(2), -> CustZip VARCHAR(10), -> CustBal DECIMAL(8,2), -> CONSTRAINT PKcustno PRIMARY KEY (CustNo) -> ); Query OK, 0 rows affected (0.06 sec) mysql> SHOW COLUMNS FROM ex_module3.Customer; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | CustNo | varchar(8) | NO | PRI | | | | CustFirstName | varchar(8) | NO | | NULL | | | CustLastName | varchar(8) | NO | | NULL | | | CustCity | varchar(20) | YES | | NULL | | | CustState | varchar(2) | YES | | NULL | | | CustZip | varchar(10) | YES | | NULL | | | CustBal | decimal(8,2) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 7 rows in set (0.01 sec) mysql>
Q2. Write a CREATE TABLE statement for the Employee table. Choose data types appropriate for the DBMS used in your course. The EmpFirstName, EmpLastName, and EmpEMail columns are required (not null).
Ans.
mysql> CREATE TABLE Employee ( -> EmpNo char(8), -> EmpFirstName char(20) NOT NULL, -> EmpLastName char(20) NOT NULL, -> EmpPhone char(20), -> EmpEmail char(30) NOT NULL, -> CONSTRAINT PKempno PRIMARY KEY (EmpNo) -> ); Query OK, 0 rows affected (0.03 sec) mysql> SHOW COLUMNS FROM ex_module3.Employee; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | EmpNo | char(8) | NO | PRI | | | | EmpFirstName | char(20) | NO | | NULL | | | EmpLastName | char(20) | NO | | NULL | | | EmpPhone | char(20) | YES | | NULL | | | EmpEmail | char(30) | NO | | NULL | | +--------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>
Q3. Write a CREATE TABLE statement for the OrderTbl table. Choose data types appropriate for the DBMS used in your course. The OrdDate column is required (not null).
Ans.
mysql> CREATE TABLE OrderTbl ( -> OrdNo char(8), -> OrdDate char(10) NOT NULL, -> CustNo char(8), -> EmpNo char(8), -> CONSTRAINT PKordno PRIMARY KEY (OrdNo) -> ); Query OK, 0 rows affected (0.10 sec) mysql> mysql> SHOW COLUMNS FROM ex_module3.OrderTbl; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | OrdNo | char(8) | NO | PRI | | | | OrdDate | char(10) | NO | | NULL | | | CustNo | char(8) | YES | | NULL | | | EmpNo | char(8) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 4 rows in set (0.03 sec) mysql>
Q4. Identify the foreign keys and 1-M relationships among the Customer, Employee, and OrderTbl tables. For each relationship, identify the parent table and the child table.
Ans.
OrderTblテーブルが子テーブルとなり、EmpNoカラムについてはEmployeeテーブルが親テーブル、CustNoカラムについては、Customerテーブルが親テーブルとなる。
それぞれ、EmployeeテーブルとOrderTblテーブル、CustomerテーブルとOrderTblテーブルが1:Mの関係となる。
Q5. Extend your CREATE TABLE statement from problem (3) with referential integrity constraints.
Ans.
Problem3で作成したテーブルを修正する。
mysql> SHOW COLUMNS FROM ex_module3.OrderTbl; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | OrdNo | char(8) | NO | PRI | | | | OrdDate | char(10) | NO | | NULL | | | CustNo | char(8) | YES | | NULL | | | EmpNo | char(8) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 4 rows in set (0.03 sec) mysql> ALTER TABLE ex_module3.OrderTbl ADD CONSTRAINT FOREIGN KEY (CustNo) REFERENCES Customer (CustNo); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ex_module3.OrderTbl ADD CONSTRAINT FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM ex_module3.OrderTbl; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | OrdNo | char(8) | NO | PRI | | | | OrdDate | char(10) | NO | | NULL | | | CustNo | char(8) | YES | MUL | NULL | | | EmpNo | char(8) | YES | MUL | NULL | | +---------+----------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql>
Q6. From examination of the sample data and your common understanding of order entry businesses, are null values allowed for the foreign keys in the OrderTbl table? Why or why not? Extend the CREATE TABLE statement in problem (5) to enforce the null value restrictions if any.
Ans.
Problem 3でのcreate tableクエリの要件は“The OrdDate column is required (not null).”なので、他カラムについてはnullを許容している。
“Extend the CREATE TABLE statement in problem (5) to enforce the null value restrictions if any.”なので、not nullとする。
mysql> ALTER TABLE ex_module3.OrderTbl MODIFY COLUMN CustNo char(8) NOT NULL; ERROR 1832 (HY000): Cannot change column 'CustNo': used in a foreign key constraint 'OrderTbl_ibfk_1'
外部キー制約を一旦外す必要がある？
外部キー制約の制約名を確認したいが、クエリー発行時に明示してなかった。。
以下によると、どうやら2つの外部キー制約がそれぞれ制約名”OrderTbl_ibfk_1”と”OrderTbl_ibfk_2”として発行されている。
mysql> SHOW CREATE TABLE ex_module3.OrderTbl; +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | OrderTbl | CREATE TABLE `OrderTbl` ( `OrdNo` char(8) NOT NULL DEFAULT '', `OrdDate` char(10) NOT NULL, `CustNo` char(8) DEFAULT NULL, `EmpNo` char(8) DEFAULT NULL, PRIMARY KEY (`OrdNo`), KEY `CustNo` (`CustNo`), KEY `EmpNo` (`EmpNo`), CONSTRAINT `OrderTbl_ibfk_1` FOREIGN KEY (`CustNo`) REFERENCES `Customer` (`CustNo`), CONSTRAINT `OrderTbl_ibfk_2` FOREIGN KEY (`EmpNo`) REFERENCES `Employee` (`EmpNo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
外部キー制約を削除する
mysql> mysql> ALTER TABLE ex_module3.OrderTbl DROP FOREIGN KEY `OrderTbl_ibfk_1`; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ex_module3.OrderTbl DROP FOREIGN KEY `OrderTbl_ibfk_2`; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
以下の通りテーブルのカラムを確認。KeyがMULとなっている。外部キー制約がまだ残っている？
mysql> SHOW COLUMNS FROM ex_module3.OrderTbl; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | OrdNo | char(8) | NO | PRI | | | | OrdDate | char(10) | NO | | NULL | | | CustNo | char(8) | YES | MUL | NULL | | | EmpNo | char(8) | YES | MUL | NULL | | +---------+----------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql>
テーブルの発行クエリを再度確認する。CustNoカラムとEmpNoカラムに対する外部キー制約の記述が見当たらなくなっている。
mysql> SHOW CREATE TABLE ex_module3.OrderTbl; +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | OrderTbl | CREATE TABLE `OrderTbl` ( `OrdNo` char(8) NOT NULL DEFAULT '', `OrdDate` char(10) NOT NULL, `CustNo` char(8) DEFAULT NULL, `EmpNo` char(8) DEFAULT NULL, PRIMARY KEY (`OrdNo`), KEY `CustNo` (`CustNo`), KEY `EmpNo` (`EmpNo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
CustNoカラムとEmpNoカラムに対してNOT NULL制約を適用する。
mysql> ALTER TABLE ex_module3.OrderTbl MODIFY COLUMN CustNo char(8) NOT NULL; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ex_module3.OrderTbl MODIFY COLUMN EmpNo char(8) NOT NULL; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
無事NOT NULL制約は適用された。
mysql> SHOW COLUMNS FROM ex_module3.OrderTbl; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | OrdNo | char(8) | NO | PRI | | | | OrdDate | char(10) | NO | | NULL | | | CustNo | char(8) | NO | MUL | NULL | | | EmpNo | char(8) | NO | MUL | NULL | | +---------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql>
再度外部キー制約を適用する。
mysql> ALTER TABLE ex_module3.OrderTbl ADD CONSTRAINT FKcustno (CustNo) REFERENCES Customer(CustNo); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CustNo) REFERENCES Customer(CustNo)' at line 1 mysql> ALTER TABLE ex_module3.OrderTbl ADD CONSTRAINT FKcustno FOREIGN KEY (CustNo) REFERENCES Customer(CustNo); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ex_module3.OrderTbl ADD CONSTRAINT FKempno FOREIGN KEY (EmpNo) REFERENCES Employee(EmpNo); Query OK, 0 rows affected (3.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> SHOW COLUMNS FROM ex_module3.OrderTbl; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | OrdNo | char(8) | NO | PRI | | | | OrdDate | char(10) | NO | | NULL | | | CustNo | char(8) | NO | MUL | NULL | | | EmpNo | char(8) | NO | MUL | NULL | | +---------+----------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> mysql> SHOW CREATE TABLE ex_module3.OrderTbl; +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | OrderTbl | CREATE TABLE `OrderTbl` ( `OrdNo` char(8) NOT NULL DEFAULT '', `OrdDate` char(10) NOT NULL, `CustNo` char(8) NOT NULL, `EmpNo` char(8) NOT NULL, PRIMARY KEY (`OrdNo`), KEY `FKcustno` (`CustNo`), KEY `FKempno` (`EmpNo`), CONSTRAINT `FKcustno` FOREIGN KEY (`CustNo`) REFERENCES `Customer` (`CustNo`), CONSTRAINT `FKempno` FOREIGN KEY (`EmpNo`) REFERENCES `Employee` (`EmpNo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
Q7. Extend your CREATE TABLE statement for the Employee table (problem 2) with a unique constraint for EmpEMail. Use a named constraint clause for the unique constraint.
Ans.
Problem 2.で作成したテーブルのEmpEmailカラムをUNIQUEとなるようconstraintを適用する。
(カラム名に誤りがあった。EmpEMailをEmpEmailとして作成してしまった。ALTER TABLEのクエリーで一度に正解まで持っていく方が早いが、ここは分けて実行する。まずはカラム名を修正する。)
mysql> SHOW COLUMNS FROM ex_module3.Employee; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | EmpNo | char(8) | NO | PRI | | | | EmpFirstName | char(20) | NO | | NULL | | | EmpLastName | char(20) | NO | | NULL | | | EmpPhone | char(20) | YES | | NULL | | | EmpEmail | char(30) | NO | | NULL | | +--------------+----------+------+-----+---------+-------+ 5 rows in set (0.01 sec) mysql> ALTER TABLE ex_module3.Employee CHANGE COLUMN EmpEmail EmpEMail char(30) NOT NULL; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM ex_module3.Employee; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | EmpNo | char(8) | NO | PRI | | | | EmpFirstName | char(20) | NO | | NULL | | | EmpLastName | char(20) | NO | | NULL | | | EmpPhone | char(20) | YES | | NULL | | | EmpEMail | char(30) | NO | | NULL | | +--------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>
EmpEmailカラムに対してUNIQUE制約を追加する。
mysql> ALTER TABLE ex_module3.Employee ADD UNIQUE (EmpEMail); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM ex_module3.Employee; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | EmpNo | char(8) | NO | PRI | | | | EmpFirstName | char(20) | NO | | NULL | | | EmpLastName | char(20) | NO | | NULL | | | EmpPhone | char(20) | YES | | NULL | | | EmpEMail | char(30) | NO | UNI | NULL | | +--------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>
