Data definition language & constraints

Data Definition Language

Peer instruction

What result would the following query obtain?

image-20240923104841433

A)

B)

C)

What result would the following query obtain?

image-20240923105419498

A)

image-20240923105438226

B)

image-20240923105654619

C)

Error

What result would the following query obtain?

image-20250929094421338

A)

B)

C)

Error

Example: Two tables with primary keys and foreign key constraints

DROP TABLE IF EXISTS departments;

CREATE TABLE departments (	
	department_number INTEGER, 
	department_name VARCHAR(30), 
	location VARCHAR(30), 
	PRIMARY KEY (department_number)
);

DROP TABLE IF EXISTS employees;

CREATE TABLE employees(	
	id INTEGER, 
	employee_name VARCHAR(30), 
	job VARCHAR(30), 
	manager INTEGER, 
	hiredate DATE, 
	salary INTEGER, 
	commission INTEGER, 
	department_number INTEGER, 
	PRIMARY KEY (id),
	FOREIGN KEY (department_number) REFERENCES departments(department_number)
);

Insert into departments (department_number,department_name,location) values (10,'ACCOUNTING','NEW YORK');
Insert into departments (department_number,department_name,location) values (20,'RESEARCH','DALLAS');

insert into employees (id,employee_name,job,manager,hiredate,salary,commission,department_number) values (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into employees (id,employee_name,job,manager,hiredate,salary,commission,department_number) values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into employees (id,employee_name,job,manager,hiredate,salary,commission,department_number) values (7521,'WARD','SALESMAN',7698,'1981-02-22', 1250,500,30);
insert into employees (id,employee_name,job,manager,hiredate,salary,commission,department_number) values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);

Exercise 1

Reference: https://www.mysqltutorial.org/mysql-basics/mysql-check-constraint/

Implement the following constraints in the departments and employees DDL script

• A department number should auto increment from 1 to n.

• A department name and location cannot be null.

• An employee must be hired after 1980.

• An employee cannot have a negative salary, but they can receive 0 (In the case of an intern).

• The default value for an employees manager is 7839.

• If a department number in the departments table is changed, it is also changed for the relevant employees in the employee table.

Create a new table projects:

• Each project has an auto-incrementing project id, a project name (not null), a budget (must be greater than 1000), and a department number.

• Ensure that if a department is deleted, all its projects are also deleted automatically.

Try to insert two rows for each of the constraints / rules. One valid and one invalid.

Verify that all your constraints work as intended.

Exercise 2

In this exercise you will create a script such as this (or use Workbenchs tools):

DROP TABLE if exists employees;
DROP TABLE if exists departments;

CREATE TABLE departments (	
	department_number INTEGER, 
	department_name VARCHAR(30), 
	location VARCHAR(30), 
	PRIMARY KEY (department_number)
); 

Insert into departments (department_number,department_name,location) values (10,'ACCOUNTING','NEW YORK');
Insert into departments (department_number,department_name,location) values (20,'RESEARCH','DALLAS');

The script has to drop all tables if they exists, create new tables and insert test data.

Requirements

Create a table named "students" with the following columns:

• student_id (INT, primary key)

• first_name (VARCHAR, 50)

• last_name (VARCHAR, 50)

• birthdate (VARCHAR,10)

• enrollment_date (DATETIME)

• status (VARCHAR, 15)

Modify the "students" table by adding a new column named email of type VARCHAR(100).

Modify a Column Change such that the data type of the birthdate column in the "students" table to DATE.

Create a new table named "courses" with the following columns:

• course_id (INT, primary key)

• course_name (VARCHAR, 100)

• instructor_id (INT, foreign key referencing an "instructors" table)

  • An instructor has a name, email & auto incrementing id

Add a default value of 'Active' for the status column in the "students" table.

Create a table named "books" with the following columns:

• book_id (INT, primary key, auto incrementing)

• title (VARCHAR, 100)

• author (VARCHAR, 100)

• isbn (VARCHAR, 13, unique)

Populate the tables with at least 5 entities for each table.

(Advanced optional)

• Modify the database such that 1 book is used on each course.