Narrative for RDBMS – database that can be deployed in a commercial database management system

The aim of the project is to assess the student’s ability to design and construct a database that can be deployed in a commercial database management system.  The initial phase of the project requires the student to design a relational database for the scenario provided on Moodle.   The business rules of the scenario must be developed and implemented via the database schema and SQL as follows:

• Relational Schema
• Tables
• Attributes
• Primary Keys
• Foreign Keys
• Constraints
• Primary Key
• Range
• Value
• Test Data
• Queries
• Select
• Insert
• Update
• Delete
• Create

The second requirement of the project is to use the database created to demonstrate Codd’s rules with respect to your individual database.  The first 10 rules MUST be demonstrated using SQL including an explanation (rules 11 and 12 do not require SQL).

• Relational Schema
• PDF of Schematic of the database i.e. tables and relationships
• SQL script to create the schema (CREATE COMMANDS) along with comments.
• Test Data
• SQL script to INSERT data along with comments.
• Queries
• One SQL script with each query separated along with comments.
• Doc file with each of Codd’s Rules clearly labeled followed by the SQL code demonstrating the rule and a narrative explaining the rule.
• Project Report: A project report (Max 2 pages) is also to be submitted, it should contain a brief explanation of all elements of the project along with any specific instructions required for the implementation of your project.

Narrative for RDBMS

Dr Mary Mulcahy runs a dental practice in a small town.  She can treat most cases, although a few specialist cases are referred to larger practices in Cork city where the required expertise and equipment are available.

Patients ask Helen, the office secretary, for appointments, either by post, phoning or dropping in.  She arranges a suitable appointment by referring to the appointments diary unless they owe over a certain amount, or for too long, as seen from the patient’s chart.  She writes the new appointment into the diary and, if it is the patient’s first visit, she makes a new chart for the patient and puts it into the charts filing cabinet.  Appointment details are also written into the patient’s chart.

Sometimes patients contact Helen to rearrange or even cancel appointments.  Re- arrangements are made by referring to the appointments diary to find a free time, and tippexing out the old time.  Cancellations are done by simply tippexing out the appointment in the diary.  The details in the patient’s chart are also updated with rearrangements and cancellations (late cancellations are charged a €10 late cancellation fee).

Every Tuesday morning, Helen checks the appointment diary and makes a list of all next week’s appointments.  She sends a reminder to all the patients (finding their addresses in their charts), enclosing an appointment card containing the appointment and treatment details with the reminders.  Next, at about 2.30 p.m. she prepares bills by searching the patient charts to find details of any unpaid treatments.  Then she looks up the Treatment Fees guidelines book, which Dr Mulcahy updates from time to time.  The bills, itemising all unpaid treatments and late cancellations, are sent to patients in the afternoon post.

Patients pay by cheque, credit card or cash, either by post or by dropping in.  The bill or the bill number is enclosed with the payment.  Treatments which have been paid for are marked as such in the patient’s file so that they will not be billed again.  Patients often arrange to make several small payments for a large bill.

When a patient arrives for an appointment, he/she presents the appointment card to Helen.  The patient sits in the waiting room until the dentist is ready to see him/ her, whereupon Helen passes the appointment card to Dr Mulcahy so that she can see what treatments are to be carried out.  After each visit, Dr Mulcahy completes the appointment card with details of work done and puts the filled card into her “Visit cards out tray”.  Helen takes the card and arranges appointments with the patient for any required follow-up treatments written on the card, and enters them into the appointments diary.  The visit card is then filed in the patient’s chart.  Sometimes patients make payments “on the spot” before leaving the clinic.

If the patient needs specialist treatment which Dr Mulcahy cannot provide, she writes the name of an appropriate specialist on the filled visit card and the secretary sends a patient referral to the specialist.  After specialist treatment, the specialist posts a dental report to Dr Mulcahy, who reads it and files it in the patient’s chart.