P2cbkWSV

1/*
2This script creates tables for the "Small company" case database, and inserts -- test data in all tables
3*/
4-- ------------------------------------------------------------------
5-- Drop table in case it exist in the database
6DROP TABLE IF EXISTS DEPARTMENT;
7 
8-- Table structure
9CREATE TABLE DEPARTMENT (
10  DepID int NOT NULL,
11  Name varchar(16) NOT NULL,
12  Code char(2) NOT NULL,
13  PRIMARY KEY (DepID)
14);
15
16-- Inserting new records for table
17INSERT INTO DEPARTMENT VALUES
18(1, 'IT', 'IT'),
19(2, 'Finance', 'FI'),
20(3, 'Production', 'PR'),
21(4, 'Sales', 'SA');
22-- -----------------------------------------------------------------------
23--
24-- Drop table in case it exist in the database
25DROP TABLE IF EXISTS PERSON; 
26
27-- Table structure
28CREATE TABLE PERSON (
29  PersonID char(4) NOT NULL,
30  Department int,
31  FName varchar(16) NOT NULL,
32  LName varchar(32) NOT NULL,
33  City varchar(16),
34  Salary int,
35  Tax decimal (3,1),
36  SDate date,
37  PRIMARY KEY (PersonID),
38  FOREIGN KEY (Department) REFERENCES DEPARTMENT(DepID)
39);
40
41-- Inserting new records for table
42INSERT INTO PERSON VALUES
43('2222',3,'Mikko','Nieminen','Turku',2900,24.2,'2015-03-01'),
44('2233',1,'Hannu','Virta','Turku',3100,32.3,'2005-10-10'),
45('2234',4,'Liisa','Lehtipuu','Kerava',3200,33.0,'2004-12-1'),
46('2333',3,'Katja','Kataja','Vantaa',2300,19.4,'2015-05-01'),
47('2345',NULL,'Keijo','Kuusi','Kerava',2580,24.0,'2010-08-12'),
48('3567',1,'Kari','Mänty','Helsinki',2650,22.3,'2007-09-15'),
49('3568',3,'Hannu','Haapanen','Helsinki',3400,35.1,'2003-05-10');
50-- -----------------------------------------------------------------------
51--
52-- Drop table in case it exist in the database
53DROP TABLE IF EXISTS PROJECT; 
54
55-- Table structure
56CREATE TABLE PROJECT (
57  ProjID varchar(4) NOT NULL,
58  Name varchar(32) NOT NULL,
59  Priority smallint,
60  Location varchar(32),
61  PRIMARY KEY (ProjID)
62);
63
64-- Inserting new records for table
65INSERT INTO PROJECT VALUES
66('P1', 'Electronic billing', 2, 'Turku'),
67('P2', 'Reporting upgrade', 1, 'Kerava'),
68('P3', 'Statistics report', NULL, NULL),
69('P4', 'Personnel training', 2, 'Turku'),
70('P5', 'Customer service', 3, 'Joensuu'),
71('P6', 'SAP implementation', 1, 'Helsinki');
72-- -----------------------------------------------------------------------
73--
74-- Drop table in case it exist in the database
75DROP TABLE IF EXISTS PROJECT_PERSON; 
76
77-- Table structure
78CREATE TABLE PROJECT_PERSON (
79  Project varchar(4) NOT NULL,
80  Person char(4) NOT NULL,
81  HoursActual int,
82  HoursPlanned int,
83  PRIMARY KEY (Project,Person),
84  FOREIGN KEY (Project) REFERENCES PROJECT(ProjID),
85  FOREIGN KEY (Person) REFERENCES PERSON(PersonID)   
86);
87
88-- Inserting new records for table
89INSERT INTO PROJECT_PERSON VALUES
90('P1', '2222', 300, 300),
91('P1', '2233', 150, 200),
92('P1', '2333', 200, 200),
93('P1', '2345', 100, 100),
94('P2', '2222', 0, 100),
95('P4', '2333', 150, NULL),
96('P4', '2345', 200, 250),
97('P4', '3567', 300, 200),
98('P5', '2233', 500, 600),
99('P5', '3567', 200, 200),
100('P5', '2222', 10, 200);
101-- -----------------------------------------------------------------------