Question 8 (8 marks)
Convert each of the following ER diagram fragments into a relational schema expressed as a collection of PostgreSQL CREATE TABLE statements. Your schemas must show all primary key constraints and foreign key constraints. None of the attributes a, b, c, e, f, g may contain null values. Determine other not null constraints from the context. You may add any new attributes that you need to represent all aspects of the ER diagram; assign them types and constraints as appropriate. The precise name of any new attributes is not important, as long as they are different from existing attributes. Also, document any semantic aspects suggested by the diagram which cannot be represented in the relational schema using standard SQL constraints.
Use the following to determine the type of each attribute:
- any attribute called id is a serial attribute
- attributes a, f and g have type integer
- attributes b, c, e and m have type text
Exercises:
-
Map this entity and its attributes into an SQL schema.
-
Map this ER design into an SQL schema.
-
Map this class hierarchy to an SQL schema using the ER mapping.
Instructions:
- Type your answers to this question into a file called q8.txt
- Submit via: give cs3311 exam_q8 q8.txt or Webcms3