Database Design – ITS113
Assignment – Asset Management system
This assignment puts you in a hypothetical, but realistic, scenario where you work on database
design intended to support the needs of an organisation. The assignment is divided into three
parts which are intended to reinforce the iterative methodology used in a typical project. The first
part is an individual effort; each student will work on their own to develop the first draft of the
design. The second and third parts will be a group effort, where students will collaborate in
groups of no more than four, to deliver a more substantial body of work that builds on part one.
The assignment should be delivered as two components:
Entity relationship model document – This component should be delivered as a single file.
The preferred format is PDF, however Libre Office or Microsoft Office files are also
MySQL database – This component should be delivered as a MySQL dump file, exported
from your database.
Both of these components should be uploaded to the Moodle assignment page as separate files.
Entity Relationship Model Document
This document should capture the business rules, based on your understanding of the provided
information. You will need to identify the entities involved and create external model(s) that
show their relationships. Use a combination of descriptive text and entity relationship
diagrams. You should document the assumptions you make and write down your reasoning
when faced with design choices that are not straight forward.
The information provided in the scenario is far from exhaustive and a process of discovery will be
required to gain a better understanding. Some provided information may not be directly relevant
to the design of the database. You will have the opportunity to ask questions during tutorials. You
can also do your own research to discover how real world organisations operate. Your
documentation should capture this discovery process. One way of doing this is to provide proper
attribution for the sources of your information.
Page 1 of 3
Select one of the external models you produced above. This model should have at least three
entities and three relationships. Convert this external model into an internal model and
implement this as a MySQL database schema. Your database should be populated with enough
sample data to demonstrate how it works. All tables should have data – approximately three to
five rows per table should be sufficient.
Export the MySQL database into a MySQL dump file. This can be achieved from MySQL
Workbench, using the Server/Data Export menu item. You should generate an SQL dump with
the following settings:
Select: All objects in the schema
Select: Dump Structure and Data
Select: Export to Self-Contained File
Enable: Create Dump in a Single Transaction
Enable: Include Create Schema
In advanced options:
You are a part of a team that has been given a contract to provide visual effects for a number of
scenes in a big budget movie. The movie production company will be on a tight schedule and
want a streamlined system for digitally handling almost all interaction with your newly formed
company. You have been tasked with the design of a database that will support an asset
management system. Time is of the essence, so the strategy is to come up with the simplest
possible system that will support the workflow. The design should be future proof so that
additional features can be added as needed, without causing disruptions or data loss.
The shots you will be creating involve the addition of computer generated characters into live
action scenes. The expected workflow is as follows:
You will be supplied with raw camera footage from each shoot.
Besides the video, the raw camera footage also contains metadata, such as the lens used,
zoom, aperture, shutter speed, etc.
There will be additional information available for each shot, such as distance to camera
for key elements, cast / characters, locations and colour temperature of studio lights, etc.
Each set will have a 3D file supplied that provides a rough model of the set. This can be
used to project backgrounds onto the set when compositing.
Each shoot will be referenced by a scene number and within each scene there will be
several shots. Some shots can have multiple takes.
The footage will all be green screen studio shots, with some props placed in the scene.
Page 2 of 3
? Typically each shot will consist of a take of the scene only, without any actors and also
matching takes with the actors.
All related takes will be done using motion controlled camera movement.
You will be supplied with the motion control data.
There should be a person-in-charge for every asset, who will have responsibility for that
item. They should always be reachable via email and on at least one phone number, but
the system should store any additional contact information available.
Any props that were used on set and will require to be animated will be packaged and
shipped to you.
Each physical prop will have an embedded NFC tag that uniquely identifies the asset.
When the NFC tag is read, it will produce a 64 character string.
Props will need to be scanned to generate 3D models and texture maps.
Once props are digitised, they need to be shipped back promptly.
The movie production company wants the NFC tags scanned as soon as the props arrive
and also when they are sent back, to track the custody of these assets.
Your company will have to create the remaining elements required for each shot and
composite them. These elements will usually be:
background matte paintings, mapped onto 3D scene geometry
computer generated characters, animated using motion capture data
rendered props, based on 3D scans and animated using, physics emulation, particle
systems or motion capture, as required
In order to be able to work efficiently, you want to be able to see all the assets that are
required in a shot or a scene.
Shots and scenes need to be prioritised, so that there is clear visibility of what needs to be
The final deliverable is a number of scenes, each comprising of one or more shots. Each
shot will be delivered as a sequence of rendered frames.
The director expects to be able to see work-in-progress, which will be the latest test
render for each shot.
To get the maximum use of the render farm, any shot that has had an update since the
last test render should be flagged as needing a new test render.
Some assumptions you can make:
The database does not need to store the data itself. You can assume that the data will be
stored on a network and available to all who require access. All files can be accessed using
In most cases, your system does not need to understand the content of the files, however
it should be possible to assign categories to files so that they can be correctly classified as
the right kind of asset.
The design of ITS113 database
Database Design – ITS113