ICT330 Tutor-Marked Task January 2026 Presentation This task is value 18% of the final designate for ICT330, Database Administration Systems. The gash- off date for this task is Monday, 02 March 2026, 2355 hours. Stamp to College students: You are to encompass the next particulars in your submission: Direction Code, Title of the TMA, SUSS PI No., Your Name, and Submission Date. Acknowledge All Questions. (Total 100 marks) Please insist that Questions 1, 2 and 3 are unrelated though they bear got the identical domain. Every build a matter to may perchance perchance perchance furthermore be worked on independently of the opposite questions. Interrogate of 1 (33 marks) It is envisioned that each and every citizens can use a portal to originate and/or abolish appointments for public well being practitioner, categorised as general practitioners, specialists, physiotherapists and lots others. The portal will allow citizens to insist scientific prerequisites that they are attempting to peep a well being practitioner for. Electorate first choose from a customary list of scientific prerequisites that easiest match their prerequisites. The customary list entails scientific prerequisites, each and every scientific situation has a varied code, a title and a description. After deciding on the linked scientific prerequisites from the customary list, citizens enter an extensive description for every and every of these scientific prerequisites selected. Every detailed description is timestamped. Subsequent, citizens originate an appointment build a matter to. They choose these scientific prerequisites they bear got described earlier or for outdated requests, that they are attempting to explore session for. A build a matter to identification is generated with a build a matter up to now and time. If the described prerequisites for the appointment build a matter to are newly entered, the machine selects a general practitioner per the earliest availability. It is a ways a coverage that appointment for newly described scientific prerequisites are first viewed by a general practitioner who will practice judgement to determine whether referrals for specialised consultations are required. Stamp that the machine recordsdata also a self-discipline of distinctiveness, e.g., sports activities physiotherapy or inner medication, for every and every scientific situation within the customary list the build acceptable. In the same blueprint, a self-discipline of distinctiveness may perchance perchance perchance furthermore be recorded for every and every well being practitioner if acceptable. If the scientific prerequisites selected for the sizzling build a matter to are all of us which had been previously viewed by a well being practitioner, then a well being practitioner from the identical distinctiveness is selected. As an instance, if the citizen saw a sports activities physiotherapist previously for these prerequisites, then a sports activities physiotherapist (as a exchange of a general practitioner) is selected for the unique appointment build a matter to, all but again per the earliest availability. It is a ways a necessity to file which slots for every and every well being practitioner had been taken to allow the machine to determine earliest obtainable lawful well being practitioner. A self-discipline of distinctiveness has a varied distinctiveness code, a title and a description. A session by any well being practitioner can lead to referral to other specialists or therapists or can lead to a discover up session. For each and every referral or discover- up session, the machine generates unique appointment build a matter to. On account of this truth, an appointment build a matter to will be initiated by a citizen or through a referral or for a discover- up session for the citizen. For appointment build a matter to for a discover up session, the identical class of well being practitioner will be assigned to the unique build a matter to. No longer all consultations provoke unique appointment requests if it’s assessed that is doable that a citizen’s scientific situation is resolvable with the sizzling session. Moreover, now not all appointment requests atomize with a session as some citizens may perchance perchance perchance pass over their appointment. Presently, a session is both a scientific session or a therapy session, e.g., physiotherapy. The well being practitioner going through the session will file one or extra session notes for every and every scientific situation the session is for. For a therapy session, the therapist recordsdata the duration of the session and the treatments offered e.g., manipulation, strengthening exercise. The therapist chooses from a customary list of likely therapy treatments. Every medication has a varied code, a title and a description. The therapist then recordsdata cramped print of the medication conducted e.g., neck rotation and extension for strengthening exercise. For a scientific session, the well being practitioner may perchance perchance perchance build a matter to for investigations to be implemented, e.g., blood test or ultrasound scan. The well being practitioner chooses also from a customary list of likely investigations. Every investigation has a varied code, a title and a description. The results of the investigation must peaceable be recorded within the machine as soon as obtainable. A scientific session may perchance perchance perchance furthermore result in a prescription for medication. The well being practitioner chooses from a customary list of medication. Every drug has a varied code, a title and one or extra contraindications e.g., pregnancy. The well being practitioner has to substantiate that that citizens must now not allergic to prescribed medication, moreover to those medication chosen raise out now not have interaction negatively with each and every other. The well being practitioner also signifies the dosage, duration and instruction for every and every prescribed drug within the machine. With out adding any attribute/identifier now not described within the build a matter to, form a conceptual entity relationship (ER) mannequin from the statements of requirements to insist the guidelines mannequin, exhibiting the guidelines requirements in a single design. entities, with entities title, linked attributes and identifier, (16 marks) relationships with most and minimal cardinalities and relationship title (17 marks) Express your assumption(s) for most productive recordsdata requirements which must now not specified. Indicate your mannequin as ONE design. Interrogate of 2 (32 marks) You are given the desk: Prescription(prescriptionId, deliveryDatetime, patientId, title, address, drugCode, drugName, dosage, instruction, contraindication) This desk is susceptible by a pharmacy to issue prescribed medication if a affected person build a matter to for dwelling transport. A prescription is known by a prescription identification. Every prescription contains one or extra medication, each and every with its required dosage. A drug is known by a drug code, has a drug title and springs with one or extra instructions e.g., after food and no alcohol, and one or extra contraindications e.g., pregnant females and diabetic persons. A affected person is known by affected person identification. Every prescription is assigned a transport date and time and a affected person to issue to. Interrogate of 2a Formulate and list the functional and multi- valued dependencies (MVD). Form now not introduce any unique columns now not within the given desk. Express your assumption(s) for most productive recordsdata requirements which must now not specified. (7 marks) Interrogate of 2b Arrangement dependency graph and propose candidate key(s) for the desk. Indicate composite keys in brackets. (4 marks) Interrogate of 2c (i) Indicate whether the MVDs in your resolution to Q2(a) are subsumed or now not subsumed. (ii) Prepare the BCNF design to normalise the desk to Boyce Codd Long-established Carry out (BCNF) and to Fourth Long-established Carry out (4NF). Indicate the vogue you practice the steps for all FDs and unsubsumed MVDs. For each and every pair of two tables resulting from an iteration, whisper whether each and every of them is in BCNF or in 4NF or in any other case and insist why. (10 marks) Interrogate of 2d Assemble your BCNF and 4NF tables and constraints clearly by giving each and every desk and the referential integrity constraint(s), if any, within the varieties described beneath: Tables on this include: TABLE- NAME(col- name1, col- name2, …, col- title n) inform (PK) in opposition to the first key and inform (FK) in opposition to the foreign keys, if any. Referential integrity constraint(s), if any, along with the runt one desk on this include: TABLE- NAMEx.col- namex1 …col- namexn must exist in TABLE- NAMEy.col- namey1…col- nameyn Stamp that there is now not any must demonstrate the derivation steps or insist them for this build a matter to phase. Merely present the final tables, the first keys, the foreign keys and the foreign key constraints. (11 marks) Interrogate of three (35 marks) All SQL statements for this build a matter to (assign, insert and choose statements) MUST be in textual tell material structure. You MUST also paste the screenshots of the of executing all of your SQL statements. Interrogate of 3a (i) Change into the ERD in Figure Q3(a)(i) into a logical mannequin. Figure Q3(a)(i) Indicate the tables within the logical mannequin within the include: TABLE- NAME(col- name1, col- name2, …, col- title n) inform (PK) in opposition to the first key, inform (FK) in opposition to the foreign keys, if any, including integrity constraints within the include: TABLE- NAMEx.col- namex1 …col- namexn must exist in TABLE- NAMEy.col- namey1…col- nameyn (ii) Talk over with the description of the columns for the column definition, and then code the use of SQL statements to enforce all tables derived from the ERD in Figure Q3(a)(i). All columns are required moreover the column comments. Checklist your SQL assign statements within the inform that they must peaceable be accomplished. 1CT330 patientId An integer, auto generated with starting mark 1 and increment 1. title (of affected person) At most 60 characters in length dob Date and value mustn’t ever be later than the sizzling datetime address At most 100 characters in length consultDatetime Datetime anguish At most 1024 characters in length prognosis At most 512 characters. mark A decimal number with 5 digits of which 2 digits are after the decimal point. Price is between $80 and $320, with default mark $120. doctorId Exactly 5 characters in length, starts and ends with a letter with digits in between. title (of specialist) At most 60 characters in length distinctiveness Exactly 20 characters in length, with a mark both paediatrics, dermatology, rheumatology, urology, gastroenterology, geriatric, cardiology, respiratory, renal. reviewDatetime Datetime with default recent date and time. Stamp that 1. (patientId, reviewDatetime) is an alternative key. 2. reviewDatetime must peaceable be a minimal of three hours after consultDatetime comments At most 1024 characters in length ranking A host from 1 to five Constraints for the relationships in Figure Q3(a)(i): has delete and exchange are disallowed attendsTo delete and exchange are disallowed isGiven delete and exchange cascaded (iii) Populate the tables you created in Interrogate of three(a)(ii). You may perchance well perchance maybe perchance like to title the linked recordsdata rows from the stare shown in Table Q3(a)(ii) to populate the tables created for Interrogate of three(a)(ii). Checklist your SQL insert statements within the inform that they must peaceable be accomplished. Figure Q3(a)(ii) Enlarged model of the guidelines rows is shown in 3 vertical desk cells, in Table doctorid title distinctiveness patientId title dateOfBirth address consultDateTime 2 C001S Samuel cardiology 1 Patrick 16/1/1955 12 Bedok Avenue 2/2/2026 9:30 3 C001S Samuel cardiology 2 Peter 15/2/1990 15 Clementi Avenue 2/2/2026 15:30 4 C001S Samuel cardiology 3 Philemon 18/3/1960 18 Dunman Avenue 4/2/2026 14:30 5 C012S Sarah cardiology NULL NULL NULL NULL NULL 6 D002S Sebastian dermatology 5 Paul 28/5/2010 28 Grange Avenue 2/2/2026 9:00 7 D002S Sebastian dermatology 5 Paul 28/5/2010 28 Grange Avenue 17/2/2026 11:30 8 G004S Simon gastroenterology NULL NULL NULL NULL NULL 9 G005S Sophie geriatric 1 Patrick 16/1/1955 12 Bedok Avenue 15/1/2026 11:30 10 G005S Sophie geriatric 1 Patrick 16/1/1955 12 Bedok Avenue 16/1/2026 11:30 11 G005S Sophie geriatric 3 Philemon 18/3/1960 18 Dunman Avenue 2/2/2026 10:30 12 P003S Silas paediatrics 4 Phoebe 22/4/2015 22 Frankel Avenue 4/2/2026 10:30 13 P009S Samantha paediatrics 4 Phoebe 22/4/2015 22 Frankel Avenue 9/2/2026 10:30 14 P010S Steven paediatrics NULL NULL NULL NULL NULL 15 R006S Spencer renal NULL NULL NULL NULL NULL 16 R007S Stella respiratory 3 Philemon 18/3/1960 18 Dunman Avenue 21/1/2026 14:30 17 R008S Susan rheumatology 5 Paul 28/5/2010 28 Grange Avenue 10/2/2026 10:30 18 R011S Sabrina respiratory NULL NULL NULL NULL NULL anguish prognosis mark reviewDateTime 2 Irregular coronary heart rhythm that is now not customary arrhythmia 120 2/2/2026 12:52 3 Breathlessness mountain climbing stairs, intermittent chest pains septal defects 240 NULL 4 behind and skipped heartbeat with chest pains arrhythmia 120 NULL 5 NULL NULL NULL NULL 6 dry and itching scalp psoriasis flare-up 120 NULL 7 scaly entrance of neck psoriasis flare-up 120 NULL 8 NULL NULL NULL NULL 9 Insomnia for the previous 3 weeks, feeling a strategy of loss terror 80 15/1/2026 17:30 10 Irritation and pains within the finger joints arthritis 80 29/1/2026 14:33 11 Irritation and pains within the knee joints arthritis 80 2/2/2026 13:35 12 flu with loud wheezing asthma attack 120 4/2/2026 14:15 13 fever, headache, stiff neck, photophobia and stressed meningitis 120 12/2/2026 8:35 14 NULL NULL NULL NULL 15 NULL NULL NULL NULL 16 breathlessness with chest tightness and pains asthma 120 NULL 17 swollen and painful with psoriasis flare-up drug-prompted psoriasis 100 11/2/2026 8:30 18 NULL NULL NULL NULL comment ranking 2 The examination used to be rather rushed 4 3 NULL NULL 4 NULL NULL 5 NULL NULL 6 NULL NULL 7 NULL NULL 8 NULL NULL 9 Physician used to be affected person and understood my burden 5 10 Physician used to be cautious in my physical examination 5 11 Physician saw me 2 hours later than appointed time 1 12 The medication offered some relief 5 13 Physician used to be simply in prognosis and helped alleviate the pains 5 14 NULL NULL 15 NULL NULL 16 NULL NULL 17 NULL NULL 18 NULL NULL Q3(a)(ii) Enlarged Model. Interrogate of 3b This build a matter to is per the tables implemented from the ERD in Figure Q3(a). You may perchance well perchance maybe perchance like to NOT assign views the use of the Fetch Belief issue, but you may perchance perchance perchance use inline views in your build a matter to design. You MUST reproduction your SQL statements into your resolution doc in TEXT structure. You MUST present comments on each and every of your SQL statements. You MUST present a screenshot of your output you procure whilst you occur to reside your SQL issue. Marks are given to alternate solutions that adhere to the above instructions. Formulate SQL queries to present the next initiatives: (i) Checklist the afternoon (2 pm and later) consultations for pains and the build the mark is a minimal of $100$ patientId consultDateTime anguish prognosis mark doctorId 1 2026-02-02 15:30:00 Breathlessness mountain climbing stairs, intermittent chest pains septal defects 240.00 C001S 2 2026-01-21 14:30:00 breathlessness with chest tightness and pains asthma 120.00 R007S 3 2026-02-04 14:30:00 behind and skipped heartbeat with chest pains arrhythmia 120.00 C001S (ii) Checklist the ages of the patients on the time of visits. You may perchance well perchance even rob 1 365 days to be 8766 hours or (3651 / 4) days. patientId title dateOfBirth address AgeYears consultDateTime anguish prognosis mark 1 Patrick 1955-01-16 12 Bedok Avenue 70 2026-01-15 11:30:00 Insomnia for the previous 3 weeks, feeling a strategy of loss terror 80 2 Patrick 1955-01-16 12 Bedok Avenue 71 2026-01-16 11:30:00 Irritation and pains within the finger joints arthritis 80 3 Patrick 1955-01-16 12 Bedok Avenue 71 2026-02-02 09:30:00 Irregular coronary heart rhythm that is now not customary arrhythmia 120 4 Peter 1990-02-15 15 Clementi Avenue 35 2026-02-15 15:30:00 Breathlessness mountain climbing stairs, intermittent chest pains septal defects 240 5 Philemon 1960-03-18 18 Dunman Avenue 65 2026-01-21 14:30:00 breathlessness with chest tightness and pains asthma 120 6 Philemon 1960-03-18 18 Dunman Avenue 65 2026-02-02 10:30:00 Irritation and pains within the knee joints arthritis 80 7 Philemon 1960-03-18 18 Dunman Avenue 65 2026-02-04 14:30:00 behind and skipped heartbeat with chest pains arrhythmia 120 8 Phoebe 2015-04-22 22 Frankel Avenue 10 2026-02-04 10:30:00 flu with loud wheezing asthma attack 120 9 Phoebe 2015-04-22 22 Frankel Avenue 10 2026-02-09 10:30:00 fever, headache, stiff neck, photophobia and stressed meningitis 120 10 Paul 2010-05-28 28 Grange Avenue 15 2026-02-02 09:00:00 dry and itching scalp psoriasis flare-up 120 11 Paul 2010-05-28 28 Grange Avenue 15 2026-02-10 10:30:00 swollen and painful with psoriasis flare-up drug-prompted psoriasis 100 12 Paul 2010-05-28 28 Grange Avenue 15 2026-02-17 11:30:00 scaly entrance of neck psoriasis flare-up 120 (iii) Checklist specialists with out a session in January 2026. doctorId title distinctiveness 1 C001S Samuel 2 C012S Sarah 3 D002S Sebastian 4 G004S Simon 5 P003S Silas 6 P009S Samantha 7 P010S Steven 8 R006S Spencer 9 R008S Susan 10 R011S Sabrina iv) Checklist the assortment of specialists in each and every distinctiveness, and the number with and with out a session. distinctiveness Number of Experts Number of Experts With Search the advice of Number of Experts With out Search the advice of 1 cardiology 2 1 2 2 dermatology 1 1 3 3 gastroenterology 1 0 4 4 geriatric 1 1 5 5 paediatrics 3 2 6 6 renal 1 0 7 7 respiratory 2 1 8 8 rheumatology 1 1 9 (v) For each and every affected person, depend the assortment of specialists consulted, the assortment of consultations and full mark paid per distinctiveness. Checklist per affected person identification and distinctiveness in alphabetical inform. patientId title distinctiveness Number of Experts consulted Number of Search the advice of Total Price 1 Patrick cardiology 1 1 21 2 Patrick geriatric 1 2 3 3 Peter cardiology 1 1 4 4 Philemon cardiology 1 1 5 5 Philemon geriatric 1 1 6 6 Philemon respiratory 1 1 7 7 Phoebe paediatrics 2 2 8 8 Paul dermatology 1 2 9 9 Paul rheumatology 1 1 1 (vi) Checklist patients who survey specialists from bigger than one distinctiveness. Checklist in descending inform of assortment of specialties and ascending inform of affected person identification. patientId title Number of Area of skills 1 3 Philomen 2 1 Patrick 3 5 Paul (vii) Checklist patients who survey specialists from dermatology moreover to those from rheumatology. patientId title dateOfBirth address 1 5 Paul 2010-05-28 (viii) Checklist patients who survey specialists from cardiology but now not from geriatric. patientId title dateOfBirth address 1 2 Peter 1990-02-15 (ix) Checklist specialists with the best assortment of consultations. doctorId title distinctiveness 1 C001S Samuel 2 G005S Sophie (x) Checklist specialists with primarily the most assortment of patients. doctorId title distinctiveness 1 C001S Samuel xi) Checklist specialists with the assortment of consultations they bear got, the assortment of opinions and their common ranking (insist with one digit after the decimal point). Experts with out opinions may perchance perchance perchance maybe bear zero (insist as a full number) as their common ranking. doctorl title distinctiveness Number of seek the advice of Number of Reviews Reasonable ranking 1 C001S Samuel cardiology 3 1 4.0 2 C012S Sarah cardiology 0 0 0 3 D002S Sebastian dermatology 2 0 0 4 G004S Simon gastroenterology 0 0 0 5 G005S Sophie geriatric 3 3 3.7 6 P003S Silas paediatrics 1 1 5.0 7 P009S Samantha paediatrics 1 1 5.0 8 P010S Steven paediatrics 0 0 0 9 R006S Spencer renal 0 0 0 10 R007S Stella respiratory 1 0 0 11 R008S Susan rheumatology 1 1 4.0 12 R011S Sabrina respiratory 0 0 0 (25 marks) END OF ASSIGNMENT Write My Task Want Professional Abet with ICT330 Database Administration Systems TMA? Native Singapore Writers Personnel 100% Plagiarism-Free Essay Very most practical Pride Price Free Revision On-Time Offer
- WE OFFER THE BEST CUSTOM PAPER WRITING SERVICES. WE HAVE DONE THIS QUESTION BEFORE, WE CAN ALSO DO IT FOR YOU.
- Assignment status: Already Solved By Our Experts
- (USA, AUS, UK & CA PhD. Writers)
- CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS
QUALITY: 100% ORIGINAL PAPER – NO ChatGPT.NO PLAGIARISM – CUSTOM PAPER
Looking for unparalleled custom paper writing services? Our team of experienced professionals at AcademicWritersBay.com is here to provide you with top-notch assistance that caters to your unique needs.
We understand the importance of producing original, high-quality papers that reflect your personal voice and meet the rigorous standards of academia. That’s why we assure you that our work is completely plagiarism-free—we craft bespoke solutions tailored exclusively for you.
Why Choose AcademicWritersBay.com?
- Our papers are 100% original, custom-written from scratch.
- We’re here to support you around the clock, any day of the year.
- You’ll find our prices competitive and reasonable.
- We handle papers across all subjects, regardless of urgency or difficulty.
- Need a paper urgently? We can deliver within 6 hours!
- Relax with our on-time delivery commitment.
- We offer money-back and privacy guarantees to ensure your satisfaction and confidentiality.
- Benefit from unlimited amendments upon request to get the paper you envisioned.
- We pledge our dedication to meeting your expectations and achieving the grade you deserve.
Our Process: Getting started with us is as simple as can be. Here’s how to do it:
- Click on the “Place Your Order” tab at the top or the “Order Now” button at the bottom. You’ll be directed to our order form.
- Provide the specifics of your paper in the “PAPER DETAILS” section.
- Select your academic level, the deadline, and the required number of pages.
- Click on “CREATE ACCOUNT & SIGN IN” to provide your registration details, then “PROCEED TO CHECKOUT.”
- Follow the simple payment instructions and soon, our writers will be hard at work on your paper.
AcademicWritersBay.com is dedicated to expediting the writing process without compromising on quality. Our roster of writers boasts individuals with advanced degrees—Masters and PhDs—in a myriad of disciplines, ensuring that no matter the complexity or field of your assignment, we have the expertise to tackle it with finesse. Our quick turnover doesn’t mean rushed work; it means efficiency and priority handling, ensuring your deadlines are met with the excellence your academics demand.
