The objective of this assignment is to measure your understanding of the basic concepts in the relational
database model and using entity-relationship model for database design. The assessment is in two parts,
split into four tasks which cover Basic ER Modelling and Basic Relational Modelling. The tasks are as
Part A: Entity-Relationship Modelling (12 Marks)
1. Design and plan for the implementation of a database system, diagramming the design to a high
standard using UML notation through the diagramming tool Lucidchart.
2. Model the activities of an organisation and present the model as an Entity-Relationship (ER) diagram.
Analyse this ER diagram, and possibly modify it, based on additional client requirements.
3. Map an ER diagram into a relational database schema, showing every step of the mapping.
Part B: Relational Database Model (8 Marks)
4. Answer a series of short questions about a Relational Database model.
To complete this assessment, you must be familiar with Lucidchart, which is covered during the Week 1-
This assessment will measure your ability to:
• Describe various data modelling and database system technologies.
• Explain the main concepts for data modelling and characteristics of database systems.
Task 1: Designing an Entity-Relationship Model
Fast Airconditioning Case Study
Fast Airconditioning (FA) is a company that sells air-conditioning products in Australia. The following are
the requirements for managing data about staff, offices, and properties for FA.
FA has stores throughout Australia. Each store is allocated several staff members. One staff member
manages the operations of the store. The data describing a store includes a unique store number,
address (i.e., street, suburb, and postcode), several contact types (e.g., email, fax, phone), and the name
of the staff member who currently manages the store. FA keeps track of the date each manager starts
his/her position at the store he/she is managing.
It is important to distinguish between two types of FA staff members (i.e., supervisors and agents) as
supervisors have additional responsibilities. Supervisors are responsible for the day-to-day activities of
a team of staff called sales agents. Each supervisor is responsible for up to a maximum of 8 agents at
any one time. Agents can be assigned to one supervisor and there may be agents yet to be assigned to
any supervisors. Each sales agent is based in one store but must also travel to a prespecified list of other
stores on demand. The data for each staff member includes a unique staff number, name, address,
position, salary, and name of supervisor (where applicable).
FA has a range of products that it sells to customers in Australia. At any given time at a store, a particular
product may or may not be available in stock. The website of FA needs to show the list of all the stores
where a particular product is available so customers can decide to go to another store if they wish. Some
products are not available at any stores and can only be purchased online. Other data for each product
includes a unique product number, cost of purchase, and price for sale. Each product is also associated
with several other product that are considered as reasonable replacements for it.
Based on the given description, model the given business rules, and present your model as an Entity-Relationship
(ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all
applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes, relationships and
their cardinality and participation constraints.
If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model
restrict you from representing your model.
You must use UML notation and the diagramming tool Lucidchart to draw your diagram. Your diagram must be
drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model.
A special note: This is an open-ended question with many different models that can be derived. Your model is
assessed based on how accurately it represents business rules described above.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: firstname.lastname@example.org 微信:itcsdx