I\'ve made the ER model above. But am not sure how to go on to make a relational

ID: 3741002 • Letter: I

Question

I've made the ER model above. But am not sure how to go on to make a relational model from here.

Derive a Relational Model from the ER-model above. List all the relations derived from the entity types of the ERD, and their initial attributes.

2. For each relationship type in the ERD justify whether it leads to a new relation, a new attribute in a relation derived from an entity type, or to a merger of two relations derived from two entity types.

3. List all the relations in the final relational model, and all their attributes

4. List all the constraints included in the conceptual schema in addition to the relevant primary keys and foreign keys.

An example format is provided.

Deriving Entity types

RELATION (attribute, attribute, attribute)

Deriving Relationship types

RELATIONSHIP NAME was derived by adding RELATION with attribute and attribute as foreign keys to link with RELATION and RELATION because of MULTIPLICITY and/or PARENT/CHILD

RELATIONSHIP NAME was derived by merging RELATION and RELATION because of MULTIPLICITY and/or PARENT/CHILD

RELATIONSHIP NAME was derived by adding attribute to RELATION as a foreign key to link with RELATION because of MULTIPLICITY and/or PARENT/CHILD

Relational Model

RELATION (attribute, attribute, attribute)

Foreign Keys

Relation (attribute) is dependent on Relation (attribute).

Other Constraints

The X attribute of the Y relation can only be from 1000 to 9999.

Explanation / Answer

For the above E-R Diagram model in question given following questions are answered.

1.The above E-R model contains following relations

STUDENT (studentID, firstName, lastName, PhoneNumber, streetNo, streetName,
suburb, city, postcode)

STAFF (staffID, firstName, lastName, emailaddress, unitID (fk))

UNIT (unitID, unitName, availableYearSemester)

ASSIGNMENTS (assignmentName, unitName (fk)),dueDateTime)

SLEEPPATTERNS (smartwatchID, totaltimeawake, totaltimeasleep)

In the above relations Underlined attributes are Primary keys, and underlined attribute followed by fk indicates it is a foreign key.

For each relationship type in the ERD following relations are created

2. a) Since Student is assigned to Staff a new relation should be created with

STUDENT_STAFF (studentID, staffID)

  b) Since Student has a sleeppatterns   STUDENT relation should be related to
         this by using smartWatchID as foreign key in STUDENT modify student
        relation as

STUDENT (studentID, firstName, lastName, PhoneNumber, streetNo, streetName,
suburb, city, postcode, smartWatchID(fk))

c) Since more than one STUDENT is enrolled with more than one UNIT a new
relation should be created with STUDENT_UNIT(studentID, unitID)

d) UNIT is related to ASSIGNMENTS with foreign key unitname.

e) Since more than one STAFF is assigned to more than one UNIT new relation
       should be created with STAFF_UNIT (staffID, unitID) and unitID should be
       removed from STAFF as it leads to redundancy of staff details for every unit if
       it is assigned with Unit.

So STAFF(staffID, firstName, lastName, emailaddress) relation is redesigned.

3. The Relational model contains following modified relations

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 (staffID, unitID) both keys are taken from STAFF AND UNIT relations

4. for 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

Constraints should be given by taking into account the domain problem and its values. but in ER Model no any such values are given.

NOTE:

IN ER MODEL SOME WORDS ARE NOT ALLOWED TO SUBMIT ANSWER SO I HAVE CHANGED TO STAFF.

Navigate

I\'ve made my code for the following problem and the function for area of the pa

I\'ve mainly done application development, but have also worked on simple web pr

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

I\'ve made the ER model above. But am not sure how to go on to make a relational

Question

Explanation / Answer

Related Questions

Navigate