Example

The actual commands are shown in bold. All the commands should be entered while in mysql shell. To open mysql shell, you can use the command mysql -u root -p.

create database xyz;
use xyz;

CREATE TABLE Course (
  CrsCode varchar(7),
  DeptId varchar(3),
  CrsName varchar(30),
  Descr varchar(100),
  PRIMARY KEY (CrsCode),
  UNIQUE (DeptId, CrsName)
);

desc Course;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| CrsCode | varchar(7)  | NO   | PRI | NULL    |       |

| DeptId  | varchar(3)  | YES  | MUL | NULL    |       |

| CrsName | varchar(30) | YES  |     | NULL    |       |

| Descr   | varchar(100)| YES  |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+


INSERT INTO COURSE VALUES('COSC472', 'CS', 'BIG DATA I', 'Introduction to Big Data');

INSERT INTO COURSE VALUES('COSC341', 'CS', 'Programming Languages', 'Formal definition of programming languages');



SELECT * FROM Course;

+---------+--------+-----------------------+--------------------------------------------+

| CrsCode | DeptId | CrsName               | Descr                                      |

+---------+--------+-----------------------+--------------------------------------------+

| COSC341 | CS     | Programming Languages | Formal definition of programming languages |

| COSC472 | CS     | BIG DATA I            | Introduction to Big Data                   |

+---------+--------+-----------------------+--------------------------------------------+



CREATE TABLE Professor (

  Id integer PRIMARY KEY,

  Name varchar(25),

  DeptId varchar(3)

);


DESC Professor;

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| Id     | int(11)     | NO   | PRI | NULL    |       |

| Name   | varchar(25) | YES  |     | NULL    |       |

| DeptId | varchar(3)  | YES  |     | NULL    |       |

+--------+-------------+------+-----+---------+-------+


INSERT INTO Professor VALUES (1, 'ANDREY KASHLEV', 'CS');


CREATE TABLE Teaching (
  ProfId integer,
  CrsCode varchar(7),
  Semester varchar(6),
  PRIMARY KEY (CrsCode, Semester),
  FOREIGN KEY (CrsCode) REFERENCES Course (CrsCode),
  FOREIGN KEY (ProfId) REFERENCES Professor (Id)
);

desc Teaching;

+----------+------------+------+-----+---------+-------+

| Field    | Type       | Null | Key | Default | Extra |

+----------+------------+------+-----+---------+-------+

| ProfId   | int(11)    | YES  | MUL | NULL    |       |

| CrsCode  | varchar(7) | NO   | PRI | NULL    |       |

| Semester | varchar(6) | NO   | PRI | NULL    |       |

+----------+------------+------+-----+---------+-------+


INSERT INTO Teaching VALUES(1, 'COSC472', 'W2017');



SELECT * FROM Teaching;

+--------+---------+----------+

| ProfId | CrsCode | Semester |

+--------+---------+----------+

|      1 | COSC472 | W2017    |

+--------+---------+----------+