SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 HEALTH CLINIC DATABASE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anajjar
Starting Member

Canada
4 Posts

Posted - 12/04/2013 :  20:36:07  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/05/2013 :  01:45:10  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/05/2013 :  05:26:16  Show Profile  Reply with Quote
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]
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/05/2013 :  05:41:45  Show Profile  Reply with Quote
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? ..
);
Go to Top of Page

anajjar
Starting Member

Canada
4 Posts

Posted - 12/05/2013 :  20:55:07  Show Profile  Reply with Quote
not sure why I cant enter records this is what I am doing:

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

Thanks
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/06/2013 :  03:16:56  Show Profile  Reply with Quote
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);
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000