COMP3311 20T3 The University of New South Wales Database Systems
COMP3311 Database Systems
20T3
[Instructions] [Notes] [Database] [Course Website] [Cheat Sheets]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12] Question 8 (7 marks)
Consider the following ER diagram, describing the part-time and casual employees in a small company.
Assume that all people have an employee ID (a number), a name and a position (e.g. manager, developer, etc.) Salaries are determined based on the position and so are not stored in this part of the data model. For part-time employees, we need to record the fraction they work (e.g. 50% of full-time, etc.). For casual employees, we need to record the hours they work. Note that casuals can only work for one time period on any given day (i.e. one shift).
For each exercise below, do the following:
Express your schema as one or more PostgreSQL CREATE TABLE statements.
Give any attribute and table constraints that help to enforce the semantics implied in the diagram.
Specify appropriate primary keys (always) and foreign keys (if needed) for all tables. You must specify appropriate types for attributes, based on the type information given in the diagram.
Explain how the total participation and disjoint semantics are/are-not preserved in the schema.
Exercises:
a. Map the above ER diagram into a relational schema, using the ER-style mapping for
translating the subclasses.
b. Map the above ER diagram into a relational schema, using the single-table mapping for translating the subclasses.
Note: If writing any constraint would require a large complicated expression, you do not need to write the expression; simply write “(large complex constraint)” and assume that it’s correct.
Instructions:
Type your answers to this question into a file called q8.txt Submit via: submit q8.txt
End of Question