IT403 Fundamentals of Databases Assignment: Relational Algebra

This assignment solution for IT403 Fundamentals of Databases covers fundamental concepts including primary and foreign keys, and relational algebra. The solution provides examples of relations illustrating primary and foreign keys, explaining their roles in database design. The solution also addresses relational algebra queries, demonstrating how to retrieve specific data based on given criteria, such as finding students who have taken a specific course, students with specific grades, and courses that were not offered in a specific term. The student-provided solution offers clear explanations and examples to facilitate understanding of database concepts. This assignment is a valuable resource for students studying database systems and relational database design.

Running Head: FUNDAMENTALS OF DATABASE 1
FUNDAMENTALS OF DATABASE
Student Name
Institution
Course
Date

FUNDAMENTALS OF DATABASE 2
Question 1
Give an example of 3 relations illustrating primary keys and foreign keys.
Solution
Some of the basic concepts of primary and foreign keys are (Connolly & Begg, 2015):
 A primary key identifies a unique record in a relation
 A primary key cannot be null and should be very unique in a certain entry
 Foreign keys identify other entries in a relation
 In order to refer a record uniquely in another table, it should also be unique in that table
in order to avoid conflicts, a foreign key should hence be a primary key of a referencing
table
An Example of 3 relations
(i)Relation customer
TABLE Customer
{
CustomerID int NOT NULL,
Name varchar (15),
Phone VarChar (10),
PRIMARY KEY (CustomerID)
}

FUNDAMENTALS OF DATABASE 3
(ii)Relation Item
TABLE Item
{
ItemID int NOT NULL,
ItemName VarChar,
ItemPrice int,
PRIMARY KEY (ItemID)
}
(iii)Relation Order
TABLE Order
{
OrderID int NOT NULL,
CustomerID int,
ItemID int,
Quantity int,
Total int,
PRIMARY KEY (OrderID),

FUNDAMENTALS OF DATABASE 4
FOREIGN KEY (ItemID),
FOREIGN KEY (CustomerID),
}
As observed in the relations above, the relation “Order” has ”CustomerID” and “ItemID”
as its Foreign keys while the “OrderID” is its primary key. This clearly shows that foreign keys
are the primary keys of other relations (Lee & Zheng, 2015).
Question 2
Consider the following relations (Luo et al., 2018):
Student(sID, surName, firstName, campus, email)
Course(dept, cNum, name)
Offering(oID, dept, cNum, term, instructor)
Took(sID, oID, grade)
Such as:
Offering[dept, cNum] ⊆ Course[dept, cNum]
Took[sID] ⊆ Student[sID]
Took[oID] ⊆ Offering[oID]

FUNDAMENTALS OF DATABASE 5
Answer the following query using relational algebra:
Give the Student number of all students who have taken the course number “343” by the
department “CS”
Answer
ЛsIDƠdept=”csc”˄ cNum=343(Took Offering)
Find sID of all students who have earned some grade over 80 and some grade below 50.
Answer
(ЛsIDƠgrade >80Took) ∩ (ЛsIDƠgrade <50Took)
Find the Terms when the course number “369” by the department “CS” was not offered.
Answer
( ЛtermOffering)- ( ЛtermƠ dept=”csc” ˄cNum=369Offering)
Find the Department and course number of courses that have never been offered.
Answer
(Лdept, cNumCourse)-( Лdept,cNumOffering)

FUNDAMENTALS OF DATABASE 6
References
Connolly, T., & Begg, C. (2015). Database systems. Pearson Education UK.
Lee, C. H., & Zheng, Y. L. (2015, June). Automatic SQL-to-NoSQL schema transformation over
the MySQL and HBase databases. In 2015 IEEE International Conference on Consumer
Electronics-Taiwan (pp. 426-427). IEEE.
Luo, S., Gao, Z. J., Gubanov, M., Perez, L. L., & Jermaine, C. (2018). Scalable linear algebra on
a relational database system. IEEE Transactions on Knowledge and Data
Engineering, 31(7), 1224-1238.