<ERD to Relational Model Part 2 |Java & Friends Activities |Information System Development Part 1 >
DATABASE MODELING & DESIGN:
From ERD Diagram To Relational Model Part 3
The Enhanced Entity Relationship (EER) – Another Exercise
In this section we will use DBDesigner to verify the database schema mainly the design integrity and consistency aspects. The following is Entity Relationship Model of ABC Medical Centre. We will map the Entity Relationship Model into Relational Model.
Solution: Use the previous rules to define tables. You can go through part-by-part, counter-clockwise or clockwise.
|
Firstly we analyze the generalization part. It is a straight forward and the easiest conversion.
CREATE TABLE Doctor(
Doctorid INTEGER,
Name VARCHAR(30),
Dob DATE,
Address VARCHAR(50),
Phoneno VARCHAR(20),
Salary NUMERIC(20,2),
CONSTRAINT PKDoctor PRIMARY KEY (Doctorid)
)
CREATE TABLE Medical(
Doctorid INTEGER,
Overtimerate NUMERIC(4,2),
CONSTRAINT PKMedical PRIMARY KEY (Doctorid),
CONSTRAINT FKMedical FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid)ON DELETE CASCADE
)
CREATE TABLE Specialist(
Doctorid INTEGER,
Fieldarea VARCHAR(30),
CONSTRAINT PKSpecialist PRIMARY KEY (Doctorid),
CONSTRAINT FKSpecialist FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid)ON DELETE CASCADE
)
CREATE TABLE Appointment(
Apptno INTEGER,
Date DATE,
Time DATETIME,
Doctorid INTEGER,
Patientno INTEGER,
CONSTRAINT PKAppointment PRIMARY KEY (Apptno),
CONSTRAINT FKAppointment FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid),
CONSTRAINT FKAppointment1 FOREIGN KEY (Patientno) REFERENCES Patient(Patientno)
)
CREATE TABLE Patient(
Patientno INTEGER,
Name VARCHAR(40),
Address VARCHAR(50),
PhoneNo VARCHAR(20),
Dob DATE,
CONSTRAINT PKPatient PRIMARY KEY (Patientno)
)
-------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Payment(
Paymentno INTEGER,
Details VARCHAR(60),
Method VARCHAR(20),
Patientno INTEGER,
CONSTRAINT PKPayment PRIMARY KEY (Paymentno),
CONSTRAINT FKPayment FOREIGN KEY (Patientno) REFERENCES Patient(Patientno)
)
CREATE TABLE Bill(
Billno INTEGER,
Total NUMERIC(10,2),
Doctorid INTEGER,
CONSTRAINT PKBill PRIMARY KEY (Billno),
CONSTRAINT FKBill FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid)
)
CREATE TABLE Pay_Bill(
Paymentno INTEGER,
Billno INTEGER,
CONSTRAINT PKPay_Bill PRIMARY KEY (Paymentno, Billno),
CONSTRAINT FKPaymentno FOREIGN KEY (Paymentno) REFERENCES Payment(Paymentno),
CONSTRAINT FKBillno FOREIGN KEY (Billno) REFERENCES Bill(Billno)
)
Converting ERD Model to Relational Model (MySQL)
(This is an optional part, you can skip this part)
Next, let verify our MySQL script. Create a database, dump and execute the MySQL script. We are using NetBeans 6.0. Keep in mind that the script can be executed directly using the MySQL Command Line Client console.
Re-arranged for MySQL code execution. The referred Foreign Keys’ tables must be created first and consider the NOT NULL constraints for the columns on the tables that having foreign keys mainly for the 1-M with optional (minimum = 0 cardinalities).
CREATE TABLE Doctor(
Doctorid INTEGER,
Name VARCHAR(30),
Dob DATE,
Address VARCHAR(50),
Phoneno VARCHAR(20),
Salary NUMERIC(20,2),
CONSTRAINT PKDoctor PRIMARY KEY (Doctorid)
)type=innodb;
CREATE TABLE Patient(
Patientno INTEGER,
Name VARCHAR(40),
Address VARCHAR(50),
PhoneNo VARCHAR(20),
Dob DATE,
CONSTRAINT PKPatient PRIMARY KEY (Patientno)
)type=innodb;
CREATE TABLE Medical(
Doctorid INTEGER,
Overtimerate NUMERIC(4,2),
CONSTRAINT PKMedical PRIMARY KEY (Doctorid),
CONSTRAINT FKMedical FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid) ON DELETE CASCADE
)type=innodb;
CREATE TABLE Specialist(
Doctorid INTEGER,
Fieldarea VARCHAR(30),
CONSTRAINT PKSpecialist PRIMARY KEY (Doctorid),
CONSTRAINT FKSpecialist FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid) ON DELETE CASCADE
)type=innodb;
CREATE TABLE Appointment(
Apptno INTEGER,
Date DATE,
Time DATETIME,
Doctorid INTEGER,
Patientno INTEGER,
CONSTRAINT PKAppointment PRIMARY KEY (Apptno),
CONSTRAINT FKAppointment FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid),
CONSTRAINT FKAppointment1 FOREIGN KEY (Patientno) REFERENCES Patient(Patientno)
)type=innodb;
CREATE TABLE Payment(
Paymentno INTEGER,
Details VARCHAR(60),
Method VARCHAR(20),
Patientno INTEGER NOT NULL,
CONSTRAINT PKPayment PRIMARY KEY (Paymentno),
CONSTRAINT FKPayment FOREIGN KEY (Patientno) REFERENCES Patient(Patientno)
)type=innodb;
CREATE TABLE Bill(
Billno INTEGER,
Total NUMERIC(10,2),
Doctorid INTEGER,
CONSTRAINT PKBill PRIMARY KEY (Billno),
CONSTRAINT FKBill FOREIGN KEY (Doctorid) REFERENCES Doctor(Doctorid)
)type=innodb;
CREATE TABLE Pay_Bill(
Paymentno INTEGER,
Billno INTEGER,
CONSTRAINT PKPay_Bill PRIMARY KEY (Paymentno, Billno),
CONSTRAINT FKPaymentno FOREIGN KEY (Paymentno) REFERENCES Payment(Paymentno),
CONSTRAINT FKBillno FOREIGN KEY (Billno) REFERENCES Bill(Billno)
)type=innodb;
|
Reverse Engineer the Relational Model Back to ERD Using DBDesigner
The following is the table schemas when we reverse engineered using DBDesigner. If the previous design violates the SQL standards, there will be error(s) during the reverse engineering process.
The following are useful notes and tutorials on ERD, Normalization and database modeling in PDF format: