Part I: Database Design
Fitchburg ABA Clinic wants to create a database with the following entities:
• Employee
o EmpID
o Name
o Position
o Salary
o DateofHire o DateofBirth o Age
o Skills
o Degrees (i.e. M.S., Ph.D)
o Certifications (i.e. BCBA, SLP)
• Client
o ClientID
o FirstName o LastName o DateofBirth o Age
o Allergies
• Diagnosis
o DiagnosisCode
o Description
• Parent
o ParentID o FirstName o LastName o Address
- Street
- City
- State
- ZipCode
o Telephone
• InsuranceCompany
o InsCoID
o Name
o Address
- Street
- City
- State
- ZipCode
o Telephone
• Therapy
o TherapyID
o Name
o RecommendedUnits
o CostperUnit
• TimeSlot
o TimeslotID o DayofWeek o StartTime o EndTime
• Room
o RoomID
o Capacity
o MaxOccupants
There are also some additional business rules:
1. A parent can have many children (clients.) Also, information can be kept on multiple parents for one child
2. A parent can have more than one insurance policy used for billing. We also want to store the policy number and effective date
3. A client is scheduled for a particular therapy in a particular room during a given timeslot
4. A client can have 1 or more diagnosis codes
5. Keeping track of billing is not included in this application. This is merely a scheduling application.
6. We want to keep track of the date of service for a scheduled therapy as well as the duration
From the ERD shown below design the following:
1. a set of normalized relations
2. the scripts to create the tables defined by step 1
Part II
SQL -
Use the Richard's Catering Case Study for the following queries:
1. For each staff member, list the number of events that he or she has worked. Your output should include the last name, first name and the number of events.
2. For each staff member, list the number of each type of event that he or she has worked. Your output should include the last name, first name, event_type and the number of events.
3. Find all maincourses that have not been served at wedding receptions. List out the description.
4. List all dishes that have only been served at night (i.e. start hour >= 6) This the dish description.
5. Calculate the total cost for each event.
Attachment:- richards.pdf