Question

1.) Using MS SSMS, create a new query file (sql) and write and execute SQL statements to insert at least five records in each table, preserving all constraints. Put in enough data to demonstrate how the database will function [name the file PopulateDBWithData.sql].

2.) Using MS SSMS, create a new query file (sql) and write SQL statements that will process five non-routine requests for information from the database just created. For each, write the request in English (using the -- [two dashes] before and after the English to turn it into a comment), followed by the corresponding SQL command [name the file DBQueries.sql].

3.) Using MS SSMS, create a new query file (sql) and create at least one trigger and write the code for it [name the file Trigger.sql].

Create Table Patient ( Name (25) (10) (25) (12), (25), (10) (20), (10) (20)); VARCHAR PatientNo CHAR VARCHAR address phone dateOfBirth CHAR VARCHAR VARCHAR VARCHAR sex insuranceCo policyNo relationshipTolnsured CHAR VARCHAR Create Table Appointment ( patientNo apptdate appttime CHAR CHAR (10), (10) (10), (20) (10) (10)); CHAR VARCHAR reason staffNo visitNo CHAR CHAR Create Table Visit VisitNo (10) (10), (10), (10) (20), (20) (20), (10), (10) (10)); CHAR PatientNo visitdate CHAR CHAR visittime CHAR duration VARCHAR VARCHAR reason visitType visitCost staffNo VARCHAR CHAR CHAR roomNo CHAR Create Table Staff ( StaffNo CHAR (10), (20), (20) (20), (10) (7)); VARCHAR name title VARCHAR specialty address phone VARCHAR CHAR CHAR Create Table Availability (10) (10), (10) (10)); staffNo availDate CHAR CHAR startTime endTime CHAR CHAR Create Table InsurancePolicy ( (20) (10), (20), (20), (10), (10) (10) (10) (10)); VARCHAR company policyNo insuredName СHAR VARCHAR VARCHAR CHAR policytype medicalCoPay labCoPay pharmacyCoPay startDate endingDate CHAR CHAR CHAR CHAR Create Table DiagnosisMenu ( diagCode diagName VARCHAR (20), (20)); VARCHAR Create Table ProcedureMenu ( procCode procName (10) (20) (10)); CHAR VARCHAR CHAR cost Create Table Room ( roomNo CHAR (10), (20), (20)); roomType VARCHAR condition VARCHAR Create Table PrescriptionScript ( scriptNo (10) (10) (10) (20), (10), (20), (10)); CHAR visitNo CHAR date Written CHAR itemPrescribed VARCHAR quantityPrescribed directions numberRefills CHAR VARCHAR CHAR Create Table LabTest ( (10) (10) testNo CHAR prescriptionNo textType testDate CHAR VARCHAR (20), (10), (10) (10), (20)) CHAR testTime CHAR CHAR cost VARCHAR result Create Table PrescriptionMedication (10) (10), (20), (10), (10) (10), (10)); RXNumber СHAR scriptNo drugDispensed dateDispensed quantityDispensed refillsRemaining CHAR VARCHAR СHAR CHAR CHAR CHAR cost Create Table ProcedurePerformed ( visitNo CHAR (10), (10) (20)) procCode result CHAR VARCHAR Create Table Diagnosis ( VisitNo (10) (10) (10) (20) (20) (20)) CHAR diagCode dateOnset СHAR CHAR VARCHAR symptoms severity prognosis VARCHAR VARCHAR Create Table Referral ( refNo (10) (10) (20), (20) CHAR visitNo CHAR refTo VARCHAR VARCHAR reason Create Table Bill ( invoiceNo СHAR (10), (10), (10) (10) (10) (10)); billDate CHAR CHAR totalAmount dueDate СHAR patientNo amountPaid СHAR CHAR Create Table Charge ( (10) (20) (10) (10); invoiceNo CHAR serviceType serviceDate VARCHAR СHAR amountCharged СHAR Create Table Payment ( (10) (10), (10) (20) (20), (10)); invoiceNo CHAR date CHAR amountPaid CHAR patientPayer insuranceCoPayer insurancePolNoPayer VARCHAR VARCHAR CHAR

Answer 1

There are total 4 classes. Student class is inherited and publicly accessed by 2 more classesFirstYearStudent and SecondYearStudent and there is 1 more School class. So total 4 classes

In main function when School object is called 100 constructors from FirstYearStudent of Student and 100constructors from SecondYearStudent of Student and 1 of school constructor and 100 constructor of FirstYearStudent itself and 100 constructors of SecondYearStudent which means s a total of 401 objects will be created because 401 will be created.