(Solved): Database Assignment...
HealthCare is a private hospital looking to develop an effective Relational Database to cater to the needs of their growing business, develop a database for HealthCare which will satisfy all their requirements.
The database should contain information about the doctors, nurses, patients, diseases and departments of HealthCare:
For patients - name, address, diseases, phone number and related information to store. For doctors/Nurses - the company wants to record the name, address, salary, speciality, and department / identification number
Relevant assumptions, if required can be made.
The database should provide the following information:
- Statistics of the doctors/nurses/patients and diseases
- Number of operations and cancellations for a specific day
Understanding of database
By relating your discussion to the above scenario where applicable,
- Compare database systems to file based systems. Your discussion should include the advantages, disadvantages and limitations of each.
- Discuss the DBMS three Level architecture and explain, using a diagram, the difference between the external level, conceptual level and internal level.
- Identity all the possible entities that you can find in the problem scenario and list them in alphabetical order.
- Using Crow’s foot notation, design a conceptual data model for the New Airline database in a modelling tool of your choice. All entity types and the relationships between them must be clearly shown. Your design should show all cardinality and participation constraints accurately. Any enhanced features used (e.g.
generalization/specialization) must be indicated.
- Discuss the principles of normalization and demonstrate by examples the steps you followed to achieve normal forms in relation to the given scenario. Your demonstration must show the full process with explanations, starting from UNF, 1NF, 2NF to 3NF. (There is no requirement here to go through the full list of attributes in the data model for the HealthCare).
- Produce an ERD with all the normalized entities containing the relevant attributes. State any de-normalization, if any, in all your relations and clearly state the reasons for de-normalizing relations in your implemented solution.
- Map an ER model devised above into a set of logical relations in the relational data model.
- Produce a script using appropriate SQL commands (DDL) to create a set of database tables using MySQL relational database. Your tables should also show all constraints applied at either the column or the table level. You are expected to implement the database so that it meets the requirements described in the scenario.
- By using appropriate SQL statements, populate all the tables in the database you created with some data. The data should be meaningful but does not need to be extensive.
- All components of the assignment report must be word processed (hand written text or hand drawn diagrams are not acceptable), font size must be within the range of 12 point to 14 point including the headings, body text and any texts within diagrams.
- Standard and commonly used fonts such as Times New Roman, Arial or Calibri should be used.
- Your document must be aligned left or justified with line spacing of 1.5.
- All figures, graphs and tables must be numbered and labelled.
- Material from external sources must be properly refereed and cited within the text using the Harvard referencing system.
- All components of the assignment (text, diagrams, code, etc.) must be submitted in one pdf file.