xCSUc5u9

1drop database if exists kat_db;
2create database if not exists kat_db;
3use kat_db;
4create table if not exists Patients(
5PatientID int not null PRIMARY KEY,
6PatientName varchar(50),
7TypeOfAnimal varchar(50),
8DateOfLastApt date,
9PatientAge int,
10Microchipped boolean,
11OwnerID int not null,
12DoctorID int not null,
13foreign key (OwnerID) references Owners(OwnerID),
14foreign key (DoctorID) references Doctors(DoctorID)
15);
16
17create table if not exists Owners(
18OwnerID int not null PRIMARY KEY,
19OwnerFirstName varchar(50),
20OwnerLastName varchar(50),
21PatientID int,
22OwnerPhone int,
23OwnerZip int,
24PrefferedPaymentMethod varchar(50),
25foreign key (PatientID) references Patients(PatientID)
26);
27
28create table if not exists Doctors(
29DoctorID int not null PRIMARY KEY,
30DoctorFirstName varchar(50),
31DoctorLastName varchar(50),
32PatientID int,
33DoctorPhone int,
34DoctorZip int,
35AnimalSpecialty varchar(50),
36foreign key (PatientID) references Patients(PatientID)
37);
38
39insert into Patients
40(PatientID, TypeOfAnimal, PatientAge, PatientName, DateOfLastApt, Microchipped, DoctorID, OwnerID)
41values 
42(01, 'dog', 6, 'Dima', '2017-02-05', 1, 101, 1001),
43(02, 'dog', 3, 'Misha', '2017-02-05', 1, 102, 1002),
44(03, 'cat', 2, 'Pistol', '2017-06-09', 0, 103, 1003),
45(04, 'cat', 1, 'Nessie', '2017-09-04', 0, 104, 1004),
46(05, 'cat', 2, 'Charlie', '2016-06-04', 1, 105, 1005),
47(06, 'cat', 12, 'Francis', '2015-05-07', 1, 106, 1006),
48(07, 'rabbit', 5, 'Bunny', '2015-05-06', 0, 107, 1007),
49(08, 'turtle', 6, 'Aqua', '2017-06-08', 0, 108, 1008),
50(09, 'dog', 16, 'Sammie', '2012-09-07', 1, 109, 1009),
51(10, 'dog', 14, 'Dog', '2016-04-06', 0, 110, 1010)
52;
53
54insert into Owners
55(OwnerID, OwnerFirstName, OwnerLastName, OwnerPhone, OwnerZip, PrefferedPaymentMethod, PatientID)
56values 
57(101, 'Kat', 6, 'Dima', '2017-02-05', 1, 101, 1001),
58(102, 'Hunter', 3, 'Misha', '2017-02-05', 1, 102, 1002),
59(103, 'Vicky', 2, 'Pistol', '2017-06-09', 0, 103, 1003),
60(104, 'Vasnessa', 1, 'Nessie', '2017-09-04', 0, 104, 1004),
61(105, 'Weston', 2, 'Charlie', '2016-06-04', 1, 105, 1005),
62(106, 'Jonas', 12, 'Francis', '2015-05-07', 1, 106, 1006),
63(107, 'Devin', 5, 'Bunny', '2015-05-06', 0, 107, 1007),
64(108, 'Grego', 6, 'Aqua', '2017-06-08', 0, 108, 1008),
65(109, 'Jackson', 16, 'Sammie', '2012-09-07', 1, 109, 1009),
66(110, 'dog', 14, 'Dog', '2016-04-06', 0, 110, 1010)
67;
68
69insert into Patients
70(PatientID, TypeOfAnimal, PatientAge, PatientName, DateOfLastApt, Microchipped, DoctorID, OwnerID)
71values 
72(01, 'dog', 6, 'Dima', '2017-02-05', 1, 101, 1001),
73(02, 'dog', 3, 'Misha', '2017-02-05', 1, 102, 1002),
74(03, 'cat', 2, 'Pistol', '2017-06-09', 0, 103, 1003),
75(04, 'cat', 1, 'Nessie', '2017-09-04', 0, 104, 1004),
76(05, 'cat', 2, 'Charlie', '2016-06-04', 1, 105, 1005),
77(06, 'cat', 12, 'Francis', '2015-05-07', 1, 106, 1006),
78(07, 'rabbit', 5, 'Bunny', '2015-05-06', 0, 107, 1007),
79(08, 'turtle', 6, 'Aqua', '2017-06-08', 0, 108, 1008),
80(09, 'dog', 16, 'Sammie', '2012-09-07', 1, 109, 1009),
81(10, 'dog', 14, 'Dog', '2016-04-06', 0, 110, 1010)
82;
83	
84drop database kat3_db;
85create database if not exists kat3_db;
86use kat3_db;
87create table if not exists Book(
88BookNumber int not null,
89BookName varchar(50),
90BookPrice decimal(5,2),
91CoverType varchar(15) default 'hardcover',
92PublicationDate date,
93primary key (BookNumber)
94);
95
96create table if not exists Course(
97CourseNo int not null,
98CourseName varchar(20),
99Semester varchar(10),
100BookNumber int not null,
101primary key (CourseNo),
102foreign key (BookNumber) references Book(BookNumber)
103);
104
105insert into Book
106(BookNumber, BookName, BookPrice, CoverType, PublicationDate)
107values 
108(1, 'Math', 023.64, 'paperback', '1999-02-05'),
109(3, 'English', 999.36, 'e-book', '2013-06-04'),
110(5, 'Calc', 056.69, 'paperback', '1964-05-05'),
111(6, 'C++', 053.98, 'paperback', '2016-03-04'),
112(7, 'Programming', 113.02, 'paperback', '2001-10-11'),
113(9, 'Art', 250.99, 'paperback', '1996-11-11'),
114(10, 'Networking', 036.64, 'e-book', '2014-05-06'),
115(12, 'Drawing', 111.36, 'paperback', '2013-06-04'),
116(13, 'Robotics', 012.03, 'e-book', '2016-06-05'),
117(14, 'Computer', 25.03, 'e-book', '2001-06-04')
118;
119
120insert into Book
121(BookNumber, BookName, BookPrice, PublicationDate)
122values
123(2, 'Reading', 364.20, '2016-06-05'),
124(4, 'Database', 036.64, '2017-06-05'),
125(8, 'Wellness', 050.00, '1999-10-10'),
126(11, 'Linux', 55.36, '2013-06-08'),
127(15, 'FYE', 03.01, '1991-02-05')
128;
129
130insert into Course
131(CourseNo, CourseName, Semester, BookNumber)
132values
133(111, 'IntroToMath', 'Spring', 1),
134(222, 'IntroToReading', 'Fall', 2),
135(333, 'IntroToEnglish', 'Spring', 3),
136(444, 'IntroToDatabase', 'Fall', 4),
137(555, 'IntroToCalc', 'Spring', 5),
138(666, 'IntroToC++', 'Spring', 6),
139(777, 'IntroToProgramming', 'Fall', 7),
140(888, 'IntroToWellness','Spring', 8),
141(999, 'IntroToArt', 'Fall', 9),
142(010, 'IntroToNetworking', 'Spring', 10),
143(011, 'IntroToLinux', 'Fall', 11),
144(012, 'IntroToDrawing', 'Spring', 12),
145(013, 'IntroToRobotics', 'Fall', 13),
146(014, 'IntroToComputer', 'Spring', 14),
147(015, 'IntroToFYE', 'Summer', 15)
148;