Database Systems Assignment: Relational Database Design

This document presents a comprehensive solution to a database systems assignment, addressing various aspects of database design and management. The assignment covers fundamental concepts such as database normalization, entity integrity, and relational models. It includes practical exercises i...Read More

Running head: DATABASE SYSTEMS
DATABASE SYSTEMS
Name of the Student
Name of the University
Author Note

1DATABASE SYSTEMS
Section A.
QustionA1.
[a]
(i) Students (StudID, fname, lname)
Unit (uCode, description)
Enrols (EnrolID, StudID, uCode)
(ii) Primary key and foreign key in each table:
Table Primary key Foreign key
Students StudID
Unit uCode
Enrols EnrolID StudID, uCode
(ii) Students (StudID (pk), fname, lname)
Unit (uCode (pk), description)
Enrols (EnrolID (pk), StudID (fk), uCode (fk))
[b]
(i)
Entity integrity guarantees that every row of a table contains distinctive value and
non-null primary key. Thus it ensures that each table signifies a single instance of entity in
the table. Relational state model ensures that every relation or a table will have an identifier

2DATABASE SYSTEMS
known as primary key. As entity integrity ensures unique identity, thus by implementation of
relational model it will ensure that no duplicate value is added. As in relational model
primary key attribute is provided to each row. From figure 1 it can be stated that each
attribute contains an entity that is a primary key. Thus it ensures data integrity and also with
each attribute having a primary key it ensures that every relation maintained contains an
identifier. In the attribute faculty, department, student, unit and AcadStaff the primary key
entities are facNo, depNo, stuId, uCode and staffID respectively.
(ii)
This would be consistent with the database as there is 1 to many relations in between
the Student and the unit.
[c]
The staff table would be implemented as per the image provided below:
Here deptID is the mandatory foreign key filed used in the table.
Question A2.
[a]

3DATABASE SYSTEMS
There are basically three types of anomalies that have the capability affect a database.
These includes insert anomaly, delete anomaly and update anomaly. The characteristics of
these three anomalies are as follows:
Insert anomaly: this anomaly occurs when the user is not able to insert a data due to some
other missing data. This anomaly is mostly seen in the fields where foreign key must not be
NULL. As a result of this anomaly data redundancy can occur. Considering table 1, it can be
stated that insertion anomaly will occur in case a new student is being enrolled and the user
wants to enter it in the student name but he has not assigned with any studID. Thus it is
important for the student to have studID otherwise insertion anomaly will get generated.
Delete anomaly: this anomaly occurs when data gets lost unintentionally or gets deleted as
the other field related to this data is being deleted. Surpassingly in the student table, the user
wants to delete stuID for the student Susan Ross, it will delete the entire row. This type of
anomaly is known as delete anomaly.
Update anomaly: this anomaly occurs in the situation when a data is being partially uploaded
in the database. Thus to avoid such situation it becomes important to normalize a database.
For example in case the user wants update the credit of Daniel turco to 4. As a result each
field having 3 credit points will get updated to 4.
[b]
(i)
Functional dependencies
stuID stuName
Dept Buiding, Tel
Unit, stuID Grade, Credit

4DATABASE SYSTEMS
(ii) Partial dependency
Unit, stuID -> Grade, Credit
Grade and Credit are partially dependent on Unit.
(iii)
Student (StuID (pk), StuName)
Department (Dept (pk), Building, Tel)
Unit (Unit (pk))
Credit (ScoreID (pk),StudID(fk),Unit (fk), Grade, Credit )
A3.
[a]
Select COUNT(stuID), DeptNo from PhDStudent
GROUP BY DeptNo.
[b]
Select AcaStaff.fName, AcaStaff.lName, AcaStaff.highestDegeree, PhDStudent.stuName
From AcadStaff
JOIN PhDStudent ON PhDStudent.staffID = AcadStaff.staffID
JOIN Department ON PhDStudent.deptNo = Department.deptNo

