search

idunsollie / DB1100

DB1100 - Databases and MySQL
0 stars 0 forks source link

#2 Datascript for Happy Paws AB #2

Open idunsollie opened 4 years ago

idunsollie commented 4 years ago

Datascript Happy Paws AB.txt

idunsollie commented 4 years ago

The .txt file contains:

CREATE TABLE Address ( PostCode INT(4), Address VARCHAR(150),

CONSTRAINT PK_Address PRIMARY KEY(PostCode) ); CREATE TABLE Owner ( ID INT AUTO_INCREMENT, Name VARCHAR(150) NOT NULL DEFAULT 'Missing Name', Email VARCHAR(150), PhoneNr INT(8) NOT NULL, PostCode_Address INT(4),

CONSTRAINT PK_Owner PRIMARY KEY(ID), CONSTRAINT FK_Owner_Address FOREIGN KEY (PostCode_Address) REFERENCES Address(PostCode) );

CREATE TABLE Dog ( ID INT AUTO_INCREMENT, Name VARCHAR(50) NOT NULL DEFAULT 'Missing Name', Race VARCHAR(100), Sex ENUM('B','G'), Age INT(2), ID_Owner INT(4),

CONSTRAINT PK_Dog PRIMARY KEY(ID), CONSTRAINT FK_Dog_Owner FOREIGN KEY (ID_Owner) REFERENCES Owner(ID) );

CREATE TABLE Operation ( Nr INT AUTO_INCREMENT, Name VARCHAR(100) NOT NULL DEFAULT 'Missing Name', EstPrice FLOAT(7,2), EstTime TIME,

CONSTRAINT PK_Operation PRIMARY KEY(Nr) );

CREATE TABLE Surgeon ( EmpNr INT(5) NOT NULL, Name VARCHAR(150), Specialty VARCHAR(50),

CONSTRAINT PK_Surgeon PRIMARY KEY(EmpNr) );

CREATE TABLE Dog_Operation ( OperationID INT AUTO_INCREMENT, ID_Dog INT(4), Nr_Operation INT(4), TotalPrice FLOAT(7,2), TotalTime TIME,

CONSTRAINT PK_Dog_Operation PRIMARY KEY(OperationID), CONSTRAINT FK_Dog_Operation_Dog FOREIGN KEY (ID_Dog) REFERENCES Dog(ID), CONSTRAINT FK_Dog_Operation_Operation FOREIGN KEY (Nr_Operation) REFERENCES Operation(Nr) );

CREATE TABLE Operation_Surgeon ( Nr INT AUTO_INCREMENT, EmpNr_Surgeon INT(5) NOT NULL, Nr_Operation INT, Date DATE,

CONSTRAINT PK_Operation_Surgeon PRIMARY KEY(Nr), CONSTRAINT FK_Operation_Surgeon_Surgeon FOREIGN KEY (EmpNr_Surgeon) REFERENCES Surgeon(EmpNr), CONSTRAINT FK_Operation_Surgeon_Operation FOREIGN KEY (Nr_Operation) REFERENCES Operation(Nr) );

INSERT INTO Address (PostCode, Address) VALUES (0853, 'Linnévägen 2'), (0873, 'Solvägen 43'), (0585, 'Storgatan 7A'), (0823, 'Skeda Allé 21'), (0678, 'St Larsgatan 8C');

INSERT INTO Owner (Name, Email, PhoneNr, PostCode_Address) VALUES ('Iselin Sørum', 'Iselin@hotmail.com', 45362785, 0853), ('Nils Nilsson', 'Nils@hotmail.com', 34254326, 0873), ('Linus Svensson', 'Linus@hotmail.com', 56783454, 0585), ('Marius Hansen', 'Marius@hotmail.com', 98675839, 0823), ('Sven Nilsson', 'Sven@hotmail.com', 23431255, 0678);

INSERT INTO Dog (Name, Race, Sex, Age, ID_Owner) VALUES ('Jojjo', 'Pudel', 'G', 2, 1), ('Milla', 'Tax', 'G', 5, 2), ('Wilma', 'Bulldog', 'G', 9, 3), ('Nils', 'Tax', 'B', 11, 3), ('Mosse', 'Jack Russel', 'B', 1, 4), ('Musse', 'Tax', 'B', 5, 5), ('Cissi', 'Jack Russel', 'G', 8, 5);

INSERT INTO Operation (Name, EstPrice, EstTime) VALUES ('Magomvridning', 27998.00, 11500), ('Benfraktur', 17000.00, 21000), ('Korsbandskada', 12799.00, 12000), ('Diskbrock', 8497.00, 2000);

INSERT INTO Surgeon (EmpNr, Name, Specialty) VALUES (11001, 'Karl Adamsson', 'Dermatologi'), (11002, 'Harry Isaksson', 'Kardiologi'), (11003, 'Elina Smith', 'Neurologi'), (11004, 'Zoe Holm', 'Odontologi');

INSERT INTO Dog_Operation(ID_Dog, Nr_Operation, TotalPrice, TotalTime) VALUES (2, 1, 34599.00, 23700), (6, 1, 36599.00, 24740), (7, 2, 18559.00, 14210), (4, 4, 10014.00, 5308), (7, 4, 11359.00, 10217);

INSERT INTO Operation_Surgeon(EmpNr_Surgeon, Nr_Operation, Date) VALUES (11002,1,20191001), (11002,1,20191014), (11001,2,20191031), (11004,4,20191107), (11004,4,20191121);