Reference no: EM132358294

Database Administration Assignment -

Learning Outcomes -

Create and maintain a database server - Critical Thinking, Analytical, Comparing, Contrasting, Decision-making, Researching, Making judgment about complex issues, Communicate conclusions effectively.

Implementing Database security policies - Critical Thinking, Analytical, Comparing, Contrasting, Decision-making, Researching, Making judgment about complex issues, Communicate conclusions effectively.

Tasks - Objectives:

1. Create and maintain a database server.

2. Implementing Database security policies.

Task 1 -

Submit a work proposal for this assignment which must include:

• Understanding of deliverables - a detail description of deliverables.
• General overview of proposed plan - initial understanding of solution for the task-2 and task-3.
• Resources identified.

The work proposal must be submitted in a word file.

Task 2 -

Shams Hospital is one of the leading health care service provider in Oman. It is established in the year 1996 and has a current patient's strength of over 500 patients. The patient belong to different parts of the country. It employs full-time and part-time personnel. It offers various medical services in the field of outpatient and Inpatient. The hospital has an online system operational round the clock.

As a Database Administrator (DBA) working for Shams Hospital, discuss the following:

a) Discuss the various reasons with relevant examples of your own that how Database administrator can provide database security to the Shams Hospital database.

b) Discuss the database server architecture which will include memory structure and storage structure.

Note: Solution for this task (a and 1b) must be written in your own words with relevant diagram wherever necessary and to be written in maximum of five pages.

Task 3 -

a) Discuss the key security features available in the data management software that can be applied for the scenario given in task 2a.

Note: The solution must be written in your own words with relevant examples not exceeding 3 pages. You can use research papers for your reference only and your answer should not be limited to the research paper only. You may refer various other resources and provided references for the same.

b) Consider the scenario given in task-2a and create any four users (e.g. User1, User2, User3, and User4) in the Shams Hospital database and assign the privilege to connect to the database. Out of the four users, one user (i.e. User1) is for doctor and two users (i.e. User2 and User3) are for the managers. The fourth user (i.e. User4) is for the admin staff.

Create two roles with the following privileges:

• Data_Entry: can only insert and view the records on the table created by User4.
• HoD: can only edit and delete the records on the table created by User4.

Assign the roles created above to the users as indicated below:

• Assign the role Data_Entry to the doctor (i.e. User1)
• Assign the role HoD to the managers (i.e. User2 and User3)
• For the admin staff (i.e. User4) assign the privilege to create a new table. You may create any new table relevant to the task- 2a scenario.

Your solution must include the necessary SQL commands as screenshots and brief explanation for each of the steps.

Note: The names of the four users must be based on your first name. For example, if your name is Ahmed, the user names should be Ahmed1, Ahmed2, Ahmed3 and Ahmed4. If the user names are inappropriate, marks shall be deducted accordingly.

Note - Need only answer task 2 (a and b) they should maximum 5 pages and task 3 only (a) not more than 3 pages no need to solve (b).