create database DoctorDB

use DoctorDB

create table Person(
PId				int				primary key identity,
PFName			varchar(15)		not null,
PLName			varchar(15)		not null,
StreetAddress	varchar(50),
City			varchar(15),
State			nchar(2)		check (State like '[A-Z][A-Z]'),
Zip				int				check (Zip like '[0-9][0-9][0-9][0-9][0-9]'),
PhoneNumber		int);

create table Doctor(
DoctorID		int				primary key identity,
PId				int				references Person (PId));

create table Patient(
PatientID		int				primary key identity,
PId				int				references Person (PId),
SecPhoneNumber	int,
DOB				date);

create table PatientVisit(
VisitID			int				primary key identity,
PatientID		int				references Patient (PatientID),
DoctorID		int				references Doctor (DoctorID),
VisitDate		date,
DocNote			varchar(300));

create table DocSpeciality(
DoctorID		int				references Doctor (DoctorID),
SpcID			int				references Specialty (SpcID),
primary key (DoctorID, SpcID));

create table Specialty(
SpcID			int				primary key identity,
SpecialtyName	varchar(40));

create table PVisitTest(
VisitID			int				primary key references PatientVisit (VisitID),
TestID			int				references Test (TestID));

create table Test(
TestID			int				primary key identity,
TestName		varchar(40));

create table PVisitPrescription(
VisitID			int				primary key references PatientVisit (VisitID),
PrescriptionID	int);

create table Prescription(
PrescriptionID	int				primary key identity,
PrescriptionName	varchar(40));