Developing Data Model and Designing Data Queries for Eye Candy Cinemas

Task 1: Develop Data Model

1. Read the requirements carefully and identify the purpose of this database, and draw a list of use cases (this is pretty much already given to you in the requirements).

2. As you develop your data model, note any assumption you make and any question you have for your client.

3. Develop a data model for Eye Candy Cinemas from all the information given above. The EntityRelationship Diagram is to include:

• the entities required
• the attributes for each entity
• the data types and constraints on the attributes
• the primary key of each table
• the relationships between the tables, including cardinality and foreign keys

4. For each relationship, define which referential constraints apply. List the relationships and their constraints. In each case explain your choice.

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

The stated purpose of this database is to provide data for a web application. From the description of the required functionality above, you are to extract a list of queries you think will be needed by this application. This will provide the basis for the stored procedures you will write in a later task.

1. For each required function, prepare a list of queries (just the names of the queries) that will be needed to fulfill the function. One example is given

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.

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

1. Create a new database called EyeCandyCinemas.

2. Create and run SQL statements to create each of the tables that you have identified in your database design. Make sure to include appropriate key fields, such as primary keys and foreign keys. Save these statements in a file as you will need to include them in your submission.

3. Create – but do not run yet - SQL statements to insert test data into the tables you have created. Use the values you have prepared in the previous task.

4. Save these SQL statements in one single file populateDB.txt , so that you can reuse them if you need to add some records when you are testing your queries, and also so you can set the order of insertion (see point 5)

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.