Database IT & Computer Science Coursework Research (Other (Not Listed) Sample)

Name:
Course:
Professor:
Date:
DataBase and ERD Diagram of a hospital
 

Data Type

Not Null

pk

fk

identity

check

unique

description










Doctor_Office


















office_id

int

Y

Y


Y


Y

artificial unique id for table

name

varchar(50)

Y







address

varchar(100)

Y







phone_nbr

varchar(20)

Y





Y

main phone to clinic

type

varchar(30)

Y




type in ('express', 'full service', 'emergency')





























Doctor


















doctor_id

int

Y

Y


Y


Y

artificial unique id for each doctor

name

varchar(50)

Y







specialty_type

varchar(20)

Y




specialty_type in ('pediatrician', 'cardiologist', 'internist')

valid types are pediatrician, cardiologist, and internist

salary

numeric (10,2)

Y




>0



office_id

int

Y


Y




reference Doctor_Office (office_id)




























Record


















record_id

int

Y

Y


Y


Y

artificial unique number for each record

date

datetime

Y




default getdate()



amount_charged

numeric(10,2)

Y




>0



diagnosis_type

varchar(20)

Y




primary_diagnosis_type in('fever','cold','surgery')


valid types are fever, cold, and stomach ache

doctor_id

int

Y


Y




reference Doctor (doctor_id)

patient_id

int

Y


Y




reference Patient (patient_id)



















Patient


















patient_id

int

Y

Y


Y


Y

artificial unique id for each patient

name

varchar(50)

Y







address

varchar(100)

Y







account_balance

numeric(10,2)

Y




>0






























Payment


















payment_id

int

Y

Y


Y


Y

artificial unique id for each payment

date

datetime

Y




default getdate()



payment_type

varchar (20)

Y




payment_type in('cash', 'check', 'credit card')


valid types are cash, check, and credit card

patient_id

int

Y


Y




reference Patient(patient_id)

amount

numeric(10,2)

Y




>0



Requirements 1: (using a procedure) system should create all the database tables
The procedure creates database tables and sets all the primary keys and the foreign keys to the required tables.
USE [Hospital]
GO
/****** Object: StoredProcedure [dbo].[DatabaseTables] Script Date: 4/28/2017 12:39:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[DatabaseTables]
AS
BEGIN
--create the doctor_office table and set the primary key
Create table Doctor_Office (
office_id int primary key NOT NULL IDENTITY(1,1),
name varchar(50) NOT NULL,
address varchar(100) NOT NULL,
phone_nrb varchar(20) NOT NULL ,
type varchar(30) NOT NULL
)
Create table Doctor (
doctor_id int primary key NOT NULL IDENTITY(1,1),
name varchar(50) NOT NULL,
speciality_type varchar(20) NOT NULL,
salary int NOT NULL ,
office_id int NOT NULL
)
Create table Patient (
Patient_id int primary key NOT NULL IDENTITY(1,1),
name varchar(50) NOT NULL,
address varchar(100) NOT NULL,
account_balance int NOT NULL
)
Create table Payment (