create a data base please just need the tables to run at mariadb Medical practice database data dictionary Table 2 Medical practice database data dictionary Table name Attribute name Contents Data type Format Range Reqd Key type FK referenced table Patient Patient ID Patient's unique system generated identifier INTEGER 99999 10000 99999 Y PK Title Patient's title, e g Mr, Ms, Mrs, Dr, etc NVARCHAR(20) Xxxxxx FirstName Patient's first name NVARCHAR(50) Xxxxxx Y MiddleInitial Patient's middle initial of their name NCHAR(1) X LastName Patient's last name NVARCHAR(50) Xxxxxx Y HouseUnitLotNum The number of the Patient's residence, unit or lot NVARCHAR(5) XXXXX Y Street Name of street, road, etc where the patient resides NVARCHAR(50) Xxxxxx Y Suburb Name of the suburb where the patient resides NVARCHAR(50) Xxxxxx Y State Name of the state where the patient resides NVARCHAR(3) XXX Y PostCode Post Code of Patient's residential address NCHAR(4) XXXX Y HomePhone Patient's home phone number NCHAR(10) XXXXXXXXXX MobilePhone Patient's mobile phone number NCHAR(10) XXXXXXXXXX MedicareNumber Patient's Medicare number NCHAR(16) XXXXXXXXXXXXXXXX DateOfBirth Patient's date of birth DATE dd mm yyyy Y Gender Patient's gender NCHAR(20) male, female, unspecified, indeterminate, intersex Y Practitioner Practitioner ID Practitioner's unique system generated identifier INTEGER 99999 10000 99999 Y PK MedicalRegistrationNumber Practitioner's Medical Registration Number NCHAR(11) XXXXXXXXXXXXXXXX Y AK Title Practitioner's title, e g Mr, Ms, Mrs, Dr, etc NVARCHAR(20) Xxxxxx FirstName Practitioner's first name NVARCHAR(50) Xxxxxx Y MiddleInitial Practitioner's middle initial of their name NCHAR(1) X LastName Practitioner's last name NVARCHAR(50) Xxxxxx Y HouseUnitLotNum The number of Practitioner's residence, unit or lot NVARCHAR(5) XXXXX Y Street Name of street, road, etc where the patient resides NVARCHAR(50) Xxxxxx Y Suburb Name of the suburb where the patient resides NVARCHAR(50) Xxxxxx Y State Name of the state where the patient resides NVARCHAR(3) XXX Y PostCode Post Code of Practitioner's residential address NCHAR(4) XXXX Y HomePhone Practitioner's home phone number NCHAR(8) XXXXXXXXXX MobilePhone Practitioner's mobile phone number NCHAR(8) XXXXXXXXXX MedicareNumber Practitioner's Medicare number NCHAR(16) XXXXXXXXXXXXXXXX DateOfBirth Practitioner's date of birth DATE dd mm yyyy Y Gender Practitioner's gender NCHAR(20) male, female, unspecified, indeterminate, intersex Y PractitionerType Ref The type of Practitioner NVARCHAR(50) XXXXXXXXXXXXXXXX Y FK PractitionerType Availability WeekDayName Ref The name of the day of the week NVARCHAR(9) Mon to Fri Y PK, FK WeekDays Practitioner Ref The ID of the Practitioner INTEGER 99999 10000 99999 Y PK, FK Practitioner WeekDays WeekDayName The name of the day of the week NVARCHAR(9) Xxxxxxxxxx Mon to Fri Y PK PractitionerType PractitionerType The type of Practitioner NVARCHAR(50) Xxxxxxxxxx Y PK Appointment Practitioner Ref The ID of the Practitioner INTEGER 99999 10000 99999 Y PK, FK Practitioner AppDate The date of the appointment DATE dd mm yyyy Y PK, AK AppStartTime The time of the appointment TIME 00 00 AM PM Y PK, AK Patient Ref The ID of the Patient NVARCHAR(20) 99999 10000 99999 Y FK, AK Patient Develop a script using Data Definition Language (DDL) elements of Structured Query Language (SQL) to cr ate a database with the name MedicalPractice Save your script file as yourFirstname yourSurname Cr ateMedicalPracticeDatabase sql Using DDL elements of SQL, write the script to cr ate each of the tables described in the medical practice data model, appending to the end of the script file Your script must precisely match the details specified in the data model and must include the cr ation of the tables, using the table names specified by the data model columns, using the column names specified by the data model column data types using the data types and sizes specified by the data model primary key constraints specified by the data model foreign key constraints required to implement the relationships specified by the data model unique index constraints required to implement the alternate keys specified by the data model Note the following alternate keys exist in the data model Table 4 Alternate Key Table Alternate Key Practitioner MedicalRegistrationNumber Appointment Patient Ref, AppDate, AppStartTime Execute your script on the Database Server Check and ensure that your script executes without errors and that it cr ates the database, tables and constraints as defined by the Medical Practice Data Model provided in the table If the database has been cr ated successfully, you can now proceed to the next task and populate the database with data Otherwise, correct any errors in your script, before you proceed

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Feb 17, 2024

create a data base please. just need the tables to run at mariadb... Medical practice database data dictionary Table 2 Medical practice database

create a data base please.

just need the tables to run at mariadb...

Medical practice database data dictionary

Table 2 Medical practice database data dictionary

