· 8 years ago · Mar 21, 2018, 11:58 AM
1CREATE DATABASE IF NOT EXISTS 0408982209_GAGN2HS05BU_Skilaverkefni_2;
2USE 0408982209_GAGN2HS05BU_Skilaverkefni_2;
3
4CREATE TABLE IF NOT EXISTS employee (
5 EMP_NUM INT NOT NULL AUTO_INCREMENT,
6 EMP_LNAME VARCHAR(30),
7 EMP_FNAME VARCHAR(30),
8 EMP_INITIAL VARCHAR(10),
9 EMP_HIREDATE DATE,
10 JOB_CODE INT NOT NULL, # FK
11 EMP_YEARS INT,
12 PRIMARY KEY (EMP_NUM),
13 FOREIGN KEY (JOB_CODE) REFERENCES job (JOB_CODE)
14);
15
16CREATE TABLE IF NOT EXISTS job (
17 JOB_CODE INT NOT NULL AUTO_INCREMENT,
18 JOB_DESCRIPTION VARCHAR(50),
19 JOB_CHG_HOUR FLOAT,
20 JOB_LAST_UPDATE DATE,
21 PRIMARY KEY (JOB_CODE)
22);
23
24CREATE TABLE IF NOT EXISTS assignment (
25 ASSIGN_NUM INT NOT NULL AUTO_INCREMENT,
26 ASSIGN_DATE DATE,
27 PROJ_NUM INT NOT NULL, # FK
28 EMP_NUM INT NOT NULL, # FK
29 ASSIGN_JOB INT,
30 ASSIGN_CHG_HR FLOAT,
31 ASSIGN_HOURS FLOAT,
32 ASSIGN_CHARGE FLOAT,
33 PRIMARY KEY (ASSIGN_NUM),
34 FOREIGN KEY (PROJ_NUM) REFERENCES project (PROJ_NUM),
35 FOREIGN KEY (EMP_NUM) REFERENCES employee (EMP_NUM)
36);
37
38CREATE TABLE IF NOT EXISTS project (
39 PROJ_NUM INT NOT NULL AUTO_INCREMENT,
40 PROJ_NAME VARCHAR(50),
41 PROJ_VALUE FLOAT,
42 PROJ_BALANCE FLOAT,
43 EMP_NUM INT NOT NULL, # FK
44 PRIMARY KEY (PROJ_NUM),
45 FOREIGN KEY (EMP_NUM) REFERENCES employee (EMP_NUM)
46);
47
48INSERT INTO employee VALUES
49 (101, 'News', 'John', 'G', '2000-11-08', 502, 4),
50 (102, 'Senior', 'David', 'H', '1989-07-12', 501, 15),
51 (103, 'Arbough', 'June', 'E', '1996-12-01', 503, 8),
52 (104, 'Ramoras', 'Anne', 'K', '1987-11-15', 501, 17),
53 (105, 'Johnson', 'Alice', 'K', '1993-02-01', 502, 12),
54 (106, 'Smithfield', 'William', '', '2004-06-22', 500, 0),
55 (107, 'Alonzo', 'Maria', 'D', '1993-10-10', 500, 11),
56 (108, 'Washington', 'Ralph', 'B', '1991-08-22', 501, 13),
57 (109, 'Smith', 'Larry', 'W', '1997-07-18', 501, 7),
58 (110, 'Olenko', 'Gerald', 'A', '1995-12-11', 505, 9),
59 (111, 'Wabash', 'Geoff', 'B', '1991-04-04', 506, 14),
60 (112, 'Smithson', 'Darlene', 'M', '1994-10-23', 507, 10),
61 (113, 'Joenbrood', 'Delbert', 'K', '1996-11-15', 508, 8),
62 (114, 'Jones', 'Annelise', '', '1993-08-20', 508, 11),
63 (115, 'Bawangi', 'Travis', 'B', '1992-01-25', 501, 13),
64 (116, 'Pratt', 'Gerald', 'L', '1997-03-05', 510, 8),
65 (117, 'Williamson', 'Angie', 'H', '1996-06-19', 509, 8),
66 (118, 'Frommer', 'James', 'J', '2005-01-04', 510, 0);
67
68INSERT INTO job VALUES
69 (500, 'Programmer', 35.75, '2006-11-20'),
70 (501, 'System Analyst', 96.75, '2006-11-20'),
71 (502, 'Database Designer', 125.00, '2007-03-24'),
72 (503, 'Electrical Engineer', 84.50, '2007-11-20'),
73 (504, 'Mechanical Engineer', 67.90, '2007-11-20'),
74 (505, 'Civil Engineer', 55.78, '2007-11-20'),
75 (506, 'Clerical Support', 26.87, '2007-11-20'),
76 (507, 'DSS Analyst', 45.95, '2007-11-20'),
77 (508, 'Applications Designer', 48.10, '2007-03-24'),
78 (509, 'Bio Technician', 34.55, '2006-11-20'),
79 (510, 'General Support', 18.36, '2006-11-20');
80
81INSERT INTO assignment VALUES
82 (1001, '2007-03-22', 18, 103, 503, ),
83 (1002, '2007-03-22', 22, 117, 509, ),
84 (1003, '2007-03-22', 18, 117, 509, ),
85 (1004, '2007-03-22', 18, 103, 503, ),
86 (1005, '2007-03-22', 25, 108, 501, ),
87 (1006, '2007-03-22', 22, 104, 501, ),
88 (1007, '2007-03-22', 25, 113, 508, ),
89 (1008, '2007-03-22', 18, 103, 503, ),
90 (1009, '2007-03-22', 15, 115, 501, ),
91 (1010, '2007-03-22', 15, 117, 509, ),
92 (1011, '2007-03-22', 25, 105, 502, ),
93 (1012, '2007-03-22', 18, 108, 501, ),
94 (1013, '2007-03-22', 25, 115, 501, ),
95 (1014, '2007-03-22', 22, 104, 501, ),
96 (1015, '2007-03-22', 15, 103, 503, ),
97 (1016, '2007-03-22', 22, 105, 502, ),
98 (1017, '2007-03-22', 18, 117, 509, ),
99 (1018, '2007-03-22', 25, 117, 509, ),
100 (1019, '2007-03-22', 25, 104, 501, ),
101 (1020, '2007-03-22', 15, 101, 502, ),
102 (1021, '2007-03-22', 22, 108, 501, ),
103 (1022, '2007-03-22', 22, 115, 501, ),
104 (1023, '2007-03-22', 22, 105, 502, ),
105 (1024, '2007-03-22', 15, 103, 503, ),
106 (1025, '2007-03-22', 18, 117, 509, );
107
108INSERT INTO project VALUES
109 (15, 'Evergreen', 1453500.00, 1002350.00, 103),
110 (18, 'Amber Wave', 3500500.00, 2110346.00, 108),
111 (22, 'Rolling Tide', 805000.00, 500345.20, 102),
112 (25, 'Starflight', 2650500.00, 2309880.00, 107);