Reference no: EM133823566

Database Fundamentals

Assessment 1:

Introduction
In this assessment, you are required to write a report of no more than 1500 words. You need to create an ER diagram based on the case study given and construct a written specification.

Case Study:
Victorian Hospital is a private hospital that includes a few departments, rooms, doctors, nurses, compounders, and other staff working in the hospital. Patients who have different kinds of ailments come to the hospital and get checkup done from the concerned doctors. If required they are admitted to the hospital and discharged after treatment.

The aim of this case study is to design and develop a database for the hospital to maintain the records of various departments, rooms, and doctors in t he hospital. It maintains records of the regular patients.

In hospital, there are many departments like Emergency, Dental, I.C.U., Operation Theater, Laboratory, M.R.I. and Cancer Department. There is an OPD where patients come and get a card (that is, entry card of the patient) for check up from the concerned doctor. After making entry in the card, they go to the concerned doctor's room and the doctor checks up their ailments. According to the ailments, the doctor either prescribes medicine or admits the patient in the concerned department. The patient may choose either private or general room according to his/her need. But before getting admission in the hospital, the patient has to fulfill certain formalities of the hospital like room charges.

Table Description:
DEPARTMENT: This table consists of details about the various departments in the hospital. The information stored in this table includes depart ment name, department location, and facilities available in that department.

DOCTORS: This table stores information about all the doctors working for the hospital and the departments they are associated with. Each doct or is given an identity number starting with DR or DC prefixes only.
PATIENT: Details of regular patients are stored in this table. Information stored includes date of visit, diagnosis, treatment, medicine recomme nded, status of treatment, etc.
ROOM_DETAILS: It contains details of all rooms in the hospital. The details stored in this table include room number, room type (general or pri vate), status (whether occupied or not), if occupied, then patient number, patient name, charges per day, etc.
You are hired for this project. After the first meeting with the board members, you decide a database system will serve as backbone for designing the system.

Activity 1: Draw a detailed ER diagram
You can use the description in the case study for the ER diagram.

Activity 2: Write Specification from Entity Relationship Diagram
Compose the operational guidelines for the Entity-Relationship Diagram in activity 1. Exclude attribute descriptions and focus on specifying all cardinalities, participation constraints, and identifying strong and weak relationships. Please use plain English rather than technical database terminology.

The format structure for the report
You are required to follow the following structure for this assessment:
Introduction
Task 1
Task 2
Conclusion

Submission Guidelines
You need to answer all the questions, use appropriate diagrams where required and submit in one report (.docx/pdf) file.
Your report needs to follow the naming convention below:
"Assignment 1_X_YY.docx" where X is your name and Y is your student ID.
The Late penalty applies on late submission, 10% per day would be deducted.
Incidence of plagiarism will not be tolerated and would be penalized.

Assessment 2:

Activity 1: Normalisation
Create a relational schema and demonstrate the First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) for the provided tables. When breaking down composite attributes like ‘full name,' make sure to select suitable attribute names, such as dividing it into ‘firstname' and ‘lastname.'

Activity 2: Normalisation
Create a relational schema and demonstrate the Third Normal Form (3NF) for the database and table design you had created in your first assignment. When breaking down composite attributes like ‘full name,' make sure to select suitable attribute names, such as dividing it into ‘firstname' and ‘lastname.' Get Assistance Now!!

The format structure for the report
You are required to follow the following structure for this assessment. Below is the description for the report:
Introduction
Task 1
Task 2
Conclusion

Assessment 3:

Introduction
For this assignment, you will need to construct a database, execute CRUD operations within the SQL database, and engage in a discussion concerning data security concerns.

Activity 1: SQL Queries (Case Study):
Activity 1 is the continuation for the assessment 1. Victorian Hospital is very pleased with your report and has approved the report. Based on the ER diagram created in assessment 1, you are to create the database.
Write SQL queries to create the table. Select suitable data types for each attribute and identify appropriate primary and foreign keys as needed.
Write SQL queries to insert some data into each table (At least 10 records).
Write the SQL queries for the tables created
Create 5 SQL queries.
Create a View.
Complexity of the query.
You are required to use a database tool to run the query. Screenshots of the query and result should be included in the report.

Activity 2: Database Administration
Examine the database established during Activity 1 and deliberate on the various forms of user access permissions necessary to uphold the database's integrity and safeguard its security. Additionally, explore the potential security concerns that could emerge if user inputs aren't adequately validated before being utilized as queries within the database. Support your analysis with relevant instances drawn from a case study.

Tools Required
For this assessment, you are expected to use from the following tools for the database:
Postgresql

MS SQL Server
DBreaver
Microsoft Data Studio
Azure Data Studio Additional Tools This is for the advanced user.

The format structure for the report You are required to follow the following structure for this assessment. Below is the description for the report: • Introduction
Task 1
Task 2
Conclusion
Submission Guidelines
You need to answer all the questions, use appropriate diagrams where required and submit them in one report (.docx/pdf) file.
Your report needs to follow the naming convention below:
"Assignment 3_X_YY.docx" where X is your name and Y is your student ID.
The assignment is to be submitted via the Assignment 3 submission link in LMS.