Assignment on DBMS Architecture

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