Type B problems_solutions

Type B problems_solutions - Type B problems ERDs to RDMs...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Type B problems – ERDs to RDMs 6.3.1 B1 – Solution to CSU Students Each entity becomes a table. We get the following four tables, with attributes distributed into what seems to be the sensible places. Each attribute appears only once. Primary keys have been identified for Student and Subject tables, but there is no apparent key for Enrolment or Assessment_Item unless attributes are duplicated. Student {Student_Id , Student_name} Subject {Subject_Code , Subject_Name} Enrolment {Student_Grade_in_Subject} Assessment_Item {Assessment_Item_Number, Assessment_Item_Mark} Each 1:m relationship without attributes can be implemented by placing a foreign key in the many side of the relationship. The foreign key references the entity on the one side of the relationship. The tables become Student {Student_Id , Student_name} Subject {Subject_Code , Subject_Name} Enrolment {Student_Id , Subject_Code , Student_Grade_in_Subject} Assessment_Item {Student_Id , Subject_Code , Assessment_Item_Number , Assessment_Item_Mark} Note that the combination of foreign key items often provides a useful primary key in a table which has no other obvious primary key. Check each Primary Key for uniqueness Each primary key uniquely identifies a row in the corresponding table. Solution STUDENT table Student_Id, Student_Name SUBJECT table Subject_Code, Subject_Name ENROLMENT table Student_Id, Subject_Code, Student_Grade_in_Subject FOREIGN KEY (Student_Id) REFERENCES student FOREIGN KEY (Subject_Code) REFERENCES subject Note: This is one primary key but it consists of two attributes.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ASSESSMENT ITEM table Student_Id Subject_Code Assessment_Item_Number Assessment_Item_Mark FOREIGN KEY (Student_Id, Subject_Code) REFERENCES enrolment Note: One foreign key but consists of two attributes Discussion Note the way foreign keys are identified in the solution. This method makes it clear what table the foreign key relates to. 6.3.2 B2 – Solution to Conference City TOPIC Topic_id , Topic_name SPEAKER Speaker_id , Name, Phone, Topic_id FOREIGN KEY (Topic_id) REFERENCES topic VENUE Venue_id , Location, Capacity, Seating_style, Fax, Phone CONFERENCE Conference_code , Conference_name, Start_date, End_date, Venue_id FOREIGN KEY (Venue_id) REFERENCES venue PARTICIPANT Participant_id , Name, Address, Phone CATERER Caterer_id , Name, Phone, Position CONFERENCE_SPEAKER Conference_id, Speaker_id, Date, Time , Duration, Rating FOREIGN KEY (Conference_id) REFERENCES conference FOREIGN KEY (Speaker_id) REFERENCES speaker Note: The rating given by CC is, in fact, the audience rating. CONFERENCE_CATERER Conference_id, Caterer_id , Hours_worked FOREIGN KEY (Conference_id) REFERENCES conference FOREIGN KEY (Caterer_id) REFERENCES caterer CONFERENCE_PARTICIPANT Conference_id, Participant_id , Days_attended FOREIGN KEY (Conference_id) REFERENCES conference FOREIGN KEY (Participant_id) REFERENCES participant
Background image of page 2
The three tables above are required in order to to implement the three many-to-many relationships in the ERD. A relational database requires a bridging (composite) table to
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 10

Type B problems_solutions - Type B problems ERDs to RDMs...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online