DAT 560G: Database Design and ****SQL
Fall 2024, Mini A
Assignment #3: SQL ****Part ****2
Instructions
1. This is an individual assignment. You may not discuss your approach to solving these questions with anyone (or Generative AI), other than the instructor or TA.
2. Please include only your Student ID on the submission.
3. ****The only allowed ****material is:
a. ****Class notes
b. ****Content posted on the LMS
c. ****Textbook
4. You are not permitted to use other online resources.
5. T here will ****be ****TA ****office ****hours. ****See the ****course ****schedule.
Background
The North American Disasters Database is a comprehensive repository designed to document and analyze various disasters that have occurred across North America. From natural calamities such as hurricanes, earthquakes, and wildfires to human-made 代 写DAT 560G: Database Design and SQLincidents like industrial accidents and environmental crises, this database aims to provide a detailed record of significant events that have impacted North America.
Types relation ****contains the following information about each type of disaster in the database:
• Type of each disaster. This is the primary key.
• Natural ****indicates whether this type of disaster is natural or caused by people (man-made)
• Mode : what causes this type of disaster
• Size: the typical size of the disaster, in square miles
• Frequency: Every how many years the event can be expected.
Types (Type, Natural, Mode, Size, Frequency)
Places ****relation describes the cities where disasters may occur. It captures information about the cities and states where disasters occur, including geographic, demographic, and economic data. NOT all cities have disasters. It includes the following attributes.
• City: Name of the city. This is the primary key.
• State: Name of the state.
• Region: Name of the region within the US.
• Size: Geographic size of the city in square miles.
• Population: Number of people living in the city.
• Income: Average income of the residents in the city, in dollars.
• Homeowners: Percentage of houses that are owned by the occupants rather than rented.
• Budget: The city's annual budget.
• FoundingDate: The date when the city was founded.
• ManagerFirstName: The first name of the emergency manager in the city.
• ManagerLastName: The lastname of the emergency manager in the city.
• Gender: Gender of the emergency manager.
• Salary: Annual salary of the emergency manager.
• StartingDate: The date when the emergency manager started their position.
Places (City, State, Region, Size, Population, Income, Homeowners, Budget, FoundingDate,
ManagerFirstName, ManagerLastName, Gender, Salary, StartingDate, Environmental, Quality)
Teams ****relation records details about the response teams, including their budget, size, and the demographics of their members. There can be more than one response team for a city. If a city has disasters, then it must have at least one team. Some of the cities that do not have disasters may not have any response teams.
• Team: Unique identifier for the response team.
• City: Name of the city where the response team is based. This is a foreign key, to the places relation
• Organization: The organization to which the response team belongs.
• Budget: The annual budget of the response team, in dollars
• FirstDate: The date when the response team was first established.
• Employees: The number of employees in the response team.
• ManagerFirstName: The first name of the manager of the response team.
• ManagerLastName: The lastname of the manager of the response team.
• Gender: Gender of the manager.
• Salary: Annual salary of the manager in dollars.
• Female: Percent of the team that is female.
• Minority: Percent of the team that is minorities.
• ServiceArea: This is the part of the city that this team serves. Can have values of either: All, North, South, East, West.
Teams (Team, City, Organization, Budget, FirstDate, Employees, ManagerFirstName, ManagerLastName, Gender, Salary, Female, Minority, ServiceArea)
Te amSkills relation lists the specific skills possessed by these teams, providing insight into their capabilities. Not all teams need to have skills. Some teams can have several skills. Different teams will have different numbers of skills.
• Team: Unique identifier for the response team. This is a foreign key.
• Skill: Specific skill possessed by the response team. This is part of the multi-attribute key
TeamSkills (Team, Skill)
Disasters relation documents the details of each disaster event, including the type, location, response team involved, and the impact in terms of duration, casualties, property damage, and the number of responders. It includes the following information:
• Type: The type of disaster. Foreign Key that refers to the Type in the CauseTypes table.
• City: Name of the city where the disaster occurred. This is a foreign Key referring to Places.
• Team: Unique identifier for the response team that responded to the disaster. Foreign key referring to Teams relation.
• Name: A unique name or identifier for the disaster. This is part of the multi-attribute key.
• Date: The date when the disaster occurred.
• Year: The year when the disaster occurred.
• Month: The month when the disaster occurred.
• Duration: The duration of the disaster, in hours.
• Casualties: The number of casualties resulting from the disaster.
• PropertyDamage: The estimated property damage caused by the disaster, in US dollars.
• Responders: The number of responders involved in addressing the disaster.
Disasters (Type, City, Team, Name, Date, Duration, Casualties, PropertyDamage, Responders, Year, Month)
Relations
Types ****(Type, Natural, Mode, Size, Frequency)
Places (City, State, Region, Size, Population, Income, Homeowners, Budget, FoundingDate,
ManagerFirstName, ManagerLastName, Gender, Salary, StartingDate, Environmental, Quality)
Teams (Team, City, Organization, Budget, FirstDate, Employees, ManagerFirstName,
ManagerLastName, Gender, Salary, Female, Minority, ServiceArea)
TeamSkills (Team, Skill)
Disasters (Type, City, Team, Name, Date, Duration, Casualties, PropertyDamage, Responders,
Year, Month)
Assignment
Each of ****these questions is ****10 points. Submit answers ****to ****these ****online ****on ****Canvas, ****by ****6 ****am ****the ****day of ****the lab. They are autom atically ****graded.
1) Find **all cities that have more than 2 disasters. **List the cities that have the most disasters **first down to **those **who **only **have **the **fewest .
2) List **all **of **the **skills **related **to **teams **that **responded **to **Dam **failures **after **2010 . Please **only **list **the skill **once .
3) In order to compare **gender differences in manager **salaries, **please list the average **salar y **for team managers **by **gender . Only **include **teams **with **average **budgets **greater **than **$5 **million **for **teams established **after 2010, **and **not **in **the **state **of **California . Do **not **include **teams **with **null **values for **their **team **manager ’ sgender .
4) In **order **to **find **an **experienced **team **manager **with **spills , find **all **managers **who **have **experience with 2 or more disasters with an oil **spill, train spill, or truck **spill. **Include the manager’sfirst and lastname , team , and **the **number **of **disasters **the **manager **has **overseen . **Sort **the **list **with **the **most experienced **first.
5) List **the **cities **in **this **database **that **have **NOT **been **affected **by **disasters .
6) Find **the **types **of **disasters **with **six **or **fewer **occurrences **in **this **database .
Each of ****these questions is 5 points . Submit ****answers to ****these ****on ****Canvas ****before ****the ****next lab ****session. They will be grad ed by ****TAs.
For **each **question , submit **your **SQL **code **and **a **screenshot **of **the **results . If **the **results **are too **long , **partial **results **are **fine . Include **relevant **attributes **for **each **result , to **explain **that the result is correct. Do NOT **include many unnecessary attributes. Do NOT **use SELEC *T ** .
7) Find **the **cities **with **less **than 5 disasters . Include **the **population , state , region , **and **number **of disasters.
8) Is **there a city **that has a **population **over 900,000 **and has **had **both **natural **as **well **as **man-made disasters? List **it then. In **your query **results, **make sure **each row **contains **a **unique **city/Natural- type **combination . **Also **list **the **city **name **and **population .
9) Are **there **any **pairs **of **teams **that **have **responded **to **the **same **type **of **disaster **and **the **same **city ? List **team **pairings **only **once **and **include **the **city **name **in **your **results .
10) Find **teams **with **less **than **150 **employees **and **more **than **70 **females . List **the **team , **the **number **of disasters , and **the **average **number **of **responders .
11) Find cities with more than two **disasters, **populations greater **than **500,000, **and disaster **recovery teams **led **by **female **managers . List **the **name **of **the **city **only **once **in **your **results .
12) Please **identify **how **many **disasters **happened **in **each **city **ofCalifornia **that **occurred **in **October or **January, **had populations **LESS **THAN **500,000, **and **casualties **above **900. **Include **the **city, number **of **casualties , the **population , and **the **date **of **the **disaster **in **your **answer .
13) Find **the **minimum **population **and **the **average **budget **for **states **with **less **than **five **disasters . List the **state , number **of **disasters , minimum **population , and **average **budget — order **by **state **name .
14) How **much **time **did **you **spend **on **this **assignment ?
WX：codinghelp
