Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

Enhanced Entity Relationships:From ERD to Relational Model, Exercises of Medical Sciences

United Lutheran SeminaryMedical Sciences

Entity Relationships Excercises include Patients,Payment,Appointment,Doctor,Bill,Medical and Specialist.

Typology: Exercises

2021/2022

Uploaded on 02/11/2022

sadayappan
sadayappan 🇺🇸

4.5

(15)

246 documents

1 / 7

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Enhanced Entity Relationship: From ERD to Relational
Model – Exercise & Answer
The following is Entity Relationship Model of ABC Medical Centre. Map the Entity
Relationship Model into Relational Model.
Need to study and properly understand the 7 rules (from mannino book) in converting the ERD to
Table schemas (relational model). The following SQL script try to cover the whole ERD diagram.
pf3
pf4
pf5

Partial preview of the text

Download Enhanced Entity Relationships:From ERD to Relational Model and more Exercises Medical Sciences in PDF only on Docsity!

Enhanced Entity Relationship: From ERD to Relational

Model – Exercise & Answer

The following is Entity Relationship Model of ABC Medical Centre. Map the Entity

Relationship Model into Relational Model.

Need to study and properly understand the 7 rules (from mannino book) in converting the ERD to

Table schemas (relational model). The following SQL script try to cover the whole ERD diagram.

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 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,

Primary Key Appno

Foreign Key Doctorid references Doctor(Doctorid)

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) )

Patient(Patientno, Name, Address, Phoneno, Dob)

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) )

Payment(Paymentno, Details, Method, Patientno)

Primary Key Paymentno

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) )

Bill(Billno, Total, Doctorid)

Primary Key Billno

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) )

Pay_Bill(Paymentno, Billno)

Primary Key Paymentno, Billno

Foreign Key Paymentno references Payment(Paymentno)

Foreign Key Billno references Bill(Billno)