Answers To Question Number - 1

Answer to question number 1
Part a
The architecture provides a greater flexibility for the development teams of the
companies which would be helpful in updating any part of an application which is independent
from the other parts.
Part b
The ability changes a logical schema without changing the external schema is called
logical data independence. In the example provided a student can easily view their results in
MyInfo but cannot change in the Students record database.
Part c
The ability changes a physical schema without changing the application program is called
logical data independence. The developer would be able to add or update data which would not
require the program of MyInfo to be changed.
Answer to question number 2
Part a
Part i
The set of attribute which would be able to identify a tuple uniquely is known as the
super key. For example (Student Number, Student Name) and (Student Number, Unit code)
Part ii

Candidate key is a super key but not a primary key. For example, Student Number and
Unit code are the candidate keys.
Part iii
Primary is the unique identity attribute of a relation which is required for each tuple in a
relation. Student Number is the primary key in provided relation.
Part b
Part i
The primary key value of entity cannot be null as stated by the entity integrity constraint.
The Registration Number cannot be duplicated in MOTOR_VEHICLE.
Part ii
The primary key value of a table can be referenced in another table as per the referential
integrity constraints. The Registration Number in MOTOR_VEHICLE can be referenced in
MOTOR_VEHICLE_DRIVER.
Answer to question number 3
Part i
π Titles (Unit)⨝ σ(UnitOffering)Offering.Sememter = 1, Offering.Year = 2020
Part ii
π StudentName(Student)CourseCode = ICT285
Part iii

σ StudentName(Student)CourseCode = ICT285 ∧ CourseCode = ICT285
Part iv
σ StudentName(Student)CourseCode = ICT285 ∧ CourseCode = ICT285
Part v
π StudentName (Student) ⨝ σ(Enrollement)⨝ σ(UnitOffering)Offering.Sememter = 2,
Offering.Year = 2019
Answer to question number 4
Part i
Select * from Employee where empGender = ‘Female’;
Part ii
Select Employee.empName, Employee.empAddress from Employee inner join Department on
Employee.deptNo = Department.deptNo where Department.deptName = ‘IT’;
Part iii
Select Employee.empName, from Employee inner join Department on Employee.deptNo =
Department.deptNo inner join Project on Project.deptNo = Department.deptNo where
Project.projName = ‘Gigantic Project’;
Part iv

Select SUM (WorksOn.hoursWorked), from WorksOn inner join Project on Project.projNo =
WorksOn.ProjNo where Project.projName = ‘Gigantic Project’;
Part v
Select Employee.empName, SUM (WorksOn.hoursWorked) from Employee inner join
WorksOn on WorksOn.empNo = Employee.empNo group by Employee.empName;
Answer to question number 5
Part i
EmployeeSocialSecurityNumber -> EmployeeFamilyName
DepartmentNumber -> DepartmentName, DepartmentExtension
Part ii
The relation is in First normal form as there is no repetition in the primary key field
EmployeeSocialSecurityNumber.
Part iii
If the record EmployeeSocialSecurityNumber = 888 department name HQ is deleted and
deletion anomaly takes place.
Part iv
Department (DepartmentNumber, DepartmentName, DepartmentExtension)
Employee (EmployeeSocialSecurityNumber, EmployeeFamilyName, DepartmentNumber)
Part v

CREATE TABLE Department (DepartmentNumber int PRIMARY KEY, DepartmentName
varchar(50), DepartmentExtension int);
CREATE TABLE Employee (EmployeeSocialSecurityNumber int PRIMARY KEY,
DepartmentName varchar(50), DepartmentNumber int FOREIGN KEY REFERENCES
Department(DepartmentNumber));
Answer to question number 6
The entity named result is to be created with entities ResultID, StudentNumer, UnitCode,
PointsObtained. The relationship in between Unit to result is one to one, whereas the relationship
in between Student and Result is One to many.
Question 7
a. Problem of lost update takes place when two separate transactions are looking for
updating same column within same row in database at same time. A transaction updates
specific column in specific row, while other which began shortly afterward didn’t see the
update before updating same value. In the transaction schedule, both transaction id A and
B tries to update transaction id column as same time, after operation column is updated,
which causes problem of lost update in this transaction schedule.
b. For solving the problem of lost update, few protocols concerning positioning of lock
update operation must be followed in each transaction. Here, Two Phase Locking (2-PL)
is used for solving problem of lost update, which ensures serializablity. This problem of
lost update could be solved by following protocol of two phase locking within two
phases. The growing phase where new locks over data items might be acquired, however
none could be released. The shrinking phase where existing locks might be released

however no new locks could be acquired. In this transaction schedule, growing phase
could be used for preventing problem of lost update.
c. Two phase locking might limit concurrency which occur in schedule as transaction might
not be able in releasing item after using it. In transaction schedule, while 1st row is
updated, and 2nd row tries to update at same time, this causes deadlock in the transaction
schedule.
d. In transaction management, durability is ACID property that guarantees transaction
which have committed would survive permanently. Durability could be achieved through
flushing log records of the transactions to the non-volatile storage just before
acknowledgement of commitment. It is property which ensures transactions are
permanently saved and don’t disappear accidently or get deleted, even during database
crash. It is set of the properties which guarantees reliability of every transactions of
database.
Question 8
a. Online Transaction Processing Systems (OLTP) are characterized by huge quantity of
short online transactions such as INSERT, DELETE and UPDATE, whereas Business
Intelligence Systems are characterized by transactions’ low volume. OLTP systems’
major emphasis is putting on quite quick query processing as well as maintaining
integrity of data within multi-access environments. However, in Business Intelligence
Systems, queries are very complex often as well as involve aggregations.
b. Data cleansing is process to detect as well as correct inaccurate or corrupt records from
record set, database or table as well as refers to identify incorrect, irrelevant, inaccurate
or incomplete parts of data, then replacing, deleting or modifying coarse or dirty data.

ETL is general procedure to copy data from a source to destination systems that
represents data quite differently from source or in different context when compared to
source. Data is extracted from source system. In next stage, series of functions or rules
are applied to extracted data for preparing it to load into end target. Finally, the data is
loaded into end target by load phase that could be any data store which includes simple
delimited data warehouse.
c. In operation of drill down, data of less details is converted to data of high details. This
could be done though moving down into concept hierarchy and adding new dimension to
it.
Question 9
a. select default_tablespace from dba_users
where username = 'DATA_OWNER';
DEFAULT_TABLESPACE
USERS
grant create view, create procedure, create sequence to data_owner;
b. i. A view could be created named sales where the view would display salenumber,
customernumber, branch, date and amount for every customer within the database.

ii. create view sales as (select salenumber, customernumber, branch, date, amount
from sale);
iii. View behaves like table as queries could be performed for storing as well as
fetching data from the view. View is updatable only if statement against view
could be translated in corresponding statement against underlying table.
iv. By using view, the users could query as well as modify only data they could see.
Permission for accessing sales view should be revoked or granted explicitly,
regardless of permissions on underlying tables of sales view. If same owner is for
sales along with the underlying tables, there is no need of permission for
underlying tables. By defining sales view as well as granting permission for them,
user could be restricted from data’s different subsets.
Question 10
a. Derived attribute is attribute value of whose is calculated by using other attributes.
Derived attribute does not need to be stored physically in the database, it could be derived
with the help of using algorithm. Here, Grade Point Average needs to be calculated by
adding all the scores of each unit and then divided by total units which is completed.
After calculating Grade Point Average could be stored in a specific column within the
database by the help of queries.
b. i. There are several attributes within database of a large retail company such as
customerid, orderid, salesrepid, product, price, quantity where orderid is the primary key

for the table. These attributes customerid, orderid, salesrepid, product along with the
primary key of the table could benefit from indexing. Indexing would help in speeding up
the SELECT query. Indexing would help in making unique row or not have duplicate
values. In indexing is set in fill text index, data could be searched against huge string
values.
ii. The attributes price and quantity do not need to be indexed as values of these attributes
have limited results and do not create have huge set of data. Values of these attributes
could be fetched quite easily with the help of queries. Hence, indexing is not required for
these attributes.