Reference no: EM132683134
COMP 30021 Database Administration - Middle East College
LO 1: Create and maintain a database Server.
LO 2: Implement Database security policies.
Assignment Objective
The objective is to enable the student to work on a relational database server and involve in creating, securing and maintaining of objects needed for databases and implement database security policies. The theoretical design aspects of ER diagrams that were learnt in the pre- requisite module are being reinforced on implementation mode. Hence, student's ability to think and engage with the topic is tested.
Assignment Tasks
1) Task 1 - Submit a work proposal for this assignment on or before 30/10/2020 (23:59) which must include:
• Understanding of deliverables - a detail description of deliverables.
• General overview of proposed plan - initial understanding of solution to all the tasks.
• Resources identified for completion of the given tasks.
The work proposal must be submitted in a word file through the link available in Moodle.
2) Task 2 - Assume that you are working as a database administrator. After the system requirement analysis, the following ER diagram is designed for a company involved in the sales of Agricultural Products.
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 by name AgroSalesDB_yourD with appropriate size (you can decide about the size such as 10M, 20M, 30M,..) and segment management.
ii. Create three users by name Rahma_yourID, Syed_yourID and Ali_yourID and link the thus created users to the above tablespace. (Passwords of each of the users can be same as Username)
iii. Grant the privileges of connecting to the database and creating of objects to all the users created in (ii) above,
iv. Through the Login credentials of the user Rahma_YourID, create the tables Employee and Department as shown in the E-R diagram.
v. Through the Login credentials of the user Syed_YourID, create the tables Customer and Vehicle as shown in the E-R diagram.
vi. Through the Login credentials of the user Ali_YourID, create the tables Order, Product and Order_Product as shown in the E-R diagram.
vii. Create one complex view using the thus created tables based on your analysis
viii. Create any two synonyms for any of the two tables thus created.
ix. Create index on any one of the thus created tables.
x. Create one sequence of your choice that will be helpful for the Agro Company.
xi. Insert at least 2 rows to the table for which sequence is created.
xii. Insert at least 2 rows with appropriate values on any two tables having primary key foreign key relationship.