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

MySQL Database

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:

Enable: complete-insert

Disable: extended-insert

The Scenario

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

done first.

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

a URI.

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.