Assignment: Database Management Systems
Question One
Consider the following relational database schema. Suppose that all the relations were created by (and hence are owned by) user X, who wants to grant the following privileges to user accounts A, B, C, and D:
EMPLOYEE
Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno
DEPARTMENT
Dname, Dnumber, Mgr_ssn, Mgr_start_date
DEPT_LOCATIONS
Dnumber, Dlocation
PROJECT
Pname, Pnumber, Plocation, Dnum
WORKS_ON
Essn, Pno, Hours
DEPENDENT
Essn, Dependent_name, Sex, Bdate, Relationship
Write SQL statements to for the following privileges. Use views were appropriate.
(a) Account A can modify DEPARTMENT and PROJECT relations.
(b) Account B can retrieve only DNAME, MGR_SSN, DNUMBER and DLOCATION attributes of DEPARTMENT and DEPT_LOCATION.
(c) Account C can insert or update on WORKS_ON relation and can grant any of these privileges to other users.
(d) Account D can retrieve any attribute of DEPENDENT and WORKS_ON.
Question Two
1. Consider the Student_Course table for the IT344 with the following fields
a. Std_ID
b. Course_ID
c. Std_Course_Mark
And the Course Table with the following fields
a. Course_ID
b. Course_Name
c. Maximum_Mark
d. Minimum_Mark
When the new row in the Student_Course table is added, you need to update the Maximum_Mark and the Minimum_Mark.
Write the trigger called ‘Max_Min' allowing to update the Maximum_Mark and the Minimum_Mark.
Question Three
(a) What are the advantages of fragmentation in distributed database management system?
(b) What is concurrency control? Describe problems with concurrent execution of transactions in Distributed database, which are not present in centralized databases?
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Assignment-Database-Management-Systems.rar