The project guide includes the marking criteria

IT5012

IT5012 Data Handling and Web Concepts Project 1: Data Handling

OVERVIEW

Part 2 - Creating and Accessing a Database

Learning Outcomes

You must complete Part 1 and Part 2 on your own. Part 3 is a collaborative task.

You can ask a tutor to clarify the instructions and for advice but they cannot assist you in completing the tasks required – you must carry out the tasks yourself!

Computer Power Plus 2

IT5012 Data Handling and Web Concepts Project 1: Data Handling

Part 1 and Part 2 will be submitted together for final marking.

Part 3 can be submitted independently, at anytime.

The documentation is to be uploaded to iQualify, using the assessment upload facility.

You also need to notify your tutor per email of your submission.

Create a folder called IT5012 on your home drive, and inside it a subfolder called Project 1, in which you will save your files for this assessment.

Save answers for Part 1 into a document named IT5012_Project1_Part1_<YourStudentID>. Save answers for Part 2 into a document named IT5012_Project1_Part2_<YourStudentID>. Collect all the documentation for Part 3 into a subfolder called Part 3 Research.

Eye Candy Cinemas is an entertainment company that has several cinema complexes, each with several theatres. The company would like you to develop a database that will provide a basis for a website application it is developing.

Requirements

Additionally, the company management would like a special function to make sure that all its theatres are being optimally utilised:

Monitor Movie Screenings: allows to check how many movies are screened per day or per week in each location and each theatre. This could for an exact day or week, or as average over a week.

Movie information consists of movie title, director, release year, duration and a link to the imdb website page for the movie.

Movie screenings tell users which movie is played in which theatre at what time and on which day. Users can search fro screenings by movie title, by day or by time, and also by ticket price (e.g. movie today anytime, under $25.00)
Simplified pricing: there will be just one ticket price per child and one ticket price per adult for each theatre, irrespective of weekday and time of day.

Here are some useful sources of information.

Movie information:

Task 1: Develop Data Model

o the data types and constraints on the attributes

o the primary key of each table

5.Demonstrate that all your tables are in 3rd normal form by explaining how each of the normalisation criteria are met.

Task 2: Design Data Queries

2.Note any question you have and any point you would need to clarify with the customer. Also note any assumption you had to make.

3.Review your database design and adjust it to make sure that it will enable these queries. It is normal to make changes after looking at more detailed requirements. Explain one of the changes you had to make.

In an excel spreadsheet or in a document, list the actual data you will need in your tables. In particular, think of how you would test some of the queries you have listed above.

For example, here is the start of the information you might need for a movie table (this might not quite match your design, but it give you an idea). Have a table like this for each of your database tables. Remember to include values for any key that you need.

Remember to consider in which order you will need to create your tables to ensure referential integrity at all times.

This is because without a sound design, you may encounter problems in the implementation part.

If you are unsure about certain points, you may consult your tutor. You may need to refine or correct your design before you proceed to the next part.

Task 4: Create Database

For this task, you will be using SQL Server Management Studio.

5.Now run the data insertion SQL statements in the correct order needed to ensure referential integrity at all times. Correct the file populateDB.txt as appropriate, and make sure you submit it for marking.

6.Run appropriate SELECT statements to check that the records have been inserted correctly.

You will be using SQL Server management Studio’s Query Editor.

Remember that you will also need to save a copy of your code and any required
documentation/explanation in file IT5012_Project1_Part2_<YourStudentID>for submission.

4.Test the SQL query by executing it with the input values you defined in your test cases.

Compare the actual results with the expected results and write down any bugs you find. You can try other values too. You may need to insert or modify some data for your tests.

Once you have created, tested, corrected, documented and saved all your queries, arrange for a demonstration of your code with your tutor.

Computer Power Plus 10

- It shows: cinema name, theatre name, capacity
- Variable inputs: the city (e.g. ‘Wellington’), the min capacity (e.g. 200)

2.SQL Query

Test Case 3
SELECT… (same as above)
WHERE (Cinema.city = ‘wlgn’)
AND (Theatre.capacity > 200)

Test Case 2
SELECT… (same as above)

The first execution of the tests gave the following results.

SELECT Cinema.cinemaName, Theatre.theatreName, Theatre.capacity FROM Theatre
JOIN Cinema
ON (Cinema.cinemaID = Theatre.cinemaID)
WHERE (Cinema.city = ‘Wellington’)
AND (Theatre.capacity >= 200)

This actually corrects both bug 1 and bug 2.

SELECT Cinema.cinemaName, Theatre.theatreName, Theatre.capacity FROM Theatre
JOIN Cinema
ON (Cinema.cinemaID = Theatre.cinemaID)
WHERE (Cinema.city = ‘Wellington’)
AND (Theatre.capacity >= 200)

Computer Power Plus 12

Computer Power Plus 13

IT5012 Data Handling and Web Concepts Project 1: Data Handling

PART 3: RESEARCH EMERGING TREND

Note: You will need to document your research through notes, annotated articles, links, etc. You will need to provide this evidence when submitting your project. You will be asked to save all electronic evidence as well as your presentation notes in a folder, and then zip the folder into a file named IT5012_Project1_Part3_<YourStudentID>.

2.Frame a more specific question that you want to investigate. Keep it simple and specific, this is not academic research paper. For example you might want to ask how data is stored in a particular type of database

7.Submit your presentation files to your tutor at least 24 hours ahead of your presentation.

Computer Power Plus 17

Computer Power Plus 18

IT5012 Data Handling and Web Concepts Project 1: Data Handling