1 Introduction
The purpose of this project is to give you some experience in database design. We will explore both theoretical and practical aspects of the database design steps. Once you master it well, it is a simple matter of translating the theoretical concepts you developed into the terms of a concrete DBMS to implement the database. In our case, you can implement the database tables and load data using your local MySQL.
This is a team project. Two students constitute a team. Only a few teams consist of three students with more requirements.
The project consist of two parts, report part in word or pdf and presentation part in classroom. All team members will have the same score.
2 Project selection and initiation
Let assume that Bradley University needs to build up a database to manage our hospital, with a pharmacy, also with drug details (Drug@FDA), with at least one quarter of existing report the drug side effect (FAERS) from FDA for our future knowledge discovery.
The following subsections show some design sample of Hospital Management Subsystem and Pharmacy Management Subsystem. It also include Drug@FDA and FAERS subsystems with downloadable data in them.
2.1 Hospital Management Subsystem Samples
2.1.1 Hospital Management Subsystem
example 1
One example,
https://123projectlab.com/er-diagram-for-hospital-managementsystem/
2.1.2 Hospital Management Subsystem
example 2
Another example,
https://www.freeprojectz.com/entity-relationship/hospitalmanagement-system-er-diagram
2.1.3 Hospital Management Subsystem
example 3
Another example https://creately.com/diagram/example/iqsfzf701/new-e-r-diagram-for-hospital-management-system
2.2 Pharmacy Management Subsystem Samples
2.2.1 Pharmacy Management Subsystem 1
One example,
https://1000projects.org/pharmacy-management-system-dbms-project.html
2.2.2 Pharmacy Management Subsystem 2
Another example,
https://www.freeprojectz.com/entity-relationship/pharmacymanagement-system-er-diagram
2.2.3 Pharmacy Management Subsystem 3
Another example, https://www.youtube.com/watch?v=xSJphE6vngg
2.3 Drug@FDA Subsystem
Official E-R Diagram is located at: https://www.fda.gov/drugs/drug-approvalsand-databases/drugsfda-data-files.
It has 11 tables below:
ActionTypes Lookup.txt
ApplicationDocs.txt
Applications.txt
ApplicationsDocsType Lookup.txt
MarketingStatus.txt
MarketingStatus Lookup.txt
Products.txt
SubmissionClass Lookup.txt
SubmissionPropertyType.txt
Submissions.txt
TE.txt
Please merge it into your hospital and pharmacy E-R diagram
2.4 FDA Adverse Event Reporting System (FAERS) Quarterly Data Subsystem
Go to FAERS link
https://www.fda.gov/drugs/development-approval-process-drugs/drug-approvals-and-databases, follow the link, download one quarter data. If you download faers ascii 2022Q4, for example, faers ascii 2022Q4 ASCII, you can refer to file ASE NTS.pdf for E-R diagram.
You need to figure it out by yourself that what the data types based on the given data and E-R diagram and how to load it into the existing system.
3 Your Report Requirements
Good DBAs must be able to prepare a full design report. You should hand in the full, legible report with a cover page that gives your names, the title of the project, and its brief description. The final report should include sufficient detail to describe all steps of your design.
You must merge all 4 subsystem together into a E-R diagram, without losing the existing information and demonstrate its efficacy by providing a set of useful queries that your database can support, and no redundancy. In the process, you must use standard relational modeling tools and techniques we examined in class. In concrete terms, the report and this part of the project consists of the following steps:
1. Choose one hospital management subsystem E-R (no data, you need to create some data), one pharmacy management subsystem E-R (no data, you need to create some data). Merge with Data@FDA E-R, FAERS E-R. You can use any one of hospital management subsystem E-Rs, or pharmacy management subsystem E-Rs shown above, or your can search online and use them. Note, if your team has three memebers, you are not allowed to use hospital management subsystem E-Rs, or pharmacy management subsystem E-Rs shown above but either search online or create by yourself.
For all teams, the contents should not be less than those shown in the samples. When merging these 4 sub-systems, you need to remove all redundant tables/columns if needed. For example, you shouldn’t keep two tables, one is customer table, and the other is patient table. Also, set up primary and foreign key constraints when merging them.
2. Formulate in English at least 10 realistic queries you believe would be useful to somebody using the database. The queries must be realistic and you should have more than few complex queries and cover 90% of all tables. Note, the queries that the database system must be able to answer ultimately determine what information needs to be maintained in the database.
Specify the assumptions about the database in English. Here you talk about attributes, keys, the nature of relationships between entities, etc.
Do not discuss something that is obvious. In addition, don’t make too many simplifying assumptions.
3. Present an ER diagram, which will reflect your choice of the entity sets, their relevant attributes, and the relationships among them. Again, make your relationships realistic and meaningful.
For each relation schema, specify the functional dependencies satisfied by the schema. Based on the knowledge of functional dependencies, you can analyze how good your initial DB design is, i.e. the potential for repetition of information and other anomalies we examined in class. For each relation schema, specify the normal forms it satisfies.
If necessary, perform decompositions of the relations, such that the entire database is in BCNF.
4. Convert the ER diagram into a relational database schema.
5. Formulate some data and insert your one hospital management subsystem, pharmacy management subsystem. Insert all data download from Data@FDA, one quarter data from FAERS. Note, if you changed the design of Data@FDA and FAERS, you maybe need to change the data format by yourself.
6. Formulate all of your queries using relational algebra.
7. For 10 queries, you need show it in your report and the related screenshots results.
Present Your Database Design
Here are some tips of how to make a good presentation: https://www.betterup.com/blog/how-to-give-a-good-presentation https://hbr.org/2013/06/how-to-give-a-killer-presentation
And many others online materials of how to make good presentations.
If you need solution of above problem then then don't worry, realcode4you expert solve this problem with an reasonable price.
If you need other help related to other databases like Oracle, SQL Server, PostgreSQL, MongoDB then our expert also provide top and best quality solution with complete support and reasonable price.
For more details you can send your request or requirement details at:
realcode4you@gmail.com
Comments