Consider the following scenario (Read carefully! This is not the same as the sample midterm!): Each computer program, whether written in Python, JavaScript, C++, or any other languages, is made up of statements. Each statement contains variables which can be read, updated, and in some languages, deleted. A collection of programs makes up an application. John is an application developer, and he likes to set up a database to keep track of how the variables are used in each of the programs. 1. Create a relational ERD and insert the diagram here:

2. Write out the CREATE TABLE statements for each of your ERD entities:

3. Come up with additional requirements on your own to the above scenario which will require at least 2 additional entities to your ERD in Question 1. Be reasonable and realistic! Describe the addition requirements:

4. Extend the ERD from Question 1 and include the diagram here: (highlight the new entities in a different color using the Fill tool in Visio)

5. Before you continue, calculate the following: Write down your BCIT ID (Without the A): Divide by 4: What is the remainder? Your answer should be either 0, 1, 2, or 3. Write down the remainder here: Using this remainder from your calculation, 1) identify the relational operator(s) below, 2) come up with some sample data for one or two of your tables from question 4, and 3) give an example of applying the relational operator on your sample data. (E.g., if the remainder of your calculation is 3, you need to provide an example of applying the NATURAL JOIN operator on two tables from question 4.) Show your table(s) with sample data and the result of applying the relational operator on your table(s). 0: PRODUCT 1: SELECT with a condition (please specify a condition of your choice!) 2: NATURAL JOIN 3: PROJECT after a SELECT Sample data: Relational operator: Result of applying relational operator to sample data:

6. One more time, before you continue, calculate the following: Write down your last (family) name: Write down the number characters in your last (family) name: Divide by 3: What is the remainder? Your answer should be either 0, 1, or 2. Write down the remainder here: Based on the remainder from your calculation, 1) identify the pair below, 2) show an example of an unnormalized relation with multivalued attributes (show the keys and attributes). You may show some sample data but not required. (e.g., if your remainder is 0, create an unnormalized relation that involve programs and statements.) 0: Programs and Statements 1: Statements and Variables 2: Applications and Programs

Unnormalized relation: