J. Correia Lopes

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:medialib:a6

A6: Integrity constraints. Indexes, triggers, user functions and database populated with data

This artefact contains the physical schema of the database, the identification and characterisation of the indexes, the support of data integrity rules with triggers and the definition of the database user-defined functions. This artefact also contains the database's workload as well as the complete database creation script, including all SQL necessary to define all integrity constraints, indexes and triggers.

1. Database workload

Understanding the nature of the workload for the application, and the performance goals, is essential to developing a good database design. The workload includes:

  • the most important queries (SELECT) and how often they arise
  • the most important updates (UPDATE, DELETE) and how often they arise
  • the desired performance for these queries and updates
  • an estimate of the number of tuples for each relation

1.1. Estimate of tuples

Relation reference Relation name Order of magnitude Estimated growth
R01 user tens of thousands dozens per day
R02 author thousands units per day
R03 collection hundreds units per day
R04 work thousands units per day
R05 author_work thousands units per day
R06 nonbook hundreds units per day
R07 publisher hundreds units per day
R08 book thousands units per day
R09 location tens units per day
R10 item tens of thousands dozens per day
R11 loan hundreds of thousands hundreds per day
R12 review tens of thousands dozens per day
R13 wish_list tens of thousands dozens per day

1.2. Most frequent queries

Query reference SELECT01
Query description User's profile
Query frequency hundreds per day
 SELECT name, email, obs, img 
  FROM "user" 
  WHERE "user".email = $email; 
Query reference SELECT02
Query description My wish list
Query frequency dozens per day
 SELECT WORK.id, WORK.title, WORK.obs, WORK.img, WORK.year, "user".name
  FROM WORK, "user", wish_list
  WHERE wish_list.id_user = $userId AND wish_list.id_work = WORK.id AND WORK.id_user = "user".id; 
Query reference SELECT03
Query description My items
Query frequency hundreds per day
 SELECT id, title, obs, img, YEAR
  FROM WORK
  WHERE id_user = $userId; 
Query reference SELECT04
Query description My loans
Query frequency dozens per day
 SELECT WORK.id, WORK.title, WORK.obs, WORK.img, WORK.year, "user".name
  FROM WORK, "user", loan, item
  WHERE loan.id_user = "user".id AND loan.id_work = WORK.id AND loan."end" > now() AND loan."start" > $start AND loan."end" < $end AND loan.id_user = $userId; 
Query reference SELECT05
Query description Search by type
Query frequency dozens per day
 -- IF TYPE = BOOK
SELECT WORK.id, WORK.title, WORK.obs, WORK.img, WORK.year, "user".name
  FROM WORK, "user", book
  WHERE WORK.id_user = "user".id AND WORK.id = book.id_work;
 
-- ELSE
SELECT WORK.id, WORK.title, WORK.obs, WORK.img, WORK.year, "user".name
  FROM WORK, "user", nonbook
  WHERE WORK.id_user = "user".id AND WORK.id = nonbook.id_work AND nonbook.type = $workType; 
Query reference SELECT06
Query description Search
Query frequency hundreds per day
SELECT id, title, obs, img, YEAR, ts_rank_cd(textsearch, query) AS rank
  FROM WORK, to_tsquery($search) AS query, to_tsvector(title || ' ' || obs) AS textsearch
  WHERE query @@ textsearch\\ ORDER BY rank DESC;
Query reference SELECT07
Query description Item's page
Query frequency hundreds per day
SELECT WORK.id, WORK.title, WORK.obs, WORK.img, WORK.year, "user".name
  FROM WORK, "user"
  WHERE WORK.id_user = "user".id AND WORK.id = $id;
 
SELECT "user".name, comment.description, comment."date"
  FROM "user", comment
  WHERE comment.id_user = "user".id AND comment.id_work = $id;
 
SELECT rate.rate
  FROM "user", rate
  WHERE rate.id_user = $userId AND rate.id_work = $id;
 
SELECT AVG(rate)
  FROM rate
  WHERE rate.id_work = $id; 

1.3. Most frequent modifications

Query reference UPDATE01
Query description Update user information
Query frequency hundreds per month
 UPDATE "user"
  SET email = $email, name = $email
  WHERE id = $id; 
Query reference UPDATE02
Query description Update work information
Query frequency hundreds per month
 UPDATE WORK
  SET title = $title, obs = $obs, YEAR = $year
  WHERE id = $id; 
Query reference UPDATE03
Query description Update loan
Query frequency hundreds per month
 UPDATE loan
  SET END = $endDate
  WHERE id = $id; 
