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 |

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