Solved: IT303 – Homework 3
- For the following narrative, with entities underlined, diagram the logical model. (5.0 points)
Northern Lights Broadcasting owns 7 radio stations across northern Minnesota and Wisconsin. Each radio station is located in a different city and has its own building address.
Each station has its own employees. Each station also has a station managerwho is one of that station’s employees (hint: no new entity needed for station manager – just a second relationship from employee to radio station). A few of a station’s employees have supervisory responsibilities. Each station employee reports to a supervisor. A supervisor can supervise multiple employees. (hint: no new entity required here – but a relationship is needed)
Some, but not all, of the station employees may be on-air personalities, referred to as show hosts. Each show has at least one show host and some shows have more than one show host. Some employees host more that one show. Therefore, a particular employee record might not relate to any show host records, or could relate to more than one show host record. A particular show host record will relate to only one employee and a one show.
Each show has a schedule. The schedule documents when that show is broadcast. A show will have multiple schedule entries. A schedule record will be for a particular show. Each schedule entry will indicate the show, the date, the start time, and end time. This way of scheduling the show accommodates occasional variations in the timing of a particular show.
- The Southside Softball League coordinator needs a report that shows all the players on each team. Using the diagram below for your reference, write an SQL query that shows the team_id, team_name, player_id, and player_name for all players on all teams in the league. Order the output in ascending order by team_id, then player_id. (2.5 points)
- Greenfield Community Services management needs to have a staff commitment listing. Using the diagram below for your reference, write an SQL query that shows the events that each staff member is committed to along with their scheduled start times, end times, and roles. The listing should show all staff commitments for the current year (EVENT.event_date > ‘2011-01-01’). The SQL should output the following columns by table as follows:
STAFF: last_name, first_name
EVENT: event_name, event_date
EVENT_STAFF: start_time, end_time, role
The listing should be ordered on last_name, first_name, event_date, start_time (from EVENT_STAFF) – all ascending. (3.5 points)
- The Southside Softball League coordinator needs a league schedule report that shows the games, teams involved, field assigned and time for each scheduled game. Using the diagram below for your reference, write an SQL query that shows the game_id, field_id, start_date_time, end_date_time, and home and away team names. Order the output ascending by start_date_time, then field_id.
Note: Both the home_team_id and away_team_id columns in the GAME_SCHEDULE table relate to the team_id column in the TEAM table. Since home_team_id and away_team_id will have different values, one GAME_SCHEDULE record will reference two separate records in the TEAM table.
Hint: In order to get both the home and away teams for this report requires 2 separate joins from GAME_SCHEDULE back to the parent table TEAM – you will need to create 2 separate aliases for TEAM, but only one for GAME_SCHEDULE. (3.5 points)
- For the following narrative, with entities underlined, diagram the logical model. (4.5 points)
Shelley’s Fresh Catch is a seafood wholesaler and retailer. Shelley’s is a wholesaler to restaurant customers and also a retailer operating several fresh seafood retail markets around town. Each morning Shelley’s buys seafood items brought in by local fishermen. The seafood items Shelley’s purchases that morning are placed in their distribution center inventory. The computerized listing of distribution center inventory of seafood items is then updated to show the quantity of each seafood item available. Not all seafood items will be present in distribution center inventory because availability of specific items is seasonal. (Note: The relationship from SEAFOOD_ITEM to DISTRIBUTION_CENTER _NVENTORY is one-to-one. The list of SEAFOOD_ITEMS is a complete list of items that is ever in stock (like a catalog), and so, it is a superset of current DISTRIBUTION_CENTE_ INVENTORY. Not all SEAFOOD_ITEMS will be present in DISTRIBUTION_CENTER_INVENTORY at any one time.)
A restaurant customer can check Shelley’s current distribution center inventory to determine what seafood items are available that day. They can place an order up until 10 am. A customer order consists of one or more order items. Each order item specifies a quantity of a particular seafood item. Only items available in distribution center inventory can be ordered. The distribution center inventory quantities available are adjusted as orders are placed. When the order is complete, the customer can specify which retail market they want for order pick up. Shelley’s packages customer orders and delivers them to the retail market requested.
Seafood items in distribution center inventory that have not been allocated to customer orders by 10 a.m. are allocated between their retail markets. Each retail market has its own market inventory which is replenished daily from Shelley’s current inventory. A market’s inventory consists of the list of seafood items (and quantities) allocated to it that day. Each market displays items for sale to the public from its market inventory and holds orders to be picked up by restaurant customers.