Designing a complete database schema with all the details database create table names and table primary, foreign key, unique key, not null all column names constrain should be specify and sample data for for each table minimum 5 rows, also write create, insert sql query for all the tables BIT

Improving MySQL database security and performance is crucial for the overall health and reliability of your application. Below are steps you can take to enhance both security and performance. Please note that these recommendations are aimed at beginners, and it's important to adapt them to your specific use case and environment.

Database Security:

1. Use Strong Passwords:

   • Set strong and unique passwords for MySQL user accounts.
   • Avoid using default usernames and passwords.

2. Limit User Privileges:

   • Grant the minimum necessary privileges to MySQL users.
   • Avoid using the GRANT ALL statement unless absolutely necessary.

3. Update MySQL Regularly:

   • Keep your MySQL server up to date with the latest security patches.

4. Firewall Configuration:

   • Use firewalls to restrict access to your MySQL server.
   • Allow only trusted IP addresses to connect to the MySQL port.

5. Encrypt Connections:

   • Enable SSL/TLS for encrypting data in transit.
   • Use the --require_secure_transport option to enforce secure connections.

6. Backup and Recovery:

   • Regularly backup your databases and store backups securely.
   • Practice restoring from backups to ensure they are valid.

7. Audit Logging:

   • Enable MySQL's audit logging to monitor and track database activity.
   • Review and analyze logs for suspicious activity.

8. Use Prepared Statements:

   • Use parameterized queries or prepared statements to prevent SQL injection attacks.

9. Input Validation:

   • Validate and sanitize user inputs to prevent malicious data.

10. Security Plugins:

    • Consider using security plugins like MariaDB's Audit Plugin or third-party solutions.

Database Performance:

1. Indexes:

   • Properly index your tables based on the types of queries your application performs.
   • Avoid over-indexing, as it can negatively impact write performance.

2. Query Optimization:

   • Analyze and optimize your SQL queries using EXPLAIN to identify bottlenecks.
   • Avoid using SELECT * when not all columns are needed.

3. Caching:

   • Implement query caching and result caching where applicable.
   • Utilize tools like Memcached or Redis for caching.

4. Table Partitioning:

   • Consider partitioning large tables to improve query performance.
   • Partition based on frequently used criteria.

5. Regular Maintenance:

   • Regularly run maintenance tasks like optimizing tables and rebuilding indexes.
   • Use tools like OPTIMIZE TABLE and ANALYZE TABLE when needed.

6. Buffer Pool Size:

   • Adjust the MySQL InnoDB buffer pool size to fit in available memory.
   • Monitor and tune the buffer pool for optimal performance.

7. Connection Pooling:

   • Use connection pooling to minimize the overhead of establishing and tearing down connections.

8. InnoDB Configuration:

   • Optimize InnoDB settings such as innodb_buffer_pool_size, innodb_log_file_size, and others based on your server's capacity.

9. Storage Engine Selection:

   • Choose the appropriate storage engine based on your application requirements (e.g., InnoDB for transactional applications, MyISAM for read-heavy applications).

10. Monitoring:

    • Implement monitoring tools to track database performance over time.
    • Use tools like MySQL's Performance Schema and third-party solutions.
    •  

Table: Customers

• Columns: CustomerID (Primary Key), FirstName, LastName, Email, Phone, Address
• CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL, Email VARCHAR(255), Phone VARCHAR(20), Address VARCHAR(500) );

Table: Suppliers

• Columns: SupplierID (Primary Key), SupplierName, ContactPerson, Email, Phone, Address

CREATE TABLE Suppliers (

    SupplierID INT PRIMARY KEY,

    SupplierName VARCHAR(255) NOT NULL,

    ContactPerson VARCHAR(255),

    Email VARCHAR(255),

    Phone VARCHAR(20),

    Address VARCHAR(500)

);

Table: Drugs

• Columns: DrugID (Primary Key), Name, Manufacturer, UnitPrice, QuantityInStock, ExpiryDate, CategoryID (Foreign Key)

CREATE TABLE Drugs (

    DrugID INT PRIMARY KEY,

    Name VARCHAR(255) NOT NULL,

    Manufacturer VARCHAR(255),

    UnitPrice DECIMAL(10,2) NOT NULL,

    QuantityInStock INT NOT NULL,

    ExpiryDate DATE,

    CategoryID INT,

    FOREIGN KEY (CategoryID) REFERENCES DrugCategories(CategoryID)

);

Table: Orders

• Columns: OrderID (Primary Key), CustomerID (Foreign Key), OrderDate, TotalAmount, Status

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE NOT NULL, TotalAmount DECIMAL(10,2) NOT NULL, Status VARCHAR(50) NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );

Table: Deliveries

• Columns: DeliveryID (Primary Key), OrderID (Foreign Key), DeliveryDate, DeliveryStatus

CREATE TABLE Deliveries ( DeliveryID INT PRIMARY KEY, OrderID INT, DeliveryDate DATE NOT NULL, DeliveryStatus VARCHAR(50) NOT NULL, FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) );

Table: Payments

• Columns: PaymentID (Primary Key), OrderID (Foreign Key), Amount, PaymentDate
• CREATE TABLE Payments ( PaymentID INT PRIMARY KEY, OrderID INT, Amount DECIMAL(10,2) NOT NULL, PaymentDate DATE NOT NULL, FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) );

