Database Systems Report

DocumentDB

The database name is DocumentDB and it contains four tables: User, Document, Revision, and DocumentCoauthers.
The User table stores information about the users of the document system, such as their name, email, role, and last login. The primary key of this table is the id column, which is a unique identifier generated automatically. The email column is also unique, meaning that no two users can have the same email address. The disabled column is a bit value that indicates whether the user account is active or not. The emailVerified column is a datetime value that shows when the user verified their email address. The role column is a varchar value that indicates the user’s role in the system, such as ‘user’, ‘admin’. The handle column is a varchar value that represents the user’s nickname or alias. The image column is a text value that stores the URL of the user’s profile picture.
The Document table stores information about the documents created by the users, such as their name, creation date, update date, author, and publication status. The primary key of this table is the id column, which is a unique identifier generated automatically. The authorId column is a foreign key that references the id column of the User table, meaning that each document has one author who is a user of the system. The published column is a bit value that indicates whether the document is published or not. The baseId column is a foreign key that references the id column of the Document table, meaning that each document can have a base document that it is derived from. The handle column is a varchar value that represents the document’s title or alias. The head column is a unique identifier that references the id column of the Revision table, meaning that each document has one head revision that is the active version of the document.
The Revision table stores information about the revisions made to the documents, such as their data, creation date, document, and author. The primary key of this table is the id column, which is a unique identifier generated automatically. The data column is a text value that stores the content of the revision. The createdAt column is a datetime value that shows when the revision was created. The documentId column is a foreign key that references the id column of the Document table, meaning that each revision belongs to one document. The authorId column is a foreign key that references the id column of the User table, meaning that each revision has one author who is a user of the system.
The DocumentCoauthers table serves as a join table (also known as an association table or junction table) in the database schema. Its purpose is to establish a many-to-many relationship between the User table and the Document table. The primary key of this table is the combination of the documentId and userEmail columns, meaning that each pair of document and user email is unique. The documentId column is a foreign key that references the id column of the Document table. The userEmail column is a foreign key that references the email column of the User table. The createdAt column is a datetime value that shows when the co-authorship was established.

Database schema

CREATE TABLE "User" (
    "id" uniqueidentifier NOT NULL DEFAULT NEWID(),
    "name" varchar(255) NOT NULL,
    "email" varchar(255) UNIQUE NOT NULL,
    "image" text,
    "createdAt" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "disabled" bit NOT NULL DEFAULT 0,
    "emailVerified" datetime,
    "role" varchar(255) NOT NULL DEFAULT 'user',
    "handle" varchar(255),
    "lastLogin" datetime,
    PRIMARY KEY ("id"),
);

CREATE TABLE "Document" (
    "id" uniqueidentifier NOT NULL DEFAULT NEWID(),
    "name" varchar(255) NOT NULL DEFAULT 'Untitled Document',
    "createdAt" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "authorId" uniqueidentifier NOT NULL,
    "published" bit NOT NULL DEFAULT 0,
    "baseId" uniqueidentifier,
    "handle" varchar(255),
    "head" uniqueidentifier NOT NULL,
    CONSTRAINT "Document_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id"),
    CONSTRAINT "Document_baseId_fkey" FOREIGN KEY ("baseId") REFERENCES "Document"("id"),
    PRIMARY KEY ("id")
);

CREATE TABLE "Revision" (
    "id" uniqueidentifier NOT NULL DEFAULT NEWID(),
    "data" text NOT NULL,
    "createdAt" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "documentId" uniqueidentifier NOT NULL,
    "authorId" uniqueidentifier NOT NULL,
    CONSTRAINT "Revision_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id"),
    CONSTRAINT "Revision_documentId_fkey" FOREIGN KEY ("documentId") REFERENCES "Document"("id"),
    PRIMARY KEY ("id")
);

CREATE TABLE "DocumentCoauthers" (
    "documentId" uniqueidentifier NOT NULL,
    "userEmail" varchar(255) NOT NULL,
    "createdAt" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "DocumentCoauthers_documentId_fkey" FOREIGN KEY ("documentId") REFERENCES "Document"("id"),
    CONSTRAINT "DocumentCoauthers_userEmail_fkey" FOREIGN KEY ("userEmail") REFERENCES "User"("email"),
    PRIMARY KEY ("documentId","userEmail")
);

Seeding the database

