You are given the following entity relationship diagram for a clinic database. Write the sql queries to
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 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 |
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George