Project Part 1
Project Guidelines: In this first part of the course project, you will design the relational database that stores all the data about business case described. You should use your own database system on your laptop or an internet-accessible server. Following is the business case and a list of steps for this part of the project. Note that in this first part, you will only deal with the database side of this project – a suitable web interface will be designed in the second part of the project, where you will also have a little more freedom about choosing the programming language.
We Do Care (WDC) is one of the largest group of hospitals in the country having several subsidiary hospitals associated with it. WDC also has a research academic and research hospital, namely WDCARH (We Do Care Academic and Research Hospital). Each subsidiary hospital affiliated with WDC has its own independent database management system. It is becoming very difficult for WDCARH to collect all patients’ data for research and treatment purposes and to analyze them collectively to draw meaningful insights.
A tremendous amount of data is a very important factor for any researching work. For example: when WDCARH hospital decides to do a medical research on Nephrosis, it needs to collect information of examination data of patients from initial function to final renal failure, from a lot of kidney disease patients of all ages, gender, race, and with any other medical conditions. If WDCARH research hospital is only able to collect data from few of hospitals with same data formats, it will not have enough or full set of data to support a credible research. The only way for WDCARH hospital to solve this issue is to collect and analyze all data of all patients in all WDC subsidiary hospitals.
For this project, identify appropriate entities (minimum seven entities including associate entities if any), identify relationship among entities, attributes of each entity and their datatype, size, and constraints, and implement a centralized relational database system that collects relevant data about all patients from all WDC affiliated hospitals and their respective departments, including doctors and all treatments served for several diseases.
i．Please consider following while designing the database schema,
ii．Doctors work at one specific hospital only and they are not the employee of WDC hospitals
iii．Multiple treatments may be given to patients for same disease
iv．Same treatment may be given to patients’ multiple times on same day (e.g. same medicine at every 6 hours)
v．Hospital can have many departments and each department can have many employees
vi．Patient’s gender must be recorded as M (male), F(female), or U(unknown) and marital status must be recorded as M(married), S (single), D (divorced), or W (widow or widower)
vii．Patient’s race must be recorded e.g. Asian, Hispanic, Latin American, African American etc.
viii．Treatment status must be recorded as S(successful), R(repeat), F(failed)
ix．Treatment specific to disease can be of three kinds namely, Pharma or Procedure or Surgery
Primary goal of WDCARH is medical research and hence data about accounting, billing, insurance is not needed
For any other business rules not defined, please state assumptions you have made along with database design.
Submit following deliverables towards project part 1 assignment
- a) Create a logical E-R model for database schema with appropriate relationships amongst them
- b) Create a relational model, depicting all entities, attributes (name, type, size, mandatory/optional, primary key),
relationships (foreign keys)
- c) UserelationalmodeltogenerateDDLtocreateobjects,primarykeys,foreignkeys,andotherconstraints.Include
DDL generated for all tables and/or screenshots of tables definitions in RDBMS.
- d) Write commands and apply necessary CHECK constraints to apply business rules to enforce data consistency
- e) Populate meaningful sample data for all entities (at least 10 to 15 records par entity)
- f) List total number of records populated for each entity (just record counts, not full data set)
- g) Write SQL queries using each of following,
Q1) Table joins with at least 3 tables in join Q2) Sub-query with group function
Q3) Co-related subquery
Q4) SET operators
Q5) Read only view with at least four table joins
For each of above query use proper column alias, built-in functions, appropriate sorting and submit following three items for each of above queries.
A1) Select query
A2) Result of the query
A3) Statement about what information you intend to achieve through the query
Submit a properly documented description and justification of your entire design (no more than half page), assumptions made, if any along with deliverables as above. This should be a paper of a single PDF document with all screenshots inserted within. Clearly state course, section, submission date, student names and Ids on title page. If you are working in group, name and ID of both students must be listed.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:dmxyzl003