diff options
| Commit | 51642800a775461be01a68250d864fffd8db26d3 (patch) | |
|---|---|---|
| Author | 2024-03-25 20:08:01 +0200 | |
| Committer | 2024-03-25 20:08:01 +0200 | |
| Files | 490ecd5aa7e22742f18132a446804d530c9e340b | |
| uni-projects-51642800a775461be01a68250d864fffd8db26d3.tar uni-projects-51642800a775461be01a68250d864fffd8db26d3.tar.gz uni-projects-51642800a775461be01a68250d864fffd8db26d3.zip | ||
| Parent | c901e9571bd128d6ffc1ba92d379fea01366a50f (diff) | |
[DB] Added databases creation script
1 files changed, 522 insertions, 0 deletions
| -rw-r--r-- | Year 3/Databases/Schemas-MoviesPCShips-MYSQL.sql | 522 |
diff --git a/Year 3/Databases/Schemas-MoviesPCShips-MYSQL.sql b/Year 3/Databases/Schemas-MoviesPCShips-MYSQL.sql new file mode 100644 index 0000000..4d72f59 --- /dev/null +++ b/Year 3/Databases/Schemas-MoviesPCShips-MYSQL.sql @@ -0,0 +1,522 @@ +-- USE master
+-- GO
+-- if exists (select * from sysdatabases where name='movies')
+-- DROP DATABASE movies
+-- GO
+
+DROP DATABASE movies; -- This could fail, it's fine!
+
+CREATE DATABASE movies;
+-- GO
+USE movies;
+-- GO
+
+----- Create Tables -----
+
+CREATE TABLE MOVIE (
+ TITLE VARCHAR(255) NOT NULL,
+ YEAR INTEGER NOT NULL,
+ LENGTH INTEGER,
+ INCOLOR CHAR(1),
+ STUDIONAME CHAR(50),
+ `PRODUCERC#` INTEGER
+);
+
+CREATE TABLE MOVIESTAR (
+ NAME CHAR(30) NOT NULL,
+ ADDRESS VARCHAR(255),
+ GENDER CHAR(1),
+ BIRTHDATE DATETIME
+);
+
+CREATE TABLE STARSIN (
+ MOVIETITLE VARCHAR(255) NOT NULL,
+ MOVIEYEAR INTEGER NOT NULL,
+ STARNAME CHAR(30) NOT NULL
+);
+
+CREATE TABLE MOVIEEXEC (
+ `CERT#` INTEGER NOT NULL,
+ NAME CHAR(30),
+ ADDRESS VARCHAR(255),
+ NETWORTH INTEGER
+);
+
+CREATE TABLE STUDIO (
+ NAME CHAR(50) NOT NULL,
+ ADDRESS VARCHAR(255),
+ `PRESC#` INTEGER
+);
+
+----- Create Constraints -----
+ALTER TABLE MOVIE ADD CONSTRAINT PK_MOVIE PRIMARY KEY(TITLE,YEAR);
+
+ALTER TABLE MOVIESTAR ADD CONSTRAINT PK_MOVIESTAR PRIMARY KEY(NAME);
+
+ALTER TABLE MOVIEEXEC ADD CONSTRAINT PK_MOVIEEXEC PRIMARY KEY(`CERT#`);
+
+ALTER TABLE STUDIO ADD CONSTRAINT PK_STUDIO PRIMARY KEY(NAME);
+
+ALTER TABLE STARSIN ADD CONSTRAINT PK_STARSIN PRIMARY KEY(MOVIETITLE,MOVIEYEAR,STARNAME);
+
+ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_MOVIEEXEC FOREIGN KEY(`PRODUCERC#`) REFERENCES MOVIEEXEC(`CERT#`);
+
+ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_STUDIO FOREIGN KEY(STUDIONAME) REFERENCES STUDIO(NAME);
+
+ALTER TABLE STARSIN ADD CONSTRAINT FK_STARSIN_MOVIE FOREIGN KEY(MOVIETITLE, MOVIEYEAR) REFERENCES MOVIE(TITLE, YEAR);
+
+ALTER TABLE STARSIN ADD CONSTRAINT FK_STARSIN_MOVIESTAR FOREIGN KEY(STARNAME) REFERENCES MOVIESTAR(NAME);
+
+------- Insert Studio ------------
+INSERT INTO STUDIO
+ VALUES ('Disney','500 S. Buena Vista Street',1);
+
+INSERT INTO STUDIO
+ VALUES ('USA Entertainm.','', 2);
+
+INSERT INTO STUDIO
+ VALUES ('Fox','10201 Pico Boulevard', 3);
+
+INSERT INTO STUDIO
+ VALUES ('Paramount','5555 Melrose Ave', 4);
+
+INSERT INTO STUDIO
+ VALUES ('MGM','MGM Boulevard', 5);
+
+------- Insert MovieExec ------------
+INSERT INTO MOVIEEXEC (NAME, ADDRESS, `CERT#`, NETWORTH)
+ VALUES ('George Lucas', 'Oak Rd.', 555, 200000000);
+
+INSERT INTO MOVIEEXEC (NAME, ADDRESS, `CERT#`, NETWORTH)
+ VALUES ('Ted Turner', 'Turner Av.', 333, 125000000);
+
+INSERT INTO MOVIEEXEC (NAME, ADDRESS, `CERT#`, NETWORTH)
+ VALUES ('Stephen Spielberg', '123 ET road', 222, 100000000);
+
+INSERT INTO MOVIEEXEC (NAME, ADDRESS, `CERT#`, NETWORTH)
+ VALUES ('Merv Griffin', 'Riot Rd.', 199, 112000000);
+
+INSERT INTO MOVIEEXEC (NAME, ADDRESS, `CERT#`, NETWORTH)
+ VALUES ('Calvin Coolidge', 'Fast Lane', 123, 20000000);
+
+------- Insert Movie ------------
+INSERT INTO MOVIE
+ VALUES ('Pretty Woman', 1990, 119, 'Y', 'Disney', 199);
+
+INSERT INTO MOVIE
+ VALUES ('The Man Who Wasn''t There', 2001, 116, 'N', 'USA Entertainm.',
+ 555);
+
+INSERT INTO MOVIE
+ VALUES ('Logan''s run', 1976, NULL, 'Y', 'Fox', 333);
+
+INSERT INTO MOVIE
+ VALUES ('Star Wars', 1977, 124, 'Y', 'Fox', 555);
+
+INSERT INTO MOVIE
+ VALUES ('Empire Strikes Back', 1980, 111, 'Y', 'Fox', 555);
+
+INSERT INTO MOVIE
+ VALUES ('Star Trek', 1979, 132, 'Y', 'Paramount', 222);
+
+INSERT INTO MOVIE
+ VALUES ('Star Trek: Nemesis', 2002, 116, 'Y', 'Paramount', 123);
+
+INSERT INTO MOVIE
+ VALUES ('Terms of Endearment', 1983, 132, 'Y', 'MGM', 123);
+
+INSERT INTO MOVIE
+ VALUES ('The Usual Suspects', 1995, 106, 'Y', 'MGM', 199);
+
+INSERT INTO MOVIE
+ VALUES ('Gone With the Wind', 1938, 238, 'Y', 'MGM', 123);
+
+------- Insert MovieStar ------------
+
+INSERT INTO MOVIESTAR
+ VALUES ('Jane Fonda', 'Turner Av.', 'F', '1977-07-07');
+
+INSERT INTO MOVIESTAR
+ VALUES ('Alec Baldwin', 'Baldwin Av.', 'M', '1977-07-06');
+
+INSERT INTO MOVIESTAR
+ VALUES ('Kim Basinger', 'Baldwin Av.', 'F', '1979-07-05');
+
+INSERT INTO MOVIESTAR
+ VALUES ('Harrison Ford', 'Prefect Rd.', 'M', '1955-05-05');
+
+INSERT INTO MOVIESTAR
+ VALUES ('Debra Winger', 'A way', 'F', '1978-06-05');
+
+INSERT INTO MOVIESTAR
+ VALUES ('Jack Nicholson', 'X path', 'M', '1949-05-05');
+
+INSERT INTO MOVIESTAR
+ VALUES ('Sandra Bullock', 'X path', 'F', '1948-12-05');
+
+------- Insert StarsIn ------------
+INSERT INTO STARSIN
+ VALUES ('Star Wars', 1977, 'Kim Basinger');
+
+INSERT INTO STARSIN
+ VALUES ('Star Wars', 1977, 'Alec Baldwin');
+
+INSERT INTO STARSIN
+ VALUES ('Star Wars', 1977, 'Harrison Ford');
+
+INSERT INTO STARSIN
+ VALUES ('Empire Strikes Back', 1980, 'Harrison Ford');
+
+INSERT INTO STARSIN
+ VALUES ('The Usual Suspects', 1995, 'Jack Nicholson');
+
+INSERT INTO STARSIN
+ VALUES ('Terms of Endearment', 1983, 'Jane Fonda');
+
+INSERT INTO STARSIN
+ VALUES ('Terms of Endearment', 1983, 'Jack Nicholson');
+
+INSERT INTO STARSIN
+ VALUES ('The Usual Suspects', 1995, 'Sandra Bullock')
+
+-- USE master
+-- GO
+-- if exists (select * from sysdatabases where name='pc')
+ DROP DATABASE pc;
+-- GO
+
+CREATE DATABASE pc;
+-- GO
+USE pc;
+-- GO
+
+CREATE TABLE laptop(
+ code int NOT NULL,
+ model varchar(4) NOT NULL,
+ speed decimal(4, 0) NOT NULL,
+ ram decimal(4, 0) NOT NULL,
+ hd decimal(3, 0) NOT NULL,
+ price float NOT NULL,
+ screen int NOT NULL);
+
+ CREATE TABLE pc(
+ code int NOT NULL ,
+ model varchar(4) NOT NULL ,
+ speed decimal(4, 0) NOT NULL ,
+ ram decimal(4, 0) NOT NULL ,
+ hd decimal(3, 0) NOT NULL ,
+ cd varchar(3) NOT NULL ,
+ price float NOT NULL
+);
+
+CREATE TABLE product(
+ maker char(1) NOT NULL ,
+ model varchar(4) NOT NULL ,
+ type varchar(7) NOT NULL
+);
+
+CREATE TABLE printer(
+ code int NOT NULL ,
+ model varchar(4) NOT NULL ,
+ color char(1) NOT NULL ,
+ type varchar(6) NOT NULL ,
+ price float NOT NULL
+);
+
+ALTER TABLE laptop ADD CONSTRAINT PK_laptop PRIMARY KEY(code);
+
+ALTER TABLE pc ADD CONSTRAINT PK_pc PRIMARY KEY(code);
+
+ALTER TABLE product ADD CONSTRAINT PK_product PRIMARY KEY (model);
+
+ALTER TABLE printer ADD CONSTRAINT PK_printer PRIMARY KEY(code);
+
+ALTER TABLE laptop ADD CONSTRAINT FK_laptop_product FOREIGN KEY(model) REFERENCES product(model);
+
+ALTER TABLE pc ADD CONSTRAINT FK_pc_product FOREIGN KEY(model) REFERENCES product(model);
+
+ALTER TABLE printer ADD CONSTRAINT FK_printer_product FOREIGN KEY(model) REFERENCES product(model);
+
+----Product------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+insert into product values('B','1121','PC');
+insert into product values('A','1232','PC');
+insert into product values('A','1233','PC');
+insert into product values('E','1260','PC');
+insert into product values('A','1276','Printer');
+insert into product values('D','1288','Printer');
+insert into product values('A','1298','Laptop');
+insert into product values('C','1321','Laptop');
+insert into product values('A','1401','Printer');
+insert into product values('A','1408','Printer');
+insert into product values('D','1433','Printer');
+insert into product values('E','1434','Printer');
+insert into product values('B','1750','Laptop');
+insert into product values('A','1752','Laptop');
+insert into product values('E','2111','PC');
+insert into product values('E','2112','PC');
+----PC------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+insert into pc values(1,'1232',500,64,5,'12x',600);
+insert into pc values(2,'1121',750,128,14,'40x',850);
+insert into pc values(3,'1233',500,64,5,'12x',600);
+insert into pc values(4,'1121',600,128,14,'40x',850);
+insert into pc values(5,'1121',600,128,8,'40x',850);
+insert into pc values(6,'1233',750,128,20,'50x',950);
+insert into pc values(7,'1232',500,32,10,'12x',400);
+insert into pc values(8,'1232',450,64,8,'24x',350);
+insert into pc values(9,'1232',450,32,10,'24x',350);
+insert into pc values(10,'1260',500,32,10,'12x',350);
+insert into pc values(11,'1233',900,128,40,'40x',980);
+----Laptop------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+insert into laptop values(1,'1298',350,32,4,700,11);
+insert into laptop values(2,'1321',500,64,8,970,12);
+insert into laptop values(3,'1750',750,128,12,1200,14);
+insert into laptop values(4,'1298',600,64,10,1050,15);
+insert into laptop values(5,'1752',750,128,10,1150,14);
+insert into laptop values(6,'1298',450,64,10,950,12);
+----Printer------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+insert into printer values(1,'1276','n','Laser',400);
+insert into printer values(2,'1433','y','Jet',270);
+insert into printer values(3,'1434','y','Jet',290);
+insert into printer values(4,'1401','n','Matrix',150);
+insert into printer values(5,'1408','n','Matrix',270);
+insert into printer values(6,'1288','n','Laser',400);
+
+-- USE master
+-- GO
+-- if exists (select * from sysdatabases where name='ships')
+ DROP DATABASE ships;
+-- GO
+
+CREATE DATABASE ships;
+-- GO
+USE ships;
+-- GO
+
+----- Tables -----
+CREATE TABLE BATTLES (
+ NAME VARCHAR(20) NOT NULL ,
+ DATE DATETIME NOT NULL
+);
+
+CREATE TABLE CLASSES(
+ CLASS VARCHAR(50) NOT NULL ,
+ TYPE VARCHAR(2) NOT NULL ,
+ COUNTRY VARCHAR (20) NOT NULL ,
+ NUMGUNS INT,
+ BORE REAL,
+ DISPLACEMENT INT
+);
+
+CREATE TABLE SHIPS(
+ NAME VARCHAR(50) NOT NULL ,
+ CLASS VARCHAR(50) NOT NULL ,
+ LAUNCHED INT
+);
+
+CREATE TABLE OUTCOMES(
+ SHIP VARCHAR(50) NOT NULL ,
+ BATTLE VARCHAR(20) NOT NULL ,
+ RESULT VARCHAR(10) NOT NULL
+);
+
+----- Constraints -----
+ALTER TABLE BATTLES ADD CONSTRAINT PK_BATTLES PRIMARY KEY(NAME);
+
+ALTER TABLE CLASSES ADD CONSTRAINT PK_CLASSES PRIMARY KEY(CLASS);
+
+ALTER TABLE SHIPS ADD CONSTRAINT PK_SHIPS PRIMARY KEY(NAME);
+
+ALTER TABLE SHIPS ADD CONSTRAINT FK_SHIPS_CLASSES FOREIGN KEY(CLASS) REFERENCES CLASSES(CLASS);
+
+ALTER TABLE OUTCOMES ADD CONSTRAINT PK_OUTCOMES PRIMARY KEY(SHIP,BATTLE);
+
+ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_BATTLES FOREIGN KEY(BATTLE) REFERENCES BATTLES(NAME);
+
+ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_SHIPS FOREIGN KEY(SHIP) REFERENCES SHIPS(NAME);
+
+----- Classes -----
+INSERT INTO CLASSES
+ VALUES ('Bismarck', 'bb', 'Germany', 8, 15, 42000);
+
+INSERT INTO CLASSES
+ VALUES ('Iowa', 'bb', 'USA', 9, 16, 46000);
+
+INSERT INTO CLASSES
+ VALUES ('Kongo', 'bc', 'Japan', 8, 14, 32000);
+
+INSERT INTO CLASSES
+ VALUES ('North Carolina', 'bb', 'USA', 12, 16, 37000);
+
+INSERT INTO CLASSES
+ VALUES ('Renown', 'bc', 'Gt.Britain', 6, 15, 32000);
+
+INSERT INTO CLASSES
+ VALUES ('Revenge', 'bb', 'Gt.Britain', 8, 15, 29000);
+
+INSERT INTO CLASSES
+ VALUES ('Tennessee', 'bb', 'USA', 12, 14, 32000);
+
+INSERT INTO CLASSES
+ VALUES ('Yamato', 'bb', 'Japan', 9, 18, 65000);
+
+----- Battles -----
+INSERT INTO BATTLES
+ VALUES ('Guadalcanal', '1942-11-15');
+
+INSERT INTO BATTLES
+ VALUES ('North Atlantic', '1941-05-25');
+
+INSERT INTO BATTLES
+ VALUES ('North Cape', '1943-12-26');
+
+INSERT INTO BATTLES
+ VALUES ('Surigao Strait', '1944-10-25');
+
+----- Ships -----
+INSERT INTO SHIPS
+ VALUES ('California', 'Tennessee', 1921);
+
+INSERT INTO SHIPS
+ VALUES ('Haruna', 'Kongo', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Hiei', 'Kongo', 1914);
+
+INSERT INTO SHIPS
+ VALUES ('Iowa', 'Iowa', 1943);
+
+INSERT INTO SHIPS
+ VALUES ('Kirishima', 'Kongo', 1915);
+
+INSERT INTO SHIPS
+ VALUES ('Kongo', 'Kongo', 1913);
+
+INSERT INTO SHIPS
+ VALUES ('Missouri', 'Iowa', 1944);
+
+INSERT INTO SHIPS
+ VALUES ('Musashi', 'Yamato', 1942);
+
+INSERT INTO SHIPS
+ VALUES ('New Jersey', 'Iowa', 1943);
+
+INSERT INTO SHIPS
+ VALUES ('North Carolina', 'North Carolina', 1941);
+
+INSERT INTO SHIPS
+ VALUES ('Ramillies', 'Revenge', 1917);
+
+INSERT INTO SHIPS
+ VALUES ('Renown', 'Renown', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Repulse', 'Renown', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Resolution', 'Renown', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Revenge', 'Revenge', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Royal Oak', 'Revenge', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Royal Sovereign', 'Revenge', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Tennessee', 'Tennessee', 1920);
+
+INSERT INTO SHIPS
+ VALUES ('Washington', 'North Carolina', 1941);
+
+INSERT INTO SHIPS
+ VALUES ('Wisconsin', 'Iowa', 1944);
+
+INSERT INTO SHIPS
+ VALUES ('Yamato', 'Yamato', 1941);
+
+INSERT INTO SHIPS
+ VALUES ('Yamashiro', 'Yamato', 1947);
+
+INSERT INTO SHIPS
+ VALUES ('South Dakota', 'North Carolina', 1941);
+
+INSERT INTO SHIPS
+ VALUES ('Bismarck', 'North Carolina', 1911);
+
+INSERT INTO SHIPS
+ VALUES ('Duke of York', 'Renown', 1916);
+
+INSERT INTO SHIPS
+ VALUES ('Fuso', 'Iowa', 1940);
+
+INSERT INTO SHIPS
+ VALUES ('Hood', 'Iowa', 1942);
+
+INSERT INTO SHIPS
+ VALUES ('Rodney', 'Yamato', 1915);
+
+INSERT INTO SHIPS
+ VALUES ('Yanashiro', 'Yamato', 1918);
+
+INSERT INTO SHIPS
+ VALUES ('Schamhorst', 'North Carolina', 1917);
+
+INSERT INTO SHIPS
+ VALUES ('Prince of Wales', 'North Carolina', 1937);
+
+INSERT INTO SHIPS
+ VALUES ('King George V', 'Iowa', 1942);
+
+INSERT INTO SHIPS
+ VALUES ('West Virginia', 'Iowa', 1942);
+
+----- Outcomes -----
+INSERT INTO OUTCOMES
+ VALUES ('Bismarck', 'North Atlantic', 'sunk');
+
+INSERT INTO OUTCOMES
+ VALUES ('California', 'Surigao Strait', 'ok');
+
+INSERT INTO OUTCOMES
+ VALUES ('Duke of York', 'North Cape', 'ok');
+
+INSERT INTO OUTCOMES
+ VALUES ('Fuso', 'Surigao Strait', 'sunk');
+
+INSERT INTO OUTCOMES
+ VALUES ('Hood', 'North Atlantic', 'sunk');
+
+INSERT INTO OUTCOMES
+ VALUES ('King George V', 'North Atlantic', 'ok');
+
+INSERT INTO OUTCOMES
+ VALUES ('Kirishima', 'Guadalcanal', 'sunk');
+
+INSERT INTO OUTCOMES
+ VALUES ('Prince of Wales', 'North Atlantic', 'damaged');
+
+INSERT INTO OUTCOMES
+ VALUES ('Rodney', 'North Atlantic', 'ok');
+
+INSERT INTO OUTCOMES
+ VALUES ('Schamhorst', 'North Cape', 'sunk');
+
+INSERT INTO OUTCOMES
+ VALUES ('South Dakota', 'Guadalcanal', 'damaged');
+
+INSERT INTO OUTCOMES
+ VALUES ('Tennessee', 'Surigao Strait', 'ok');
+
+INSERT INTO OUTCOMES
+ VALUES ('Washington', 'Guadalcanal', 'ok');
+
+INSERT INTO OUTCOMES
+ VALUES ('West Virginia', 'Surigao Strait', 'ok');
+
+INSERT INTO OUTCOMES
+ VALUES ('Yamashiro', 'Surigao Strait', 'sunk');
+
+INSERT INTO OUTCOMES
+ VALUES ('California', 'Guadalcanal', 'damaged');
|