Microsoft Word - Assessment 1 v1.2.docx
Database System
Assessment Guide
Assessment 1 - Case Study
Database System – Assessment Guide 1|Page
This project is the main assessment of this subject. It is worth 20% of your total assessment.
You are required to design a database system for AITGames who operates a platform game
store franchise in Australia. All work is to be completed individually. If parts or all of the
source code you submit is not your own work, you will receive ZERO.
Scenario
You are contracted by AITGames to develop software that records everything about the
games industry. Your first task is to design a database structure to work with this software.
Specifically, the database would need to be able to record the following things:
• Developers who create games. This includes individual developers and teams of
developers. Production Studio for teams would need to be recorded as well. (Hint:
only developers in a team would have a relationship with a production studio)
• Games & Series. Information about every game in the series must be recorded. Some
Games can appear in multiple Game Series (such as Wii family sports pack). Game
information needed to be display in the system includes: Name, Platform, Release
Date, Genre, and price.
• Advertising video. Some games will have advertising on television and/or other social
media. Each advertising clip has a director who can work with many ads. The length
of the advertisement is different depending on the
oadcasting channel, and needs to
e recorded.
• Sale promotion and campaign. Some promotions will be valid for a single time period
and some of them may be valid at multiple times. The database must keep track of
each promotion’s information, such as time period, the products included in the
promotion, promotion details and also the promotion name.
• AITGames store. Every store needs to maintain stock available of games and set the
minimum stock alert for each game in the system (Different store
anches may have
different stock alert level). Each store
anch has many staff members and information
about them must be kept in the database including job title and salary. There are also
different types of employees such as managers and accountants. Managers have their
own parking space and manage other employees. Accountants have a certificate to be
a CA (Chartered Accountant).
Task
Your task is to develop an entity relationship diagram, relational model, and data dictionary
for the scenario. Specifically, you must follow these rules:
ER Diagram:
• Create an entity relationship diagram using any drawing software of your choice.
• Your ERD must include all entities mentioned in the scenario. You are free to add
more if you wish.
• Each entity must have at least 3 attributes excluding keys and the ones explicitly
mentioned in the scenario. For example, game information should have a minimum
Database System
Assessment Guide
Assessment 1 - Case Study
Database System – Assessment Guide 2|Page
total of 9 attributes (key, name, platform, release date, genre, price and 3 others of
your choice).
• Note: remember to convert any many to many (M:N) relationships into composite
entities.
• Note: remember to add in your primary and composite keys for all entities in your
diagram.
Relational Model
• Convert your completed ERD to relational model using Excel or Li
eOffice Calc.
• Make sure your relational model is in 3rd normal form. Note: you don't need all the
previous forms evident, just 3rd normal form.
• Include a minimum of 5 tuples of data in each table.
• Note: remember to add in all your foreign keys at this stage. Hint: The foreign key is
added to the table that sits on the Many to the One side. So many something should
elate (foreign key) to the one thing.
Data Dictionary
• Create a data dictionary for all your attributes using Microsoft Excel or Li
eOffice
Calc.
• Your data dictionary must include the following:
o Field name, type, and source table (table the attribute is from)
o Description
o Minimum and maximum length
o Format
o Range
What to submit
Submit your assignment via canvas. Each section is due at different times (Check Canvas):
• 1 x High quality image (ERD)
• 1 x MS Excel file (relational model)
• 1 x MS Excel file (data dictionary)
Late penalty
Check subject syllabus