Let's start building our database project by creating the necessary tables. Firstly, we create a database projectone and then we use it to create the tables.

Our first table is PhysicianSpecialties, which is used to store the different medical specialties of physicians. This table contains an ID for each specialty and its corresponding name. We define SpecialtyID as an integer and SpecialtyName as a varchar with a maximum length of 50 characters. For the primary key, we set SpecialtyID as the constraint using pk_PhysicianSpecialties.

Our second table is ZipCodes, which contains the zip code, city, and state information of different areas. The table is defined to hold zip codes as a varchar with a maximum length of 10 characters, city as a varchar with a maximum length of 50 characters, and state as a varchar with a maximum length of 2 characters. We set the primary key as the zip code using pk_ZipCodes.

Next, we create PhysicianPractices, which holds information about different medical practices. The table includes PracticeID, PracticeName, AddressLine1, AddressLine2, ZipCode, Phone, Fax, and WebsiteURL columns. We set PracticeID as the primary key constraint using pk_PhysicianPractices. We also define a foreign key constraint using fk_PhysicianPractices_ZipCodes that relates the ZipCode column in PhysicianPractices table to the ZipCode column in ZipCodes table.

Now, we create Physicians table to store information about the physicians. This table contains PhysicianID, FirstName, LastName, PracticeID, SpecialtyID, and Email columns. We set PhysicianID as the primary key using pk_Physicians. We also define two foreign key constraints using fk_Physicians_Practices and fk_Physicians_PhysicianSpecialities, which are used to refer to PracticeID and SpecialtyID columns in PhysicianPractices and PhysicianSpecialties tables correspondingly.

After Physicians table, we create a table to store Patient information. This table is called Patients, and it includes PatientID, FirstName, MiddleInitial, LastName, Address_Line1, Address_Line2, ZipCode, Phone_Home, Phone, and Email columns. We set PatientID as the primary key using pk_Patients.

Continuing, we create Referrals table, which includes ReferralID, StartDate, EndDate, PatientID, and PhysicianID columns. ReferralID is the primary key using pk_Referrals. Besides, we define two foreign key constraints using fk_Referrals_Patients and fk_Referrals_Physicians to refer to PatientID and PhysicianID columns in Patients and Physicians tables, respectively.

At this stage, we create Services table, where ServiceID and ServiceName columns will be used to store different services' details. We set ServiceID as the primary key using pk_ServiceID.

Then, we create Frequencies table, which holds different intervals, such as daily or weekly, which will be relevant for the referrals. This table contains FrequencyID and Frequency columns. As expected, we define FrequencyID as the primary key using pk_Frequencies.

Finally, we create ReferralServices table, which links Referrals, Services, and Frequencies tables. This table contains ReferralID, ServiceID, and FrequencyID columns. We set ReferralID and ServiceID as the composite primary key using pk_ReferralServices. We also define three foreign key constraints using fk_ReferralServices_Referrals, fk_ReferralServices_Services, and fk_ReferralServices_Frequencies. These keys are used to refer to the ReferralID column in Referrals table, ServiceID column in Services table, and FrequencyID column in Frequencies table relatively.

Lastly, we create PaymentTypes table, which is used to store the different payment types that may be relevant for medical referrals. This table includes PaymentTypeID and PaymentType columns. We set PaymentTypeID as the primary key using pk_PaymentTypes.

In summary, we have now created all the necessary tables for our database projectone.

To create a table for contracts, the following fields are required: ContractID, ReferralID, StartDate, EndDate, PaymentTypeID, InsuranceID, and NegotiatedRate. The ContractID will serve as the primary key, while ReferralID, PaymentTypeID and InsuranceID will be foreign keys referencing the Referrals, PaymentTypes, and InsuranceCompanies tables, respectively.

Similarly, for the EmployeeTypes table, the fields needed are EmployeeTypeID and EmployeeType, with EmployeeTypeID as the primary key.

The EmployeeTitles table requires EmployeeTitleID and EmployeeTitle fields, also with EmployeeTitleID as the primary key.

In the EmployeeSkillLevels table, the fields required are SkillLevelID and SkillLevel, and similarly, SkillLevelID will be the primary key.

For the BillingRates table, EmployeeTypeID, SkillLevelID, and BillingRate are the required fields. Here, the primary key will be a combination of EmployeeTypeID and SkillLevelID, and the foreign keys will reference the EmployeeTypes and EmployeeSkillLevels tables.

The EmployeeRanks table requires RankID, EmployeeTypeID, TitleID, SkillLevelID, HourlyRate, and Salary. The primary key is RankID, while the foreign keys reference the EmployeeTypes, EmployeeTitles and EmployeeSkillLevels tables.

The Employees table requires EmployeeID, FirstName, MiddleInitial, LastName, AddressLine1, AddressLine2, ZipCode, Phone, CellPhone, Email, RankID, HourlyWage, and Salary. The primary key is EmployeeID, while RankID is a foreign key referencing the EmployeeRanks table and ZipCode is a foreign key referencing the ZipCodes table.

The Shifts table requires ShiftID, ShiftName, StartTime and EndTime fields, with ShiftID designated as the primary key.

Finally, the Availability table requires EmployeeID, WeekOf, DayOfWeekID, and ShiftID fields, with the primary key being a combination of these fields. Here, EmployeeID is a foreign key referencing the Employees table, while DayOfWeekID and ShiftID are foreign keys referencing the DayOfWeek and Shifts tables, respectively.

Rewritten version:

The foreign key constraint named "fk_Availability_Shifts" is created for the "ShiftID" column, which references the "Shifts" table.

A new table named "MedicalSuppliers" is created, with columns such as "SupplierID", "SupplierName", "Address_Line1", "Address_Line2", "ZipCode", "Phone", "Fax", and "Email". The "SupplierID" column is set as the primary key with a constraint named "pk_MedicalSuppliers". Additionally, the "ZipCode" column is set with a foreign key constraint named "fk_MedicalSuppliers_ZipCodes", which references the "ZipCodes" table.

Another table named "Supplies" is created with columns such as "SupplyID", "SupplyDescription", and "CostPerUnit". The "SupplyID" column is set as the primary key with a constraint named "pk_Supplies".

A new table named "SupplyInventory" is created with columns such as "SupplyID", "SupplierID", "DateReceived", "UnitCost", and "Quantity". The primary key for this table is a combination of "SupplyID", "SupplierID", and "DateReceived" columns with a constraint named "pk_SupplyInventory". Moreover, two foreign key constraints are applied to this table. One is named "fk_SupplyInventory_Supplies", which references the "Supplies" table, and the other is named "fk_SupplyInventory_Suppliers", which references the "MedicalSuppliers" table.

The "Visits" table is created with columns such as "VisitID", "DateRendered", "StartTime", "EndTime", "EmployeeID", and "PatientID". The "VisitID" column is set as the primary key with a constraint named "pk_Visits". Furthermore, two foreign key constraints are added to this table: "fk_Visits_Employees" referencing the "Employees" table, and "fk_Visits_Patients" referencing the "Patients" table.

Finally, a new table named "VisitDetails" is created that has columns such as "VisitID", "VisitDetailID", "SupplyID", "SupplyQuantity", "ServiceID", and "Charge". The combination of "VisitID" and "VisitDetailID" columns are set as the primary key with a constraint named "pk_VisitDetails". Also, two foreign key constraints are applied to this table: "fk_VisitDetails_Supplies", which references the "Supplies" table, and "fk_VisitDetails_Services", which references the "Services" table.