- Individual Final Project Create A Database System Purpose To Demonstrate The Ability To Propose Implement And Query T 1 (899.32 KiB) Viewed 41 times
I am having trouble on number 3: I don't understand what a tableis? and how do I make it?
I am also having alot of trouble on number 4. Please send how todo the CREAT TABLE and INSERT TABLE
Individual Final Project Create A Database System 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, and other ventures throughout the country. Last year, NHD purchased several malls that will work as in southern California. The term mall in this case is defined as a building that leases various spaces to individuals or small business companies to market and sell their products. These buildings are in areas where they have shown potential for a positive financial return. David is the chief information officer for NHD. David's responsibility is to provide information to the board of directors so it can make strategic decision about future development ventures. Because most businesses in these malls are small business owners, most of these malls do their bookkeeping on paper and David is concerned that the data he needs from the malls will not be easy to obtain. Because of the paper-based systems, David expects it to be difficult to obtain items such as total sales, total commissions, and dealer sales. David believes that by creating a specialized database for the mall managers to use, he can ensure that the data he will be easy for managers to create and maintain. The malls will be able to use the database to create the reports he needs in order to efficiently 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 buildings are divided into booths that are rented to dealers, who then fill the booths with inventory that is sold to customers. A dealer might be a small company or an individual. It is the dealer's responsibility to manage its own inventory; the mall does not maintain an inventory list for the dealers. As dealers sell items from its inventory, the mall records the dealer number and the price of each item using the information on the item's price tag. At the end of the month, the mall generates a list of total sales for each dealer, computes the mall's commission, deducts the dealer's rent for booth space, and then issues the dealer a check for the remaining amount. David determined that the database must manage sales, booths in the mall, and dealers that rent the booths. At the end of the month, the database must be able to produce a complete list of sales by dealer. In addition, the database must determine the revenue owed to each dealer by deducting the dealer'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 a database. David selected one mall to serve as the pilot for the project, which will allow him to test the database before implementing it with all of the malls. Lisa, is the chosen mall's manager and she is excited about replacing the mall's manual systems with a database. After meeting with Lisa, David has collected a couple of forms that are currently being used to manage the mall. (See Figure 1 and Figure 2).
Figure 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. Name: Address: City: Phone: Tax ID: 34-5690654 Comments: Call cell phone first. Up to 15% discount is approved. Figure 1: Dealer Information Form A-17 12x8 $175 NHD Memories Antique Mall Dealer Information Form Figure 2 illustrates the map of the various spaces leased to dealers. Currently this is kept on paper and is used to determine which booths are leased or vacant. In addition, to this form, Lisa needs to keep up with 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 rafters above it or and carpeting. A-16 A-15 A-14 A-13 A-12 12x8 12x8 12x10 12x10 12x18 $175 $175 $230 $230 $310 A-18 12x12 $290 Marcia Tyler 9800 Harbor Lane Cleveland (423) 890-8788 A-19 12x18 $310 A-20 12x18 $310 B-12 B-11 8x12 8x12 $175 $175 8-13 C-13 8x12 8x16 $175 $150 B-14 C-14 8x10 8x20 $120 $200 B-15 C-15 8x10 8x20 $120 $200 State: TN ZIP: 37364 Cell Phone: (423) 645-8900 C-12 8x16 $150 8-10 B-09 8x10 8x10 $120 $120 C-06 8x8 $90 C-07 C-11 8x8 $90 Front Counter A-11 12x18 $310 8x8 $90 C08 8x8 $90 C-09 8x8 $90 C-10 8x8 $90 A-10 A-09 A.08 A-07 12x10 12x10 12x10 12x12 $230 $230 $230 $290 Page 2 8-08 B-07 B-06 8x10 8x8 8x8 $120 $105 $105 C.05 B-05 8x8 8x8 $90 $105 C-04 B-04 8x8 8x8 $90 $105 B-03 8x10 $120 B-02 8x10 C-03 8x8 $90 C-02 8x8 $90 $120 B-01 Col 8x8 8x10 $90 $120 Figure 2: Map of the Selected Mall A-06 12x18 $310 A-05 12x12 $290 A-04 12x12 $290 A-03 12x12 $290 A-02 12x18 $310 A-01 12x18 $310
David is a CSUF alumni and is seeking the assistance from IS majors to help him with this project. Because your team has been working on developing databases, your team has been asked to create a preliminary database 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 attributes needed to capture and automate the manual processes for NHD case. Define each of the entities using the 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 (Studentld, StudentName, Email, Age, Major) *Note: Attributes which comprise the primary key should be underlined and attributes that are foreign keys should be italized. 2. Use any drawing tool (e.g., MS Visio or MS drawing toolbar) and create an illustration of the proposed database design model that will automate the current manual processes at NHD. Because relationships are critical to the database design, identify the minimum and maximum cardinalities in each of the relationships between entities. Provide brief justifications for the cardinalities depicted in the proposed data model. *Note: In addition to minimum and maximum cardinalities, relationship lines should be illustrated according to type of relationship (e.g., identifying and non-identifying) and weak and strong entities should be differentiated as well. 3. For each entity, create a matrix or table that provides the fields, data types, field size, and description (if applicable). Provide a brief description for each table that explains the table's purpose. This can be done in one sentence or two. Place the description above each table. 4. Implement and populate the database by using your knowledge of SQL DDL. Reference the 3NF entities from previous steps as a guide to define the properties and structure in SQL syntax. Enforce referential integrity by defining PK/FK constraints in the SQL script. Include 15 dummy records for each table. This dummy data should be well thought out as you will be using the data to run specific queries 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 submitted as a separate file. Combine both the CREATE TABLES and INSERT INTO VALUES script into one text file (.txt) and make sure the script can be executed together without any errors. 5. Once the database has been implemented, create a database diagram in SQL Server Management Studio (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 3
In order to verify that the database has been populated, write individual SQL statements that provide 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 SQL queries 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 a particular 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. . .