$1.$ Colonial Adventure Tours database currently is designed as below $5$ tables $($primary keys are boxed$)$:

Guide $($GuideNum$,$ LastName, FirstName, Address, City, State, PostalCode, PhoneNum, HireDate$)$

Trip $($TripID$,$ TripName, StartLocation, State, Distance, MaxGrpSize, Type, Season$)$

Customer $($CustomerNum$,$ LastName, FirstName, Address, City, State, PostalCode, Phone$)$

Reservation $($ReservationID$,$ TripID, TripDate, NumPersons, TripPrice, OtherFees, CustomerNum$)$

FK TripID $->$Trip

FK CustomerNum $->$ Customer

TripGuides $($TripID$,$ GuideNum$)$

FK TripID $->$ Trip

FK GuideNum $->$ Guide

Now you need expand the database design to accommodate following new user views. Use DBDL to

describe your new design with new tables. Also draw a diagram for the whole database using ER model

$($any style described in the textbook$)$

$$ A reservation agent handles each reservation, list the number, last name, first name of the

agent that handled the reservation along with the reservation $($every reservation has only

one agent, one agent handles many reservations$)$

$$ Adventure classes are created to prepare participant for future adventure. For each

participant, list his or her number, last name, first name, address, city, state, zip code, and

date of birth.

$$ List each class$$s class number, description, maximum number of person in class and class

fee.

$$ For each scheduled class, list the class date, class number, class description, number of

enrollment, guide$$s name who teaches it and each participant$$s name and address $($Assume

at most one class scheduled on one day, one participant can enroll in many classes on

different days and allowed to repeat the same class. On any specific day a class has only one

guide serve as teacher. The same class on different days can have different guides as

teacher. One guide can teach different classes on different days$)$

$2.$ Now let$$s design a new database from scratch for a pharmacy business. Based on following user

views, please design the database and list all tables using DBDL$.$ Also draw a diagram for the whole

database using ER model of your choice

a$.$ List every customer$$s customerID, last name, first name, address, phone, insurance

company $($assume every customer has insurance$)$

b$.$ List every doctor$$s$,$ ID$,$ last name, first name, business address, phone

c$.$ List every insurance company$$s ID$,$ name, phone

d$.$ List every insurance company$$s in$-$network doctor$$s last name and first name $($assume one

doctor could be in multiple insurance company$$s network$)$

e$.$ List every prescription$$s customerID, date, total amount due, pay by insurance amount, pay

by customer amount, insurance company ID$,$ doctor ID of the doctor who prescribes it$,$ in$-$

network or not

f$.$ Count every customer$$s total bill between two given dates.