程序代写案例-46HW1

欢迎使用51辅导,51作业君孵化低价透明的学长辅导平台,服务保持优质,平均费用压低50%以上! 51fudao.top
2/14/21 14:46HW1
⻚码:1/2https://bytes.usc.edu/cs585/s21_DBDS012/hw/HW1/index.html

HW1: 6 points
In this assignment, you need to create a conceptual Entity-Relational (ER) diagram, to model the scenario
described below [designing a DB for a new dental practice!]; note that your design is not going to be at a logical
level, ie. you don't have to worry about how to represent your design using relational tables (including bridges) or
other data models.
Please submit your work as a single image file in .jpg or .png form that shows the entire diagram, via D2L Dropbox
(pics/DropboxSubmission.jpg) (not via Blackboard). ALSO include a README.txt that contains any design choices
you want to highlight, and/or assumptions you made [if the ER diagram were 'code', these would be 'comments'].
You can create the ER diagram using any software of your choice, including:
yEd (http://www.yworks.com/products/yed (http://www.yworks.com/products/yed))
draw.io (browser-based)
Lucidchart (browser-based)
Creately (browser-based)
Vertabelo (entirely online)
DIA
Project Mogwai (https://github.com/mirkosertic/MogwaiERDesignerNG (https://github.com/mirkosertic/MogwaiERDesignerNG))
E/R Assistant (Windows only: https://highered.mheducation.com/sites/0072942207/student_view0/e_r_assistant.html
(https://highered.mheducation.com/sites/0072942207/student_view0/e_r_assistant.html))
Visio
erwin DM: https://erwin.com/products/erwin-data-modeler/ (https://erwin.com/products/erwin-data-modeler/)
...
After constructing the ER diagram, save, or take a screengrab snapshot, submit it [as a .jpg or .png image file].
Note that you can even draw your diagram legibly on paper and take a photo of it and submit that - but having said
that, I'd encourage you to use a diagramming tool, that will make your result look professional, and have you follow
industry practice.
You need to use Crow's Foot Notation for the ER diagram. For each relationship, indicate the cardinality (minimum
and maximum participation), also via Crow's Foot symbols - use this
(pics/ERD_Relationship_Symbols_Quick_Reference-1.png) infographic (from www.vivekmchawla.com/) as a guide
[you don't need to denote cardinality as (1,1) etc., instead, you would use the notation shown in the infographic, ie.
use symbols such as |O and ||].
How much detail should your diagram contain? Use this (pics/Sample_ER_Diagram.jpg) sample as a guide (eg. you
do not need to indicate data types for attributes).
You (a database designer/analyst) have been hired by the owners of a new (yet to be launched) dental practice, to
design a comprehensive database that would help run their operation smoothly.
The following paragraphs contain descriptions of various aspects of the business - your goal is to use them to
create an ER diagram that captures the business operation, from a DB perspective.
The owners just want the big pieces in place, for now - you might asked back to do a detailed design later
[depending on how good your current design will turn out!].
The owners plan to take a loan (of $300,000) from a local bank, to launch the business - the loan (plus interest)
would need to be paid off in installments that would span 10 years.
Startup costs include furniture, dental equipment, software (for scheduling, billing etc), supplies, training, etc.
2/14/21 14:46HW1
⻚码:2/2https://bytes.usc.edu/cs585/s21_DBDS012/hw/HW1/index.html
Staff that need to be hired include front o!ce workers, dental hygienists, regular dentists, periodontists,
endodontists, orthodontists and dental surgeons. The medical professionals have licenses that need to be kept up
to date [https://www.dbc.ca.gov/licensees/dds/renewals.shtml
(https://www.dbc.ca.gov/licensees/dds/renewals.shtml)]; the business needs to track this.
The business hopes to service around 100 patients. Each patient's billing record consists of their insurance
information (patient ID, insurance provider name, insurance subscriber ID, insurance coverage type ("savings plan"),
amount of insurance coverage), and payment details (amount owed, amount paid by insurance, amount paid by the
patient); their medical record would consist of procedures (eg teeth cleaning), treatments (eg gum disease) and
surgeries (eg tooth extraction).
The bulk of the patients are expected to carry dental insurance, so the staff needs to maintain a list of insurance
providers. Billing insurance providers consists of submitting a patient's insurance (subscriber) ID, type of treatment
(its code, eg https://bytes.usc.edu/cs585/s21_DBDS012/lectures/discussions/pics/DentalCodes.png
(https://bytes.usc.edu/cs585/s21_DBDS012/lectures/discussions/pics/DentalCodes.png)), treatment date, amount
requested/billed/claimed, amount paid (by the insurance company to the dental business).
Daily practice consists of hygienists and doctors (together called 'doctors') serving patients, in one of 8 rooms
which need to be scheduled for a specific doctor and patient. Front o!ce staff would routinely contact patients to
do the scheduling. A patient, at any time, would be in one of the following 'states', when it comes to scheduling:
contacted, scheduled, recently visited, up for next visit, dormant.
Each morning, the owners would like to see the day's schedule; at the end of the day, they would like a report that
shows billable income generated by the days' services.
The staff (including doctors) get paid monthly salaries - there are no hourly rates or overtime pay.
The owners plan to lease a medical building near a major hospital - there is a monthly lease schedule for the them to
follow.
Supplies are restocked every month - everything from needles to drugs to paper towels. There are also other other
monthly operating costs - facilities cleaning, utilities, food in the breakout room, etc.
Each month, the owners would want to see a report that contains expenditure and income so that they can assess
profit or loss.
When their business takes off, the owners have grand visions of using a data driven approach (analytics) to
maximize their ROI!
The above is what the dental practice owners have in mind! Your task now is to (conceptually) design a good
database for them, to help run their operation smoothly. Note - there isn't a single solution, or a single 'good'
solution. You are free to make intelligent choices about what data to store where (entities), and how to connect all
the pieces (relations) - this includes making reasonable assumptions about anything not stated above. Note too
that some requirements stated above, might not be able to be captured in an ER diagram - this is fine. Also, feel
free to come up with an EER diagram if you see a need for one. Be sure to document your design decisions (they
would serve to provide rationale for "why you created your design the way you did").
Submission checklist:
.jpg or .png pic of your ER diagram
README.txt description file
You can post questions (and answer others' :)) on Piazza, under 'hw1'.
Have fun!

欢迎咨询51作业君
51作业君

Email:51zuoyejun

@gmail.com

添加客服微信: abby12468