INSERT INTO "User" ("id", "name", "email", "image", "createdAt", "updatedAt", "disabled", "emailVerified", "role", "handle", "lastLogin") VALUES
('6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a', 'Ibrahim El-bastawisi', 'ibastawisi@outlook.com', 'https://lh3.googleusercontent.com/a-/AFdZucqRNOevkdXVPpIy5G2G3amfqHE9cEqyJnT134tPAA=s96-c', '2022-07-25 06:00:49.336', '2023-10-06 17:44:05.684', 0, '2022-07-25 06:00:49.336', 'admin', 'ibastawisi', '2023-12-15 17:04:07.842'),
('e2227106-b281-47cd-bff9-68da843e044b', 'Radwan Abdallah', 'radwanabdallah913@gmail.com', 'https://lh3.googleusercontent.com/a/ACg8ocLrcCdPOz5VjjSasH1nxFN1RS_aO4oNmqUyQhHungFZxRU=s96-c', '2023-09-30 07:15:35.825', '2023-09-30 07:18:18.69', 0, '2023-09-30 07:18:18.69', 'user', NULL, '2023-10-29 19:28:01.719'),
('da084f7a-47fd-4963-ab50-9df8fee9234c', 'Anter', 'moanter953@gmail.com', 'https://lh3.googleusercontent.com/a/AEdFTp6AFNg2mh0VQq9qsWk3jJYZg-y79FT_wbErYrdXIQ=s96-c', '2023-02-01 04:21:37.306', '2023-10-02 15:36:43.884', 0, '2023-02-01 04:21:37.306', 'user', NULL, '2023-10-06 18:52:59.781'),
('8b7302c2-585d-457b-bc71-c376c975893c', 'muhammed sergany', 'muhammedsergany@gmail.com', 'https://lh3.googleusercontent.com/a/AEdFTp5KBMjqd-lOMw-g5n9hSv4ktCwZVIDCj9W--rJV=s96-c', '2022-12-09 17:37:37.013', '2022-12-09 17:37:37.013', 0, NULL, 'user', NULL, NULL),
('7528ebee-15f5-4bb5-8415-e38f1279f4de', 'Emad Mohamed', 'emadelbastawisi@gmail.com', 'https://lh3.googleusercontent.com/a/ACg8ocINdrlfTwkS_I24HdCzzP09ZvGlAPbSFurpsGCPyFvO=s96-c', '2023-09-22 15:15:08.982', '2023-10-05 10:50:34.813', 0, '2023-09-22 15:15:08.982', 'user', 'emadbastawisi', '2023-10-17 19:26:23.001');
INSERT INTO "Document" ("id", "name", "createdAt", "updatedAt", "authorId", "published", "baseId", "handle", "head") VALUES
('5e05dfa1-a0fd-4b94-b681-b72df1fd6512', 'test', '2023-09-18 17:12:58.02', '2023-11-26 16:05:58.771', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a', 0, NULL, 'test', '8dffb86e-f907-4d74-8da4-6bfac969b90b'),
('eef16970-a8e6-425d-a4ac-414b18f29dab', 'Timetable', '2023-09-26 18:29:07.385', '2023-10-21 16:57:51.697', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a', 0, NULL, 'timetable', 'bba51cbf-5333-4130-a866-4c99df91b67a'),
('0da2be69-1c72-4313-bd24-e04d12b3af83', 'Modern Control Revision', '2023-11-15 15:01:49.554', '2023-11-15 22:07:11.57', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a', 0, NULL, 'modern-control-revision', 'e646a106-1c35-424c-92ca-cc49e8eb28db'),
('29771ad8-53c4-4fe0-afa6-75a74fc2406c', 'Computer Architecture Report 1', '2023-10-18 12:18:46.487', '2023-10-20 18:46:10.379', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a', 1, NULL, 'computer-architecture-report-1', 'cdbefb13-d1e4-4c29-8125-16146d5e74dc'),
('eeae6834-f68f-47c6-9ab0-cbb6a052a573', 'Modern Control Midterm Revision', '2023-11-20 16:49:41.634', '2023-11-20 17:05:23.665', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a', 0, '0da2be69-1c72-4313-bd24-e04d12b3af83', 'modern-control-midterm-revision', '4dca15ec-c7f3-4897-b7b3-8f1b3f995373')
INSERT INTO "DocumentCoauthers" ("documentId", "userEmail", "createdAt") VALUES
('5e05dfa1-a0fd-4b94-b681-b72df1fd6512', 'emadelbastawisi@gmail.com', '2023-10-01 17:01:36.453'),
('eef16970-a8e6-425d-a4ac-414b18f29dab', 'moanter953@gmail.com', '2023-10-06 18:51:08.107'),
('eef16970-a8e6-425d-a4ac-414b18f29dab', 'radwanabdallah913@gmail.com', '2023-09-30 07:15:35.825');
INSERT INTO "Revision" ("id", "data", "createdAt", "documentId", "authorId") VALUES
('b9d1d52c-7939-485b-85eb-1b8309ff1334', 'This is the first test data', '2023-11-07 18:33:16.778', '5e05dfa1-a0fd-4b94-b681-b72df1fd6512', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a'),
('f4a3ea37-776e-443a-bcbd-b83d8cc2f349', 'This is the second test revision data', '2023-10-01 18:24:15.229', '5e05dfa1-a0fd-4b94-b681-b72df1fd6512', '7528ebee-15f5-4bb5-8415-e38f1279f4de'),
('8dffb86e-f907-4d74-8da4-6bfac969b90b', 'This is the latest test data', '2023-11-26 16:05:58.771', '5e05dfa1-a0fd-4b94-b681-b72df1fd6512', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a'),
('96376d99-f58a-4daf-b983-5ee18a94f91e', 'This is the first existing timetable data', '2023-09-29 16:31:58.415', 'eef16970-a8e6-425d-a4ac-414b18f29dab', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a'),
('f31fae18-1d16-47a6-9715-56c800b3a7e8', 'This is the second timetable data', '2023-09-30 07:25:21.077', 'eef16970-a8e6-425d-a4ac-414b18f29dab', 'e2227106-b281-47cd-bff9-68da843e044b'),
('13904336-36bf-4220-a39a-5611b9d75685', 'This is the third timetable data', '2023-10-02 16:39:13.982', 'eef16970-a8e6-425d-a4ac-414b18f29dab', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a'),
('bba51cbf-5333-4130-a866-4c99df91b67a', 'This is the latest timetable data', '2023-10-21 16:57:51.697', 'eef16970-a8e6-425d-a4ac-414b18f29dab', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a'),
('e646a106-1c35-424c-92ca-cc49e8eb28db', 'This is the latest Modern Control Revision data', '2023-11-15 22:07:11.57', '0da2be69-1c72-4313-bd24-e04d12b3af83', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a'),
('cdbefb13-d1e4-4c29-8125-16146d5e74dc', 'This is the latest Computer Architecture Report 1 data', '2023-10-20 18:46:10.379', '29771ad8-53c4-4fe0-afa6-75a74fc2406c', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a'),
('4dca15ec-c7f3-4897-b7b3-8f1b3f995373', 'This is the latest Modern Control Midterm Revision data', '2023-11-20 17:05:23.665', 'eeae6834-f68f-47c6-9ab0-cbb6a052a573', '6c53ec5c-fd3f-4b35-ab2c-26c78ce6eb4a');

Manipulating the database

To select users ordered by their last login date, you can use the following query:

SELECT * from "User" ORDER BY "lastLogin" DESC;

To select all documents with the word "control" in their name, you can use the following query:

SELECT * from "Document" WHERE "name" LIKE '%control%';

To select all the documents based on other documents, you can use the following query:

SELECT * from "Document" WHERE "baseId" IS NOT NULL;

To update the publication status of a document, you can use the following query:

UPDATE "Document" SET "published" = 1 WHERE "handle" = 'timetable';

To select all the documents published by a user, you can use the following query:

SELECT * FROM "Document"
WHERE "published" = 1 AND "authorId" = (
  SELECT "id" FROM "User" WHERE "handle" = 'ibastawisi'
);

To select the name and email of the author of a document, you can use the following query

SELECT "name", "email" FROM "User"
WHERE "id" = (
  SELECT "authorId" FROM "Document" WHERE "handle" = 'timetable'
);

To select the name and email of the co-authors of a document, you can use the following query:

SELECT "name", "email" FROM "User"
WHERE "email" IN (
  SELECT "userEmail" FROM "DocumentCoauthers"
  WHERE "documentId" = (
  	SELECT "id" FROM "Document" WHERE "handle" = 'timetable'
	) 
);

To select all revisions of published documents, you can use the following query:

SELECT * from "Revision"
WHERE "documentId" IN (
	SELECT "id" FROM "Document" WHERE "published" = 1
);

To delete a user from the User table, you can use the following query:

DELETE FROM "User" WHERE "email" = 'muhammedsergany@gmail.com';