INF 4401 – Introduction To Databases

CI5 : RDBMS

Learn how to create a database in an RDBMS.

Creating a database from scratch (∼1h30 – medium)

In this exercice, we will practice how to setup a database in an RDBMS system. Start by downloading this file.

We will run SQL code online. Please go to this website. There, click on Fichier and Ouvrir... and select the file downloaded above (rdbms.ipynb). You should see the content of the page change.

What you have here is called a notebook. It is decomposed into cells that you can execute independently from each others. To run a cell (that must contain SQL), select the cell and and click on Executer at the top.
Solution

The first part of the file contains the examples from the slides. You can run them to check what they do.

In this exercice, we will reuse the E/R diagram from the previous lab.
Solution

We recall that the database schema associated with this diagram is:

Teacher(teacherID: String, name: String, salary: Float)
Student(studentID: String, name: String, birthdate: Date)
Department(departmentID: String, name: String, budget: Integer)
Course(courseID: String, name: String, credits: Integer)
Session(sessionID: String, semester: Integer, year: Integer)
manages(departmentID: String, managerID: String)
belongsTo(teacherID: String, departmentID: String)
advisor(studentID: String, teacherID: String)
teaches(teacherID: String, sessionID: String)
attend(studentID: String, sessionID: String, grade: Integer)
instance(sessionID: String, courseID: String)
prerequisite(previousCourseID: String, nextCourseID: String, minimalGrade: Integer)

WARNING: You might lose your work if you close your browser. You need to save it by clicking on Fichier, Enregistrer Sous...

Create a database for this schema in SQL. Do not forget to specify to primary keys and foreign keys.
Note 1: If you make a mistake, you might have to delete a table to recreate it.
Note 2: You can see the tables created by executing .tables in a cell or .schema.

CREATE TABLE Teacher(
    teacherID TEXT,
    name TEXT,
    salary FLOAT,
    PRIMARY KEY (teacherID)
);
CREATE TABLE Student(
    studentID TEXT,
    name TEXT,
    birthdate DATE,
    PRIMARY KEY (studentID)
);
CREATE TABLE Department(
    departmentID TEXT,
    name TEXT,
    budget INT,
    PRIMARY KEY (departmentID)
);
CREATE TABLE Course(
    courseID TEXT,
    name TEXT,
    credits INT,
    PRIMARY KEY (courseID)
);
CREATE TABLE Session(
    sessionID TEXT,
    semester INT,
    year INT,
    PRIMARY KEY (sessionID)
);
CREATE TABLE manages(
    departmentID TEXT,
    managerID TEXT,
    PRIMARY KEY (departmentID),
    FOREIGN KEY (departmentID) REFERENCES Department(departmentID),
    FOREIGN KEY (managerID) REFERENCES Teacher(teacherID)
);
CREATE TABLE belongsTo(
    teacherID TEXT,
    departmentID TEXT,
    PRIMARY KEY (teacherID),
    FOREIGN KEY (departmentID) REFERENCES Department(departmentID),
    FOREIGN KEY (teacherID) REFERENCES Teacher(teacherID)
);
CREATE TABLE advisor(
    studentID TEXT,
    teacherID TEXT,
    PRIMARY KEY (studentID),
    FOREIGN KEY (studentID) REFERENCES Student(studentID),
    FOREIGN KEY (teacherID) REFERENCES Teacher(teacherID)
);
CREATE TABLE teaches(
    teacherID TEXT,
    sessionID TEXT,
    PRIMARY KEY (teacherID, sessionID),
    FOREIGN KEY (sessionID) REFERENCES Session(sessionID),
    FOREIGN KEY (teacherID) REFERENCES Teacher(teacherID)
);
CREATE TABLE attend(
    studentID TEXT,
    sessionID TEXT,
    grade INTEGER,
    PRIMARY KEY (studentID, sessionID),
    FOREIGN KEY (sessionID) REFERENCES Session(sessionID),
    FOREIGN KEY (studentID) REFERENCES Student(studentID)
);
CREATE TABLE instance(
    sessionID TEXT,
    courseID TEXT,
    PRIMARY KEY (sessionID),
    FOREIGN KEY (sessionID) REFERENCES Session(sessionID),
    FOREIGN KEY (courseID) REFERENCES Course(courseID)
);
CREATE TABLE prerequisite(
    previousCourseID TEXT,
    nextCourseID TEXT,
    minimalGrade INT,
    PRIMARY KEY (previousCourseID, nextCourseID),
    FOREIGN KEY (previousCourseID) REFERENCES Course(courseID),
    FOREIGN KEY (nextCourseID) REFERENCES Course(courseID)
);

