A question from Data base management system

Create ERDs of these scenarios and corresponding SQL Statements to Entities and Relationships of each scenario.

(d) Automobile Distribution Center Ford distribution centers provide automotive parts to authorized dealers and the dealers distribute the parts to Customers throughout North America. Ford is faced with pressure to provide excellent Customer Service at minimum Cost. Maintaining a well organized database of information, will contribute to achieving this goal. Ford keeps the following information about each of its distribution centers: identification number, location (X longitude coordinate and Y latitude coordinate), address (city, state, zip code) and name of the Contact person. The following information is kept about each dealer: identification number, dealer's location (X longitude Coordinate and Y latitude Coordinate), address (name of the city, State, zip Code), name of the Contact person and the identification number of the primary distribution centers serve this dealer. A distribution center sends a shipment to its dealers every week. The distribution centers keep the following information about each shipment the date, an identification number of the product shipped, an identification number of the dealer that receives the shipment. The following information is kept about each product: a product identification number name, price, weight, value. Ford keeps the following information about the flow and cost data for all distribution centers to dealer channels: distribution center identification number, dealer identification number, product identification number, the number of miles between each distribution center and its dealers using the road network, the quantity of products being shipped and the dollar value of the shipment. (e) Housing Society Example The University Housing Office receives many applications from graduate and married students requesting an apartment on campus. The housing villages are sited in five different locations and each village has about 500 apartments. Each apartment falls into one of the apartment categories. An apartment category is determined based on village location, whether the apartment has dish washer or not, a single or double bedroom, central or window unit air conditioner, furniture or not. Housing officekeeps the following information about the Current residents: the SSN of the family head, name, address, telephone number, marital status, the name of the major college and department in which the family head is enrolled in. Housing office keeps the following information about the students that have applied for in campus housing, but have not been assigned to an apartment yet: SSN of the applicant, name, address, telephone number, marital status, the name of the major college and department in which the family head is enrolled in, the preference about the apartment category. (b) A Crop farm Example Rohit's family owns and operates a 640-acre farm for several generations. Since the farm business is growing, Rohit is thinking to build a database that would make easier the management of the activities in the farm. He is considering the following requirements for the database: 1. For each livestock classification group (for example, cow, horse etc.), Rohit keeps track of the following: identification number, classification, total number of livestock per classification group (for example, number of Cows, number of horses etc.). 2. For each crop the following information is recorded Crop identification number and classification. 3. Rohit has recorded the yield of each crop classification group during the last ten years. The records consist of the year, yield, sales, price of the crop and the amount of money earned. 4. Rohit has recorded the yield of each livestock classification group during the last ten years. The records consist of the following historical data: the year, (historical) selling price per head, number of livestock in the end of the year, number of livestock sold during one-year period, and the total amount of money earned. (c) Airline Example Major airlines companies that provide passenger services in Taiwan are: UniAir, TransAsia Airways, Far Eastern Transport, Great China Airlines etc. Taiwan's Federal Aviation Administration (TFAA) keeps a database with lots of information on all airlines. This information is made accessible to all airlines in Taiwan with the intention of helping the Companies assess their Competitive position in the domestic market. The information kept consists of: 1. 2. 3. 4. Each airpline has an identification number, name of the contact person and telephone number. For each aircraft identification number, capacity and model is recorded. Each employee has an employee identification number, name, address, birthday, sex, position with the company and qualification. Each route has a route identification number, origin, destination, classification (into domestic or international route), distance of the route and price charged per passenger. Each airline keeps information about their buy sell transactions (for example, selling an airplane ticket is a sell transaction, paying for maintenance is a buy transaction). Each transaction has a transaction identification number, date, description and amount of money paid/received. 5. Q. Attempt any four parts. Create ERDs of these scenarios and corresponding SQL Statements to Entities and Relationships of each scenario. (a) Country Bus Company A Country bus Company owns a number of buses. Each bus is allocated to a particular route, although some routes may have several buses. Each route passes through a number of towns. One or more drivers are allocated to each stage of a route, which corresponds to a journey through some or all of the towns on a route. Some of the towns have a garage where buses are kept and each of the buses are identified by the registration number and can carry different numbers of passengers, since the vehicles vary in size and can be single or double-decked. Each route is identified by a route number and information is available on the average number of passengers carried per day for each route. Drivers have an employee number, name, address, and sometimes a telephone number. (b) A Crop farm Example Rohit's family owns and operates a 640-acre farm for several generations. Since the farm business is growing, Rohit is thinking to build a database that would make easier the management of the activities in the farm. He is considering the following requirements for the database: 1. For each livestock classification group (for example, cow, horse etc.), Rohit keeps track of the following: identification number, classification, total number of livestock per classification group (for example, number of Cows, number of horses etc.). 2. For each crop the following information is recorded Crop identification number and classification. 3. Rohit has recorded the yield of each crop classification group during the last ten years. The records consist of the year, yield, sales, price of the crop and the amount of money earned. 4. Rohit has recorded the yield of each livestock classification group during the last ten years. The records consist of the following historical data: the year, (historical) selling price per head, number of livestock in the end of the year, number of livestock sold during one-year period, and the total amount of money earned. (d) Automobile Distribution Center Ford distribution centers provide automotive parts to authorized dealers and the dealers distribute the parts to Customers throughout North America. Ford is faced with pressure to provide excellent Customer Service at minimum Cost. Maintaining a well organized database of information, will contribute to achieving this goal. Ford keeps the following information about each of its distribution centers: identification number, location (X longitude coordinate and Y latitude coordinate), address (city, state, zip code) and name of the Contact person. The following information is kept about each dealer: identification number, dealer's location (X longitude Coordinate and Y latitude Coordinate), address (name of the city, State, zip Code), name of the Contact person and the identification number of the primary distribution centers serve this dealer. A distribution center sends a shipment to its dealers every week. The distribution centers keep the following information about each shipment the date, an identification number of the product shipped, an identification number of the dealer that receives the shipment. The following information is kept about each product: a product identification number name, price, weight, value. Ford keeps the following information about the flow and cost data for all distribution centers to dealer channels: distribution center identification number, dealer identification number, product identification number, the number of miles between each distribution center and its dealers using the road network, the quantity of products being shipped and the dollar value of the shipment. (e) Housing Society Example The University Housing Office receives many applications from graduate and married students requesting an apartment on campus. The housing villages are sited in five different locations and each village has about 500 apartments. Each apartment falls into one of the apartment categories. An apartment category is determined based on village location, whether the apartment has dish washer or not, a single or double bedroom, central or window unit air conditioner, furniture or not. Housing officekeeps the following information about the Current residents: the SSN of the family head, name, address, telephone number, marital status, the name of the major college and department in which the family head is enrolled in. Housing office keeps the following information about the students that have applied for in campus housing, but have not been assigned to an apartment yet: SSN of the applicant, name, address, telephone number, marital status, the name of the major college and department in which the family head is enrolled in, the preference about the apartment category. (b) A Crop farm Example Rohit's family owns and operates a 640-acre farm for several generations. Since the farm business is growing, Rohit is thinking to build a database that would make easier the management of the activities in the farm. He is considering the following requirements for the database: 1. For each livestock classification group (for example, cow, horse etc.), Rohit keeps track of the following: identification number, classification, total number of livestock per classification group (for example, number of Cows, number of horses etc.). 2. For each crop the following information is recorded Crop identification number and classification. 3. Rohit has recorded the yield of each crop classification group during the last ten years. The records consist of the year, yield, sales, price of the crop and the amount of money earned. 4. Rohit has recorded the yield of each livestock classification group during the last ten years. The records consist of the following historical data: the year, (historical) selling price per head, number of livestock in the end of the year, number of livestock sold during one-year period, and the total amount of money earned. (c) Airline Example Major airlines companies that provide passenger services in Taiwan are: UniAir, TransAsia Airways, Far Eastern Transport, Great China Airlines etc. Taiwan's Federal Aviation Administration (TFAA) keeps a database with lots of information on all airlines. This information is made accessible to all airlines in Taiwan with the intention of helping the Companies assess their Competitive position in the domestic market. The information kept consists of: 1. 2. 3. 4. Each airpline has an identification number, name of the contact person and telephone number. For each aircraft identification number, capacity and model is recorded. Each employee has an employee identification number, name, address, birthday, sex, position with the company and qualification. Each route has a route identification number, origin, destination, classification (into domestic or international route), distance of the route and price charged per passenger. Each airline keeps information about their buy sell transactions (for example, selling an airplane ticket is a sell transaction, paying for maintenance is a buy transaction). Each transaction has a transaction identification number, date, description and amount of money paid/received. 5. Q. Attempt any four parts. Create ERDs of these scenarios and corresponding SQL Statements to Entities and Relationships of each scenario. (a) Country Bus Company A Country bus Company owns a number of buses. Each bus is allocated to a particular route, although some routes may have several buses. Each route passes through a number of towns. One or more drivers are allocated to each stage of a route, which corresponds to a journey through some or all of the towns on a route. Some of the towns have a garage where buses are kept and each of the buses are identified by the registration number and can carry different numbers of passengers, since the vehicles vary in size and can be single or double-decked. Each route is identified by a route number and information is available on the average number of passengers carried per day for each route. Drivers have an employee number, name, address, and sometimes a telephone number. (b) A Crop farm Example Rohit's family owns and operates a 640-acre farm for several generations. Since the farm business is growing, Rohit is thinking to build a database that would make easier the management of the activities in the farm. He is considering the following requirements for the database: 1. For each livestock classification group (for example, cow, horse etc.), Rohit keeps track of the following: identification number, classification, total number of livestock per classification group (for example, number of Cows, number of horses etc.). 2. For each crop the following information is recorded Crop identification number and classification. 3. Rohit has recorded the yield of each crop classification group during the last ten years. The records consist of the year, yield, sales, price of the crop and the amount of money earned. 4. Rohit has recorded the yield of each livestock classification group during the last ten years. The records consist of the following historical data: the year, (historical) selling price per head, number of livestock in the end of the year, number of livestock sold during one-year period, and the total amount of money earned