Table name	Attribute name	Contents	Data type	Format	Range	Reqd	Key type	FK referenced table
Patient	Patient_ID	Patient's unique system-generated identifier.	INTEGER	99999	10000-99999	Y	PK
	Title	Patient's title, e.g. Mr, Ms, Mrs, Dr, etc.	NVARCHAR(20)	Xxxxxx
	FirstName	Patient's first name.	NVARCHAR(50)	Xxxxxx		Y
	MiddleInitial	Patient's middle initial of their name.	NCHAR(1)	X
	LastName	Patient's last name.	NVARCHAR(50)	Xxxxxx		Y
	HouseUnitLotNum	The number of the Patient's residence, unit or lot.	NVARCHAR(5)	XXXXX		Y
	Street	Name of street, road, etc. where the patient resides.	NVARCHAR(50)	Xxxxxx		Y
	Suburb	Name of the suburb where the patient resides.	NVARCHAR(50)	Xxxxxx		Y
	State	Name of the state where the patient resides.	NVARCHAR(3)	XXX		Y
	PostCode	Post-Code of Patient's residential address.	NCHAR(4)	XXXX		Y
	HomePhone	Patient's home phone number.	NCHAR(10)	XXXXXXXXXX
	MobilePhone	Patient's mobile phone number.	NCHAR(10)	XXXXXXXXXX
	MedicareNumber	Patient's Medicare number.	NCHAR(16)	XXXXXXXXXXXXXXXX
	DateOfBirth	Patient's date of birth.	DATE	dd/mm/yyyy		Y
	Gender	Patient's gender.	NCHAR(20)	male, female, unspecified, indeterminate, intersex		Y
Practitioner	Practitioner_ID	Practitioner's unique system-generated identifier.	INTEGER	99999	10000-99999	Y	PK
	MedicalRegistrationNumber	Practitioner's Medical Registration Number.	NCHAR(11)	XXXXXXXXXXXXXXXX		Y	AK
	Title	Practitioner's title, e.g. Mr, Ms, Mrs, Dr, etc.	NVARCHAR(20)	Xxxxxx
	FirstName	Practitioner's first name.	NVARCHAR(50)	Xxxxxx		Y
	MiddleInitial	Practitioner's middle initial of their name.	NCHAR(1)	X
	LastName	Practitioner's last name.	NVARCHAR(50)	Xxxxxx		Y
	HouseUnitLotNum	The number of Practitioner's residence, unit or lot.	NVARCHAR(5)	XXXXX		Y
	Street	Name of street, road, etc. where the patient resides.	NVARCHAR(50)	Xxxxxx		Y
	Suburb	Name of the suburb where the patient resides.	NVARCHAR(50)	Xxxxxx		Y
	State	Name of the state where the patient resides.	NVARCHAR(3)	XXX		Y
	PostCode	Post-Code of Practitioner's residential address.	NCHAR(4)	XXXX		Y
	HomePhone	Practitioner's home phone number.	NCHAR(8)	XXXXXXXXXX
	MobilePhone	Practitioner's mobile phone number.	NCHAR(8)	XXXXXXXXXX
	MedicareNumber	Practitioner's Medicare number.	NCHAR(16)	XXXXXXXXXXXXXXXX
	DateOfBirth	Practitioner's date of birth.	DATE	dd/mm/yyyy		Y
	Gender	Practitioner's gender.	NCHAR(20)	male, female, unspecified, indeterminate, intersex		Y
	PractitionerType_Ref	The type of Practitioner.	NVARCHAR(50)	XXXXXXXXXXXXXXXX		Y	FK	PractitionerType
Availability	WeekDayName_Ref	The name of the day of the week.	NVARCHAR(9)		Mon-to-Fri	Y	PK, FK	WeekDays
	Practitioner_Ref	The ID of the Practitioner.	INTEGER	99999	10000-99999	Y	PK, FK	Practitioner
WeekDays	WeekDayName	The name of the day of the week.	NVARCHAR(9)	Xxxxxxxxxx	Mon-to-Fri	Y	PK
PractitionerType	PractitionerType	The type of Practitioner.	NVARCHAR(50)	Xxxxxxxxxx		Y	PK
Appointment	Practitioner_Ref	The ID of the Practitioner.	INTEGER	99999	10000-99999	Y	PK, FK	Practitioner
	AppDate	The date of the appointment.	DATE	dd/mm/yyyy		Y	PK, AK
	AppStartTime	The time of the appointment.	TIME	00:00 AM/PM		Y	PK, AK
	Patient_Ref	The ID of the Patient.	NVARCHAR(20)	99999	10000-99999	Y	FK, AK	Patient

Develop a script using Data Definition Language (DDL) elements of Structured Query Language (SQL) to cr&ate a database with the name MedicalPractice. Save your script file as yourFirstname_yourSurname_Cr&ateMedicalPracticeDatabase.sql.
Using DDL elements of SQL, write the script to cr&ate each of the tables described in the medical practice data model, appending to the end of the script file. Your script must precisely match the details specified in the data model and must include the cr&ation of the:
1. tables, using the table names specified by the data model
2. columns, using the column names specified by the data model
3. column data types using the data types and sizes specified by the data model
4. primary key constraints specified by the data model
5. foreign key constraints required to implement the relationships specified by the data model
6. unique index constraints required to implement the alternate keys specified by the data model. Note the following alternate keys exist in the data model:

Table 4 Alternate Key

Table	Alternate Key
Practitioner	MedicalRegistrationNumber
Appointment	Patient_Ref, AppDate, AppStartTime

Execute your script on the Database Server. Check and ensure that your script executes without errors and that it cr&ates the database, tables and constraints as defined by the Medical Practice Data Model provided in the table. If the database has been cr&ated successfully, you can now proceed to the next task and populate the database with data. Otherwise, correct any errors in your script, before you proceed.