Homework 2 Problem 1: United Nations Draw An Entity
Homework 2homework 2 Problem 1 United Nationsdraw An Entity Relation
Homework 2homework 2 Problem 1, United Nations draw an entity-relationship diagram for the following case. The United Nations wants to develop a global database, tracking countries, cities, and multinational companies. For each country, the UN wants to store its name, capital, population, and size. The UN assigns each city in the world a unique, global, numeric identifier. It also wants to record the name of the city, the name of the city’s mayor (or chief executive), its population, and its size in square miles. Obviously, a city is associated with only one country. Each city also has city-owned buildings. Each building a city owns has a type (fire station, police station, etc.), an address (its unique identifier), and the year it was built. Each multinational company has a globally unique identifier, a name, a president, and the year it was founded. A country has many companies operating in it, and a multinational company can operate in more than one country. Each company has many employees, with each employee having an employee number (unique within the company), a name, and a year of hire. Some employees manage other employees within the same company. The UN is also interested in the agricultural output of each country based on crops. A crop has a unique name, the season it’s harvested, and its watering requirements on a scale from 1 to 10. The UN wants to track the annual yield of each crop in each country.
Paper For Above instruction
Introduction
The development of a comprehensive database for the United Nations (UN) aims to facilitate efficient management and analysis of data related to countries, cities, city-owned infrastructures, multinational companies, employment, and agriculture. A well-designed Entity-Relationship (ER) diagram serves as a blueprint to visualize relationships among these entities, ensuring data integrity and supporting various analytical queries.
Entities and Their Attributes
- Country: Attributes include name> (primary key), capital> (which is a city), population> and size>.
- City: Attributes consist of city_id (globally unique, numeric, primary key), name>, mayor>, population>, and size in miles²>.
- Building: Each city owns multiple buildings with attributes such as address (unique within the city) (primary key), type> (fire station, police station, etc.), and year built>.
- Multinational Company: Attributes include company_id (globally unique) (primary key), name>, president>, and year founded>.
- Country-Company Relationship: A many-to-many relationship, as countries can host multiple companies, and each company can operate in multiple countries. This is a junction table with attributes such as operating in> (linking countries and companies).
- Employee: Attributes include employee_number (unique within company), name>, and year_hired>. Employees are associated with the company they work for.
- Employee Management: A recursive relationship where employees manage other employees within the same company.
- Crop: Attributes are crop_name (primary key)>, season>, and watering_requirements> (scale 1-10).
- Yield: Tracks the annual yield of each crop in each country, establishing a relationship between Crop and Country, with attribute yield_amount>.
Relationships and Cardinalities
- Country - City: One-to-many (one country has many cities). Each city belongs to exactly one country (mandatory). Minimum and maximum cardinality reflects this with (1,1) for country and (1,N) for cities.
- City - Buildings: One-to-many (each city owns multiple buildings). Each building is owned by exactly one city; thus, (1,1) for city and (1,N) for buildings.
- Country - Company: Many-to-many, with an associative entity or junction table. Both sides are optional, but typically, a country can have zero or many companies, and a company can operate in zero or more countries.
- Company - Employee: One-to-many; each employee belongs to one company, but a company has many employees. Recursive relationship for employee management indicates that an employee can manage many employees.
- Crop - Yield - Country: Many-to-many; each crop's yield is tracked per country annually. This involves a junction entity Yield with attributes year and amount>.
Diagram Construction Notes
- Entities are represented as rectangles labeled with their names.
- Attributes are ovals connected to their entity rectangles, with primary keys underlined.
- Relationships are diamonds connecting entities, with lines indicating the nature of the relationship.
- Cardinality is shown near the lines, such as 1:N, M:N, or 1:1, with minimum and maximum constraints.
- Recursive relationships (manages employees) are depicted within the employee entity, with a loop indicating management.
Conclusion
The ER diagram incorporating the above entities, relationships, and cardinalities provides a comprehensive visual mapping of the UN's data requirements. It facilitates database normalization, integrity, and supports efficient data retrieval for analysis related to countries, cities, infrastructure, corporate operations, employment, and agriculture.