You have learned a great deal about database objects, concepts, and table relationships, all…

You have learned a great deal about database objects,
concepts, and table relationships, all requisite knowledge for planning a
relational database. Now it is time to consider which database objects are required
for the 4Corners Pharmacy database and how to relate the tables.

On a piece of paper, complete the following:

Paul wants to monitor prescription refills. He wants to use
the UPN field to query which drugs are refilled most often. (For example, Paul wants
to know how many times the drug Phalastat, with UPN 366, was refilled and by
whom.) At the same time, he needs to determine
»

You have learned a great deal about database objects,
concepts, and table relationships, all requisite knowledge for planning a
relational database. Now it is time to consider which database objects are required
for the 4Corners Pharmacy database and how to relate the tables.

On a piece of paper, complete the following:

Paul wants to monitor prescription refills. He wants to use
the UPN field to query which drugs are refilled most often. (For example, Paul wants
to know how many times the drug Phalastat, with UPN 366, was refilled and by
whom.) At the same time, he needs to determine which employees filled a given
prescription. (For example, customer Marvin Gattis was prescribed Hyometadol on
7/7/2017, his doctor authorized five refills, and Cynthia Jones refilled the
prescription for Mr. Gattis three times.) Suppose that tblRx lists every
prescription that every customer has filled at 4Corners Pharmacy and contains
the following fields: PrescriptionID, UPN, Quantity, Date, Refills,
Instructions, CustID, and DoctorID. Also suppose that tblEmployee lists every
employee working at 4Corners Pharmacy and contains the following fields: EmpID,
FirstName, LastName, DOB, SSN, Address, City, State, and ZIP. What would you do
to create a relationship between these two tables so that Paul can query them
to answer his questions? Identify the primary and foreign keys necessary to
create this relationship. Would you use the Cascade Update Related Fields and
Cascade Delete Related Records options? Why or why not?

»