Chapter 1 Level 1
Chapter 1 Level 1
1. Plan the Employee and Customer tables needed in the 4Corners Pharmacy database using the information Don garnered during the discovery phase. Planning each table in this database will be a time consuming process. After you complete these two tables, challenge yourself to plan other tables.
This requires reviewing what data is already being collected, what data is not being collected, and what outputs the stakeholders at 4Corners Pharmacy want to see. You must then brainstorm to determine how this data might be divided into tables. Answers will vary, but several tables should be evident:
• Customers
• Drugs
• Prescriptions
• Doctors
• Employees
• Classes
In addition to these tables, more advanced students might see the need for the following tables:
• Health Plans
• Households
• Clinics
• Job Titles
• Refills
2. Name the Employee and Customer tables according to their contents and the Leszynski/Reddick naming conventions.
This will require adding the tbl tag (prefix) to table names. If you continue to plan additional tables, note also that to conserve space, the following suggested table names reduce Customers to Customer, Drugs to Drug, and so on, and abbreviate Prescriptions to Rx.
• tblCustomer
• tblDrug
• tblRx
• tblDoctor
• tblEmployee
• tblClass
• tblHealthPlan
• tblHousehold
• tblClinic
• tblJobTitle
• tblRefill
3. List the fields in each table.
Answers will vary, but fields might include:
• tblCustomer:CustID, CustFirst, CustLast, Phone, DOB, Gender, Balance, ChildCap, PlanID, HouseID, HeadHH, and Allergies
• tblEmployee: EmpID, EmpFirst, EmpMI, EmpLast, SSN, DOB, StartDate, EndDate, Address, City, State, ZIP, JobID, Memo, Phone, Cell, Salary, HourlyRate, and Review
If you continue with the challenge and create additional tables for the database, your fields may appear as follows:
• tblDrug: UPN, Name, Generic, Description, Unit, Dosage, DosageForm, Cost, Price, Interactions,PregCategory, and
1. Plan the Employee and Customer tables needed in the 4Corners Pharmacy database using the information Don garnered during the discovery phase. Planning each table in this database will be a time consuming process. After you complete these two tables, challenge yourself to plan other tables.
This requires reviewing what data is already being collected, what data is not being collected, and what outputs the stakeholders at 4Corners Pharmacy want to see. You must then brainstorm to determine how this data might be divided into tables. Answers will vary, but several tables should be evident:
• Customers
• Drugs
• Prescriptions
• Doctors
• Employees
• Classes
In addition to these tables, more advanced students might see the need for the following tables:
• Health Plans
• Households
• Clinics
• Job Titles
• Refills
2. Name the Employee and Customer tables according to their contents and the Leszynski/Reddick naming conventions.
This will require adding the tbl tag (prefix) to table names. If you continue to plan additional tables, note also that to conserve space, the following suggested table names reduce Customers to Customer, Drugs to Drug, and so on, and abbreviate Prescriptions to Rx.
• tblCustomer
• tblDrug
• tblRx
• tblDoctor
• tblEmployee
• tblClass
• tblHealthPlan
• tblHousehold
• tblClinic
• tblJobTitle
• tblRefill
3. List the fields in each table.
Answers will vary, but fields might include:
• tblCustomer:CustID, CustFirst, CustLast, Phone, DOB, Gender, Balance, ChildCap, PlanID, HouseID, HeadHH, and Allergies
• tblEmployee: EmpID, EmpFirst, EmpMI, EmpLast, SSN, DOB, StartDate, EndDate, Address, City, State, ZIP, JobID, Memo, Phone, Cell, Salary, HourlyRate, and Review
If you continue with the challenge and create additional tables for the database, your fields may appear as follows:
• tblDrug: UPN, Name, Generic, Description, Unit, Dosage, DosageForm, Cost, Price, Interactions,PregCategory, and