<title>Database Management Systems - DBMS Portfolio</title> <style> body { font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #000; /* Black Background */ color: #FFD700; /* Yellow Text */ } header { background-color: #1a1a1a; color: #FFD700; padding: 20px 10px; text-align: center; } .nav-links { list-style: none; padding: 0; margin: 20px 0 0; display: flex; justify-content: center; gap: 20px; } .nav-links a { text-decoration: none; color: #FFD700; font-weight: bold; padding: 5px 10px; transition: all 0.3s ease; background-color: transparent; border-radius: 4px; } .nav-links a:hover { color: #000; background-color: #FFD700; /* Yellow Background on Hover */ } section { display: none; /* Hide sections by default */ padding: 20px; } section.active { display: block; /* Show the active section */ } footer { background-color: #1a1a1a; color: #FFD700; text-align: center; padding: 10px; } .github { text-align: center; margin-top: 30px; } .github a { color: #FFD700; /* Yellow color for GitHub links */ text-decoration: none; font-weight: bold; } .github a:hover { color: #00acee; /* Blue on hover */ } .assignment { background-color: #1a1a1a; padding: 15px; margin: 20px 0; border-radius: 8px; box-shadow: 0px 0px 10px rgba(255, 255, 255, 0.2); } .assignment h3 { color: #FFD700; /* Yellow for assignment titles */ } a { color: #FFD700; /* Default yellow color for links */ text-decoration: none; } a:hover { color: #00acee; /* Blue on hover for consistency */ } </style>

Database Management Systems - DBMS Portfolio

Semester: 3 Semester | Department: Computer Science Engineering

Mentor: Guruprasad Konnurmath | Course Code: 15ECSC208

• Home
• About Me
• Learning and Assignments
• Contact

Home

Welcome to my DBMS portfolio. Here, I showcase my journey and accomplishments in learning database management systems.

About Me

I am an aspiring database professional passionate about designing efficient, secure, and reliable database systems.

Learning and Assignments

4. Pharmaceutical Database Management

Task: Design and manage a pharmaceutical database as per the given specifications.

Solution:

1. ER Diagram

The ER diagram includes the following entities and relationships:

• Entities:
  • Pharmaceutical_Company: Company_Name (PK), Phone_No
  • Drug: Trade_Name (PK), Drug_Name, Formula, Company_Name (FK)
  • Pharmacy: Pharmacy_ID (PK), Name, Address, Phone_No
  • Patient: Patient_ID (PK), Name, Age, Address
  • Doctor: Doctor_ID (PK), Name, Specialty, Years_Experience
  • Prescription: Prescription_ID (PK), Doctor_ID (FK), Patient_ID (FK), Date, Quantity
  • Contract: Contract_ID (PK), Pharmacy_ID (FK), Company_Name (FK), Start_Date, End_Date, Terms, Supervisor
• Relationships:
  • Sells: Between Pharmacy and Drug, with Price as an attribute.
  • Prescribes: Between Doctor and Patient, linked via Prescription.
  • Has_Contract: Between Pharmacy and Pharmaceutical_Company, linked via Contract.

2. Relational Schema

The schema derived from the ER diagram includes:

• Pharmaceutical_Company: Company_Name (PK), Phone_No
• Drug: Trade_Name (PK), Drug_Name, Formula, Company_Name (FK)
• Pharmacy: Pharmacy_ID (PK), Name, Address, Phone_No
• Patient: Patient_ID (PK), Name, Age, Address
• Doctor: Doctor_ID (PK), Name, Specialty, Years_Experience
• Sells: Pharmacy_ID (PK, FK), Trade_Name (PK, FK), Price
• Prescribes: Prescription_ID (PK), Doctor_ID (FK), Patient_ID (FK), Date, Quantity
• Contract: Contract_ID (PK), Pharmacy_ID (FK), Company_Name (FK), Start_Date, End_Date, Terms, Supervisor

3. Sample Data

Populated tables with sample records:

    Pharmaceutical_Company
    | Company_Name      | Phone_No    |
    |-------------------|-------------|
    | MedCorp           | 1234567890  |
    | HealthCare Ltd.   | 9876543210  |
    Drug
    | Trade_Name | Drug_Name   | Formula  | Company_Name   |
    |------------|-------------|----------|----------------|
    | Panadol    | Paracetamol | C8H9NO2  | MedCorp        |
    

4. SQL Queries

Demonstrating concepts:

• Multiple table join operations:

          SELECT Doctor.Name, Patient.Name, Drug.Trade_Name, Prescription.Quantity 
          FROM Prescription 
          JOIN Doctor ON Prescription.Doctor_ID = Doctor.Doctor_ID 
          JOIN Patient ON Prescription.Patient_ID = Patient.Patient_ID 
          JOIN Drug ON Prescription.Drug_ID = Drug.Trade_Name 
          WHERE Prescription.Quantity > 2;
          

• Clauses & Functions:

          SELECT Pharmacy.Name, COUNT(Sells.Trade_Name) AS Drug_Count 
          FROM Pharmacy 
          JOIN Sells ON Pharmacy.Pharmacy_ID = Sells.Pharmacy_ID 
          GROUP BY Pharmacy.Name 
          HAVING COUNT(Sells.Trade_Name) > 3;
          

• Subqueries:

          SELECT Name FROM Pharmacy 
          WHERE Pharmacy_ID = (SELECT Pharmacy_ID FROM Sells WHERE Trade_Name = 'Panadol');
          

• Views:

          CREATE VIEW DrugPrices AS 
          SELECT Trade_Name, Pharmacy.Name, Price 
          FROM Sells 
          JOIN Pharmacy ON Sells.Pharmacy_ID = Pharmacy.Pharmacy_ID;
          

Contact

Email: 01fe23bcs228@kletech.ac.in

LinkedIn: linkedin.com/in/yourprofile

GitHub: github.com/yourprofile

GitHub Repository

Check out the code for this portfolio on GitHub:

GitHub Repository Link

<script> function showSection(sectionId) { const sections = document.querySelectorAll("section"); sections.forEach((section) => { section.classList.remove("active"); }); document.getElementById(sectionId).classList.add("active"); } </script>