Database System Management Data Modeling Project

Database System Management Data Modeling Project The purpose of this Project is to give you real training in database design and normalization of a complete case. You will read and understand all the aspects of the scenario given below, and then will produce a database design for it. A complete database implementation is not required; however, students are required to create the tables in RDBMS (e.g. MS SQL Server, access, oracle…). A complete and correct design in the form of a UML class diagram and a set of normalized tables properly created in RDBMS (e.g. MS SQL Server, access, oracle…). is expected of the students. Students should be able to complete all parts of this project once they have completed chapters 1-9 of the textbook and have done all the exercises in chapters 1-9 of the Database System Management that accompanies the text (Already exists at Google Classroom: https://classroom.google.com/u/0/w/ODc1NzcyMDk1NDVa/t/all). An important advice: Designing a database is an error-prone activity and it is takes lots of TIME and active discussions to come up with a good final design. You should keep this in consideration and budget enough time to allow the design to evolve. You should seek your instructor's feedback on the list of normalized tables and the completed class diagram. The most important advice to the students is that you must begin work on the project as soon as possible. Do not put the project off until the week before the deadline. Alex's Sea Food Restaurant Business CASE Alex's new fish restaurant is becoming very popular and needs to computerize his operations. This includes Supply, payroll, inventory and customer orders. Supply: Alex buys his fish from two sources: from the fish market and from fishermen who go out to sea to find the best fish. Alex prefers to buy his fish directly from the fishermen, but if he cannot find what his restaurant needs; he will buy it from the market at slightly higher price. To ensure freshness, Alex uses his mobile phone to call the fishermen while still at sea. He therefore maintains a list of mobile phone numbers for fishermen on several boats. When he calls a boat at sea, he usually finds out what kinds of fish have been caught, in what quantities and when is the boat expected to arrive to shore. He will prefer the freshest fish that meet his customers' demands. When Alex decides to buy, he will usually make a reservation by phone and then sends out his truck to pick up the fish from the port where the boat is expected to arrive and at the expected time of arrival. So, he will need to maintain all this information, including the boat names, and the names and phone numbers of his contact persons on each boat. He will also need to maintain information about what fish the restaurant needs every day, and what fish is available on each boat, the boat's expected arrival times, and what he ordered and for how much. Additional information may also need to be maintained. Payroll: Alex needs to maintain detailed information about all his employees, including their personal details, their qualifications, when they were hired, their salary, whether they are part timers or work as full time. For full timers, he needs to keep track of their social security payments and income tax deductions. Also, he needs to keep track of any bonuses for excellent service or deductions as penalties, their leaves, and dates and reasons for leaving their employment. For inventory, he needs to keep track of all the room details (such as capacity, air conditioning, furniture, service sets (dishes, cups, table cloths, etc.). He also needs to keep track of all the quantities of food available, when they were acquired and their shelf life to ensure freshness. Customer orders: Currently, Alex is using a paper-based form similar to the one below for customer orders. He wishes to link all information on supply, payroll, inventory and customer orders in a single integrated database. As customary in real-life problems, you will need to make assumptions and to make your own research about a similar business. You will also need to outline in your report what research you have done and what assumptions you made. Tasks: Create a normalized list of tables for each form and report. Create an integrated list of normalized tables for the entire application. Draw the corresponding class diagram. Arrange your deliverables in a well-structured report according to required tasks & deliverables. Deliverables Checklist Hand in the following by the cut-off date (Week 14) Your integrated list of normalized tables for the entire application (the relational schema). The class diagram (Your UML schema) Good Luck,