INFS1200/7900 Information Systems
Assignment – Part 2 (15% of Final Grade)
Part 2 Due: 23 October 2019 @ 5.00 PM
In Part 2 of the assignment, a schema is provided to be implemented in MySQL.
The implementation will include creating tables, defining constraints, uploading or
entering sample data and writing SQL queries.
This part is done individually by all students.
1. Part 2: Implementation
The following describes the scope and requirements of the implementation in
PHPMyAdmin. The schema to be implemented will be provided after the submission
of the design part.
Tables and Constraints
Create a database in PHPMyAdmin called HOMECINEMA. You need to implement the
schema provided by creating the tables and constraints. Your database should enforce
basic constraints, such as:
– Referential integrity. Multiple referential integrity constraints can be extracted
from the specification.
– Domain. Attribute values are restricted to the allowed data types.
– Key and Entity integrity constraints.
– Semantic constraints if any/as given in the Universe of Discourse.
Populate the database with enough meaningful sample data (at least 5 tuples per
table) to allow us to test the functionality offered by your information system. This
data can be made up or sourced from any websites you choose.
Write the following queries in SQL in your HOMECINEMA database:
1. Find all movies with the tag ‘Action’
2. Find all accounts registered between January 1st, 2014 and January 1st, 2016
3. Find the product(s), which has casted ‘Brad Pitt’, that has been watched the
4. Find all cast members who have acted in a TV show episode, but have not
acted in a movie
5. Find the TV show(s) with the longest cumulative runtime
6. Find all accounts where the owner is older than 21 years old. (Note: This need
to be correct at the time the query is run)
7. Find all playlists that do not contain a product that aired after January 1st, 2012
8. Find all movies that are the third move in their franchise. (i.e. the sequel of a
9. Find all playlists which contain a movie that the account owner has not
10. Find which account(s) have watched at least all the products that ‘Idris Elba’
has been cast in
Each part of the assignment is allocated 100 points which will be converted into 15
Part 2 Implementation
Basic Implementation (Tables, Data Types, Primary Keys, Sufficient Data) 30 points
Foreign Keys 20 points
Queries (5 Marks Each) 50 points
This assignment targets the following assessment criteria of this course:
1. Analyse, extract and structure information system requirements from a variety
of organizational contexts
2. Express queries using the SQL language to provide correct and secure retrieval
of data from relational databases
3. Construct a small-scale information system in a relational database
4. Perform information systems analysis
Submissions will be done via Blackboard. Marking will be done through an electronic
marking tool called Gradescope, which will also be used for providing feedback.
Note: You will need to submit the following two items to blackboard separately:
1) Your completed submission template (As a PDF file)
• Name your submission PDF as: xxxxxxxx-yyyyyyyy-A2.pdf, and replace
xxxxxxxx with your student ID, and yyyyyyyy with the course code
(infs1200 or infs7900) e.g. 12345678-infs1200-A2.pdf
2) An export of your HOMECINEMA database (As a .SQL file)
• Name your .SQL file submission as: xxxxxxxx-yyyyyyyy-A2.sql, and
replace xxxxxxxx with your student ID and yyyyyyyy with the course code
(infs1200 or infs7900) e.g. 12345678-infs1200-A2.sql
Please use the supplied answer template for all answers. Your work must fit in the
predefined sections or it cannot be marked
Submit your assignment electronically via the provided links on Blackboard under the
Assessment folder. For Part 2 Implementation, a second file containing your
implementation export from PHPMyAdmin is also required. Use the export function
in PHPMyAdmin to create a .SQL file of your database. Submit this file via the “SQL File
Submission” link on Blackboard.
4. Collaboration and Plagiarism
Note that Part 2 of the assignment is done individually. If we suspect collaboration
between students has occurred or we see this occur during class, an investigation may
No collaboration with peers is needed for this assignment! Please see either the
lecturer or your tutor for guidance if you are struggling or have questions. Remember
that ignorance is not a defence!
The University has strict policies with regard to collusion and plagiarism. Penalties for
engaging in unacceptable behaviour can range from cash fines or loss of grades in a
course, through to expulsion from the University. You are required to read and
understand the policies on academic integrity and plagiarism in the course profile and
uq.edu.au/integrity. Note: Any posts on Piazza containing assignment specific
solutions/hints will be reported to the school.
5. Late Submissions
Late submissions will not be accepted at all, or if accepted, will incur a penalty of 20%
reduction each date late. Students who believe they have sound reasons for late
submission should refer to the course profile (section 5.3 on Late Submissions).
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx