vrbs5qfH

1drop table IF EXISTS treatmentline
2drop table IF EXISTS consultation
3drop table IF EXISTS treatment 
4drop table IF EXISTS pet
5drop table IF EXISTS species
6drop table IF EXISTS petowner
7go
8create table petowner
9(
10ownerid int primary key identity,
11name varchar(50),
12address varchar(100),
13) 
14create table species
15(
16specieid int primary key identity,
17name varchar(50),
18vaccinterval int,  -- interval in months between routine packages
19) 
20create table pet 
21(
22id int identity primary key,
23name varchar(50) unique,
24birthdate date,
25speciesid int foreign key references species not null,
26petowner int foreign key references petowner not null
27)
28create table treatment 
29(
30treatmentid int identity primary key,
31treatmenttext varchar(100), -- description of the treatment
32price int,
33) 
34 
35create table consultation
36(
37id int primary key identity,
38pet int foreign key references pet not null,
39starttime datetime check(datepart(hour,starttime) >= 8 and datepart(hour,starttime) <= 15 and datepart(mi,starttime) in (0,15,30,45)) not null,
40endtime datetime not null,
41ownerdescription varchar(200), -- the owners description of the reason for the visit
42petdescription varchar(1000),  -- the veterinarians description of what happened 
43payed bit,                     -- 1 if the visit is payed 
44check(datediff(mi,starttime,endtime)=15)
45)
46create table treatmentline
47(
48bookid int foreign key references consultation,
49treatmentid int foreign key references treatment
50primary key(bookid,treatmentid)
51)
52go
53insert into petowner values('hansen','nyvej 3 Svebølle')
54insert into petowner values('jensen','nyvej 5 Svebølle')
55insert into petowner values('olsen','nyvej 7 Svebølle')
56insert into petowner values('andersen','nyvej 9 Svebølle')
57
58insert into species values ('Dog',12),('Cat',12),('Rapid',24)
59
60insert into pet values ('Fido','2016.03.20',1,1),('Misser','2010.01.30',2,1),('Ninus','2017.10.6',3,2),('Trofast','2015.02.21',1,3),('Wildcat','2016.03.20',2,3),('Fatcat','2001.07.20',2,3) 
61
62insert into treatment values('Health + vacc',500),('Vacc small cats',340),('Normal consultation',450),('Routine package',598)
63
64insert into consultation values(1,'2018.4.20 08:45','2018.4.20 09:00','bad stomach','',null)
65insert into consultation values(2,'2018.5.21 09:15','2018.5.21 09:30','Routine','',null)
66insert into consultation values(3,'2018.5.27 13:15','2018.5.27 13:30','just vaccination','',null)
67insert into consultation values(4,'2015.5.27 13:30','2015.5.27 13:45','Routine','',null)
68insert into consultation values(5,'2015.5.27 13:45','2015.5.27 14:00','Routine','',null)
69insert into consultation values(6,'2009.5.27 14:45','2009.5.27 15:00','Routine','',null)
70insert into consultation values(6,'2012.5.27 14:45','2012.5.27 15:00','Routine','',null)
71
72
73insert into treatmentline values(1,1)
74insert into treatmentline values(1,3)
75insert into treatmentline values(2,4)
76insert into treatmentline values(3,1)
77insert into treatmentline values(5,4)
78insert into treatmentline values(6,4)
79go
80
81-----------------------------------------------------------
82-- Extra test data
83insert into petowner values('Someone','Xvej 3 Aarhus') -- id = 5
84
85insert into pet values ('Random dog','2010.10.6',1,5)
86insert into consultation values(7,'2010.11.20 08:45','2010.11.20 09:00','Routine','',null)
87insert into treatmentline values(8,4)
88
89insert into pet values ('Random dog 2','2011.02.21',1,1)
90insert into consultation values(8,'2010.12.20 09:15','2010.12.20 09:30','Routine','',null)
91insert into treatmentline values(9,4)
92
93
94insert into pet values ('Random cat','2012.03.20',2,5)
95insert into consultation values(9,'2009.3.27 13:15','2009.3.27 13:30','Routine','',null)
96insert into treatmentline values(10,1)
97
98insert into pet values ('Random cat 2','2013.01.30',2,2)
99insert into consultation values(10,'2018.4.27 13:30','2018.4.27 13:45','Bad stomach','',null)
100insert into treatmentline values(11,4)
101
102insert into pet values ('Random rabit','2014.03.20',3,3)
103insert into consultation values(11,'2012.2.27 13:45','2012.2.27 14:00','Routine + consultation','',null)
104insert into treatmentline values(12,4)
105insert into treatmentline values(12,3)