Instructions:

2. Questions are divided into 3 sections: a, b and c that walk you through the step-by-step process of creating a database.

3. Make sure to properly explain the choices you made while designing your database. For e.g., while choosing what data will be recorded in your database, explain why you find that information to be important for the organization.

4. Database created should contain at least 3 tables.

5. Make sure to include screenshots of your SQL code as well as outputs obtained from the queries, in your answers for part c).

Q:

a) What data would you need to store in your database for this organizations system? (15 points, 3 pt each)

i) Give attribute names of the data you would need

ii) Explain why this specific data is needed / stored in the database of the organization

iii) Explain different entities in the database clearly and separately

iv) Explain what information can be used as your primary and foreign keys.

v) Give some examples of the tasks that can be performed using your data.

b) Now, try to implement it as an Entity-Relationship (E-R) diagram

i) What are the possible entity sets, and their attributes? (1 pt)

ii) What relationships exist between them and explain why. (Explain why there are one-to-one / many-to-one relationships in your database plan) (2 pt)

iii) Draw the ER diagram you have described above. Note: what you describe in parts i) and ii) should be reflected in your diagram. (2 pt)

c) Implementing your DB design as a Relational DB. Show a screen shot of code implemented and initialized tables / outputs obtained from queries. (10 points, 2 pt each)

i) Convert your ER diagram into a Relational DB schema. Show that it satisfies all the constraints.

ii) Create your tables with at least 3 rows of data each, in MySQL.

iii) Write a SQL query selecting a specific value(s) from tables you created in part (i). Explain your query/what information you are trying to obtain from the query.

iv) Write a SQL query joining 2 (or more) tables from part (i). Show a screenshot of your resulting joined table. Explain what you were trying to obtain from the joined table.

v) Implement any arithmetic / statistical function available in SQL to calculate a meaningful value related to the data you have in your database tables (e.g., AVG, SUM functions etc.). Explain what you were trying to obtain using the function, and why the value you wanted to calculate is important/valuable for the business.

- Fire Department Information:

Every city in the United States keeps a record of fire stations located around the city along with important information about the station to aid in city development and planning. Information about fire stations (e.g., address, latitude, longitude) is helpful in determining housing projects allowed in neighborhood. Information like resources available (e.g., firefighters, equipment) at the station and their capability to handle issues influences the developments allowed in their vicinity. Design a database that records information required to keep track of all necessary information required in city planning.