Query reference INSERT01
Query description New user registered
Query frequency hundreds per month
 INSERT INTO "user" (email, name, obs, password, img, is_admin) 
  VALUES ($email, $name, $obs, $password, $img, $is_admin); 
Query reference INSERT02
Query description Create new work
Query frequency hundreds per month
 INSERT INTO WORK (title, obs, img, YEAR, id_user, id_collection) 
  VALUES ($title, $obs, $img, $year, $id_user, $id_collection); 
Query reference INSERT03
Query description Create new review
Query frequency hundreds per month
 INSERT INTO review (id_user, id_work, "date", comment, rating) 
  VALUES ($id_user, $id_work, $commentDate, $comment, $rating); 
Query reference INSERT04
Query description Create new loan
Query frequency hundreds per month
 INSERT INTO loan (id_item, id_user, START, "end") 
  VALUES ($id_item, $id_user, $start, $endDate); 
Query reference INSERT05
Query description Create new location
Query frequency hundreds per month
 INSERT INTO location (name, address, gps) 
  VALUES ($name, $address, $gps); 
Query reference INSERT06
Query description Create a new author
Query frequency hundreds per month
 INSERT INTO author (name, img) 
  VALUES ($name, $img); 
Query reference INSERT07
Query description Create a new collection
Query frequency hundreds per month
 INSERT INTO collection (name) 
  VALUES ($name); 
Query reference DELETE01
Query description Delete a review
Query frequency hundreds per month
 DELETE FROM review 
  WHERE id = $id; 
Query reference DELETE02
Query description Delete a work
Query frequency hundreds per month
 DELETE FROM WORK
  WHERE id = $id; 

2. Proposed Indexes

Indexes are used to enhance database performance by allowing the database server to find and retrieve specific rows much faster. An index defined on a column that is part of a join condition can also significantly speed up queries with joins. Moreover, indexes can also benefit UPDATE and DELETE commands with search conditions.

After an index is created, the system has to keep it synchronised with the table, which adds overhead to data manipulation operations. As indexes add overhead to the database system as a whole, they are used sensibly. The indexes proposed in the next section took in good consideration the impact on updates in the workload. The indexes benefits at least one query and none have negative impact in the updates or inserts

2.1. Performance indexes

Index reference IDX01
Related queries SELECT01
Index relation user
Index attribute email
Index type Hash
Cardinality High
Clustering No
Justification Query SELECT01 has to be fast as it is executed many times; doesn't need range query support; cardinality is high because email is an unique key; it's not a good candidate for clustering.
 CREATE INDEX email_user ON "user" USING hash (email); 
Index reference IDX02
Related queries SELECT03
Index relation work
Index attribute id_user
Index type Hash
Cardinality Medium
Clustering Yes
Justification Table is very large; query SELECT03, used to search the items of an user, has to be fast because it's executed many times; doesn't need range query support; cardinality is medium so it's a good candidate for clustering.
 CREATE INDEX user_work ON WORK USING hash(id_user); 
Index reference IDX03
Related queries SELECT04
Index relation loan
Index attribute id_user
Index type Hash
Cardinality Medium
Clustering Yes
Justification Query SELECT04, used to search the loans of an user, has to be fast because it's executed several times; doesn't need range query support; cardinality is medium so it's a good candidate for clustering. From the three candidate indexes for clustering on table loan, this has the most adequate cardinality (about 10 loans per user).
 CREATE INDEX user_loan ON loan USING hash(id_user); 
Index reference IDX04
Related queries SELECT04
Index relation loan
Index attribute start
Index type B-tree
Cardinality Medium
Clustering No
Justification To allow searching items by start date range faster; It's clustered to allow for quick range queries; cardinality is medium.
 CREATE INDEX start_loan ON loan USING btree (START); 
Index reference IDX05
Related queries SELECT04
Index relation loan
Index attribute end
Index type B-tree
Cardinality Medium
Clustering No
Justification To allow searching items by end date range faster. It's clustered to allow for quick range queries; cardinality is medium.
 CREATE INDEX end_loan ON loan USING btree ("end"); 

2.2. Full-text Search indexes

Index reference IDX06
Related queries SELECT06
Index relation work
Index attribute title
Index type GiST
Clustering No
Justification To improve the performance of full text searches while searching for works and their titles; GiST because it's better for dynamic data.
 CREATE INDEX search_idx ON WORK USING GIST (to_tsvector('english', title || ' ' || obs))

3. Triggers

