数据库代写 | INFS1200/7900 Information Systems

本次数据库代写是设计实现一个电影院管理的数据库
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.
Sample Data
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.
2
Views
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
inclusive
3. Find the product(s), which has casted ‘Brad Pitt’, that has been watched the
most times
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
sequel)
9. Find all playlists which contain a movie that the account owner has not
watched
10. Find which account(s) have watched at least all the products that ‘Idris Elba’
has been cast in
2. Assessment
Each part of the assignment is allocated 100 points which will be converted into 15
course marks.
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
management system
4. Perform information systems analysis
3
3. Submission
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
be conducted.
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).