1) Design an Object-Relational geodatabase, E-R diagram, and schema from the following elements. Subcatchments (S1, S2, S3). The study region is divided into multiple subcatchments where water is collected. Each subcatchment is described with a unique subcatchment ID, the area (square km), the average slope and the location (polygon). Rain Gages (R1, R2). A rain gage supplies precipitation for one or more subcatchments, and a subcatchment may have one or more rain gages. For example, in the Figure, R1 supplies precipitation data for S1 and S3, and R2 for S1 and S2. Each rain gage is described by a unique gage ID, hourly precipitation (mm), the time (year, month, day, hour) when the hourly precipitation is collected, and the location (point). Conduits (C1, C2, C3, C4). Conduits are the channels that move water to an outfall in the drainage system. Each conduit is described by a unique conduit ID, the length and the location (line). Junctions (J1, J2, J3, J4). A junction is the confluence of conduits (channels) (where conduits intersect). Each junction has a unique junction ID, the elevation and the location (point). A junction can connect multiple conduits and one conduit can have more than one junction. Outfalls (e.g., Out1 in the Figure). Outfalls are the terminal nodes of the drainage system (i.e., where the water flows out of the study region). Each outfall is described by a unique outfall ID, the elevation and the location (point). An outfall is an end point of a conduit (i.e, an outfall is a special type of junction). A conduit may have 0 or 1 one outfall and an outfall is always located at the end of one conduit. 1.1) Write an SQL statement to find the subcatchment(s) that are larger than 1.2 square km. The SQL needs to return the subcatchment ID, area and slope. 1.2) Write an SQL statement to find the precipitation at the rain gage R5 at 9AM, February 4, 2022. The SQL needs to return all the information of the rain gage. 1.3) Write an SQL statement to find the subcatchment(s) that has rain gage(s) with 100 mm or more precipitation at 3PM, March 15, 2022. The SQL needs to return the subcatchment id, subcatchment area, amount of precipitation and rain gage ID. 1.4) Write an SQL statement to find the outfall(s) of the subcatchment with ID S1. The SQL needs to return the subcatchment ID and the outfall ID. 1.5) Write an SQL statement to find the conduit(s) that move water from the subcatchment S3. The SQL needs to return the length and ID of the conduit(s), and the subcatchment ID. 1.6) Write an SQL statement to find the conduit(s) that have an outfall. The SQL needs to return the conduit ID and the outfall ID. 1.7) Write an SQL statement to find the subcatchment(s) where water flows to a junction with an elevation 100 meters or higher. The SQL needs to return the subcatchment ID and the junction’s location (as an object).
1) Design an Object-Relational geodatabase, E-R diagram, and schema from the following elements.
Subcatchments (S1, S2, S3). The study region is divided into multiple subcatchments where water is collected. Each subcatchment is described with a unique subcatchment ID, the area (square km), the average slope and the location (polygon).
Rain Gages (R1, R2). A rain gage supplies precipitation for one or more subcatchments, and a subcatchment may have one or more rain gages. For example, in the Figure, R1 supplies precipitation data for S1 and S3, and R2 for S1 and S2. Each rain gage is described by a unique gage ID, hourly precipitation (mm), the time (year, month, day, hour) when the hourly precipitation is collected, and the location (point).
Conduits (C1, C2, C3, C4). Conduits are the channels that move water to an outfall in the drainage system. Each conduit is described by a unique conduit ID, the length and the location (line).
Junctions (J1, J2, J3, J4). A junction is the confluence of conduits (channels) (where conduits intersect). Each junction has a unique junction ID, the elevation and the location (point). A junction can connect multiple conduits and one conduit can have more than one junction.
Outfalls (e.g., Out1 in the Figure). Outfalls are the terminal nodes of the drainage system (i.e., where the water flows out of the study region). Each outfall is described by a unique outfall ID, the elevation and the location (point). An outfall is an end point of a conduit (i.e, an outfall is a special type of junction). A conduit may have 0 or 1 one outfall and an outfall is always located at the end of one conduit.
1.1) Write an SQL statement to find the subcatchment(s) that are larger than 1.2 square km. The SQL needs to return the subcatchment ID, area and slope.
1.2) Write an SQL statement to find the precipitation at the rain gage R5 at 9AM, February 4, 2022. The SQL needs to return all the information of the rain gage.
1.3) Write an SQL statement to find the subcatchment(s) that has rain gage(s) with 100 mm or more precipitation at 3PM, March 15, 2022. The SQL needs to return the subcatchment id, subcatchment area, amount of precipitation and rain gage ID.
1.4) Write an SQL statement to find the outfall(s) of the subcatchment with ID S1. The SQL needs to return the subcatchment ID and the outfall ID.
1.5) Write an SQL statement to find the conduit(s) that move water from the subcatchment S3. The SQL needs to return the length and ID of the conduit(s), and the subcatchment ID.
1.6) Write an SQL statement to find the conduit(s) that have an outfall. The SQL needs to return the conduit ID and the outfall ID.
1.7) Write an SQL statement to find the subcatchment(s) where water flows to a junction with an elevation 100 meters or higher. The SQL needs to return the subcatchment ID and the junction’s location (as an object).
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
