Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

You are given the following entity relationship diagram for a clinic database. Write the sql queries to answer each of the following questions. Your answer

You are given the following entity relationship diagram for a clinic database. Write the sql queries to answer each of the following questions. Your answer should consist of the SQL query and also the result of the query in a tabular format when applicable.

image.png 
  1. make the SQL query to create a new database named clinic.
  2. make a SQL query to create the Patient table along with the constraints defined below. Define the constraints that show in the table. Define and name the pk constraint as PK_PATIENT on a separate line after defining the columns.

 

Table Name

Attribute

Type 

Required 

PK or FK

Constraint 

Patient

P_ID

int 

Y

PK

 

 

P_LNAME

varchar(20)

Y

 

 

 

P_FNAME

varchar(20)

Y

 

 

 

P_EMAIL 

varchar(50)

 

 

Unique 

 

3.  make a SQL query to create the BILL table along with the constraints defined below. Define the check constraint on BILL_TOTAL that allows only positive values under BILL_TOTAL column.

 

Table Name

Attribute

Type 

Required 

PK or FK

Constraint 

BILL

BILL_ID

int 

Y

PK

 

 

BILL_TOTAL

real

Y

 

> 0

 

 

4.  make a SQL query to create the payment table along with the constraints defined below. 

 

Table Name

Attribute

Type 

Required 

PK or FK

Constraint 

FK referenced table

PAYMENT

PAY_ID

int 

Y

PK

 

 

 

PAY_DATE 

date

Y

 

 

 

 

PAY_AMOUNT

real

Y

 

>0

 

 

BILL_ID

Int

Y

FK

 

BILL 

 

P_ID 

Int 

Y

FK

 

PATIENT

 

5.  Show a screenshot of the created tables in the object explorer. Expand the columns list in each table, the keys and the constraints. 

 

6.  Write insert commands to add the following rows in the PATIENT table. Show the data in the patient table. 

 

 

 

P_ID

P_LNAME

P_FNAME

P_EMAIL 

10011

Dunne

Leonna

 

10012

Smith

Kathy

 

10013

Ramas

Alfred

 

PATIENT PK P_ID P_LNAME P_FNAME P_EMAIL +| makes PAYMENT - PK PAY_ID FK FK PAY_DATE PAY_AMOUNT BILL_ID P_ID is found BILL PK BILL_ID BILL_TOTAL

Step by Step Solution

3.31 Rating (154 Votes )

There are 3 Steps involved in it

Step: 1

Lets go through the tasks one by one and write the appropriate SQL queries 1 To create a new database named clinic sql CREATE DATABASE clinic 2 To cre... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Modern Systems Analysis And Design

Authors: Joseph Valacich, Joey George

8th Edition

0134204921, 978-0134204925

More Books

Students also viewed these Databases questions