Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribedDeriving 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Databases questions

Question

Describe the nature of negative messages.

Answered: 1 week ago