my assignment

{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import sqlite3"
]
},
{
"cell_type": "code",
"execution_count": 2,
"outputs": [],
"source": [
"connection = sqlite3.connect(\"hospital.db\")\n",
"crsr =connection.cursor()"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 3,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Physician (\n",
" EmployeeID INTEGER PRIMARY KEY NOT NULL,\n",
" Name TEXT NOT NULL,\n",
" Position TEXT NOT NULL,\n",
" SSN INTEGER NOT NULL\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 4,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Department (\n",
" DepartmentID INTEGER PRIMARY KEY NOT NULL,\n",
" Name TEXT NOT NULL,\n",
" Head INTEGER NOT NULL\n",
" CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID)\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 5,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Affiliated_With (\n",
" Physician INTEGER NOT NULL\n",
" CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),\n",
" Department INTEGER NOT NULL\n",
" CONSTRAINT fk_Department_DepartmentID REFERENCES Department(DepartmentID),\n",
" PrimaryAffiliation BOOLEAN NOT NULL,\n",
" PRIMARY KEY(Physician, Department)\n",
" );\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 6,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Procedure (\n",
" Code INTEGER PRIMARY KEY NOT NULL,\n",
" Name TEXT NOT NULL,\n",
" Cost REAL NOT NULL\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 48,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 48,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Trained_In (\n",
" Physician INTEGER NOT NULL\n",
" CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),\n",
" Treatment INTEGER NOT NULL\n",
" CONSTRAINT fk_Procedure_Code REFERENCES Procedure(Code),\n",
" CertificationDate DATETIME NOT NULL,\n",
" CertificationExpires DATETIME NOT NULL,\n",
" PRIMARY KEY(Physician, Treatment)\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 8,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Patient (\n",
" SSN INTEGER PRIMARY KEY NOT NULL,\n",
" Name TEXT NOT NULL,\n",
" Address TEXT NOT NULL,\n",
" Phone TEXT NOT NULL,\n",
" InsuranceID INTEGER NOT NULL,\n",
" PCP INTEGER NOT NULL\n",
" CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID)\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 9,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Nurse (\n",
" EmployeeID INTEGER PRIMARY KEY NOT NULL,\n",
" Name TEXT NOT NULL,\n",
" Position TEXT NOT NULL,\n",
" Registered BOOLEAN NOT NULL,\n",
" SSN INTEGER NOT NULL\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 32,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Appointment (\n",
" AppointmentID INTEGER PRIMARY KEY NOT NULL,\n",
" Patient INTEGER NOT NULL\n",
" CONSTRAINT fk_Patient_SSN REFERENCES Patient(SSN),\n",
" PrepNurse INTEGER\n",
" CONSTRAINT fk_Nurse_EmployeeID REFERENCES Nurse(EmployeeID),\n",
" Physician INTEGER NOT NULL\n",
" CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),\n",
" Start DATETIME NOT NULL,\n",
" End DATETIME NOT NULL,\n",
" ExaminationRoom TEXT NOT NULL\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 11,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Medication (\n",
" Code INTEGER PRIMARY KEY NOT NULL,\n",
" Name TEXT NOT NULL,\n",
" Brand TEXT NOT NULL,\n",
" Description TEXT NOT NULL\n",
");\n",
"\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 12,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Prescribes (\n",
" Physician INTEGER NOT NULL\n",
" CONSTRAINT fk_Physician_EmployeeID REFERENCES Physician(EmployeeID),\n",
" Patient INTEGER NOT NULL\n",
" CONSTRAINT fk_Patient_SSN REFERENCES Patient(SSN),\n",
" Medication INTEGER NOT NULL\n",
" CONSTRAINT fk_Medication_Code REFERENCES Medication(Code),\n",
" Date DATETIME NOT NULL,\n",
" Appointment INTEGER\n",
" CONSTRAINT fk_Appointment_AppointmentID REFERENCES Appointment(AppointmentID),\n",
" Dose TEXT NOT NULL,\n",
" PRIMARY KEY(Physician, Patient, Medication, Date)\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 13,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Block (\n",
" Floor INTEGER NOT NULL,\n",
" Code INTEGER NOT NULL,\n",
" PRIMARY KEY(Floor, Code)\n",
");\"\"\"\n",
"\n",
"crsr.execute(command)"
],
"metadata": {
"collapsed": false,
"pycharm": {
"name": "#%%\n"
}
}
},
{
"cell_type": "code",
"execution_count": 14,
"outputs": [
{
"data": {
"text/plain": ""
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"command = \"\"\"CREATE TABLE Room (\n",
" Number INTEGER PRIMARY KEY NOT NULL,\n",
" Type TEXT NOT NULL,\n",
" BlockFloor INTEGER NOT NULL,\n",
" BlockCode INTEGER NOT NULL,\n",
" Unavailable BOOLEAN NOT NULL,\n",
" FOREIGN KEY(BlockFloor, BlockCode) REFERENCES Block\n",
");\"\"\"\n",
"\n",
...