Reference no: EM132598712 , Length: word count:1500

COMP 30021 Database Administration - Middle East College

Learning Outcome 1: Create and maintain a database server
Learning Outcome 2: Implementing Database security policies

Assignment Tasks

Task 1: Assume that you are working as a database administrator. After the system requirement analysis, the following ER diagram is designed for a typical Property Management System.

Based on the ER-Diagram, assuming that you are a Database Administrator (DBA), perform the following:
You are required to provide SQL commands with appropriate level of discussion on tasks performed (follow OFA guidelines)
i. Create tablespace with appropriate segment management.
ii. Create user and link the user to the above tablespace.
iii. Grant appropriate privileges to connect and create the required objects.
iv. Create at least 5 tables for the above scenario (Tables should include appropriate constraints. Please note that while implementing a many to many relationship of E-R diagram, you need an additional table)
v. Create one complex view based on your analysis
vi. Create any two synonyms.
vii. Create index on any one table.
viii. Create one sequence of your choice that will be helpful for the Property Management System's database.
ix. Insert at least 5 rows to the table for which sequence is created.
x. Insert at least 5 rows with appropriate values on any two tables having primary key foreign key relationship.

2) Task 2

i. Assume that you are working as a database administrator. Create four users (Names of the users should be Ahmed_YourID, Akbar_YourID, Anthony_YourID and Rahma_YourID) in a typical database system and assign them the privilege of connect to the database. Out of the four users, one user (say for example, Ahmed_YourID) should have the DBA privileges who will be able to perform the DBA role and the corresponding responsibilities.

ii. Create the table by name EMP_YourID (for example. EMP_19F2019 (Emp_Id, Emp_Name, Emp_DOB, Emp_Phone, Emp_Address). Analyze what could be the drawbacks If Emp_name is defined as a Primary Key.
iii. Create the following three roles with the mentioned privileges.
• Manager: can view, edit and delete the records on the EMP_YourID table that is created by the user Akbar_YourID.
• Owner: can only view the records on the EMP_YourID table created by the user Akbar_YourID.
• Employee: can only add the records on the EMP_YourID table created by the User Akbar_YourID.
iv. Assign the roles created above to the users as indicated below:
• Assign the role Manager to the Rahma_YourID
• Assign the role Owner to the Anthony_YourID
• Assign the role Employee to the Ahmed_YourID
v. Write appropriate SQL commands to demonstrate select, insert, update and delete commands with each user login to show the understanding of roles and privileges.

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

Attachment:- Database Administration.rar