VHmD5FQc

1-- remove (if exists), create, and use "DogKennel" database
2DROP DATABASE IF EXISTS DogKennel;
3
4CREATE DATABASE DogKennel;
5
6USE DogKennel;
7
8-- remove (if exists) and create the "Dog" table
9DROP TABLE IF EXISTS `DOG`;
10CREATE TABLE DOG(
11    DogID INT(11) PRIMARY KEY,
12    DogName VARCHAR(32) NOT NULL,
13    Breed VARCHAR(32) NOT NULL,
14    Gender VARCHAR(32) NOT NULL,
15    Shots TINYINT NOT NULL,
16    DateOfBirth datetime NOT NULL,
17    DogStatus VARCHAR(255) NOT NULL,
18    FatherID INT(11) NULL,
19    MotherID INT(11) NULL
20);
21
22ALTER TABLE `DOG`
23    DROP CONSTRAINT IF EXISTS FK__DOG__FatherID;
24
25ALTER TABLE `DOG`
26    ADD CONSTRAINT FK__DOG__FatherID
27    FOREIGN KEY (FatherID)
28    REFERENCES DOG (FatherID);
29
30ALTER TABLE `DOG`
31    DROP CONSTRAINT IF EXISTS FK__DOG__MotherID;
32
33ALTER TABLE `DOG`
34    ADD CONSTRAINT FK__DOG__MotherID
35    FOREIGN KEY (MotherID)
36    REFERENCES DOG (MotherID);
37
38-- remove (if exists) and create "CLIENT" table
39DROP TABLE IF EXISTS `CLIENT`;
40CREATE TABLE CLIENT
41(
42    ClientID      INT(11)     NOT NULL,
43    ClientName    VARCHAR(32) NOT NULL,
44    ClientAddress VARCHAR(80) NOT NULL,
45    PhoneNum      VARCHAR(32) NOT NULL
46
47);
48
49-- remove (if exists) and create "PURCHASE" table
50DROP TABLE IF EXISTS `PURCHASE`;
51CREATE TABLE PURCHASE(
52    PurchaseID INT(11) NOT NULL,
53    DateOfPurchase DATE NOT NULL,
54    TimeOfPurchase TIME NOT NULL,
55    Subtotal FLOAT NOT NULL,
56    Total FLOAT NOT NULL,
57    ClientID INT(11) NOT NULL,
58    DogID INT(11) NOT NULL
59
60);
61
62ALTER TABLE `PURCHASE`
63    DROP CONSTRAINT IF EXISTS FK__CLIENT__ClientID;
64
65ALTER TABLE `PURCHASE`
66    ADD CONSTRAINT FK__CLIENT__ClientID
67    FOREIGN KEY (ClientID)
68    REFERENCES CLIENT (ClientID);
69
70ALTER TABLE `PURCHASE`
71    DROP CONSTRAINT IF EXISTS FK__DOG__DogID;
72
73ALTER TABLE `PURCHASE`
74    ADD CONSTRAINT FK__DOG__DogID
75    FOREIGN KEY (DogID)
76    REFERENCES CLIENT (DogID);