Create three students with the following descriptions:

Student 1: ID=S1, name="Victor Hugo", birthdate="1802-02-26"
Student 2: ID=S2, name="Alphonse Daudet", birthdate="1840-05-13"
Student 3: ID=S3, name="Guy de Maupassant", birthdate="1850-08-05"

INSERT INTO Student
VALUES ("S1", "Victor Hugo", "1802-02-26"),
       ("S2", "Alphonse Daudet", "1840-05-13"),
       ("S3", "Guy de Maupassant", "1850-08-05")

Create one student with the following descriptions:

Student 1: ID=S1, name="Jean Giono", birthdate="1895-03-30"

What is the problem?

INSERT INTO Student
VALUES ("S1", "Jean Giono", "1895-03-30")

The primary key S1 is already used! It must identify uniquely a row.

Check that the table Student contains the correct value by printing it totally (in SQL).

SELECT * FROM Student;

Create these three elements in the table attend:

studentID: "S1", sessionID: "Se1", grade: 10
studentID: "S1", sessionID: "Se2", grade: 15
StudentID: "S2", sessionsID: "Se1", grade: 14

What is the problem? Correct it.

INSERT INTO attend
VALUES ("S1", "Se1", 10),
       ("S1", "Se2", 15),
       ("S2", "Se1", 14)

As sessionID is a foreign key, it needs to exists in the table Session. We could create Se1 and Se2 with:

INSERT INTO Session
VALUES ("Se1", 1, 2022),
       ("Se2", 2, 2022)

Increase all the grades for Se1 by one point.

UPDATE attend
SET grade = grade + 1
WHERE sessionID = "Se1"

In the end, S3 leaves the school. Remove him from the table Students.

DELETE FROM Student
WHERE studentID = "S3"

Can you also delete S2? Why?

You cannot delete S2 because it is used as a foreign key in attend.

Some teachers were very mean and wanted to give a negative grade to the students. How can you prevent it? You will have to delete a table if you want to modify it.

DROP TABLE attend;
CREATE TABLE attend(
    studentID TEXT,
    sessionID TEXT,
    grade INTEGER,
    PRIMARY KEY (studentID, sessionID),
    FOREIGN KEY (sessionID) REFERENCES Session(sessionID),
    FOREIGN KEY (studentID) REFERENCES Student(studentID),
    CHECK (grade >= 0)
);

Can you give a negative grade now?

If everything is ok, no.

INSERT INTO attend
VALUES ("S1", "Se1", 10),
       ("S1", "Se2", 15),
       ("S2", "Se1", 14),
       ("S2", "Se2", -1)

Create a transaction that does the following:

Create a new course with ID INF4401 called Introduction To Databases and worth 4 credits.
Create a new session for this course on semester 2 of 2023.
Make it an instance of INF4401.

BEGIN TRANSACTION;
INSERT INTO Course
VALUES ("INF4401", "Introduction To Databases", 4);
INSERT INTO Session
VALUES ("Se3", 2, 2023);
INSERT INTO instance
VALUES ("Se3", "INF4401");
COMMIT;

Create a transaction that does the following:

Give the grade 9 to the student S1 for the session created in the previous question.
Remove 10 points to this student to all his exams.

What happens? Print all the table attend. Then, undo all the changes of the transaction. Print the table attend again. What do you observe?

BEGIN TRANSACTION;
INSERT INTO attend
VALUES ("S1", "Se3", 9);
UPDATE attend
SET grade = grade - 10
WHERE studentID = "S1";
ROLLBACK;