fork download
copy 
  1. create database HospitalSystem;
  2. create table person(
  3. National_ID int PRIMARY key,
  4. FirstName varchar(50) check(FirstName NOT LIKE '%[^A-Za-z]%') not null,
  5. LastName  varchar(50) check(LastName  NOT LIKE '%[^A-Za-z]%') not null,
  6. BirthDate date CHECK(BirthDate<= CAST(SYSDATETIME() AS DATE)) not null,
  7. City varchar(50) not null,
  8. street varchar(50) not null,
  9. gender char check(gender in ('M','F')),
  10. state varchar(50) not null
  11. )
  12. create table Contact(
  13. National_ID int ,
  14. Contact varchar(15) check(Contact NOT LIKE '%[^0-9]%'),
  15.  PRIMARY KEY (National_ID, Contact),
  16.  FOREIGN KEY (National_ID) REFERENCES person(National_ID)
  17. )
  18. create table Email(
  19. National_ID int,
  20. email varchar(50) check(email  LIKE '%_@_%.__%')
  21. PRIMARY KEY (National_ID,email)
  22. FOREIGN KEY (National_ID) REFERENCES person(National_ID)
  23. )
  24. create table Employee(
  25. National_ID int ,
  26. Employee_ID int PRIMARY key IDENTITY,
  27. Salary money CHECK(Salary>=0) not null,
  28. Hire_Date date CHECK(Hire_Date<= CAST(SYSDATETIME() AS DATE)) not null,
  29. Resign_Date date CHECK(Resign_Date<= CAST(SYSDATETIME() AS DATE)) null,
  30. FOREIGN KEY (National_ID) REFERENCES person(National_ID)
  31. )
  32. create table Nurse(
  33. Nurse_ID int PRIMARY key IDENTITY,
  34. Employee_ID int,
  35. shift varchar(50),
  36. FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
  37. )
  38. create table Department(
  39. Department_Name varchar(50) PRIMARY key,
  40. Department_Floor int CHECK(Department_Floor>=0) not null,
  41. Department_Building int CHECK(Department_Building>=0) not null
  42. )
  43. create table Nurse_Department(
  44. Nurse_ID int,
  45. Department_Name varchar(50),
  46. PRIMARY KEY (Nurse_ID,Department_Name),
  47. FOREIGN KEY (Nurse_ID) REFERENCES Nurse(Nurse_ID),
  48. FOREIGN KEY (Department_Name) REFERENCES Department(Department_Name)
  49. )
  50. create table Doctor(
  51. Doctor_ID int PRIMARY KEY IDENTITY,
  52. Department_Name varchar(50),
  53. Employee_ID int,
  54. Year_Of_Experience int CHECK(Year_Of_Experience>=0) DEFAULT 0,
  55. FOREIGN KEY (Department_Name) REFERENCES Department(Department_Name),
  56. FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
  57. )
  58. create table Doctor_Qualification(
  59. Doctor_ID int,
  60. Qualification varchar(50),
  61. PRIMARY KEY(Doctor_ID,Qualification),
  62. FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
  63. )
  64. create table NonScientific_Employee(
  65. NSE_ID INT PRIMARY KEY IDENTITY,
  66. NSE_ROLE varchar(50),
  67. Department_Name varchar(50),
  68. Employee_ID int,
  69. FOREIGN KEY (Department_Name) REFERENCES Department(Department_Name),
  70. FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
  71. )
  72. create table Device(
  73. Device_ID int PRIMARY KEY,
  74. Device_Name varchar(50) not null,
  75. Device_Type varchar(50) not null
  76. )
  77. create table Room(
  78. Room_ID int PRIMARY KEY,
  79. Room_Type varchar(50) not null,
  80. Room_Capacity int CHECK(Room_Capacity>=0) not null, 
  81. Department_Name varchar(50),
  82. Device_ID int,
  83. FOREIGN KEY (Department_Name) REFERENCES Department(Department_Name),
  84. FOREIGN KEY (Device_ID) REFERENCES Device(Device_ID),
  85. )
  86. create table Nurse_AssignedRoom(
  87. Room_ID int,
  88. Nurse_ID int
  89. PRIMARY KEY(Room_ID,Nurse_ID),
  90. FOREIGN KEY (Room_ID) REFERENCES Room(Room_ID),
  91. FOREIGN KEY (Nurse_ID) REFERENCES Nurse(Nurse_ID),
  92. )
  93. create table Patient(
  94. Patient_ID int PRIMARY KEY IDENTITY,
  95. Residency_Start_Date date CHECK(Residency_Start_Date<= CAST(SYSDATETIME() AS DATE)) not null,
  96. Residency_End_Date date CHECK(Residency_End_Date<= CAST(SYSDATETIME() AS DATE)) null,
  97. Room_ID int,
  98. National_ID int,
  99. FOREIGN KEY (Room_ID) REFERENCES Room(Room_ID),
  100. FOREIGN KEY (National_ID) REFERENCES person(National_ID)
  101. )
  102. create table Prescription(
  103. Prescription_ID int PRIMARY KEY IDENTITY,
  104. Prescription_Date date CHECK(Prescription_Date<= CAST(SYSDATETIME() AS DATE)) not null,
  105. Instructions varchar(50),
  106. Diagnosis_Code varchar(50) not null,
  107. Patient_ID int 
  108. FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
  109. )
  110. create table Medicine(
  111. Medicine_ID int PRIMARY KEY,
  112. Medicine_Name varchar(50),
  113. Expiry_Date date CHECK(Expiry_Date<= CAST(SYSDATETIME() AS DATE))
  114. )
  115. create table Prescription_Medicine(
  116. Prescription_ID int,
  117. Medicine_ID int,
  118. Quantity int CHECK(Quantity> 0) not null,
  119. Dosage varchar(50) check(Dosage LIKE '%[0-9]%' -- Ensure it has digits
  120.   AND (Dosage LIKE '%mg' OR Dosage LIKE '%ml' OR Dosage LIKE '%g' OR Dosage LIKE '%L')) not null,
  121. Frequency int  CHECK(Frequency> 0) not null,
  122. PRIMARY KEY(Prescription_ID,Medicine_ID),
  123. FOREIGN KEY (Prescription_ID) REFERENCES Prescription(Prescription_ID),
  124. FOREIGN KEY (Medicine_ID) REFERENCES Medicine(Medicine_ID)
  125. )
  126. create table Appointment(
  127. Appointment_ID int PRIMARY KEY IDENTITY,
  128. Appointment_Status varchar(50) 
  129. check(Appointment_Status in('Pending','Confirme','Complete','Canceled','Reschedule','No Show')),
  130. Room_ID int,
  131. Appointment_Date datetime2 CHECK(Appointment_Date<=SYSDATETIME()) not null,
  132. Patient_ID int,
  133. Doctor_ID int ,
  134. FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
  135. FOREIGN KEY (Room_ID) REFERENCES Room(Room_ID),
  136. FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
  137. )
  138. create table Bill(
  139. Appointment_ID int PRIMARY KEY,
  140. amount money CHECK(amount>=0) not null,
  141. Tax money CHECK(Tax>=0)  not null ,
  142. Discount decimal(4,2) CHECK (Discount BETWEEN 0 AND 100) default(0)
  143. FOREIGN KEY (Appointment_ID) REFERENCES Appointment(Appointment_ID)
  144. )
Success #stdin #stdout #stderr 0.01s 5272KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 1: near "database": syntax error
Error: near line 2: near "create": syntax error
https://ideone.com/h7A3pF
language:
SQLite (sqlite 3.27.2)
created:
1 day ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!