BIS 245 Case Study Guide – Small Surgery Center | DeVry University Chicago | Complete Study Material

BIS 245 Case Study Guide – Small Surgery
Center | DeVry University Chicago |
Complete Study Material

Instructions: This guide provides a complete solution for the BIS 245 Case Study on designing
a database for a small surgery center, as outlined in the course requirements for Weeks 3 and 5.
The case study involves creating a conceptual database model using MS Visio, identifying
entities, attributes, primary keys, data types, and relationships, and discussing the database's
impact on the workplace, benefits of queries, forms, and reports, and security concerns.
Deliverables include an ERD and a 3-5 page APA paper. All calculations, designs, and
recommendations are based on provided case data and standard database design principles.




Scenario Overview
A small surgery center needs a database to manage its operations. The office manager has
identified four key entities: Patients, Doctors, Procedures, and Appointments. Additional
entities, such as Treatment, may be included based on the complexity of relationships. The tasks
include:

1. Identifying attributes and primary keys for each entity.
2. Specifying data types for each attribute.
3. Determining relationships between entities, including one-to-many and many-to-many
relationships, using an ER matrix.
4. Creating an ERD using MS Visio.
5. Writing a 3-5 page APA paper discussing the database’s impact, benefits of queries,
forms, and reports, and addressing security concerns with solutions.




Question 1: Attributes and Primary Keys for Each Entity
Entities and Attributes (based on web results,,):

 Patients Entity:
o Attributes: Patient ID, First Name, Last Name, Date of Birth, Postal Address,
Social Security Number, Sex, Contact Number, Name of Kin.
o Primary Key: Patient ID (unique identifier for each patient).

,  Doctors Entity:
o Attributes: Doctor ID, First Name, Last Name, Area of Expertise.
o Primary Key: Doctor ID (unique identifier for each doctor).
 Procedures Entity:
o Attributes: Procedure ID, Order Number, Patient ID, Doctor ID, Treatment ID.
o Primary Key: Procedure ID (unique identifier for each procedure).
 Appointments Entity:
o Attributes: Appointment ID, Patient ID, Doctor ID, Date, Time.
o Primary Key: Appointment ID (unique identifier for each appointment).
 Treatment Entity (added to capture procedure details):
o Attributes: Treatment ID, Type of Treatment, Fee.
o Primary Key: Treatment ID (unique identifier for each treatment type).

Rationale: Each entity requires a unique primary key to ensure data integrity and enable
relationships. Attributes are selected to capture essential information for the surgery center’s
operations, as specified in the case study.




Question 2: Data Types for Each Attribute
Data Types (based on standard MS Access data types and case requirements,):

 Patients Entity:
o Patient ID: AutoNumber (unique, auto-incrementing identifier).
o First Name: Short Text (up to 50 characters for names).
o Last Name: Short Text (up to 50 characters).
o Date of Birth: Date/Time (e.g., MM/DD/YYYY).
o Postal Address: Short Text (up to 255 characters for full address).
o Social Security Number: Short Text (9 characters, formatted as XXX-XX-
XXXX).
o Sex: Short Text (1 character, e.g., M/F).
o Contact Number: Short Text (10 digits, formatted as XXX-XXX-XXXX).
o Name of Kin: Short Text (up to 100 characters).
 Doctors Entity:
o Doctor ID: AutoNumber (unique identifier).
o First Name: Short Text (up to 50 characters).
o Last Name: Short Text (up to 50 characters).
o Area of Expertise: Short Text (up to 100 characters, e.g., “Orthopedic Surgery”).
 Procedures Entity:
o Procedure ID: AutoNumber (unique identifier).
o Order Number: Short Text (up to 20 characters for procedure order reference).
o Patient ID: Number (Long Integer, foreign key linking to Patients).
o Doctor ID: Number (Long Integer, foreign key linking to Doctors).
o Treatment ID: Number (Long Integer, foreign key linking to Treatment).
 Appointments Entity: