ICT285 Databases Assignment: Solutions for January 2020 Exam

This document presents a comprehensive solution to the ICT285 Databases assignment, covering various aspects of database management systems. The solution begins with an explanation of a 3-tier architecture, emphasizing its maintainability and logical data independence. It then delves into database concepts such as super keys, candidate keys, primary keys, and integrity constraints. The assignment includes solutions to SQL queries, covering SELECT statements, joins, and aggregation functions. Furthermore, it addresses database normalization, identifying anomalies and suggesting improvements to achieve the first normal form. The solution also explores entity-relationship diagrams (ERDs) and suggests enhancements. Transaction management, including concurrency control, locking protocols, and the lost update problem, are analyzed, along with discussions on data warehousing, data cleaning, and the differences between data warehouses and student record systems. Finally, it includes examples of creating views and indexing strategies within a database context, and concludes by explaining the concept of derived attributes. This assignment provides a well-rounded understanding of database concepts and their practical application.

Question 1
a. According to given Student records, architecture diagram it is a 3 tier architecture, and
the main advantage of this type of DBMS architecture is that it is easy to maintain and
modify because any type of change request will never affect any data. After all, the
application layer will perform all the validation processes based on the client request.
b. Logical data independence is a type of data independence, and this data independence
provides an ability to modify the conceptual schema without changing the external view
and program. In logical data independence, any type of changes in schema/table format
does not affect the data residing on the database. For example, if any table format change
made by University Fees Application will not affect the actual data stored in the Student
Records Database.
c. Physical data dependency is a type of data independence, and this provides an ability to
change the physical data without effecting the logical data. For example, if the actual
database is upgraded or some changes required on the Student Records Database, then
any changes made in the database will not affect existing logical schema.
Question 2
Part 1:
i. Super Key: Super key is a set of one or more attributes which can uniquely identify a
tuple or row. For example, as per the given relation Student Number and the
combination of Student Number, Student name is the super key of the given relation.
ii. Candidate Key: Candidate key is a set of an attribute which can uniquely identify a
row. A candidate key is a remaining attribute, except the primary key, are knows as a
candidate key. According to the given relation Unit code is a candidate key.
iii. Primary Key: Primary key is an attribute which is used to identify one and only one
instance of an entity uniquely. Primary key selection is depended on the developer
because a primary key is a candidate key. For example, as per the given relation,
Student_Result Student number is the best suited primary key.
Part 2:
i. Entity Integrity constraints are responsible for ensuring the value of the primary key
is not null because the primary key is used to identify a row uniquely and if the
primary key value is null, then it is not possible to identify those rows. It is obtained
by simply set the property NOT NULL of the primary key.
ii. Referential Integrity Constraint is responsible for ensuring the relationship between
the tables. The relationship between the tables can be established by using a foreign
key. The foreign key is the primary key of the table. For example, According to the
given relations Registration Number of Motor_Vehicle is the primary key, and it is
used as a foreign key in Motor_Vehicle_Driver table. Similarly in Driver table Driver
Number is the foreign key of Motot_Vehicle_Driver table.

Question 3
Part 1:
π Titles (Unit, UnitOffering) UnitOffering.Sememter = 1, Offering.Year = 2020
Part 2:
π StudentName(Student)CourseCode = ICT285
Part 3:
σ StudentName(Student)CourseCode = ICT285 ∧ CourseCode = ICT285
Part 4:
σ StudentName(Student)CourseCode = ICT285 ∧ CourseCode ≠ ICT285
Part 5:
π StudentName (Student, Enrollement, UnitOffering) UnitOffering.Sememter = 2, Offering.Year
= 2019
Question 4
Part 1:
Select * from Employee where empGender = ‘Female’;
Part 2:
Select Employee.empName, Employee.empAddress from Employee inner join Department on
Employee.deptNo = Department.deptNo where Department.deptName = ‘IT’;
Part 3:
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 4:
Select SUM (WorksOn.hoursWorked), from WorksOn inner join Project on Project.projNo =
WorksOn.ProjNo where Project.projName = ‘Gigantic Project’;
Part 5:
Select Employee.empName, SUM (WorksOn.hoursWorked) from Employee inner join
WorksOn on WorksOn.empNo = Employee.empNo group by Employee.empName;

Question 5
Part 1:
EmployeeSocialSecurityNumber -> EmployeeFamilyName
DepartmentNumber -> DepartmentName, DepartmentExtension
Part 2:
The relation is in the first normal form as there is no data duplicity and a primary key is
present, which would be unique for all the tuple.
Part 3:
When the record of Employee Social Security Number = 888 is deleted, it results in the
deletion of the DepartmentNumber = 1, Hence, the deletion anomaly takes place.
Part 4:
Department (DepartmentNumber, DepartmentName, DepartmentExtension)
Employee (EmployeeSocialSecurityNumber, EmployeeFamilyName, DepartmentNumber)
Part 5:
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));
Question 6
A new entity is to be developed with the attributes ResultID, StudentNumer, UnitCode,
PointsObtained in it having relationship one to one with the unit and many to one with the
student. Because as per the given entity it is not able to store any student result record. Student
entity is only able to store the Student number, name and course similarly in Unit entity Unit
code, title and credit points can be stored. So a new entity has to develop with the attributes
mentioned above needs to be added to the given ERD to enable a record of student’s results in
unit.
Question 7
a. As per the above transaction schedule, there a two truncations A and B both are
performing their own transaction. Both A and B first read Ticket left and both get the
same numbers of total ticket left = 10 then B Purchase 8 tickets at time id 3 and the
transaction A Purchase 6 ticket at time id 4. Both are purchasing ticket with respect to the

initial read operation here transaction a gets the tickets but due to the transaction not able
to update total ticket left then this scenario occurred and this called lost update problem.
b. The above update problem can be solved using Two phase locking protocol. In two phase
locking protocol transaction have to lock the resources before using it and at that time no
other transactions can lock the same resource until the existing lock released. So the
above problem can be solved using two phase locking protocol.
c. According to the above transaction schedule a deadlock scenario can be occur because if
the transaction follows two phase locking protocol then when a transaction lock a
resource and perform some operations then other transactions have to wait for release if
transaction A looked has read lock to a resource and waiting for a resources which is
locked by another transaction and both waited for a resource. In this scenario a deadlock
occurred.
d. Durability is a property of the transactions which make sure to store the used data
permanently without any lose and the data not disappeared during database crashed It is
accomplished by storing all transactions into a non-volatile storage medium.
Question 8
a. 1) A student focused data warehouse stored every relevant information about different
university students and a university student record system only maintain their university
student’s details.
2) A student focused data warehouse responsible for storing every single information
about the students like personal details, university related details and many more and
university student record system store and maintain very few details about the system.
b. Data cleaning is a very important process for preparing data for a data warehouse. When
a data is imported from different sources then data is stored in different format and style
it is not possible to change the entire database for storing a data into a data warehouse.
For example three different customer-focused operational systems that had been
developed by different departments in the organisation every department stored data into
the database with their own knowledge and style which is not suitable for data
warehouse. That is why it is a very important for preparing data for data warehouse.
c. Using a student result data warehouse we can easily retrieve the detailed data of the
student. Similarly in drill down concept of OLAP cube the less detailed data is converted
to a detailed data.

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_onwer;
b. i. A view is created with the name sales where the view display salenumber,
customernumber, branch, date and amount for all customers within the database of the
retail company.
ii. create view salesdetails as (select salenumber, customernumber, branch, date,
amount from sale);
iii. View behaves just like table as the queries could be performed to store along with
fetch data from salesdetails view. This view is updatable if statement against view
could be translated in corresponding statement against theunderlying table.
iv. By using salesdetails view, the users could query as well as modify only data they
could view. Permission to access salesdetails view should be revoked or granted
explicitly, regardless of permissions on underlying tables of salesdetails view. If
same owner is for sales along with the underlying tables, there is no need of
permission for underlying tables. By defining salesdetails view as well as granting
permission for them, user could be restricted from data’s different subsets.

Question 10
a. Derived attributes are the attributes having values through calculating other attributes.
Grade Point Average value should be calculated through addition of every score of all
units and divided by the total units that is completed. After calculating Grade Point
Average value could be stored in particular column in the database by using queries.
b. i. There are many attributes in database of retail company like customerid, orderid,
salesrepid, product, price, quantity where orderid is the primary key of table. These
attributes customerid, orderid, salesrepid, product along with the primary key of the table
could benefit from indexing. Indexing would help to speed up the SELECT query.
ii. The attributes price and quantity dont need to be indexed as values of the attributes have
limited results as well as dont create have large set of data. Values of these attributes could
be fetched easily by using queries. Hence, indexing is not required for the attributes.