Trigger reference TRIGGER01
Trigger description An item can only be loaned to one user in every moment.
CREATE FUNCTION loan_item() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM loan WHERE NEW.id_item = id_user AND "end" > NEW.start) THEN
        RAISE EXCEPTION 'An item can only be loaned to one user in every moment.';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER loan_item
    BEFORE INSERT OR UPDATE ON loan
    FOR EACH ROW
    EXECUTE PROCEDURE loan_item(); 
Trigger reference TRIGGER02
Trigger description An user's work cannot be in his wish list.
CREATE FUNCTION work_wish_list() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM WORK WHERE NEW.id_work = id AND NEW.id_user = id_user) THEN
        RAISE EXCEPTION 'An users item cannot be in his wish list.';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER work_wish_list
    BEFORE INSERT OR UPDATE ON wish_list
    FOR EACH ROW
    EXECUTE PROCEDURE work_wish_list(); 
Trigger reference TRIGGER03
Trigger description An item cannot be loaned to it's owner
CREATE FUNCTION item_owner() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM item 
        INNER JOIN "work" ON "work".id = "item".id_work
        WHERE NEW.id_item = item.id AND NEW.id_user = "work".id_user ) THEN
            RAISE EXCEPTION 'An item cannot be loaned to its owner.';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER item_owner
    BEFORE INSERT OR UPDATE ON loan
    FOR EACH ROW
    EXECUTE PROCEDURE item_owner(); 

4. Código SQL

database.sql
-----------------------------------------
-- Drop old schmema
-----------------------------------------
 
DROP TABLE IF EXISTS wish_list CASCADE;
DROP TABLE IF EXISTS review CASCADE;
DROP TABLE IF EXISTS loan CASCADE;
DROP TABLE IF EXISTS item CASCADE;
DROP TABLE IF EXISTS nonbook CASCADE;
DROP TABLE IF EXISTS book CASCADE;
DROP TABLE IF EXISTS author_work CASCADE;
DROP TABLE IF EXISTS "work" CASCADE;
DROP TABLE IF EXISTS collection CASCADE;
DROP TABLE IF EXISTS author CASCADE;
DROP TABLE IF EXISTS location CASCADE;
DROP TABLE IF EXISTS publisher CASCADE;
DROP TABLE IF EXISTS "user" CASCADE;
 
DROP TYPE IF EXISTS media;
 
DROP FUNCTION IF EXISTS item_owner() CASCADE;
DROP FUNCTION IF EXISTS loan_item() CASCADE;
DROP FUNCTION IF EXISTS work_wish_list() CASCADE;
 
DROP TRIGGER IF EXISTS loan_item ON loan;
DROP TRIGGER IF EXISTS item_owner ON loan;
DROP TRIGGER IF EXISTS work_wish_list ON wish_list;
 
-----------------------------------------
-- Types
-----------------------------------------
 
CREATE TYPE media AS ENUM ('CD', 'DVD', 'VHS', 'Slides', 'Photos', 'MP3');
 
-----------------------------------------
-- Tables
-----------------------------------------
 
CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    email text NOT NULL CONSTRAINT user_email_uk UNIQUE,
    name text NOT NULL,
    obs text,
    password text NOT NULL,
    img text,
    is_admin BOOLEAN NOT NULL
);
 
CREATE TABLE publisher (
    id SERIAL PRIMARY KEY,
    name text NOT NULL
);
 
CREATE TABLE location (
    id SERIAL PRIMARY KEY,
    name text NOT NULL,
    address text NOT NULL,
    gps text
);
 
CREATE TABLE author (
    id SERIAL PRIMARY KEY,
    name text NOT NULL,
    img text
);
 
CREATE TABLE collection (
    id SERIAL PRIMARY KEY,
    name text NOT NULL
);
 
CREATE TABLE "work" (
    id SERIAL PRIMARY KEY,
    title text NOT NULL,
    obs text,
    img text,
    "year" INTEGER,
    id_user INTEGER REFERENCES "user" (id) ON UPDATE CASCADE,
    id_collection INTEGER REFERENCES collection (id) ON UPDATE CASCADE,
    CONSTRAINT year_positive_ck CHECK (("year" > 0))
);
 
CREATE TABLE author_work (
    id_author INTEGER NOT NULL REFERENCES author (id) ON UPDATE CASCADE,
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    PRIMARY KEY (id_author, id_work)
);
 
CREATE TABLE book (
    id_work INTEGER PRIMARY KEY REFERENCES "work" (id) ON UPDATE CASCADE,
    edition text,
    isbn BIGINT NOT NULL CONSTRAINT book_isbn_uk UNIQUE,
    id_publisher INTEGER REFERENCES publisher (id) ON UPDATE CASCADE
);
 