5DATABASE SYSTEMS
Section B
QuestionB1.
[a]
EmployeeNo and employeeName
Here employeeNo is used as input and employeeName as output
[b] A trigger is a special type of procedure that gets executed automatically in response to
occurrence of an event in database server. DML trigger gets executed when a user tried to
modify or change a data with the help of data manipulation language event. Trigger helps in
maintaining data integrity properly. By defining and creating a trigger it ensures that data
stored is gone through specific data integrity checks. This also helps in reviewing flagged
records.
[c]
CREATE PROCEDURE Find_Employee (IN deptName VARCHAR(50))
BEGIN
SELECT * from employee where dept=deptName;
DECLARE empRec CURSOR FOR
….
OPEN empRec;
REPEAT
….

6DATABASE SYSTEMS
END REPEAT;
CLOSE empRec;
END
Question B2.
[a]
(i) in process T1
value of a= 100 , value of b =150 , value of c=1
In process T2
value of a=100, value of b=150, value of c=1
ii)

7DATABASE SYSTEMS
 T1 is used to read data item X from DB and increments the value by 10.
 T2 then reads data item X and gets incremented by 20
 T1 gives the value of X that is 60 within local cache and as it has not performed
any commit thus value will not get updated in DB.
 T1 read Y=50
 T2 writes X, as T2 performs x=x+20, i.e. X=70 and will be written in local cache.
 The value of X updated in local cache gets lost and is known as Lots Update
problem
 T1 will get increment Y by 10
 T1 will write Y = 60 to local cache
 T1 commit updates Y value as 60 to DB
 Finally X=7= and Y= 60 values get stored in DB
[b]
1. In order to maintain a proper consistency in database before and after a transaction it is
important to follow certain properties. These are known as ACID properties. C stands for
Consistency where Consistency is referred is that in every situation a database must maintain
integrity constraints before and after transaction. These is important to maintain a consistent
state after every transaction. It is important to check that no transaction creates any opposing
effect on data within the database. If the database maintained before transaction is consistent,
then it should remain consistent after transaction as well. Database administrators
responsible for managing the consistence.

8DATABASE SYSTEMS
2. A stands for Atomicity in ACID properties. ACID property ensures that every transaction
should be treated as an atomic component. This means that either all of its operation will get
implemented or none will get executed. There is no position in which database transaction
can be left partially completed. States needs to be distinct before the implementation or after
execution of the transaction. This is the responsibility of database administrator to maintain
the atomicity.
3. This is important to have every transaction serializable as it is provides highest isolation
level. This ensures that execution is done serially. This is defined as an operation that gets
executed parallel and appears to be serially executing.
[c]
The T1 and T4 are committed, hence the value stored in B would be 25, A would be
20, C would be 40 and all other values would be rolled back.
Question B3.
[a] difference between SAN and NAS technology are as follows:
NAS includes dedicated hardware that are connected through Ethernet connection to connect
with local area network. Whereas SAN uses fibre channel to connect a devices sets that are
allowed to share data with one another. NAS device works as a network node whereas SANs
are not physical always. The user can create virtual SANs with the se of defined software
program. San is used for database as it provides a network with high speed and provides
block level network.
[b]
 (i) πstuID, stuName, deptNo σ lname = "Jhonson"((AcdStaff)⨝(PhDStudent));

9DATABASE SYSTEMS
 (ii) relational algebra is a procedural language that is used to describe the ways in
which a new relation can be developed in DBMS from one or more relations in
database. It is denoted with the notation ÷. For example a(A) and b(B) is in a relation.
Then a ÷ b will give the result that will hold restricted tuples in a.

10DATABASE SYSTEMS
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Colebunders, E., Lowen, R. and Van Opdenbosch, K., 2016. Regularity for relational
algebras and approach spaces. Topology and its Applications, 200, pp.79-100.