Question
Given the ER diagram and Relation Model below. Create four rows of sample data for each of the relations identified in the Relational Model .
Given the ER diagram and Relation Model below. Create four rows of sample data for each of the relations identified in the Relational Model. Based on one of the relations of your relational model and its sample data, specify an example of each of the following, and explain why you selected it:
? A delete operation that would run successfully
? An update operation that would run successfully
? An update operation that would not run successfully
? An insert operation that would not run successfully
Deriving Entity types
Attribute: Primary Key
(fk) : Foreign Key
STUDENT (studentID, firstName, lastName, PhoneNumber, streetNo, streetName, suburb, city, postcode)
STAFF (staffID, firstName, lastName, emailaddress, unitID (fk))
UNIT (staffID, unitName, availableYearSemester)
ASSIGNMENTS (assignmentName, unitName (fk)),dueDateTime)
SLEEPPATTERNS (smartwatchID, totaltimeawake, totaltimeasleep)
Deriving Relationship types
STUDENT_STAFF (studentID, tutorID)
Since Student is assigned to Staff a new relation should be created.
STUDENT_UNIT (studentID, unitID)
Since more than one STUDENT is enrolled with more than one UNIT a new relation should be made
STAFF_UNIT (StaffID, unitID)
Since more than one STAFF is assigned to more than one UNIT, a new relation should be created and unitID should be removed from STAFF as it causes redundancy of staff details for every unit if it is assigned to Unit.
STUDENT (studentID, firstName, lastName, PhoneNumber, streetNo, streetName, suburb, city, postcode, smartWatchID(fk))
Since STUDENT has a sleeppatterns, the STUDENT relation should be related to this by using smartWatchID as a foreign key in STUDENT.
STAFF (staffID, firstName, lastName, emailaddress)
unitID is removed from STAFF.
Relational Model
STUDENT (studentID, firstName, lastName, PhoneNumber, streetNo, streetName, suburb, city, postcode, smartWatchID(fk))
STAFF (staffID, firstName, lastName, emailaddress)
UNIT (unitID, unitName, availableYearSemester)
ASSIGNMENTS (assignmentName, unitName (fk)),dueDateTime)
SLEEPPATTERNS (smartwatchID, totaltimeawake, totaltimeasleep)
STUDENT_STAFF (studentID, staffID) both keys are taken from STUDENT AND STAFF relations
STUDENT_UNIT(studentID, unitID) both keys are taken from STUDENT AND UNIT relations
STAFF_UNIT (tutorID, unitID) both keys are taken from STAFF AND UNIT relations
Constraints
Postcode in STUDENT SCHEMA should be between 0000 to 9999.
UnitID should be given only between no. of Units in 01 to 99 .
StaffID should be given only between no. of STAFF in 01 to 99 or more and should be assigned only to valid Units with IDs
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started