INFS12007900 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 anyas 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.
1
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 products, 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 shows 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 accounts 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
BasicImplementationTables,DataTypes,PrimaryKeys,SufficientData 30points 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 smallscale information system in a relational database
management system
4. Perform information systems analysis
2
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: xxxxxxxxyyyyyyyyA2.pdf, and replace
xxxxxxxx with your student ID, and yyyyyyyy with the course code
infs1200 or infs7900 e.g. 12345678infs1200A2.pdf 2 An export of your HOMECINEMA database As a .SQL file
Name your .SQL file submission as: xxxxxxxxyyyyyyyyA2.sql, and replace xxxxxxxx with your student ID and yyyyyyyy with the course code infs1200 or infs7900 e.g. 12345678infs1200A2.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.auintegrity. Note: Any posts on Piazza containing assignment specific solutionshints 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.
3