Identify the database model.

Figure A, with its entities, attributes, and relationships, represents an Entity-Relationship (ER) model. Figure B shows a Relational model derived from an ER model. The question asks for the model given in the figures, implying the conceptual model.

1. The database model given in the figures is the Entity-Relationship (ER) model.

A primary key is a column or a set of columns in a table that uniquely identifies each row in that table. It must contain unique values and cannot contain NULL values. 2. a. Primary key: A primary key is an attribute or a set of attributes that uniquely identifies each record in a table. * Example from the model: Matricle in the DOCTOR table, SSNo in the PATIENT table, ConsulNo in the CONSULTATION table, CodeSS in the MEDICATION table.

A composite key is a primary key that consists of two or more attributes (columns) that uniquely identify each record in a table. 2. b. Composite key: A composite key is a primary key that consists of two or more attributes whose values, when combined, uniquely identify each record in a table. * Example from the derived relational model: In the PRESCRIPTION table (which resolves the many-to-many relationship between CONSULTATION and MEDICATION), the combination of ConsulNo and CodeSS would form a composite primary key.

Based on Figure A, the relational model is derived as follows, with primary keys marked with an asterisk (*):
3.

• DOCTOR (Matricle*, Name, Surname, Qualification)
• PATIENT (SSNo*, Name, Surname)
• CONSULTATION (ConsulNo*, Date, Time, Matricle, SSNo)
  • Matricle is a foreign key referencing DOCTOR.
  • SSNo is a foreign key referencing PATIENT.
• MEDICATION (CodeSS*, Pharmacy, CommerceName)
• PRESCRIPTION (ConsulNo*, CodeSS*)
  • ConsulNo is a foreign key referencing CONSULTATION.
  • CodeSS is a foreign key referencing MEDICATION.
  • The combination (ConsulNo, CodeSS) forms the composite primary key.

4. The SQL code to create the database GHHosp is:

CREATE DATABASE GHHosp;

5. The SQL code to create the relations (tables) derived in Step 4 is:

USE GHHosp;

CREATE TABLE DOCTOR (
    Matricle VARCHAR(10) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL,
    Qualification VARCHAR(100)
);

CREATE TABLE PATIENT (
    SSNo VARCHAR(15) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL
);

CREATE TABLE CONSULTATION (
    ConsulNo INT PRIMARY KEY AUTO_INCREMENT,
    Date DATE NOT NULL,
    Time TIME NOT NULL,
    Matricle VARCHAR(10) NOT NULL,
    SSNo VARCHAR(15) NOT NULL,
    FOREIGN KEY (Matricle) REFERENCES DOCTOR(Matricle),
    FOREIGN KEY (SSNo) REFERENCES PATIENT(SSNo)
);

CREATE TABLE MEDICATION (
    CodeSS VARCHAR(10) PRIMARY KEY,
    Pharmacy VARCHAR(100),
    CommerceName VARCHAR(100) NOT NULL
);

CREATE TABLE PRESCRIPTION (
    ConsulNo INT NOT NULL,
    CodeSS VARCHAR(10) NOT NULL,
    PRIMARY KEY (ConsulNo, CodeSS),
    FOREIGN KEY (ConsulNo) REFERENCES CONSULTATION(ConsulNo),
    FOREIGN KEY (CodeSS) REFERENCES MEDICATION(CodeSS)
);