ICT330\u00a0 Tutor-Marked Assignment January 2026 Presentation This assignment is charge 18% of the final charge for ICT330, Database Administration Programs. The cut- off date for this assignment is Monday, 02 March 2026, 2355 hours. Repeat to Students: You would even be to consist of the next particulars in your submission: Direction Code, Title of the TMA, SUSS PI No., Your Title, and Submission Date. Reply All Questions. (Complete 100 marks) Please remark that Questions 1, 2 and 3 are unrelated though they’ve the the same area. Each and each demand will even be worked on independently of the assorted questions. Interrogate 1 (33 marks) It’s some distance envisioned that every body citizens can expend a portal to gather and\/or smash appointments for public successfully being practitioner, labeled as general practitioners, consultants, physiotherapists and hundreds others. The portal will allow citizens to inform clinical prerequisites that they are searching to overview a successfully being practitioner for. Voters first establish from a historical list of clinical prerequisites that very finest match their prerequisites. The normal list involves clinical prerequisites, every clinical condition has a distinct code, a title and a description. After picking the linked clinical prerequisites from the standard list, citizens enter an intensive description for every of these clinical prerequisites chosen. Each and each detailed description is timestamped. Next, citizens collect an appointment demand. They make a selection these clinical prerequisites they’ve described earlier or for outdated requests, that they are searching to perceive consultation for. A demand id is generated with a demand date and time. If the described prerequisites for the appointment demand are newly entered, the machine selects a general practitioner per the earliest availability. It’s a policy that appointment for newly described clinical prerequisites are first viewed by a general practitioner who will word judgement to uncover whether or not referrals for specialised consultations are required. Repeat that the machine records furthermore a field of specialty, e.g., sports physiotherapy or interior treatment, for every clinical condition in the standard list where applicable. In an identical procedure, a field of specialty will even be recorded for every successfully being practitioner if applicable. If the clinical prerequisites chosen for the latest demand are all people that were beforehand viewed by a successfully being practitioner, then a successfully being practitioner from the the same specialty is chosen. As an illustration, if the citizen saw a sports physiotherapist beforehand for these prerequisites, then a sports physiotherapist (pretty than a general practitioner) is chosen for the fresh appointment demand, again per the earliest availability. It’s well-known to chronicle which slots for every successfully being practitioner were taken to permit the machine to decide on earliest readily obtainable factual successfully being practitioner. A field of specialty has a distinct specialty code, a title and a description. A consultation by any successfully being practitioner might perchance extinguish up in referral to other consultants or therapists or might perchance extinguish up in a word up consultation. For every referral or word- up consultation, the machine generates fresh appointment demand. Hence, an appointment demand would be initiated by a citizen or thru a referral or for a word- up consultation for the citizen. For appointment demand for a word up consultation, the the same category of successfully being practitioner shall be assigned to the fresh demand. No longer all consultations originate fresh appointment requests whether it is assessed that is that you just might perchance additionally bear in mind that a citizen\u2019s clinical condition is resolvable with the latest consultation. Furthermore, not all appointment requests pause with a consultation as some citizens might perchance additionally miss their appointment. For the time being, a consultation is either a clinical consultation or a treatment session, e.g., physiotherapy. The successfully being practitioner facing the consultation will chronicle one or extra consultation notes for every clinical condition the consultation is for. For a treatment session, the therapist records the interval of the session and the therapies equipped e.g., manipulation, strengthening hiss. The therapist chooses from a historical list of that you just might perchance additionally bear in mind treatment therapies. Each and each treatment has a distinct code, a title and a description. The therapist then records well-known points of the treatment conducted e.g., neck rotation and extension for strengthening hiss. For a clinical consultation, the successfully being practitioner might perchance additionally demand for investigations to be implemented, e.g., blood test or ultrasound scan. The successfully being practitioner chooses furthermore from a historical list of that you just might perchance additionally bear in mind investigations. Each and each investigation has a distinct code, a title and a description. The implications of the investigation needs to be recorded in the machine once readily obtainable. A clinical consultation can furthermore result in a prescription for treatment. The successfully being practitioner chooses from a historical list of substances. Each and each drug has a distinct code, a title and one or extra contraindications e.g., being pregnant. The successfully being practitioner has to make sure citizens should always not allergic to prescribed treatment, besides these treatment chosen compose not work together negatively with every other. The successfully being practitioner furthermore indicates the dosage, interval and instruction for every prescribed drug in the machine. With out adding any attribute\/identifier not described in the demand, create a conceptual entity relationship (ER) model from the statements of necessities to symbolize the solutions model, showing the solutions necessities in one draw. entities, with entities title, linked attributes and identifier, (16 marks) relationships with maximum and minimal cardinalities and relationship title (17 marks) Assert your assumption(s) for simplest files necessities that should always not specified. Sign your model as ONE draw. Interrogate 2 (32 marks) You would even be given the desk: Prescription(prescriptionId, deliveryDatetime, patientId, title, take care of, drugCode, drugName, dosage, instruction, contraindication) This desk is inclined by a pharmacy to carry prescribed treatment if a patient demand for house offer. A prescription is identified by a prescription id. Each and each prescription contains one or extra treatment, every with its required dosage. A drug is identified by a drug code, has a drug title and is derived with one or extra instructions e.g., after food and no alcohol, and one or extra contraindications e.g., pregnant girls folk and diabetic people. A patient is identified by patient id. Each and each prescription is assigned a offer date and time and a patient to carry to. Interrogate 2a Formulate and list the functional and multi- valued dependencies (MVD). Attain not introduce any fresh columns not in the given desk. Assert your assumption(s) for simplest files necessities that should always not specified. (7 marks) Interrogate 2b Blueprint dependency graph and point out candidate key(s) for the desk. Sign composite keys in brackets. (4 marks) Interrogate 2c (i) Enlighten whether or not the MVDs in your answer to Q2(a) are subsumed or not subsumed. (ii) Apply the BCNF means to normalise the desk to Boyce Codd Customary Win (BCNF) and to Fourth Customary Win (4NF). Sign the means you word the steps for all FDs and unsubsumed MVDs. For every pair of two tables because of an iteration, insist whether or not every of them is in BCNF or in 4NF or in any other case and remark why. (10 marks) Interrogate second Assemble your BCNF and 4NF tables and constraints clearly by giving every desk and the referential integrity constraint(s), if any, in the types described below: Tables on this collect: TABLE- NAME(col- name1, col- name2, \u2026, col- title n) remark (PK) in opposition to the predominant key and remark (FK) in opposition to the out of the country keys, if any. Referential integrity constraint(s), if any, alongside with the dinky one desk on this collect: TABLE- NAMEx.col- namex1 \u2026col- namexn must exist in TABLE- NAMEy.col- namey1\u2026col- nameyn Repeat that there might perchance be no collect to remark the derivation steps or remark them for this demand share. Merely present the final tables, the predominant keys, the out of the country keys and the out of the country key constraints. (11 marks) Interrogate 3 (35 marks) All SQL statements for this demand (fabricate, insert and establish statements) MUST be in textual notify material format. You MUST furthermore paste the screenshots of the final result of executing your whole SQL statements. Interrogate 3a (i) Change into the ERD in Prefer Q3(a)(i) into a logical model. Prefer Q3(a)(i) Sign the tables in the logical model in the collect: TABLE- NAME(col- name1, col- name2, \u2026, col- title n) remark (PK) in opposition to the predominant key, remark (FK) in opposition to the out of the country keys, if any, including integrity constraints in the collect: TABLE- NAMEx.col- namex1 \u2026col- namexn must exist in TABLE- NAMEy.col- namey1\u2026col- nameyn (ii) Order over with the outline of the columns for the column definition, and then code the expend of SQL statements to put in power all tables derived from the ERD in Prefer Q3(a)(i). All columns are required excluding for the column feedback. Listing your SQL fabricate statements in the mutter that they needs to be performed. 1CT330 patientId An integer, auto generated with beginning charge 1 and increment 1. title (of patient) At most 60 characters in length dob Date and charge must never be later than the latest datetime take care of At most 100 characters in length consultDatetime Datetime peril At most 1024 characters in length prognosis At most 512 characters. charge A decimal number with 5 digits of which 2 digits are after the decimal point. Worth is between $80 and $320, with default charge $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 specialty Exactly 20 characters in length, with a charge either paediatrics, dermatology, rheumatology, urology, gastroenterology, geriatric, cardiology, respiratory, renal. reviewDatetime Datetime with default latest date and time. Repeat that 1. (patientId, reviewDatetime) is an change key. 2. reviewDatetime must be finally 3 hours after consultDatetime feedback At most 1024 characters in length ranking A bunch from 1 to 5 Constraints for the relationships in Prefer Q3(a)(i): has delete and change are disallowed attendsTo delete and change are disallowed isGiven delete and change cascaded (iii) Populate the tables you created in Interrogate 3(a)(ii). You’ve to title the linked files rows from the test shown in Desk Q3(a)(ii) to populate the tables created for Interrogate 3(a)(ii). Listing your SQL insert statements in the mutter that they needs to be performed. Prefer Q3(a)(ii) Enlarged version of the solutions rows is shown in 3 vertical desk cells, in Desk doctorid title specialty patientId title dateOfBirth take care of consultDateTime 2 C001S Samuel cardiology 1 Patrick 16\/1\/1955 12 Bedok Toll road 2\/2\/2026 9:30 3 C001S Samuel cardiology 2 Peter 15\/2\/1990 15 Clementi Toll road 2\/2\/2026 15:30 4 C001S Samuel cardiology 3 Philemon 18\/3\/1960 18 Dunman Toll road 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 Toll road 2\/2\/2026 9:00 7 D002S Sebastian dermatology 5 Paul 28\/5\/2010 28 Grange Toll road 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 Toll road 15\/1\/2026 11:30 10 G005S Sophie geriatric 1 Patrick 16\/1\/1955 12 Bedok Toll road 16\/1\/2026 11:30 11 G005S Sophie geriatric 3 Philemon 18\/3\/1960 18 Dunman Toll road 2\/2\/2026 10:30 12 P003S Silas paediatrics 4 Phoebe 22\/4\/2015 22 Frankel Toll road 4\/2\/2026 10:30 13 P009S Samantha paediatrics 4 Phoebe 22\/4\/2015 22 Frankel Toll road 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 Toll road 21\/1\/2026 14:30 17 R008S Susan rheumatology 5 Paul 28\/5\/2010 28 Grange Toll road 10\/2\/2026 10:30 18 R011S Sabrina respiratory NULL NULL NULL NULL NULL peril prognosis charge reviewDateTime 2 Irregular heart rhythm that is just not normal arrhythmia 120 2\/2\/2026 12:52 3 Breathlessness mountaineering stairs, intermittent chest anxiety septal defects 240 NULL 4 unhurried and skipped heartbeat with chest anxiety arrhythmia 120 NULL 5 NULL NULL NULL NULL 6 dry and itching scalp psoriasis flare-up 120 NULL 7 scaly front of neck psoriasis flare-up 120 NULL 8 NULL NULL NULL NULL 9 Insomnia for the past 3 weeks, feeling a approach of loss fear 80 15\/1\/2026 17:30 10 Inflammation and anxiety in the finger joints arthritis 80 29\/1\/2026 14:33 11 Inflammation and anxiety in the knee joints arthritis 80 2\/2\/2026 13:35 12 flu with loud wheezing asthma assault 120 4\/2\/2026 14:15 13 fever, headache, stiff neck, photophobia and puzzled meningitis 120 12\/2\/2026 8:35 14 NULL NULL NULL NULL 15 NULL NULL NULL NULL 16 breathlessness with chest tightness and anxiety asthma 120 NULL 17 swollen and painful with psoriasis flare-up drug-caused psoriasis 100 11\/2\/2026 8:30 18 NULL NULL NULL NULL comment ranking 2 The examination changed into rather rushed 4 3 NULL NULL 4 NULL NULL 5 NULL NULL 6 NULL NULL 7 NULL NULL 8 NULL NULL 9 Doctor changed into patient and understood my burden 5 10 Doctor changed into careful in my bodily examination 5 11 Doctor saw me 2 hours later than appointed time 1 12 The treatment equipped some relief 5 13 Doctor changed into simply in prognosis and helped alleviate the anxiety 5 14 NULL NULL 15 NULL NULL 16 NULL NULL 17 NULL NULL 18 NULL NULL Q3(a)(ii) Enlarged Model. Interrogate 3b This demand is per the tables implemented from the ERD in Prefer Q3(a). You’ve to NOT fabricate views the expend of the Win Survey statement, however you might perchance additionally expend inline views in your query plan. You MUST duplicate your SQL statements into your solution doc in TEXT format. You MUST present feedback on every of your SQL statements. You MUST present a screenshot of your output you fetch when you happen to attain your SQL statement. Marks are given to strategies that adhere to the above instructions. Formulate SQL queries to fabricate the next tasks: (i) Listing the afternoon (2 pm and later) consultations for anxiety and where the cost is finally $100$ patientId consultDateTime peril prognosis charge doctorId 1 2026-02-02 15:30:00 Breathlessness mountaineering stairs, intermittent chest anxiety septal defects 240.00 C001S 2 2026-01-21 14:30:00 breathlessness with chest tightness and anxiety asthma 120.00 R007S 3 2026-02-04 14:30:00 unhurried and skipped heartbeat with chest anxiety arrhythmia 120.00 C001S (ii) Listing the ages of the patients on the time of visits. You would also rob 1 yr to be 8766 hours or (3651 \/ 4) days. patientId title dateOfBirth take care of AgeYears consultDateTime peril prognosis charge 1 Patrick 1955-01-16 12 Bedok Toll road 70 2026-01-15 11:30:00 Insomnia for the past 3 weeks, feeling a approach of loss fear 80 2 Patrick 1955-01-16 12 Bedok Toll road 71 2026-01-16 11:30:00 Inflammation and anxiety in the finger joints arthritis 80 3 Patrick 1955-01-16 12 Bedok Toll road 71 2026-02-02 09:30:00 Irregular heart rhythm that is just not normal arrhythmia 120 4 Peter 1990-02-15 15 Clementi Toll road 35 2026-02-15 15:30:00 Breathlessness mountaineering stairs, intermittent chest anxiety septal defects 240 5 Philemon 1960-03-18 18 Dunman Toll road 65 2026-01-21 14:30:00 breathlessness with chest tightness and anxiety asthma 120 6 Philemon 1960-03-18 18 Dunman Toll road 65 2026-02-02 10:30:00 Inflammation and anxiety in the knee joints arthritis 80 7 Philemon 1960-03-18 18 Dunman Toll road 65 2026-02-04 14:30:00 unhurried and skipped heartbeat with chest anxiety arrhythmia 120 8 Phoebe 2015-04-22 22 Frankel Toll road 10 2026-02-04 10:30:00 flu with loud wheezing asthma assault 120 9 Phoebe 2015-04-22 22 Frankel Toll road 10 2026-02-09 10:30:00 fever, headache, stiff neck, photophobia and puzzled meningitis 120 10 Paul 2010-05-28 28 Grange Toll road 15 2026-02-02 09:00:00 dry and itching scalp psoriasis flare-up 120 11 Paul 2010-05-28 28 Grange Toll road 15 2026-02-10 10:30:00 swollen and painful with psoriasis flare-up drug-caused psoriasis 100 12 Paul 2010-05-28 28 Grange Toll road 15 2026-02-17 11:30:00 scaly front of neck psoriasis flare-up 120 (iii) Listing consultants with out a consultation in January 2026. doctorId title specialty 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) Listing the assorted of consultants in every specialty, and the number with and with out a consultation. specialty Assortment of Experts Assortment of Experts With Consult Assortment of Experts With out Consult 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 every patient, count the assorted of consultants consulted, the assorted of consultations and whole charge paid per specialty. Listing per patient id and specialty in alphabetical mutter. patientId title specialty Assortment of Experts consulted Assortment of Consult Complete Worth 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) Listing patients who ogle consultants from bigger than one specialty. Listing in descending mutter of assorted of specialties and ascending mutter of patient id. patientId title Assortment of Distinctiveness 1 3 Philomen 2 1 Patrick 3 5 Paul (vii) Listing patients who ogle consultants from dermatology besides these from rheumatology. patientId title dateOfBirth take care of 1 5 Paul 2010-05-28 (viii) Listing patients who ogle consultants from cardiology however not from geriatric. patientId title dateOfBirth take care of 1 2 Peter 1990-02-15 (ix) Listing consultants with the very finest more than a number of of consultations. doctorId title specialty 1 C001S Samuel 2 G005S Sophie (x) Listing consultants with the most more than a number of of patients. doctorId title specialty 1 C001S Samuel xi) Listing consultants with the assorted of consultations they’ve, the assorted of opinions and their lifelike ranking (charge with one digit after the decimal point). Experts with out opinions can collect zero (charge as a whole number) as their lifelike ranking. doctorl title specialty Assortment of consult Assortment of Reports Average 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 Assignment Need Expert Abet with ICT330 Database Administration Programs TMA? Native Singapore Writers Group 100% Plagiarism-Free Essay Absolute top Pride Fee Free Revision On-Time Supply<\/p>\n","protected":false},"excerpt":{"rendered":"
ICT330\u00a0 Tutor-Marked Assignment January 2026 Presentation This assignment is charge 18% of the final charge for ICT330, Database Administration Programs. The cut- off date for this assignment is Monday, 02 March 2026, 2355 hours. Repeat to Students: You would even be to consist of the next particulars in your submission: Direction Code, Title of the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-29971","post","type-post","status-publish","format-standard","hentry","category-solutions"],"_links":{"self":[{"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/posts\/29971","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/comments?post=29971"}],"version-history":[{"count":0,"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/posts\/29971\/revisions"}],"wp:attachment":[{"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/media?parent=29971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/categories?post=29971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academicwritersbay.com\/solutions\/wp-json\/wp\/v2\/tags?post=29971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}