CREATE TABLE nonbook (
    id_work INTEGER PRIMARY KEY REFERENCES "work" (id) ON UPDATE CASCADE ON DELETE CASCADE,
    TYPE media NOT NULL
);
 
CREATE TABLE item (
    id SERIAL PRIMARY KEY,
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    id_location INTEGER NOT NULL REFERENCES location (id) ON UPDATE CASCADE,
    code INTEGER NOT NULL,
    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL
);
 
CREATE TABLE loan (
    id SERIAL PRIMARY KEY,
    id_item INTEGER NOT NULL REFERENCES item (id) ON UPDATE CASCADE,
    id_user INTEGER NOT NULL REFERENCES "user" (id) ON UPDATE CASCADE,
    start_t TIMESTAMP WITH TIME zone NOT NULL,
    end_t TIMESTAMP WITH TIME zone NOT NULL,
    CONSTRAINT date_ck CHECK (end_t > start_t)
);
 
CREATE TABLE review (
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    id_user INTEGER NOT NULL REFERENCES "user" (id) ON UPDATE CASCADE,
    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
    comment text NOT NULL,
    rating INTEGER NOT NULL CONSTRAINT rating_ck CHECK (((rating > 0) OR (rating <= 5))),
    PRIMARY KEY (id_work, id_user)
);
 
CREATE TABLE wish_list (
    id_work INTEGER NOT NULL REFERENCES "work" (id) ON UPDATE CASCADE,
    id_user INTEGER NOT NULL REFERENCES "user" (id) ON UPDATE CASCADE,
    PRIMARY KEY (id_work, id_user)
);
 
-----------------------------------------
-- INDEXES
-----------------------------------------
 
CREATE INDEX email_user ON "user" USING hash (email);
 
CREATE INDEX user_work ON "work" USING hash(id_user);
 
CREATE INDEX user_loan ON loan USING hash(id_user);
 
CREATE INDEX start_loan ON loan USING btree (start_t);
 
CREATE INDEX end_loan ON loan USING btree (end_t);
 
CREATE INDEX id_location ON location USING hash (id);
 
-----------------------------------------
-- TRIGGERS and UDFs
-----------------------------------------
 
CREATE FUNCTION loan_item() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM loan WHERE NEW.id_item = id_user AND end_t > NEW.start_t) THEN
        RAISE EXCEPTION 'An item can only be loaned to one user in every moment.';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER loan_item
    BEFORE INSERT OR UPDATE ON loan
    FOR EACH ROW
    EXECUTE PROCEDURE loan_item();
 
CREATE FUNCTION item_owner() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM item 
        INNER JOIN "work" ON "work".id = "item".id_work
        WHERE NEW.id_item = item.id AND NEW.id_user = "work".id_user ) THEN
    RAISE EXCEPTION 'An item cannot be loaned to its owner.';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER item_owner
    BEFORE INSERT OR UPDATE ON loan
    FOR EACH ROW
    EXECUTE PROCEDURE item_owner();
 
CREATE FUNCTION work_wish_list() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM "work" WHERE NEW.id_work = id AND NEW.id_user = id_user) THEN
        RAISE EXCEPTION 'An users item cannot be in his wish list.';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER work_wish_list
    BEFORE INSERT OR UPDATE ON wish_list
    FOR EACH ROW
    EXECUTE PROCEDURE work_wish_list();
 
