Analyzing and create the database and tables

Royale Inn is a leading chain of hotels in California with branches in Denmark, Argentina, Mexico, Macau, and Perth. The hotel is known for providing quality services to its customers. It provides two types of suites, deluxe and super deluxe. The deluxe suite provides basic facilities, such as housekeeping, air conditioning, refrigerator, and geyser. However, the super deluxe suite provides the laundry facility in addition to the basic facilities.

Currently, the hotel management maintains booking details of the customers in the Booking file. The details stored in this file consist of booking ID, customer ID, customer name, gender, address, country, contact number, check in date, check out date, suite ID, suite name, suite type, and feedback from the customers. Similarly, the suite details are stored in the Suite file. The details stored in this file consist of suite ID, suite name, suite type, suite tariff, ID of the suite service employee, name of the employee, and booking status. In addition, details of the hotel employees are stored in the Employee file. The details stored in this file consist of employee ID, name, gender, address, department ID, department name, name of the department head, and salary. Moreover, details of all the branches of the hotel are stored in the Hotel file. The details stored in this file consist of hotel ID, hotel name, branch address, country, contact number, and number of suites and employees in the branch. 

Due to its world-class services, the hotel has succeeded in getting a number of tourists. However, the management has found that managing details of the increased number of customers in a manual file system is difficult and causing inconvenience to the visitors. In addition, the employees are facing problems in keeping track of the branches and the services provided at each branch. Therefore, the management has decided to automate the hotel management system. It has approached AxelloTech Inc. to develop an application. 

At AxelloTech Inc., Robert and his team have been assigned the task of creating the required application. Being the database developer in the team, you have been assigned the task of analyzing the application and creating the required database and tables.

You need to analyze all the files in the given scenario and perform the following tasks:

1. Identify the entities that may cause data redundancy. Justify your answer.
2. Identify the problems that may arise due to data redundancy. Justify your answer.

---

ShopMart is a leading chain of departmental stores in America. It has many stores in the United States, Bahamas, Canada, Cuba, Costa Rica, and Mexico, with its head office in California. The chain is known for quality products, affordable prices, timely service, and a free home-delivery facility. To ensure customer satisfaction and trust, the store procures the best quality goods from different countries. In addition, to ensure prompt service, the store maintains an optimum level of inventory all the time.

ShopMart deals in various categories of products, such as Household, Sports, Accessories, and Clothing. Currently, the details of all these categories are stored in the ProductCategory file. The details stored in the file consist of ID, name, and description of the categories. In addition, the details of all the products are stored in the ItemDetails file. The details stored in this file consist of ID, name, description, category ID, and unit price of the products. In addition, the ItemDetails file stores information about the quantity available for each product, the reorder level, the reorder quantity of each product, and the supplier ID. 

ShopMart procures most of the goods from regular suppliers. The details of all these suppliers are stored in the SupplierDetails file. The details stored in the file consist of ID, name, address, phone, and country of the suppliers. 

When the quantity in hand reaches the reorder level for any product, an order needs to be placed with the supplier. In this case, a purchase order and its details are stored in the OrderDetails file.

The details stored in the OrderDetails file include the following information:

• Purchase order ID
• The ID of the supplier
• The ID of the employee who placed the order
• The date of order
• The expected date of receipt of the goods
• The ID of the item ordered
• The unit price of the ordered item
• The quantity ordered
• The shipment method
• The current status of the order

Due to its presence in different cities and the quality of service it provides, ShopMart has succeeded to capture a large number of customers. With the exponential increase in the number of customers, the volume of business has also increased. Consequently, it is becoming difficult for the management to maintain the inventory, orders, and supplier details manually. Therefore, the management decides to use a computerized inventory management system. The management approaches SoftTech Inc. to develop the application. 

At SoftTech Inc., Harry and his team have been assigned the task to create the required application. Being the database developer in the team, you have been assigned the task to analyze the application and create the required database and tables. You need to analyze the requirements of the preceding application and perform the following tasks:

1. Identify the various entities and their attributes involved in the application.
2. Identify the attributes that are unique to each of the entities.
3. Identify the relationships that may exist among the entities of the application.