Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- DDL
- -- (1) DROPS
- -- (2) CREATE TABLES
- -- (3) INSERT VALUES
- -- (4) CREATE VIEWS
- -- (5) CREATE TRIGGERS/PROCEDURES
- DROP VIEW IF EXISTS books_loans;
- DROP VIEW IF EXISTS slide_loans;
- DROP VIEW IF EXISTS artefact_loans;
- DROP VIEW IF EXISTS books_in;
- DROP VIEW IF EXISTS artefacts_total;
- DROP VIEW IF EXISTS total_loans;
- DROP TRIGGER IF EXISTS delete_dig_person;
- DROP TRIGGER IF EXISTS person_cleaner;
- DROP TABLE IF EXISTS loans;
- DROP TABLE IF EXISTS slides;
- DROP TABLE IF EXISTS books;
- DROP TABLE IF EXISTS artefacts;
- DROP TABLE IF EXISTS persons;
- DROP TABLE IF EXISTS deleted_persons;
- DROP TABLE IF EXISTS digs;
- DROP TABLE IF EXISTS documents;
- -- person (person_id, p_name)
- CREATE TABLE digs (
- PRIMARY KEY (dig_id),
- dig_site VARCHAR(100),
- dig_id INT,
- d_description VARCHAR(250),
- );
- CREATE TABLE persons (
- PRIMARY KEY(p_id),
- p_id INT,
- firstname VARCHAR(35),
- lastname VARCHAR(35),
- dig_id INT,
- FOREIGN KEY (dig_id)
- REFERENCES digs(dig_id)
- );
- CREATE TABLE deleted_persons (
- PRIMARY KEY(p_id),
- p_id INT,
- firstname VARCHAR(35),
- lastname VARCHAR(35),
- dig_id INT,
- FOREIGN KEY (dig_id)
- REFERENCES digs(dig_id)
- );
- -- documents (document_id, title, published_year, d_location, author, person)
- CREATE TABLE documents (
- PRIMARY KEY(d_id),
- d_id INT,
- title VARCHAR(30),
- published_year INT,
- d_location VARCHAR(30),
- author VARCHAR(30),
- );
- -- artefacts(a_id, sequential_artefact, coordinate, depth, date_found, description, location, date_out, date_back)
- CREATE TABLE artefacts(
- PRIMARY KEY(a_id),
- a_id AS CONCAT (dig_id, '-', sequential_artefact) PERSISTED NOT NULL,
- sequential_artefact INT,
- coordinate VARCHAR(30),
- depth VARCHAR(30),
- date_found DATE,
- a_descritpion VARCHAR(50),
- a_location VARCHAR(30),
- dig_id INT,
- FOREIGN KEY (dig_id) -- DETTA FUNGERAR EJ???
- REFERENCES digs(dig_id) -- VARFÖR FUNGERAR INTE DETTA???
- );
- CREATE TABLE books(
- PRIMARY KEY (b_id),
- b_id INT,
- title VARCHAR(30),
- published_year VARCHAR(10),
- author VARCHAR(35),
- b_location VARCHAR(50),
- );
- CREATE TABLE slides(
- PRIMARY KEY (s_id),
- s_id AS CONCAT (dig_id, '-', sequential_slide) PERSISTED NOT NULL,
- sequential_slide INT,
- s_subject VARCHAR(35),
- s_description VARCHAR(40),
- s_location VARCHAR(35),
- dig_id INT,
- -- CONSTRAINT fk_inv_dig_id
- FOREIGN KEY (dig_id)
- REFERENCES digs(dig_id)
- -- ON DELETE CASCADE
- );
- CREATE TABLE loans(
- PRIMARY KEY (l_id),
- l_id INT,
- date_out DATE,
- date_back DATE,
- s_id VARCHAR(25),
- b_id INT,
- a_id VARCHAR(25),
- p_id INT
- FOREIGN KEY (s_id)
- REFERENCES slides(s_id),
- FOREIGN KEY (b_id)
- REFERENCES books(b_id),
- FOREIGN KEY (a_id)
- REFERENCES artefacts(a_id),
- CONSTRAINT fk_inv_loans_p_id
- FOREIGN KEY (p_id)
- REFERENCES persons (p_id)
- ON DELETE CASCADE
- );
- INSERT INTO digs (dig_site, dig_id, d_description)
- VALUES ('Chan-Chan', 12, 'Beach on the coast of the commune of Mehuín in southern Chile.'),
- ('Monte Verde', 43, 'Monte Verde is an archaeological site in southern Chile, located near Puerto Montt, Southern Chile'),
- ('Fell Cave', 9, 'Cueva Fell is a natural cave and archaeological site in southern Patagonia.'),
- ('Pali Aike', 4, 'The Pali-Aike National Park is a park located in the Magallanes Region of the Chilean Patagonia.'),
- ('An Yang', 6, 'An yang is a prefecture-level city in Henan province, China.'),
- ('Kykkos Monastery', 21, 'Kykkos Monastery which lies 20 km west of Pedoulas, is one of the wealthiest and best-known monasteries in Cyprus.'),
- ('Notre Dame de tyre', 67, 'Notre Dame de Tyre or Our Lady of Tyre is a monastic church in Nicosia.');
- INSERT INTO persons (p_id, firstname, lastname, dig_id)
- VALUES (21, 'Bengt', 'Andersson',12),
- (32, 'Anna', 'Nilsson',12),
- (33, 'Johan', 'Ahlberg',12),
- (97, 'Anders', 'Svensson',43),
- (12, 'Jennifer', 'Berg',43),
- (11, 'Emelie', 'Palner',9),
- (77, 'Elinor', 'Nyberg',9),
- (87, 'Hannes', 'Lindgren',9),
- (99, 'Jacob', 'Lycke',4),
- (94, 'Olivia', 'Wennborg',4),
- (55, 'Johan', 'Haggar',4),
- (67, 'Axel', 'Guditz',4),
- (61, 'Jonathan', 'Solberg',6),
- (81, 'Sara', 'Andersdotter',21),
- (85, 'Amanda', 'Silfversparre',21),
- (74, 'Niklas', 'Guldsked',21),
- (49, 'Sven', 'Laxnacke',67),
- (41, 'Calle', 'Hoffeinheim',67);
- INSERT INTO documents (d_id, title, published_year, d_location, author)
- VALUES (392, 'Life On Earth', 1992, 'Shelf: A', 'James Cameron'),
- (3582, 'Book Of Life', 2002, 'Shelf: D', 'Niklas Berg'),
- (4921, 'Artefacts 101', 1952, 'Shelf: B', 'Gunnar Martin'),
- (353, 'Cosmos', 2016, 'Shelf: C', 'Joseph Bang'),
- (981, 'Space', 2002, 'Shelf: G', 'Anna Smith'),
- (111, 'Moon landing 1969', 1970, 'Shelf: C', 'Anna Smith'),
- (455, 'Festerist 101', 1994, 'Shelf: A', 'Clarence Coleman'),
- (12, 'History of Tour de France', 2005, 'Shelf: A', 'Phil Stevenson'),
- (71, 'How to dig a tunnel', 2010, 'Shelf: A', 'Christopher O.Neil'),
- (9746, 'Basejump', 2013, 'Shelf: B', 'Dustin Hoffman'),
- (32221, 'History of dinosaurs', 1987, 'Shelf C', 'Michael Bay'),
- (4942837, 'Formel 1', 2018, 'Shelf: D', 'Stan Lee');
- INSERT INTO books (b_id, title, published_year, author, b_location)
- VALUES (123, 'FOR AZEROTH!', 2007,'Dillon McNillon', 'Borrowed'),
- (435, 'National Treasure', 2003, 'Nicolas Cage', 'Shelf:A22'),
- (563, 'WAOOW', 2001, 'Owen Wilson', 'Shelf:A22'),
- (152, 'Nonstop', 2018, 'Drake', 'Borrowed'),
- (880, 'Bridget Jones dagbok', 2002, 'Yung Nylla', 'Shelf:B12'),
- (981, 'Life of Dillon', 2001, 'John Cleese', 'Borrowed'),
- (654, 'Hejhej', 2005, 'J&J', 'Borrowed');
- INSERT INTO artefacts (sequential_artefact, coordinate, depth, date_found, a_descritpion, a_location, dig_id)
- VALUES (465, '30:40', '20 cm', '2017-05-23', 'Small round object', 'Borrowed', 21),
- (266, '30:45', '15 cm', '2017-05-23', 'Small round object', 'Borrowed', 43),
- (266, '30:35', '39 cm', '2017-05-23', 'Small round object', 'Borrowed', 9),
- (275, '32:42', '10 cm', '1997-05-29', 'Silver coin', 'Borrowed',6),
- (765, '32:42', '180 cm', '1997-05-12', 'Gold Coin', 'Borrowed',6),
- (865, '32:42', '20 cm', '1997-05-27', 'Chinese sword', 'Borrowed',6),
- (145, '32:32', '0.5 cm', '1997-01-12', 'Chinese golden knife', 'Borrowed',6),
- (132, '32:41', '200 cm', '1997-09-01', 'Chinese helmet', 'Borrowed',6),
- (166, '29:45', '234 cm', '1997-07-14', 'Chinese breast plate', 'Borrowed',6),
- (125, '27:43', '500 cm', '1997-06-14', 'Chinese shield', 'Borrowed',6),
- (151, '12:92:14', '37 cm', '1999-02-24', 'Wooden crucifix', 'In-House',21),
- (118, '30:40:21', '94 cm', '1992-05-19', 'Golden ring', 'In-House', 21),
- (119, '30:40:23', '122 cm', '1993-08-17', 'Monk cape', 'In-House', 21),
- (117, '30:40:55', '431 cm', '1992-01-12', 'Golden chain', 'In-House',21),
- (110, '30:40:12', '12 cm', '1992-08-24', 'Candle holder', 'In-House', 21),
- (109, '30:40:98', '542 cm', '1992-02-02', 'Golden candle holder', 'In-House', 21),
- (108, '30:40:24', '122 cm', '1993-02-23', 'Metal crucifix', 'In-House', 21),
- (102, '30:40:77', '32 cm', '1993-05-29', 'Wooden bowl', 'In-House', 21),
- (100, '30:40:98', '96 cm', '1992-06-28', 'black priest cape', 'In-House', 21),
- (964, '98:34', '200 cm', '2015-02-23', 'wolf skeleton', 'Borrowed',4),
- (965, '95:42', '95 cm', '2015-03-24', 'Puma skeleton', 'Borrowed', 4),
- (936, '93:43', '94 cm', '2015-03-22', 'Patagonian Hairy Armadillo', 'Borrowed',4),
- (462, '432:431', '0,1 cm', '1992-04-11', 'Old brick wall', 'Borrowed',67),
- (732, '84:42', '43 cm', '2013-05-21', 'Stuffed Lizard', 'In-House',43),
- (757, '82:53', '53 cm', '2013-08-22', 'Snake skin', 'In-House',43),
- (752, '81:95', '123 cm', '2017-08-19', 'Puma Skeleton', 'In-House',43),
- (343, '11:40', '13 cm', '1982-01-23', 'Fish skeleton', 'In-house', 9),
- (365, '11:42', '11 cm', '1983-04-13', 'Big fish skeleton', 'In-House', 9),
- (375, '11:53', '8 cm', '1982-06-19', 'Medium fish skeleton', 'In-House',9),
- (392, '11:52', '1.5 cm', '1983-05-29', 'Random fish skeleton', 'In-House', 9),
- (326, '11:39', '5.5 cm', '1982-09-27', 'Old seaweed', 'Borrowed', 9),
- (753, '321:401', '00 cm', '1999-05-23', 'Rock statue', 'Borrowed', 12),
- (725, '324:402', '45 cm', '1999-03-23', 'Wooden statue', 'Borrowed',12),
- (769, '381:471', '921 cm', '1999-06-28', 'Metal statue', 'Borrowed', 12),
- (726, '339:412', '549 cm', '2000-03-12', 'Round random thing', 'Borrowed',12),
- (212, '282:52', '70 cm', '2017-02-22', 'Small round object', 'In-House', 12),
- (214, '284:42', '30 cm', '2017-05-23', 'Dinosaur bone', 'Borrowed', 12),
- (285, '295:15', '65 cm', '2017-03-18', 'Skin bone', 'In-House',9),
- (286, '251:12', '800 cm', '2015-05-23', 'Mammoth', 'In-House',12),
- (203, '295:09', '300 cm', '2012-11-23', 'Tiger bone', 'Borrowed',12),
- (434, '302:140', '80 cm', '2002-02-06', 'Small round object', 'Borrowed',6),
- (491, '350:470', '470 cm', '2004-03-12', 'Big round object', 'In-House',4),
- (461, '730:430', '205 cm', '2007-06-21', 'Small round object', 'Borrowed',12),
- (453, '230:240', '240 cm', '2011-02-23', 'Small round object', 'Borrowed', 12),
- (361, '530:340', '120 cm', '2013-01-23', 'Small round object', 'Borrowed',12);
- INSERT INTO slides(sequential_slide, s_subject, s_description, s_location, dig_id)
- VALUES (721, 'Animal tooth', 'Pictures of tiger tooth', 'In-House', 12),
- (123, 'Animal tooth', 'Pictures of Megaladon tooth', 'Borrowed',12),
- (46, 'Animal tooth', 'Pictures of snake tooth','In-house',12),
- (15, 'Fish', 'Fish documents', 'In-House',43),
- (426, 'Human', 'Pictures of human bone', 'In-house',4),
- (646, 'Wand', 'Picture of Voldemorts wand', 'In-house', 67),
- (223, 'Fish', 'Octopus', 'In-House',21),
- (536, 'Animal tooth', 'Pictures of mammoth bone', 'Borrowed',67),
- (234, 'Coin', 'Documents of chinese Coins','Borrowed',9),
- (921, 'Sword', 'Journals swords from the roman empire', 'Borrowed',6),
- (391, 'Fish', 'Pictures of dead fishes', 'In-house',21),
- (999, 'Crucifix', 'Journals of crucifixs', 'In-house',43),
- (175, 'Statue', 'Drawings of old statues', 'Borrowed',67),
- (382, 'Wood', 'Wooden artefacts', 'Borrowed',4),
- (322, 'Wood', 'Wooden artefacts', 'Borrowed',4);
- INSERT INTO loans (l_id, date_out, date_back, s_id, b_id, a_id ,p_id)
- VALUES (48223, '2017-09-21', '2018-02-03', '12-721', NULL, NULL, 21),
- (14221, '2018-02-19', NULL,'12-123', NULL, NULL, 61),
- (37281, '2016-05-28', '2016-08-12', '12-46', NULL, NULL, 41),
- (49274, NULL, NULL, '43-15', NULL, NULL, 97),
- (84982, '2018-01-22', '2018-07-09', '4-426', NULL, NULL, 85),
- (96827, '1999-04-13', '1999-07-16', '67-646',NULL, NULL, 11),
- (23181, '2017-10-11', '2018-01-03', NULL, 435, NULL, 81),
- (68023, '2017-03-12', '2018-05-29', NULL, 123, NULL, 49),
- (67492, '2018-04-11', NULL, NULL, 563, NULL, 12),
- (75939, '1997-05-11', '2000-05-11', NULL, 152, NULL, 85),
- (30202, '2018-01-14', '2018-03-20', NULL, 880, NULL, 67),
- (84114, '2018-02-01', '2018-02-21', NULL, NULL, '12-203', 32),
- (91322, '2018-04-02', '2018-05-21', NULL, NULL, '43-732', 41),
- (30345, '2017-02-14', '2018-05-14', NULL, NULL, '21-100', 77),
- (50389, '2018-01-12', '2018-11-12', NULL, NULL, '9-285', 55),
- (82849, '2018-06-12', NULL, NULL, NULL, '12-286', 49),
- (48295, '2016-03-17', '2017-07-18', NULL, NULL, '4-965', 33),
- (84593, '2018-09-21', NULL, NULL, NULL, '4-936', 32);
- --------------------------------------------------------------------------------------------------------------------------------------------------
- -- Första Viewn, kollar vilka böcker som är utlånade
- GO
- CREATE VIEW books_loans AS
- SELECT L.l_id AS 'Loan ID',
- (L.b_id) AS 'Book ID',
- (P.p_id) AS 'Person ID',
- (P.firstname) AS 'First name',
- (P.lastname) AS 'Last name'
- FROM loans AS L
- INNER JOIN persons AS P
- ON L.p_id = P.p_id
- WHERE L.b_id IS NOT NULL;
- GO
- --------------------------------------------------------------------------------------------------------------------------------------------------
- -- View två som visar vilka slides som är utlånade.
- GO
- CREATE VIEW slide_loans AS
- SELECT L.l_id AS 'Loan ID',
- (L.s_id) AS 'Slide ID',
- (P.p_id) AS 'Person ID',
- (P.firstname) AS 'First name',
- (P.lastname) AS 'Last name'
- FROM loans AS L
- INNER JOIN persons AS P
- ON L.p_id = P.p_id
- WHERE L.s_id IS NOT NULL;
- GO
- --------------------------------------------------------------------------------------------------------------------------------------------------
- GO
- CREATE VIEW artefact_loans AS
- SELECT L.l_id AS 'Loan ID',
- (L.a_id) AS 'Artefact ID',
- (P.p_id) AS 'Person ID',
- (P.firstname) AS 'First name',
- (P.lastname) AS 'Last name'
- FROM loans AS L
- INNER JOIN persons AS P
- ON L.p_id = P.p_id
- WHERE L.a_id IS NOT NULL;
- GO
- --------------------------------------------------------------------------------------------------------------
- GO
- CREATE VIEW total_loans AS
- SELECT COUNT (L.b_id) AS 'Number of book loans', COUNT (L.s_id) as 'Numbers of slide Loans', COUNT (L.a_id) AS 'Numbers of artefact loans'
- FROM loans AS L
- GO
- -----------------------------------------------------------------------------------------------------------------
- GO
- CREATE TRIGGER delete_loans_person
- ON persons
- FOR DELETE
- AS BEGIN
- DELETE FROM loans
- WHERE loans.p_id IN (
- SELECT deleted.p_id FROM deleted
- );
- END ;
- GO
- --------------------------------------------------------------------------------------------------------------
- --------------------------------------------------------------------------------------------------------------
- /*
- GO
- CREATE TRIGGER delete_dig_person
- ON persons
- FOR DELETE
- AS BEGIN
- DELETE FROM slides
- WHERE slides.dig_id IN (
- SELECT digs.dig_id FROM digs
- INNER JOIN deleted
- ON digs.p_id = deleted.p_id
- );
- DELETE FROM digs
- WHERE digs.p_id IN (
- SELECT deleted.p_id FROM deleted
- );
- END ;
- GO
- */
- --------------------------------------------------------------------------------------------------------------
- -- DENNA TRIGGER FUNGERAR -----
- GO
- CREATE TRIGGER person_cleaner
- ON persons
- AFTER DELETE
- AS BEGIN
- INSERT INTO deleted_persons (p_id, firstname, lastname, dig_id)
- SELECT D.p_id, D.firstname, D.lastname, D.dig_id
- FROM deleted AS D
- END;
- GO
- ----------------------------------------------------------------------------------------------------------------------
- /* SELECT * FROM slides
- WHERE slides.dig_id IN (
- SELECT digs.dig_id FROM digs
- INNER JOIN persons
- ON digs.p_id = persons.p_id
- );
- */
- --------------------------------------------------------------------------------------------------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement