Your company has been awarded a contract to implement an information system for

Question

Your company has been awarded a contract to implement an information system for a foreign company. The contract includes a specification of the relevant information in the form of the following ER diagram.

Convert the ER diagram to a relational schema on the next page. Your schema can be described by a set of "CREATE TABLE" commands or by using the UML-like boxnotation used in class. Using a single sentence for each case, explain any constraints reflected in the ER diagram that are not captured by your relational schema.

Explanation / Answer

Relational schema

Rules for converting strong entity types

1.Each entity type becomes a table.

2.Each single valued attribute becomes a column.

3.Derived attributes are ignored

4.Composite attributes are represented by components.

5.Multivalued attributes are represented by separtate teble.

6.Key attribute of a entity type becomes the primary key of a table.

In the above example

Course is a strong entity ,courseName and CourseNum are single valued attribute and CourseNum is a key attribute.So it is a primary key.Primary key attribute is underlined.Representation is shown below

Course(CourseNum,CourseName)

Student is a strong entity ,StudentName and GPA are single valued attribute and StudentNum is a key attribute.So it is a primary key.Primary key attribute is underlined.Representation is shown below

Student(StudentNum,StudentName,GPA)

Professor is a strong entity ,ProfNum and ProfName are single valued attribute and ProfNum is a key attribute.So it is a primary key.Primary key attribute is underlined.Representation is shown below

Student(ProfNum,ProfName)

offSite_section is an entity and location is an attribute

OffSite_Section(location)

Rules for converting weak entity types

1.Weak entity types are converted into a table on its own with the primary key of strong entity acting as a foreign key in the table

2.The foreign key along with the key of the weak entity form the composite weak entity of the table

Section is a weak entity type and course is the strong entity associated with it.So we have to use the primary key of course entity type in Section entity type along with attributes of Section entity type

Course(CourseNum,CourseName)

Section(CourseNum,SectionNum,term)

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

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