Table: Stock

• Columns: StockID (Primary Key), DrugID (Foreign Key), QuantityInStock, LastUpdateDate

CREATE TABLE Stock (

    StockID INT PRIMARY KEY,

    DrugID INT,

    QuantityInStock INT NOT NULL,

    LastUpdateDate DATE NOT NULL,

    FOREIGN KEY (DrugID) REFERENCES Drugs(DrugID)

);

Table: DrugCategories

• Columns: CategoryID (Primary Key), CategoryName

CREATE TABLE DrugCategories ( CategoryID INT PRIMARY KEY, CategoryName VARCHAR(255) NOT NULL );

Table: Employees

• Columns: EmployeeID (Primary Key), FirstName, LastName, Email, Phone, Position

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL, Email VARCHAR(255), Phone VARCHAR(20), Position VARCHAR(100) );

Table: EmployeeRoles

• Columns: RoleID (Primary Key), RoleName

CREATE TABLE EmployeeRoles ( RoleID INT PRIMARY KEY, RoleName VARCHAR(255) NOT NULL );

Table: EmployeeAssignments

• Columns: AssignmentID (Primary Key), EmployeeID (Foreign Key), RoleID (Foreign Key), StartDate, EndDate

CREATE TABLE EmployeeAssignments ( AssignmentID INT PRIMARY KEY, EmployeeID INT, RoleID INT, StartDate DATE NOT NULL, EndDate DATE, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (RoleID) REFERENCES EmployeeRoles(RoleID) );

Table: PrescriptionRecords

• Columns: PrescriptionID (Primary Key), CustomerID (Foreign Key), PrescriptionDate, DoctorName, Notes

CREATE TABLE PrescriptionRecords ( PrescriptionID INT PRIMARY KEY, CustomerID INT, PrescriptionDate DATE NOT NULL, DoctorName VARCHAR(255), Notes TEXT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );

Table: PrescriptionItems

• Columns: PrescriptionItemID (Primary Key), PrescriptionID (Foreign Key), DrugID (Foreign Key), Dosage, Frequency, Duration

CREATE TABLE PrescriptionItems ( PrescriptionItemID INT PRIMARY KEY, PrescriptionID INT, DrugID INT, Dosage VARCHAR(100), Frequency VARCHAR(100), Duration VARCHAR(100), FOREIGN KEY (PrescriptionID) REFERENCES PrescriptionRecords(PrescriptionID), FOREIGN KEY (DrugID) REFERENCES Drugs(DrugID) );

Table: AnalyticsData

• Columns: AnalyticsID (Primary Key), Date, TotalSales, TotalProfit

CREATE TABLE AnalyticsData ( AnalyticsID INT PRIMARY KEY, Date DATE NOT NULL, TotalSales DECIMAL(10,2) NOT NULL, TotalProfit DECIMAL(10,2) NOT NULL );

Table: Discounts

• Columns: DiscountID (Primary Key), DrugID (Foreign Key), DiscountPercentage, StartDate, EndDate

CREATE TABLE Discounts ( DiscountID INT PRIMARY KEY, DrugID INT, DiscountPercentage DECIMAL(5,2) NOT NULL, StartDate DATE NOT NULL, EndDate DATE, FOREIGN KEY (DrugID) REFERENCES Drugs(DrugID) );

Table: Returns

• Columns: ReturnID (Primary Key), OrderID (Foreign Key), ReturnDate, Reason

CREATE TABLE Returns ( ReturnID INT PRIMARY KEY, OrderID INT, ReturnDate DATE NOT NULL, Reason TEXT, FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) );

Table: TaxRates

• Columns: TaxRateID (Primary Key), RatePercentage

CREATE TABLE TaxRates ( TaxRateID INT PRIMARY KEY, RatePercentage DECIMAL(5,2) NOT NULL );

Table: OrderTaxes

• Columns: OrderTaxID (Primary Key), OrderID (Foreign Key), TaxRateID (Foreign Key), TaxAmount

CREATE TABLE OrderTaxes ( OrderTaxID INT PRIMARY KEY, OrderID INT, TaxRateID INT, TaxAmount DECIMAL(10,2) NOT NULL, FOREIGN KEY (OrderID) REFERENCES Orders(OrderID), FOREIGN KEY (TaxRateID) REFERENCES TaxRates(TaxRateID) );

Table: UserProfile

• Columns: UserID (Primary Key), Username, PasswordHash, Role

CREATE TABLE UserProfile ( UserID INT PRIMARY KEY, Username VARCHAR(255) NOT NULL, PasswordHash VARCHAR(255) NOT NULL, Role VARCHAR(50) NOT NULL );

UserLogs

• Columns: LogID (Primary Key), UserID (Foreign Key), LogDate, Activity

CREATE TABLE UserLogs ( LogID INT PRIMARY KEY, UserID INT, LogDate DATETIME NOT NULL, Activity TEXT, FOREIGN KEY (UserID) REFERENCES UserProfile(UserID) );

For more guidance on Writing Project Proposals!!!

Home visits Individual / Group / Online classes in English / Sinhala / Tamil. Sample Projects/Assignments Exam Papers, Tutorials, Notes and Answers will we provided.

CALL/WHATSAPP +94 777 33 7279 | EMAIL  ITCLASSSL@GMAIL.COM