You are hired as a business analyst to design a relational ✓ Solved

You are hired as a business analyst to design a relational database using MySQL. Choose a small company or business from the provided list as the basis for your project. Options include retail businesses (clothing, computer, plumbing, auto supply, furniture, shoes), service companies (electrical, heating, air conditioning, lawn care, house remodeling), a restaurant, a theme park, a general construction contractor, a modeling agency, or a school.

Next, perform a business area analysis, which includes a business overview, the RDBMS used, security and data integrity applied, and a summary of your experience.

For the logical design, create a data model defining at least 5 entities and their attributes, normalized to the 3rd Normal Form. An entity-relationship diagram using Crow's foot notation is also required.

In the physical design stage, using MySQL or Oracle, convert all entities to tables and their attributes to columns. Define data types, primary keys, foreign keys, indexes, check and not null constraints. Then create SQL scripts to establish tables, primary keys, foreign keys, indices, sequences, etc.

For the database execution, create database objects using MySQL or Oracle. Write SQL scripts to create tables, primary keys, foreign keys, and check constraints as applicable. You will also need to create an SQL script to insert at least 5 rows into each table and output the result of the SELECT statement (SELECT * FROM

Finally, submit your project, which includes a business area analysis (step 2) in a Word file, an ER diagram in Visio, PowerPoint, or Word, a normalized entity-attribute list in a Word or text file, DDL and DML statements for all tables in a Word or text file, and the output of SELECT statements in a text or Excel file.

Paper For Above Instructions

Designing a Relational Database for a Clothing Retail Store

As a business analyst, I have chosen to design a relational database for a small clothing retail store. This store will serve various customers with diverse clothing needs, including casual wear, formal wear, and seasonal collections. The relational database will help manage inventory, customer data, sales transactions, and supplier information effectively.

Business Overview

The chosen retail business is a clothing store called "Fashion Hub." Fashion Hub offers a wide range of clothing for men, women, and children. The store aims to provide high-quality apparel at reasonable prices. With the growing demand for online shopping, the database will support both in-store and online sales, allowing customers to browse products, place orders, and track their purchases efficiently.

RDBMS Used

The relational database management system (RDBMS) chosen for this project is MySQL, known for its flexibility, performance, and ability to handle large datasets. MySQL is also open-source, making it a cost-effective choice for small businesses like Fashion Hub. The relational model will facilitate the organization and retrieval of data while ensuring relationships among different entities are properly maintained.

Security and Data Integrity Applied

To ensure security and data integrity, the database will incorporate several measures. Access control will be implemented to restrict user permissions, allowing only authorized personnel to perform sensitive operations. Data encryption techniques will be employed to protect customer information during transactions. Additionally, foreign key constraints will maintain referential integrity across related tables, preventing orphan records and ensuring data consistency.

Normalization and Logical Design

The logical design involves identifying key entities and their attributes while ensuring the database schema is normalized to the 3rd Normal Form (3NF). The identified entities for Fashion Hub are:

• Customers: Attributes include CustomerID (Primary Key), FirstName, LastName, Email, PhoneNumber.
• Products: Attributes include ProductID (Primary Key), ProductName, Category, Price, StockQuantity.
• Orders: Attributes include OrderID (Primary Key), OrderDate, CustomerID (Foreign Key), TotalAmount.
• OrderDetails: Attributes include OrderDetailID (Primary Key), OrderID (Foreign Key), ProductID (Foreign Key), Quantity, Price.
• Suppliers: Attributes include SupplierID (Primary Key), SupplierName, ContactName, PhoneNumber.

This schema ensures that data is organized without redundancy, and all tables are connected through relational links.

Entity Relationship Diagram (ERD)

An ER diagram will be created using Crow's foot notation to visually represent the relationships among the identified entities. Each entity will display its attributes, and the relationships will indicate cardinalities (one-to-many, many-to-one) among them.

Physical Design

In the physical design phase, the following table structures will be created in MySQL:

• Customers Table: Creates columns for customer attributes with CustomerID set as the primary key.
• Products Table: Defines product attributes with ProductID as the primary key.
• Orders Table: Establishes OrderID as primary key and includes a foreign key reference to Customers.
• OrderDetails Table: Contains OrderDetailID as primary key, along with foreign keys linking to Orders and Products.
• Suppliers Table: Incorporates SupplierID as primary key.

Data types will be defined for each attribute appropriately, and constraints such as NOT NULL and CHECK constraints will ensure data accuracy.

SQL Scripts

The following SQL scripts will be executed to create the database and insert data:

CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(15)
);
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Category VARCHAR(50) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
StockQuantity INT NOT NULL
);
CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
OrderDate DATETIME NOT NULL,
CustomerID INT,
TotalAmount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderDetails (
OrderDetailID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
CREATE TABLE Suppliers (
SupplierID INT AUTO_INCREMENT PRIMARY KEY,
SupplierName VARCHAR(100) NOT NULL,
ContactName VARCHAR(50),
PhoneNumber VARCHAR(15)
);

After creating tables, at least five rows will be inserted into each table to populate initial data. Select statements will be executed to verify the integrity of the created tables and their relationships.

Conclusion

The designed relational database for Fashion Hub will streamline operations by efficiently managing customer, product, order, and supplier data. Implementation of MySQL will provide a strong framework for data management and security, ultimately leading to improved service delivery.

References

• Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems. Pearson.
• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Hoffer, J. A., Venkataraman, R., & Topi, H. (2013). Modern Database Management. Pearson.
• MySQL Documentation. (2023). MySQL 8.0 Reference Manual. Oracle Corporation.
• Sadalage, P. J., & Fowler, M. (2012). NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence. Addison-Wesley.
• Rob, P., & Coronel, C. (2017). Database Systems: Design, Implementation, & Management (12th ed.). Cengage Learning.
• Elmasri, R., & Navathe, S. B. (2017). Fundamentals of Database Systems (7th ed.). Pearson Education.
• Stair, R., & Reynolds, G. (2016). Principles of Information Systems. Cengage Learning.
• Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill Higher Education.
• Date, C. J. (2003). An Introduction to Database Systems. Pearson.