2) Individual Coursework Assignment 2

This assignment is worth 50% of the overall module mark.

Coursework Brief

You must design a database from a list of requirements based on the contents of lectures, producing an entity-relationship diagram, a determinancy diagram and a relational schema, with a detailed account of how the requirements have been translated into the diagrams. These should be submitted in a Word file.

Design a database system to maintain data associated with a company. The text below has been produced from a requirements elicitation exercise.

• The company has some factories for production, as well as one sales office.
• Each factory has several production departments, and the sales office includes a number of subdivisions.
• In each production department, a manager oversees its overall operation, as well as all workers working in the production department.
• Production departments produce both product parts and products.
• Each product is comprised of a number of product parts, and each product part could be used to make up multiple products.
• Both products and product parts are stored in the warehouses owned by each factory.
• Each product is only produced by one factory and sold by one subdivision of the sales office.

(a) Produce an entity-relationship diagram which represents the domain of criminal court cases, together with the detailed explanations and justifications of each entity, relationship, cardinality, and type of relationships in the diagrams. Note that some attributes should be created and assigned to the entities in the domain.

(b) Produce a determinancy diagram to document the dependencies between data-items, together with clear and reasonable explanations and justifications.

(c) Accommodate both the entity-relationship diagram and the determinancy diagram produced to a relational schema expressed in the bracketing notation.