MS VISIO & MS ACCESS DATABASE
CASTLE’S FAMILY RESTAURANT 2
Scenario
A small surgery center needs your help to create a database. The office manager has identified the following types of data (entities): patients, doctors, procedures and appointments.
1. Please identify the attributes and primary keys needed for each of the entities.
2. Identify the data types you would use for each of the attributes.
3. Determine the relationships between the entities and find the one-to-many and many-to-many relationships.
· Use the ER matrix to determine the relationships.
4. Create an ER diagram using Visio based on your analysis.
5. Identify all foreign keys needed in the database design.
6. Create the database in Access and populate the tables.
7. To use the database as the main office application, what other entities and attributes might be needed?
Activities
1. Submit your draft database design using MS Visio using a Conceptual Visio Drawing
2. Upload completed Visio Conceptual Design.
3. Upload completed Access database file
Grading Rubric
|
Step |
Task |
|
|
1 |
Attributes |
|
|
1a |
Primary key identified for each entity (four tables) |
|
|
1b |
Attributes identified for each entity (four tables) |
|
|
2 |
Data Types |
|
|
2a |
Identified data types |
|
|
3 |
Relationships |
|
|
3a |
Identify one-to-many relationships. |
|
|
3b |
Identify many-to-many relationships. |
|
|
4 |
Foreign Keys |
|
|
4a |
Add foreign keys to tables as needed. |
|
|
5 |
ER Diagrams |
|
|
5a |
Create an ER diagram using Visio. |
|
|
6 |
Create and Populate the Database |
|
|
6a |
Create the tables in Access. |
|
|
6b |
Draw/Create the relationships in Access. |
|
|
6c |
Add five records to the procedure table. |
|
|
6d |
Add five records to the doctor's table. |
|
|
6e |
Add five records to the patient's table. |
|
|
6f |
Add five to appointments. |
|
|
7 |
Written report |
|
|
7a |
Discuss database benefits when businesses use queries, forms, and reports |
|
|
7b |
Discuss at least two security concerns should be discussed with a proposed solution to mitigate the security issues. |
|
|
7c |
Edit, spellcheck, and list references. |
|