Question
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.
- make the SQL query to create a new database named clinic.
- 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...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started