Create a Database System Using MySQL

Purpose:To demonstrate the ability to propose, implement, and query the database.Problem:NHD Development Group Inc. builds, leases, and manages shopping centers, convenience stores, andother ventures throughout the country. Last year, NHD purchased several malls that will work as insouthern California. The term mall in this case is defined as a building that leases various spaces toindividuals or small business companies to market and sell their products. These buildings are in areaswhere they have shown potential for a positive financial return. David is the chief information officerfor NHD. David’s responsibility is to provide information to the board of directors so it can makestrategic decision about future development ventures.Because most businesses in these malls are small business owners, most of these malls do theirbookkeeping on paper and David is concerned that the data he needs from the malls will not be easy toobtain. Because of the paper-based systems, David expects it to be difficult to obtain items such astotal sales, total commissions, and dealer sales. David believes that by creating a specialized databasefor the mall managers to use, he can ensure that the data he will be easy for managers to create andmaintain. The malls will be able to use the database to create the reports he needs in order toefficiently demonstrate the financial health of the malls to the board.The malls are housed in large buildings that are owned by the parent company, NHD. The buildingsare divided into booths that are rented to dealers, who then fill the booths with inventory that is sold tocustomers. A dealer might be a small company or an individual. It is the dealer’s responsibility tomanage its own inventory; the mall does not maintain an inventory list for the dealers. As dealers sellitems from its inventory, the mall records the dealer number and the price of each item using theinformation on the item’s price tag. At the end of the month, the mall generates a list of total sales foreach dealer, computes the mall’s commission, deducts the dealer’s rent for booth space, and then issuesthe dealer a check for the remaining amount.David determined that the database must manage sales, booths in the mall, and dealers that rent thebooths. At the end of the month, the database must be able to produce a complete list of sales bydealer. In addition, the database must determine the revenue owed to each dealer by deducting thedealer’s rent from the commission on the sales reported.David has discussed his goals with the board and they have agreed to go forward with developing adatabase. David selected one mall to serve as the pilot for the project, which will allow him to test thedatabase before implementing it with all of the malls. Lisa, is the chosen mall’s manager and she isexcited about replacing the mall’s manual systems with a database. After meeting with Lisa, Davidhas collected a couple of forms that are currently being used to manage the mall. (See Figure 1 andFigure 2).Page 2Figure 1 is used to obtain information about dealers. Be certain that each dealer is uniquely identified.It may be necessary to determine and create a unique identifier is one is not found on the form.Figure 1: Dealer Information FormFigure 2 illustrates the map of the various spaces leased to dealers. Currently this is kept on paper andis used to determine which booths are leased or vacant. In addition, to this form, Lisa needs to keep upwith the booth’s location (outside perimeter, insider perimeter, or aisle), its color (green, tan, yellow,or white), which dealer rents the booth (or which booths are vacant), and wheher the booth has raftersabove it or and carpeting.Figure 2: Map of the Selected MallPage 3David is a CSUF alumni and is seeking the assistance from IS majors to help him with this project. Becauseyour team has been working on developing databases, your team has been asked to create a preliminarydatabase design that will manage the NHD project.Instructions:Define the database design necessary to meet the needs of the case.□ Use the normalization steps provided in the text in order to identify the various entities and attributesneeded to capture and automate the manual processes for NHD case. Define each of the entities usingthe parenthetical method. Each relation in your database design must be a normalized relation (3NF).Please make sure to state your assumptions. An example of the parenthetical method for a“STUDENT” relation is:STUDENT (StudentId, StudentName, Email, Age, Major)*Note: Attributes which comprise the primary key should be underlined and attributes that are foreignkeys should be italized.□ Use any drawing tool (e.g., MS Visio or MS drawing toolbar) and create an illustration of theproposed database design model that will automate the current manual processes at NHD. Becauserelationships are critical to the database design, identify the minimum and maximum cardinalities ineach of the relationships between entities. Provide brief justifications for the cardinalities depicted inthe proposed data model. *Note: In addition to minimum and maximum cardinalities, relationshiplines should be illustrated according to type of relationship (e.g., identifying and non-identifying) andweak and strong entities should be differentiated as well.□ For each entity, create a matrix or table that provides the fields, data types, field size, and description (ifapplicable). Provide a brief description for each table that explains the table’s purpose. This can be donein one sentence or two. Place the description above each table.☐ Implement and populate the database by using your knowledge of SQL DDL. Reference the 3NFentities from previous steps as a guide to define the properties and structure in SQL syntax. Enforcereferential integrity by defining PK/FK constraints in the SQL script. Include 15 dummy records foreach table. This dummy data should be well thought out as you will be using the data to run specificqueries that will demonstrate that the database has resolved the issues at NHD.*Note: SQL syntax is written and saved in a text editor application (e.g., Notepad) and will be submittedas a separate file. Combine both the CREATE TABLES and INSERT INTO VALUES script into onetext file (.txt) and make sure the script can be executed together without any errors.☐ Once the database has been implemented, create a database diagram in SQL Server ManagementStudio (SSMS) by following these steps:• Right-click on the Database Diagrams item under your database in the Object Explorer• Select “New Database Diagram.”• In the “Add Table” dialog box, select all of the tables in your solution and then click “Add”button.• When your diagram is complete, select “Copy Diagram to Clipboard” from the SSMS “Edit”menu. Paste your diagram into the document that will be the final report.Page 4☐ In order to verify that the database has been populated, write individual SQL statements thatprovide a listing of the records for each table (e.g., SELECT * FROM [TABLENAME];).Provide screenshots that illustrate the “dummy” records of each table in the final report. .□ The database should be able to produce and provide a sample report with dummy data. (Hint:Fields from multiple tables should be used to develop the sample reports.) Create the SQLqueries and provide the screenshots for the following:• List the total sales for each dealer over a month’s time frame.• List the amount due from NHD to each dealer after all deductions are considered in aparticular month.• List the dealers and their corresponding booth(s) lease details.• List the revenues for NHD in a particular month.• List the booths that have not been leased.□ Discuss and explain in detail the following questions:• Why is normalization important when defining a database design?• How do validate a proposed database design model before implementation?• What challenges did you face during this course in regards to database concepts andin this project?• What would be your advice to future ISDS 402 students?□ Submit the final individual project as indicated.**Note: The final deliverable will consist of three files. The submission will consist of thefollowing three files:1. The DDL SQL script as a .txt file. This file should include both CREATE TABLESand INSERT INTO VALUES syntax.2. A .pdf file that merges the above sections into one file (except step 4, which is theDDL script).3. The SQL syntax (from step 7 above) with the necessary queries to demonstrate thefunctionality of the database. This file should be a .txt file.