Question

Create Tables - normalized relations

Do the problem below. Create tables using the schema notation only. Identify the primary keys by underlining. Turn in only the schema notation. Do not turn in your work leading to the solution. Create the schema using Word or write clearly legible copies.

Create a set of normalized relations given the bill shown.

Patient bill
Patient #: 12345			Date: 7/20/08
Patient Name: Mary Baker			Date admitted: 7/14/08
Patient Address: 300 Oak Street			Discharge date: 7/17/08
City-State-Zip: Boulder, CO 80638
Cost Center	Cost Name	Date Charged	Item Code	Desc	Charge	Bal Due
100	Room & Board	7/14/08	2000	Semi-prv room	200.00
		7/14/08	2005	Television	5.00
		7/15/08	2000	Semi-prv room	200.00
		7/16/08	2000	semi-prv room	200.00
				Subtotal		605.00
110	Laboratory	7/14/08	1580	Glucose	25.00
		7/15/08	1585	Culture	20.00
				Subtotal		45.00
125	Radiology	7/15/08	3010	X-ray chest	30.00
				Subtotal		30.00
				Balance due		680.00

Answer 1

Ans

SQL table for the above problem statement:

1 .Create Database 'Patient Bill' using below SQL Command

SQL> CREATE DATABASE Patient Bill;

2.Below is the SQL Syntax to create table-name 'PatientDetails' as shown below:

SQL> CREATE TABLE PatientDetails(

Patient int,

Patient Name varchar(1000),

Patient Address varchar(5000),

City-State-Zip varchar(5000),

Date varchar(1000),

Date admitted varchar(1000),

Discharge date varchar(1000),

PRIMARY KEY (Patient);

);

Table Created

3. Below is the SQL Syntax to create table-name 'PatientCostDetails' as shown below:

SQL> CREATE TABLE PatientCostDetails(

Cost Center int,

Cost Name varchar(1000),

Date Charged varchar(1000),

Item Code int,

Desc varchar(1000),

Charge int,

Bal Due int,

PRIMARY KEY (Cost Center));

Table created

SQL>

Underlined PRIMARY KEY parameter are

For Table PatientDetails 'Patient'

For Table PatientCostDetails 'Cost Center'

Since this column should has only 'UNIQUE Values' If the same value is inserted SQL Insert will be failed with error: 'unique constraint error'

Define the schema relation:

Patient with ID 12345 with Patient Name Mary Baker Patient Address 300 Oak Street With City-State-Zip Boulder, CO 80638

is admitted on Hospital on Date 7/14/08 and Discharge Dated on 7/17/08 and Bill generated on Date 7/20/08