Database Systems - Study Material and Solved Assignments | Desklib

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