CREATE TABLE ZipCodes
(ZipCode varchar(10) not null,
City varchar(50) not null,
State varchar(2) not null,
CONSTRAINT PK_ZipCodes PRIMARY KEY (ZipCode))
Go
CREATE TABLE Specialties
(SpecialtyID integer not null,
SpecialtyName varchar(99) not null,
CONSTRAINT PK_Specialties PRIMARY KEY (SpecialtyID))
Go
CREATE TABLE Practices
(PracticeID integer not null,
PracticeName varchar(99) not null,
Address varchar(50) not null,
ZipCode varchar(10) not null,
Phone varchar(14) not null,
Fax varchar(14) not null,
email varchar(99) not null,
CONSTRAINT PK_Practices PRIMARY KEY (PracticeID),
CONSTRAINT FK_Practices_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes)
Go
CREATE TABLE Doctors
(DoctorID integer not null,
FirstName varchar(50) not null,
LastName varchar(50) not null,
PracticeID integer not null,
SpecialtyID integer not null,
Email varchar(99) not null,
CONSTRAINT PK_Doctors PRIMARY KEY (DoctorID),
CONSTRAINT FK_Doctors_Practices FOREIGN KEY (PracticeID) REFERENCES Practices,
CONSTRAINT FK_Doctors_Specialities FOREIGN KEY (SpecialtyID) REFERENCES
Specialties)
go
CREATE TABLE Patients
(PatientID integer not null,
FirstName varchar(50) not null,
LastName varchar(50) not null,
Address varchar(99) not null,
ZipCode varchar(10) not null,
PrimaryPhone varchar(14) not null,
AlternatePhone varchar(14) not null,
Email varchar(99),
CONSTRAINT PK_Patients PRIMARY KEY (PatientID))
go
CREATE TABLE Referrals
(ReferralID integer not null,
StartDate date not null,
EndDate date not null,
PatientID integer not null,
DoctorID integer not null,
CONSTRAINT PK_Referrals PRIMARY KEY (ReferralID),
CONSTRAINT FK_Referrals_Patients FOREIGN KEY (PatientID) REFERENCES Patients,
CONSTRAINT FK_Referrals_Doctors FOREIGN KEY (DoctorID) REFERENCES Doctors)
go
CREATE TABLE Services
(ServiceID integer not null,
ServiceName varchar(50) not null,
CONSTRAINT PK_ServiceID PRIMARY KEY (ServiceID))
Go
CREATE TABLE Supplies
(SupplyID integer not null,
SupplyDescription varchar(40) not null,
Quantity integer,
CONSTRAINT PK_Supplies PRIMARY KEY (SupplyID))
Go
CREATE TABLE PaymentTypes
(PaymentID integer not null,
PaymentType varchar(50) not null,
CONSTRAINT PK_PaymentTypes PRIMARY KEY (PaymentID))
Go
CREATE TABLE InsuranceCompanies
(InsuranceID integer not null,
InsuranceCompany varchar(50) not null,
Phone varchar(15) not null,
Fax varchar(15) not null,
Email varchar(50) not null,
CONSTRAINT PK_InsuranceCompanies PRIMARY KEY (InsuranceID))
Go
CREATE TABLE Contracts
(ContractID integer not null,
StartDate date not null,
EndDate date not null,
PaymentID integer not null,
ReferralID integer not null,
InsuranceID integer,
Rate varchar(15),
CONSTRAINT PK_Contracts PRIMARY KEY (ContractID),
CONSTRAINT PK_Contracts_Referrals FOREIGN KEY (ReferralID) REFERENCES Referrals,
CONSTRAINT FK_Contracts_PaymentTypes FOREIGN KEY (PaymentID) REFERENCES
PaymentTypes,
CONSTRAINT FK_Contracts_InsuranceCompanies FOREIGN KEY (InsuranceID) REFERENCES
InsuranceCompanies)
go
CREATE TABLE ServicesNeeded
(ServiceID integer not null,
ContractID integer not null,
CONSTRAINT PK_ServicesNeeded PRIMARY KEY (ServiceID, ContractID),
CONSTRAINT FK_ServicesNeeded_Services FOREIGN KEY (ServiceID) references
Services,
CONSTRAINT FK_ServicesNeeded_Contracts FOREIGN KEY (ContractID) references
Contracts)
Go
CREATE TABLE SuppliesNeeded
(SupplyID integer not null,
ContractID integer not null,
CONSTRAINT PK_SuppliesNeeded PRIMARY KEY (SupplyID, ContractID),
CONSTRAINT FK_SuppliesNeeded_Supplies FOREIGN KEY (SupplyID) references Supplies,
CONSTRAINT FK_SuppliesNeeded_Contracts FOREIGN KEY (ContractID) references
Contracts)
Go
CREATE TABLE EmployeeTypes
(EmployeeTypeID integer not null,
EmployeeType varchar(50) not null,
CONSTRAINT PK_EmployeeTypes PRIMARY KEY (EmployeeTypeID))
Go
CREATE TABLE SkillLevels
(SkillLevelID integer not null,
SkillLevel varchar(50) not null,
CONSTRAINT PK_EmployeeSkillLevels PRIMARY KEY (SkillLevelID))
Go
CREATE TABLE Employees
(EmployeeID integer not null,
FirstName varchar(30) not null,
LastName varchar(50) not null,
Address varchar(99) not null,
ZipCode varchar(10) not null,
PrimaryPhone varchar(14) not null,
AltPhone varchar(14) not null,
Email varchar(50),
HourlyWage varchar(20),
Salary varchar(20),
EmployeeTypeID integer not null,
SkillLevelID integer not null,
CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID),
CONSTRAINT FK_Employee_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes,
CONSTRAINT FK_Employees_EmployeeTypes FOREIGN KEY (EmployeeTypeID) REFERENCES
EmployeeTypes,
CONSTRAINT FK_Employees_SkillLevels FOREIGN KEY (SkillLevelID) REFERENCES
SkillLevels)
Go
CREATE TABLE Shifts
(ShiftID integer not null,
ShiftName varchar(20),
StartTime time not null,
EndTime time not null,
CONSTRAINT PK_Shifts PRIMARY KEY (ShiftID))
Go
CREATE TABLE Availability
(AvailabilityID integer not null,
EmployeeID integer not null,
ShiftID integer not null,
DayOfWeek varchar(10) not null,
CONSTRAINT PK_Availability PRIMARY KEY (AvailabilityID, EmployeeID, ShiftID),
CONSTRAINT FK_Availability_Employees FOREIGN KEY (EmployeeID) REFERENCES
Employees,
CONSTRAINT FK_Availability_Shifts FOREIGN KEY (ShiftID) REFERENCES Shifts)
Go
CREATE TABLE Visits
(VisitID integer not null,
VisitDate date not null,
StartTime varchar(5) not null,
EndTime varchar(5) not null,
EmployeeID integer not null,
PatientID integer not null,
CONSTRAINT PK_Visits PRIMARY KEY (VisitID),
CONSTRAINT FK_Visits_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees,
CONSTRAINT FK_Visits_Patients FOREIGN KEY (PatientID) REFERENCES Patients)
go