-----------------------------------------
-- end
-----------------------------------------
data.sql
-----------------------------------------
-- Populate the database
-----------------------------------------
 
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (1,'sodales.at@Curae.co.uk','Zeph Griffin','rhoncus. Donec est. Nunc ullamcorper,','GUL95ZXR9EX','Praesent',TRUE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (2,'aliquam.iaculis.lacus@amet.co.uk','Noah Gibson','nunc ac mattis ornare, lectus','TYT71DOD7YN','sollicitudin',TRUE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (3,'amet.ante@faucibusleo.net','Aladdin Davidson','nisl elementum purus, accumsan interdum','OFK00XCC7OD','vel',TRUE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (4,'facilisis.magna.tellus@sociis.net','Thor Villarreal','Nunc quis arcu vel quam','PZJ77DKO2VZ','Cum',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (5,'ut.dolor@acturpisegestas.edu','Warren Flowers','tempus eu, ligula. Aenean euismod','SXT16TTW3MH','lobortis',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (6,'aliquet.diam.Sed@tinciduntnibh.co.uk','Bert Townsend','dolor. Quisque tincidunt pede ac','EEQ89YOB6LU','Phasellus',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (7,'Donec.consectetuer@nuncullamcorper.org','Raymond Bush','amet, faucibus ut, nulla. Cras','CKB15AAW5MM','venenatis',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (8,'scelerisque.scelerisque.dui@arcuiaculisenim.ca','Hu Randolph','sollicitudin orci sem eget massa.','DHF29AYY6TX','ut,',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (9,'Nulla@et.net','Acton Gibson','cubilia Curae; Donec tincidunt. Donec','ZYS24FHN5GR','ipsum',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (10,'dictum.Phasellus.in@viverra.edu','Byron Kerr','tellus eu augue porttitor interdum.','BMH35RMO4GH','pellentesque',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (11,'Curae.Phasellus.ornare@doloregestasrhoncus.edu','Wallace Carlson','et, magna. Praesent interdum ligula','OAU05AEM4AD','magna',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (12,'sit.amet@dignissim.edu','Porter Osborn','tellus faucibus leo, in lobortis','SST35ROU5QH','tellus',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (13,'arcu.Vestibulum@amet.org','Seth Byers','justo nec ante. Maecenas mi','QZG51RLN5NW','adipiscing',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (14,'dui.nec@ultriciesadipiscing.co.uk','Edward Cabrera','a, dui. Cras pellentesque. Sed','FDX75RDX3OH','Mauris',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (15,'magnis@cursuset.edu','Roth Barrett','dignissim pharetra. Nam ac nulla.','ZRS58TGS4KQ','massa',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (16,'mollis.nec@tempor.com','Donovan Stevenson','Suspendisse aliquet, sem ut cursus','ZYG87WQA6FX','consequat',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (17,'malesuada@necligulaconsectetuer.net','Slade Bryan','semper tellus id nunc interdum','SIC71JFQ4OE','Vestibulum',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (18,'porta.elit@temporaugueac.co.uk','Arsenio George','quis, pede. Praesent eu dui.','QIG24ZOK3EM','Phasellus',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (19,'amet.ultricies@Aliquam.edu','Joel Baxter','lorem lorem, luctus ut, pellentesque','MPS10QPK6UE','Mauris',FALSE);
INSERT INTO "user" (id,email,name,obs,password,img,is_admin) VALUES (20,'risus.In.mi@egestas.com','Paki Blevins','a, arcu. Sed et libero.','ZKC34PNB0SU','Duis',FALSE);
 
INSERT INTO "publisher" (id,name) VALUES (1,'velit. Aliquam');
INSERT INTO "publisher" (id,name) VALUES (2,'purus gravida sagittis. Duis');
INSERT INTO "publisher" (id,name) VALUES (3,'elit erat vitae risus.');
INSERT INTO "publisher" (id,name) VALUES (4,'nisl');
INSERT INTO "publisher" (id,name) VALUES (5,'urna,');
 
INSERT INTO "location" (id,name,address,gps) VALUES (1,'San Giovanni la Punta','Ap #944-757 Lorem Rd.','14.02225, -20.03493');
INSERT INTO "location" (id,name,address,gps) VALUES (2,'Rastatt','338-1413 Malesuada Rd.','-16.65487, -124.73778');
INSERT INTO "location" (id,name,address,gps) VALUES (3,'Zignago','Ap #565-1550 Ultricies Rd.','-54.70141, 124.72732');
INSERT INTO "location" (id,name,address,gps) VALUES (4,'Rocky View','P.O. Box 698, 2252 Molestie Ave','-60.90553, 177.07358');
INSERT INTO "location" (id,name,address,gps) VALUES (5,'Corswarem','P.O. Box 229, 3194 Felis. Rd.','-70.75477, 25.51611');
 
INSERT INTO "author" (id,name,img) VALUES (1,'Ginger Rocha','sed');
INSERT INTO "author" (id,name,img) VALUES (2,'Derek Robles','tempus');
INSERT INTO "author" (id,name,img) VALUES (3,'Jameson Booker','magnis');
INSERT INTO "author" (id,name,img) VALUES (4,'Yuli Spencer','consequat');
INSERT INTO "author" (id,name,img) VALUES (5,'Michael Berger','et');
INSERT INTO "author" (id,name,img) VALUES (6,'Sage Pena','condimentum');
INSERT INTO "author" (id,name,img) VALUES (7,'Claire Logan','eu');
INSERT INTO "author" (id,name,img) VALUES (8,'Remedios Kent','ligula.');
INSERT INTO "author" (id,name,img) VALUES (9,'Edward Schroeder','dictum');
INSERT INTO "author" (id,name,img) VALUES (10,'Madeson Wilkins','montes,');
INSERT INTO "author" (id,name,img) VALUES (11,'Gray Ellison','lobortis');
INSERT INTO "author" (id,name,img) VALUES (12,'Audra Joyner','neque');
INSERT INTO "author" (id,name,img) VALUES (13,'Beau Byers','nibh.');
INSERT INTO "author" (id,name,img) VALUES (14,'Pearl Horton','mauris.');
INSERT INTO "author" (id,name,img) VALUES (15,'Orla Roberson','sem');
 
INSERT INTO "collection" (id,name) VALUES (1,'sit');
INSERT INTO "collection" (id,name) VALUES (2,'aliquet nec, imperdiet');
INSERT INTO "collection" (id,name) VALUES (3,'felis.');
INSERT INTO "collection" (id,name) VALUES (4,'purus, in molestie');
INSERT INTO "collection" (id,name) VALUES (5,'molestie sodales.');
 
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (1,'massa.','nec','egestas','2018',15,2);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (2,'odio sagittis','sed consequat','eget','2017',10,3);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (3,'orci luctus et ultrices','Etiam bibendum fermentum','interdum','2017',10,1);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (4,'Aliquam adipiscing lobortis','quis arcu vel','magna.','2016',5,2);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (5,'elit. Aliquam auctor, velit','nec, malesuada ut, sem. Nulla interdum.','erat','2017',15,1);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (6,'odio vel','Proin dolor. Nulla','Cras','2016',15,3);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (7,'enim, condimentum','egestas a, scelerisque','non,','2017',5,1);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (8,'Duis gravida.','erat','aliquet','2016',5,5);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (9,'sed,','quam. Curabitur vel lectus. Cum sociis natoque penatibus et magnis','Cras','2016',1,5);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (10,'lectus justo','gravida molestie arcu. Sed eu nibh vulputate mauris sagittis placerat.','vitae','2016',14,3);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (11,'libero','et nunc. Quisque ornare tortor at risus. Nunc','nunc,','2018',17,5);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (12,'purus.','egestas a, scelerisque sed, sapien. Nunc pulvinar','molestie','2016',5,4);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (13,'et netus','tempor diam dictum sapien. Aenean massa. Integer vitae nibh. Donec','montes,','2018',1,2);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (14,'fringilla euismod','Morbi','Mauris','2017',2,5);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user,id_collection) VALUES (15,'sit amet','blandit at,','ac','2016',18,3);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user) VALUES (16,'Donec','Donec','Maecenas','2018',3);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user) VALUES (17,'elementum at, egestas','Donec consectetuer mauris id sapien. Cras dolor','orci','2016',11);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user) VALUES (18,'dignissim magna','Integer vitae nibh.','purus','2017',1);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user) VALUES (19,'mollis. Integer','a, dui. Cras pellentesque.','sollicitudin','2016',17);
INSERT INTO "work" (id,title,obs,img,YEAR,id_user) VALUES (20,'nulla.','adipiscing. Mauris','eu','2018',1);
 
INSERT INTO "author_work" (id_author,id_work) VALUES (1,19);
INSERT INTO "author_work" (id_author,id_work) VALUES (3,19);
INSERT INTO "author_work" (id_author,id_work) VALUES (6,20);
INSERT INTO "author_work" (id_author,id_work) VALUES (9,10);
INSERT INTO "author_work" (id_author,id_work) VALUES (9,18);
INSERT INTO "author_work" (id_author,id_work) VALUES (10,19);
INSERT INTO "author_work" (id_author,id_work) VALUES (13,12);
INSERT INTO "author_work" (id_author,id_work) VALUES (13,20);
INSERT INTO "author_work" (id_author,id_work) VALUES (15,20);
 
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (1,'fermentum vel,',2442970001138,4);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (3,'et,',4394574803300,2);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (4,'tincidunt nibh.',9998253919184,1);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (5,'orci lobortis',5790724372491,5);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (6,'Aenean egestas',7902191774919,4);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (7,'Nam',9910016744397,5);
INSERT INTO "book" (id_work,edition,isbn) VALUES (9,'ipsum.',6575429751537);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (11,'Morbi neque',226934510283,1);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (12,'urna. Vivamus',6970028770156,1);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (13,'amet, consectetuer',9727244828827,1);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (14,'nec ante.',3833052730187,1);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (15,'auctor odio',9890390462242,4);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (17,'semper cursus.',1934273634105,2);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (18,'vestibulum massa',8603163561783,5);
INSERT INTO "book" (id_work,edition,isbn) VALUES (19,'tempor',3727866560220);
INSERT INTO "book" (id_work,edition,isbn,id_publisher) VALUES (20,'tincidunt.',2415959918871,1);
 
INSERT INTO "nonbook" (id_work,TYPE) VALUES (2,'CD');
INSERT INTO "nonbook" (id_work,TYPE) VALUES (8,'Photos');
INSERT INTO "nonbook" (id_work,TYPE) VALUES (10,'MP3');
INSERT INTO "nonbook" (id_work,TYPE) VALUES (16,'Slides');
 
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (1,7,2,73687,'2017-02-17 09:41:14');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (2,2,5,22356,'2016-12-27 21:08:45');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (3,5,4,2680,'2018-03-08 22:50:59');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (4,2,5,12099,'2017-05-06 16:18:36');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (5,1,3,45024,'2017-06-15 05:52:10');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (6,3,4,84991,'2016-11-24 11:19:48');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (7,14,5,41867,'2018-02-03 16:55:49');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (8,8,1,84410,'2017-08-04 14:16:35');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (9,5,1,70639,'2016-08-07 09:11:14');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (10,14,2,4648,'2017-01-02 04:17:25');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (11,14,4,58858,'2017-05-26 23:10:05');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (12,10,5,15290,'2016-11-04 12:12:48');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (13,17,3,29969,'2016-07-31 20:02:18');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (14,2,4,7860,'2016-12-26 13:00:20');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (15,3,4,50862,'2018-03-17 13:19:11');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (16,13,5,42515,'2017-05-26 10:34:45');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (17,18,2,4124,'2017-07-31 07:56:01');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (18,8,2,35433,'2017-05-26 01:54:24');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (19,1,5,65780,'2017-01-14 07:16:54');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (20,11,2,77774,'2017-10-08 12:24:14');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (21,8,3,58945,'2017-11-16 11:41:48');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (22,9,4,38761,'2017-09-13 08:59:39');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (23,1,4,13344,'2017-09-13 13:22:09');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (24,11,3,52978,'2016-11-05 02:55:10');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (25,8,5,13574,'2016-12-23 02:11:35');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (26,10,1,90149,'2016-12-12 22:07:17');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (27,19,2,9840,'2017-12-19 05:52:53');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (28,12,5,56385,'2016-07-07 06:05:00');
INSERT INTO "item" (id,id_work,id_location,code,"date") VALUES (29,13,2,14620,'2016-08-05 06:51:29');
 
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (2,18,11,'2016-06-15 19:58:25','2017-11-06 03:37:05');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (3,21,15,'2016-05-22 12:21:33','2016-10-05 18:44:59');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (5,1,18,'2017-03-14 21:50:00','2018-03-30 22:45:40');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (6,22,20,'2016-11-26 07:22:57','2018-01-16 05:56:57');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (9,24,3,'2017-01-10 16:35:44','2017-10-04 09:39:26');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (12,27,2,'2016-08-21 15:01:52','2018-03-17 11:06:11');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (14,14,4,'2017-06-01 08:44:35','2018-01-22 17:35:44');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (17,13,13,'2016-09-13 14:18:09','2017-11-05 15:22:04');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (19,29,8,'2017-04-12 09:14:13','2017-08-12 17:27:59');
INSERT INTO "loan" (id,id_item,id_user,start_t,end_t) VALUES (20,17,7,'2016-07-29 10:57:48','2017-09-28 18:04:18');
 
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (1,9,'2017-01-17 05:52:05','blandit enim consequat purus. Maecenas libero est,',2);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (1,10,'2017-03-29 07:28:22','nulla magna, malesuada vel, convallis in, cursus et,',3);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (1,14,'2017-09-19 19:46:44','nisi nibh lacinia orci, consectetuer euismod est arcu ac',3);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (1,18,'2017-10-16 09:28:58','nisi',3);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (2,7,'2018-01-30 15:08:23','turpis egestas. Fusce aliquet magna a',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (3,12,'2016-05-09 18:23:19','ac metus vitae velit egestas lacinia.',1);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (3,18,'2016-06-16 06:54:14','et, rutrum eu, ultrices sit',3);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (4,4,'2017-01-16 23:34:23','Nam interdum enim non nisi. Aenean eget metus. In nec',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (4,15,'2017-02-24 08:03:14','mi. Duis risus odio,',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (6,10,'2017-12-17 23:32:57','felis, adipiscing fringilla, porttitor vulputate, posuere vulputate, lacus. Cras interdum. Nunc sollicitudin commodo',1);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (7,14,'2017-03-26 01:06:54','egestas blandit. Nam nulla magna, malesuada vel, convallis in, cursus et, eros. Proin ultrices.',3);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (7,18,'2017-07-06 18:48:11','nulla. Integer',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (8,6,'2018-02-16 12:47:38','ipsum',2);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (8,11,'2017-08-21 19:21:19','pharetra. Quisque ac libero nec',1);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (8,20,'2018-03-07 22:23:34','rhoncus',1);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (9,10,'2017-08-05 04:54:05','et,',1);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (9,18,'2017-02-04 22:58:52','imperdiet ornare. In faucibus. Morbi vehicula. Pellentesque tincidunt tempus risus.',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (11,3,'2016-10-05 01:36:42','tincidunt pede ac urna. Ut tincidunt vehicula',5);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (12,10,'2017-05-22 13:54:06','dui augue eu tellus. Phasellus elit pede, malesuada vel, venenatis vel, faucibus id,',1);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (12,14,'2017-11-01 13:07:21','enim commodo hendrerit. Donec porttitor tellus non magna. Nam ligula elit, pretium',2);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (12,19,'2017-05-15 03:10:47','sed',5);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (13,2,'2018-02-04 12:08:41','non, cursus non, egestas a, dui. Cras pellentesque. Sed dictum. Proin eget',3);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (13,18,'2017-07-19 07:07:56','Mauris vel turpis. Aliquam adipiscing lobortis risus. In mi',1);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (15,16,'2017-03-16 18:02:34','Cras eget nisi dictum augue malesuada malesuada. Integer id',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (17,17,'2017-09-27 22:56:51','justo nec ante.',5);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (18,16,'2016-08-20 19:03:34','eu, odio.',2);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (18,19,'2016-11-12 16:50:24','orci luctus et ultrices',2);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (18,2,'2018-02-21 06:30:43','lorem',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (18,7,'2017-08-30 12:28:38','ac nulla. In tincidunt congue turpis. In condimentum. Donec at arcu. Vestibulum',2);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (20,1,'2017-03-11 20:49:59','Morbi accumsan laoreet ipsum. Curabitur consequat, lectus sit amet luctus vulputate, nisi sem semper',3);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (20,12,'2018-01-11 14:46:12','facilisis non, bibendum sed, est. Nunc',2);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (20,19,'2017-09-26 07:59:12','ut, pellentesque eget, dictum placerat, augue. Sed molestie. Sed',4);
INSERT INTO "review" (id_user,id_work,"date",comment,rating) VALUES (20,20,'2017-03-10 22:05:47','libero. Donec consectetuer mauris id sapien.',2);
 
INSERT INTO "wish_list" (id_user,id_work) VALUES (1,5);
INSERT INTO "wish_list" (id_user,id_work) VALUES (1,12);
INSERT INTO "wish_list" (id_user,id_work) VALUES (2,5);
INSERT INTO "wish_list" (id_user,id_work) VALUES (2,19);
INSERT INTO "wish_list" (id_user,id_work) VALUES (4,8);
INSERT INTO "wish_list" (id_user,id_work) VALUES (4,18);
INSERT INTO "wish_list" (id_user,id_work) VALUES (5,14);
INSERT INTO "wish_list" (id_user,id_work) VALUES (6,15);
INSERT INTO "wish_list" (id_user,id_work) VALUES (6,18);
INSERT INTO "wish_list" (id_user,id_work) VALUES (6,3);
INSERT INTO "wish_list" (id_user,id_work) VALUES (7,13);
INSERT INTO "wish_list" (id_user,id_work) VALUES (11,20);
INSERT INTO "wish_list" (id_user,id_work) VALUES (12,3);
INSERT INTO "wish_list" (id_user,id_work) VALUES (13,16);
INSERT INTO "wish_list" (id_user,id_work) VALUES (13,18);
INSERT INTO "wish_list" (id_user,id_work) VALUES (14,11);
INSERT INTO "wish_list" (id_user,id_work) VALUES (15,9);
INSERT INTO "wish_list" (id_user,id_work) VALUES (16,9);
INSERT INTO "wish_list" (id_user,id_work) VALUES (16,10);
INSERT INTO "wish_list" (id_user,id_work) VALUES (16,11);
INSERT INTO "wish_list" (id_user,id_work) VALUES (17,20);
INSERT INTO "wish_list" (id_user,id_work) VALUES (18,1);
INSERT INTO "wish_list" (id_user,id_work) VALUES (19,19);
INSERT INTO "wish_list" (id_user,id_work) VALUES (19,4);
INSERT INTO "wish_list" (id_user,id_work) VALUES (20,13);
 
-----------------------------------------
-- end
-----------------------------------------

LBAW

[MediaLibrary]

teach/lbaw/medialib/a6.txt · Last modified: 12/04/2019 09:10 by Correia Lopes