HEALTH CLINIC DATABASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

HEALTH CLINIC DATABASE

Author

Topic

anajjar
Starting Member

4 Posts

Posted - 2013-12-04 : 20:36:07

Hello,

I just want someone to look over my database. I need make sure I have everything correct in order to start inputting data into the data base. Keep in mind instructor instructions might be in correct e.g. you cant have two PK in one table, maybe I am wrong. Thanks so much.

Question:
1. Patient (P-Cardno, P-name, P-address, Date-of-Registration, Doc-id, …) < Enter 5 records> P-Cardo make a number for tha
2. Doctor ( Doc-id, d-name, RoomNo, Ext-no,..)<Enter 5 records> generate docid
3. Appointment (Appt-Num, Appt-date, Appt-Time, P-Cardno, Doc-id…)< Enter 10 records> generate appt-num
4. Treatment (TreatmentNo, P-CardNo, T-Date, Doc-id …) < Enter 10 records>
5. Treatment details (TreatmentNo, lineNo, TreatmentDate, Diagnosis, Medication-Note…)< Enter 15 records>
6. Room ( RoomNo, Type) < Enter 5 records>

Please note that the PKs are underlined and highlighted but the FK’s need to be identified. Assume the cardinalities: 1: M, 1:1 where relevant (make your best decision on field types and sizes)

Answer:

create database HealthClinic

use Healthclinic;
create table Room
(Roomno int not null primary key,
RoomType char(1) not null);

create table Doctor
(Docid int identity (200,10) primary key,
Dname char(50) not null,
Roomno int not null references Room(Roomno));

create table Patient
(Pcardno int identity (200,10) primary key,
Pname char(50) not null,
Paddress char(50) null,
Dateofreg smalldatetime not null,
Docid int not null references Doctor(Docid));

create table Appointment
(Apptnum int identity (200,10) primary key,
Apptdate smalldatetime not null,
Appttime time(3) not null,
Pcardno int not null references Patient(Pcardno),
Docid int not null references Doctor(DOcid));

create table Treatment
(Treatmentno int identity (200,10) primary key,
Tdate smalldatetime not null,
Pcardno int not null references Patient(Pcardno),
Docid int not null references Doctor(Docid));

create table TreatmentDetails
(Linenumber int identity (200,10) primary key,
Treatmentno int not null references Treatment(Treatmentno),
Tdate smalldatetime not null references Treatment(Tdate),
Diagnosis char(50) not null,
Medicationdate smalldatetime not null,

I am unable to create the treatmentdetails table for some reason, and not 100% sure on the "identity (200,10) for PK.

THANKS AGAIN!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-05 : 01:45:10

TreatmentDetails should have deatils of Doctor who treated, patient who got treated atleast. So you need to add DoctorNo and patientNo as foreignkey columns in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-12-05 : 05:26:16

identity (200,10) will start at 200 and then increment by 10. There isn't an easy way to fill in the gaps, so better to have an Increment of 1. Only question then is if you start at 1? or some higher number. We start at 10,000 or sometimes 100,000 or 1,000,000 so that the order of magnitude does not change/often.

Also, we start each table at a different number, so when we have only a few test rows the if we accidentally JOIN two tables wrongly then we will get zero rows. If all tables IDENTITY start at 1 then you can successfully join anything to anything, by accident, even if it doesn't make sense!

So I suggest you use IDENTITY(1, 1) or IDENTITY (1000, 1) [or even bigger if you like]

Kristen
Test

22859 Posts

Posted - 2013-12-05 : 05:41:45

quote:
Originally posted by anajjar

I am unable to create the treatmentdetails table for some reason

Syntax is wrong. The statement (you posted) stops half way through:


create table TreatmentDetails
(Linenumber int identity (200,10) primary key,
Treatmentno int not null references Treatment(Treatmentno),
Tdate smalldatetime not null references Treatment(Tdate),
Diagnosis char(50) not null,
Medicationdate smalldatetime not null,
... more column definitions perhaps? ..
);

anajjar
Starting Member

4 Posts

Posted - 2013-12-05 : 20:55:07

not sure why I cant enter records this is what I am doing:

insert into room(roomno)
values (10, 20, 30, 40, 50);

Thanks

Kristen
Test

22859 Posts

Posted - 2013-12-06 : 03:16:56

If you want to insert multiple rows each row needs to be in parenthesis; with each pair of parenthesis list values for all the columns included in the insert statement


insert into room(roomno)
values (10), (20), (30), (40), (50);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources