Reference no: EM132563796

COMP 1011 Database Systems - Middle East College

Learning Outcome 1. Manage database instances and database storage

Learning Outcome 2. Devise and implement database security policies

Assignment Objective

As a Database Administrator, to configure the database server, choose and apply skills relevant client/server environment and manage the data in an organization's business processes.

Assignment Tasks

Part 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 to task2 to task-4.
• Timeline for completion of the given tasks.

Part 2. Consider faculties (users) of a certain college, initiates remote database connections using the college application and saves attendance of their students in the class. Students' attendance details are saved in the college database server.

Using the above context as an example, draw the diagram and briefly elaborate on the series of actions done in a transaction server process architecture.

Your solution must include the use of user process, server process, log writer, database writer process and checkpoint process in performing the transaction. Solution should also include the shared memory areas (buffer pool, log buffer and query plan cache) and physical structures (data files and log files).

Note: Solution should be written strictly in accordance with the given context of faculties as users saving data in server. Simply writing theoretical points should be avoided.

Part 3. Write the relevant SQL commands for the following. In your own words, briefly elaborate the commands/keywords. Use your first name or College Id while naming the objects.
a. Create a user (e.g. user1) and assign the privilege to connect to the database and create any table (e.g. Table1)
b. Create three more users (e.g. user2, user3 and user4), and assign the privilege to connect to the database.
c. Create two roles e.g. R1 and R2.
d. Role R1 should be assigned the privileges to insert and update records on table created by user1.
e. Role R2 should be assigned the privileges to view the rows of table created by user1.
f. Assign the role R1 to user2.
g. Assign the role R2 to user3 and user4.
h. Login as user2 and insert at least two rows in the table (table1).
i. Login as user3 and user4 and view the rows of table (table1).

Part 4. Create any two related tables of your choice and insert at least two records in each of the table. Tables should include primary key and foreign key column(s). Demonstrate the usage of views by creating any two views on the created tables. At least one of the view should be based on two tables. Solution should also include the SQL commands to retrieve the data using the views.

Note: Avoid using DEPT and EMP tables. Each table should have at least three columns. Datatype of columns should be relevant.

Attachment:- Database Systems.rar