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).
Page 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.
Figure 1: 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.
Figure 2: Map of the Selected Mall
Page 3
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 (StudentId, StudentName, Email, Age, Major)
*Note: Attributes which comprise the primary key should be underlined and attributes that are foreign
keys should be italized.
□ 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.
□ 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.
☐ 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.
☐ 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 4
☐ 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.
□ 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 and
in 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 the
following three files:
1. The DDL SQL script as a .txt file. This file should include both CREATE TABLES
and INSERT INTO VALUES syntax.
2. A .pdf file that merges the above sections into one file (except step 4, which is the
DDL script).
3. The SQL syntax (from step 7 above) with the necessary queries to demonstrate the
functionality of the database. This file should be a .txt file.
