Assignment 1

CS 640 Winter 2013

Due: Thursday, January 31, 2013
Hand in: in class or via email

Part 1

Draw an E-R diagram for a database that could be used by an individual pharmacy to store pharmaceutical records in accordance with the Community Pharmacy Standards of Practice as described in 5078-HPA Bylaws Community v2010.1 (June 18, 2010) for the College of Pharmacists of British Columbia. Your diagram should capture as much of the relevant data description as possible to allow a database system to record the data needed to comply to sections 6(2), 6(4), 6(9)(b), 6(10), 7(1)(c), 7(2), 8, 9, and 11(2).

• Be sure to include and indicate a key for each entity set.
• Preferred notation for the E-R diagram is either the notiation used in the lecture slides or the notation in Teorey et al.'s article. If you use another notation, include a legend that explains this notation.
• Include additional comments that address any data consistency requirements not represented by your E-R diagram (e.g. a second possible candidate key).

Part 2

Transform your E-R diagram into a relational database schema. Describe this schema using either text or a diagram. For each relation schema, indicate:

• the primary key,
• other (possible) candidate keys (if any), and
• any foreign keys (including the attribute names and relation names of the corresponding target attributes).

Include additional comments that address any additional data consistency requirements not represented by your presentation of the schema (e.g. those functional requirements that cannot be derived from the keys, if any).

Acknowledgement: This assignment has been adopted from Frank Tompa's earlier version of this course.

Jan. 16, 2013. Olaf Hartig