Assignment Database managemnt
smartman1212
IT344Assignment2-QuestionsDatabase.docx|
Pg. 07 |
|
خطأ! استخدم علامة التبويب "الصفحة الرئيسية" لتطبيق Heading 1 على النص الذي ترغب في أن يظهر هنا. |
|
|
|
|
Deadline: Day 23/03/2019 @ 23:59
IT 344
College of Computing and Informatics
|
|
|
|
|
|
|
|
Question One Chapter 25 page 904
1.5 Marks
Learning Outcome(s):
LO3
Explain the idea behind using SEMIJOIN in distributed query processing.
The idea behind distributed query processing using the semijoin operation is to reduce the number of tuples in a relation before transferring it to another site. Intuitively, the idea is to send the joining column of one relation R to the site where the other relation S is located; this column is then joined with S. Following that, the join attributes, along with the attributes required in the result, are projected out and shipped back to the original site and joined with R. Hence, only the joining column of R is transferred in one direction, and a subset of S with no extraneous tuples or attributes is transferred in the other direction. If only a small fraction of the tuples in S participate in the join, this can be quite an efficient solution to minimizing data transfer.
Question Two Chapter 25 page 894
1.5Marks
Learning Outcome(s):
LO3
In your own words explain what is meant by Data Fragmentation?
Given the following customer relation, apply the horizontal fragmentation based on customer's gender.
|
Customer_id |
Name |
Area |
Payment type |
Sex |
|
112 |
Ahmed |
jeddah |
Credit card |
male |
|
211 |
Khaled |
riyadh |
cash |
male |
|
311 |
Noura |
madinah |
cash |
female |
Distributed Database systems provide distribution transparency of the data over the DBs. This is achieved by the concept called Data Fragmentation. That means, fragmenting the data over the network and over the DBs. Initially all the DBs and data are designed as per the standards of any database system. In the other words, Dividing the whole table data into smaller chunks and storing them in different DBs in the DDBMS is called data fragmentation. By fragmenting the relation in DB allows:
Easy usage of Data
Efficiency
Security
Parallelism
Reliability
Balanced Storage
https://www.tutorialcup.com/dbms/data-fragmentation.htm
Fragment 1
|
Customer_id |
Name |
Area |
Payment type |
Sex |
|
112 |
Ahmed |
jeddah |
Credit card |
male |
|
211 |
Khaled |
riyadh |
cash |
male |
Fragment 2
|
Customer_id |
Name |
Area |
Payment type |
Sex |
|
311 |
Noura |
madinah |
cash |
female |
Question three
1.5Marks
Learning Outcome(s):
LO3
Suppose DBA has created three accounts Alpha, Bravo, and Charlie. Alpha is the owner of the following database schema;
STUDENT: Sid, Lname, Fname, Phone, Gpa
COURSE: Cid, Title, Level, Units, Location, Time
INSTRUCTOR: Iid, Lname, Fname, Designation, Salary, Dept
.
Write SQL statements to grant or revoke following privileges. "
Create the views if and when necessary.
a) Alpha wants to allow Bravo to retrieve information from STUDENT and INSTRUCTOR tables and also to be able to propagate the SELECT privilege to other accounts.
GRANT SELECT
ON STUDENT, INSTRUCTOR
TO Bravo
WITH GRANT OPTION;
b) Alpha wants to give a capability to retrieve Title, Location, and Time from the COURSE relation where the Level = 6 to Charlie and to be able to propagate the privilege.
CREATE VIEW CourseView AS
SELECT Title, Location, Time
FROM Course where Level=6;
GRANT SELECT ON CourseView
TO Charlie
WITH GRANT OPTION;
c) Alpha decides to revoke the SELECT privilege on the STUDENT relation from Bravo
REVOKE SELECT ON STUDENT
FROM Bravo;
Question Four
1.5 Marks
Learning Outcome(s):
LO3
Suppose a public sector university in Saudi Arabia wants to implement the strictest access control to its databases. Instead of allowing each user to control access to their own data, the university wants system resources to be controlled by the operating system under system administrator with an hierarchical approach of assigning security labels to the resource objects.
Answer the following questions;
a) Which database security mechanism would you recommend to the university?
Mandatory Access Control
b) Motivate your recommendation by describing two advantages.
Simple security property: It states that process labeled with higher classification cannot access or read information or resources.
Simple Integrity Property: In which low integrity subject cannot have the rights to write or modify high integrity data.
c) What are the two disadvantages of using this approach?
MAC models put restrictions on user access that, and according to security policies, does not allow for dynamic alteration.
MAC needs to place the operating system and associated utilities outside the access control frame work.
Another answer from Internet
A)
· Mandatory security mechanism:
B)
1. prevents illegal flow of information by enforcing strict labelling mechanism.
2. This mechanism is suitable for organisations,need central controls
3. more secure than Discretionary access control
4. This is implemented in critical systems like military applications.
C)
1. Requires strict classification of resources and users.
2. System is Rigid, and applicable to few environments.
3. Difficult to implement.
