This is how you should create a Relational Database System

01-04-16 Rochio Varela 4 comments

Introduction: Relational Database System Assignment

This Relational Database System Assignment is about the case study of Fast Track health Center. There are 5 major entities in this system which are interconnected with each other. Specialists who wish to give consultation services must be registered with the system. They must specify consultation charges to the patient, check availability of rooms before consultation. Patients in turn have to pay the consultation fees and get an appointment based upon the availability.

Task 1

1.1 Design a Relational Database System Assignment to meet the requirement of case study (Identify Primary key, foreign key, normalization, data dictionary and validity check. ERD will be created without crow’s foot notation)

Entity Relationship Diagram

An Entity Relationship diagram has been created according to the case study. It shows six entities: person, member, specialist, FTHC system, receptionist and patient. There are many relationships between these entities like check availability and book, checks availability, make appointment, confirm, specify consultation charges to etc.

 

ER Diagram Programming Assignments Help

FTHC – ER Diagram

Data Flow Diagram

 Programming Assignments Help Relational Database System Assignment

Data Flow Diagram

1.2 Creation of relational database based on prepared design

There are 5 tables in this database solution: Receptionist, person, room, patient and specialist. Their corresponding attributes and constraints are shown in the below table.

TABLE NAME ATTRIBUTES CONSTRAINTS
RECEPTIONIST MEMBERSHIP_NO PRIMARY KEY
RECEPTIONIST_FNAME
RECEPTIONIST_LNAME
GENDER
DATE_OF_BIRTH
STREET
CITY
PERSON PERSON_ID PRIMARY KEY
PERSON_FNAME
PERSON_LNAME
SPECIALIZATION
PHOTOGRAPH
LICENCE_EXPIRY_DATE
MARITAL_STATUS
GENDER
DATE_OF_BIRTH
STREET
CITY
CONTACT_NO
HIGHEST_EDUCATION
SPECIALIZATION_AREA
VALID_ADDRESS_PROOF
EVIDENCE_OF_EDUCATION
VALID_CRB
POLICE_REPORT
ROOM ROOM_NO PRIMARY KEY
AVAILABILITY
CAPACITY
ATTENDANCE_ID
TIME_OF_BOOKING
PATIENT PATIENT_ID PRIMARY KEY
PATIENT_FNAME
PATIENT_LNAME
DATE_OF_BIRTH
CONTACT_NO
GENDER
STREET
CITY
POSTCODE
SPECIALIST MEMBERSHIP_NO PRIMARY KEY
FIRST_NAME
LAST_NAME
SPECIALIZATION
PHOTOGRAPH
EXPIRY_DATE
MARITAL_STATUS
GENDER
DATE_OF_BIRTH
STREET
CITY
POST_CODE
HIGHEST_EDUCATION
SPECIALIZATION_AREA
VALID_ADDRESS_PROOF
EVIDENCE_OF_EDUCATION
VALID_CRB
POLICE_REPORT

1.3 Enhance user interface by applying a range of database tools and techniques

Database of fast track health center is created by the help of SQL Server management studio. There are 5 tables in the database: Person, receptionist, room, patient and specialist. Constraints like primary key are defined in the structure of the database. There were however no foreign keys in the proposed system. [Learn about Java Assignment Help]

If user interface of the proposed system is developed then it can have following features:

  • Manage focus by adding contrast or correct use of bold, italics, underline, text effects, text highlight color and font color etc.
  • Use of appropriate color to manage attention
  • Proper use of white spaces within the text and images
  • Use of tables, figures and spacing of letters
  • Auto focus on input must be there for automatically shifting the cursor over the input field where user is to enter
  • Making use of verbs in labels like:”yes”, “no”, “save”, “don’t save” and “cancel” etc.
  • Making appropriate use of hover controls, to draw user’s attention over the focus points of the website (Fadeyev and Fadeyev, 2008)

1.4 Create an ER diagram with CROW’s foot notation. Explain clearly about entities, corresponding relationships and attributes. Discuss about all relevant techniques to draw ER Diagram

Entity Relationship Diagram with Crow’s Foot Notation. It includes the relational integrity constraints like one to one, one to many, many to one and many to many. Entities are depicted in rectangular boxes and attributes are shown on ellipses. This ER Diagram is created by making use of draw.io online tool which is one of the techniques of creating good entity relationship diagrams.

In this diagram, person entity is connected with FTHC system via get registered relationship. A person gets registered with the FTHC system and becomes a registered member. This member can be a specialist. Every specialist gets a membership no which acts as an identification number while working within fast track health center. Every receptionist also has a membership number. A patient checks for an appointment with the receptionist and makes payment. A specialist also checks for the availability of the room for consultation and books them as per availability. Patient also checks for the availability of rooms within FTHC system. After patient makes the payment to the receptionist then later confirms the payment to FTHC system.

ER Diagram with CROW foot notation Programming Assignments Help

FTHC-ER Diagram with Crow’s Foot Notation

Read more about PHP Assignment Help

1.5 Discuss about design, development and implementation methods with strengths and weaknesses of the developed system. This will be an evaluation of your work.

This system is a virtual implementation of Fast Track Health center. It is created in accordance to the case study. Patient or specialists have to check the availability of the room before getting booked. Rooms can be booked at any time of the year if they are available. One of the drawback of this system is that it is not tracking the time when the specialists and patients can book the rooms. According to case study, rooms can be booked at least 2 weeks before the intended consultation. In addition to this, rooms can be booked only at particular time slots (morning: 7AM – 12 AM, afternoon: 12 PM – 5 PM, evening: 5PM -10 PM). Another drawback of this system is that system is not able to keep track of these time slots.

Advantage of this system: Basic functionality of the system as described in the case study is shown in the developed system. It is as follows: Specialists are able to check availability and book rooms for consultation. They specify consultation charges to the patients. Patients ask for available date and time of consultation and seek appointment with the receptionist and makes payment. This payment is forwarded to FTHC system by receptionists.

Task 2

2.1 Implementation of query language into the developed database management system (DDL commands)

Create Query

Relational Database System Assignment Programming Assignments Help

Creation of Room Table

Relational Database System Assignment

Alter Table Command

 Programming Assignments Help Alter Table Command

Drop Table Command

Also Read: Android App Development Assignment

2.2 With reference to 3.1 above, explain advantages of using query in database system and how this will be helpful for non-IT related person

Queries are commands that help us to fetch relevant information from the database. These depend upon the database engine on which they will be executed. Although basic structure of the query remains same and any person who is not from IT background can query the database easily. Select query is the easiest form of query which is used to retrieve information from the database. Anyone who wishes to view the records can fire select query and fetch records.

(Plew and Stephens, 2016)

Read more about Technical Assignment Help

Task 3

3.1 Critically review and test the developed relational database system

Relational database of FTHC is as follows:

ROOM (ROOM_NO (PK), AVAILABILITY, CAPACITY, ATTENDANCE_ID, TIME_OF_BOOKING)

PATIENT (PATIENT_ID (PK), PATIENT_FNAME, PATIENT_LNAME, DATE_OF_BIRTH, CONTACT_NO, GENDER, STREET, CITY, POSTCODE, PHOTO)

RECEPTIONIST (MEMBERSHIP_NO (PK), RECEPTIONIST_FNAME, RECEPTIONIST_LNAME, GENDER, DATE_OF_BIRTH, STREET, CITY)

PERSON (PATIENT_ID (PK), PATIENT_FNAME, PATIENT_LNAME, DATE_OF_BIRTH, CONTACT_NO, GENDER, STREET, CITY, POSTCODE)

SPECIALIST (MEMBERSHIP_NO (PK), FIRST_NAME, LAST_NAME, SPECIALIZATION, PHOTOGRAPH, EXPIRY_DATE, MARITAL_STATUS, GENDER, DATE_OF_BIRTH, STREET, CITY, POSTCODE, HIGHEST_EDUCATION, SPECIALIZATION_AREA, VALID_ADDRESS_PROOF, EVIDENCE_OF_EDUCATION, VALID_CRB, POLICE_REPORT)

 (PK is primary key. There are no foreign keys in this database right now.)

Requirement of testing the above database:

It is very essential to test this database as it is a collection of heterogeneous files. Integration of various files can introduce errors into the database. In addition to checking this, the database must be created in accordance with ACID properties. ACID stands for atomicity, consistency, isolation and durability. Any operation that is performed over the database must lead to consistent records. Two different transactions cannot take place simultaneously if they are making use of same table.  All these points must be considered while creating and maintaining a database.

Read more about Programming Assignment Help

3.2 User manual for the developed database solution

In this database solution, the basic functionality of fast track health center is described. Every person who wants to get associated with the system has to get registered with it. After successful registration, person becomes a registered member and gets a membership number. Member can be specialist. Specialist becomes eligible for providing consultation to the patients. They have to specify consultation charges to them before providing consultation. Patients have to call receptionist, check date and time and seek appointment. Patients have to pay the consultation charges to the receptionist, who in turn will forward these charges to FTHC system and further to specialists. Specialists will have to book a room in FTHC before consultation. Patients can also book a room in FTHC. (Codex.cs.yale.edu, 2016)

3.3 Discuss about the verification and validation of database

Verification and validation are essentially two phases applied in software testing. They helps us to determine whether the software or database meets the design specifications or not. The software quality must be met at any cost. Although both these terms are used interchangeably but actually there are number of differences which are listed below. In simplest terms verification is done in between the process, it is done as part of intermediary work or to check whether the process that we are following to create the product or software or database is correct and all initial requirements laid down by the client are met. Other way round, validation is the process of software testing that is applied when the software product or database is ready. In this case final product is tested. Smoke testing, regression testing, functional testing and system testing are some of the types of testing methods applied as part of validation. Have a look at table below for more precise differences:

Relational Database System Assignment

Verification and validation

Read more about Database Assignment Help

3.4 Explain various control mechanisms and how they have been used

Control mechanisms are structures or units or small embedded systems that help the developers and system analysts to evaluate the functionality of the system and whether all the components are working properly. With reference to our case study it is to be seen that all the tasks are being performed as per the initial requirement and there are no discrepancies. Some of the checks that have been implemented within the system are:

  • Specialists have to check availability of the room before booking
  • Patients have to check availability of the rooms before booking
  • Specialists have to specify the consultation charges to the patients before treatment
  • Patients have to pay the charges to the FTHC system before treatment
  • Any person who wants to get associated with FTHC system has to get registered with the system
  • Every registered member will get a membership number
  • Receptionist will have to forward the payment to FTHC center
  • Patients have to seek appointment before the actual consultation

  All these checks have been implemented within the system. These ensure that the system is working properly. These control mechanisms act as validations and are part of validation testing. (BusinessDictionary, 2016)

3.5 Implement a testing plan, elaborate 4.1 and 4.2 to discuss about various testing techniques implemented in testing this database

Every software application requires a database to be created. Whether the application is web based, mobile or desktop application, a database is the driving engine and storehouse of all its data. Front end of the application exists because of its backend. The capacity of the database depends upon the size of application and the prospective number of users who will be using it. As the application becomes more complex it requires database to be more robust and complex. It is always easy to fetch records from the database. Those records must be same as these being displayed by the front end application. This consistency is very important for a database application to work properly. Database is the driving engine of all areas of applications whether it is healthcare, military, hotels, software, banking or any other. Various database tools are available in the market like SQL Server, MySQL, SQL DB2, SQL base, SQL lite, Small SQL, Oracle, SAS, Microsoft Access, PostgreSQL, Hadoop etc. (Cs.fit.edu, 2016)

Also Read: Android Assignment Help

There are some of the most important reasons why we require testing a database:

  • It is very important to map the information within the database with the front end application. When we query a database and get some results; these results must be same as the records that we inserted via front end of the application, through insertion or update query. This implies that whenever any CRUD operation is performed over the application front end, like creation, updation, retrieve or select and delete, database that is associated with the application is evoked at the same time and all changes that are done from the front end must get reflected in the database and vice versa.
  • All database transactions must ensure that all ACID properties are being fulfilled like atomicity, consistency, isolation and durability etc. Any transaction that is performed over the database must be done in entirety or not started at all. This implies that the results of the transaction must reflect correctly over the front end of the application and within the database. There must be no discrepancy. If any transaction is done, its results must not affect the results of another transaction being done in parallel. E.g. if two different transactions are being performed on a single table, then its values cannot remain consistent and the result of the transaction will vary. To avoid this situation, one transaction must be performed at one time. It may happen that the result of one query can act as input of another query, but all this must result in same values over the application front end, i.e. over all pages of the web application.
  • All pages of the application must reflect latest information that is updated within the database.
  • Database must ensure business logic at database level. This implemented business logic must ensure consistency and must be accurate. (Softwaretestinghelp, 2015)

3.6 Discuss about the scope of improvement for this system. What extra functionalities can be integrated and how these will enhance their business better

The case study is implemented to show the basic functionality of fast track health center. Still there is lot of scope of improvement and this application can be enhanced further to elaborate the existing functionalities and can be made more comprehensive. Read more about Computer Network Assignment

  • As per the case study, rooms can be booked at any time of the year, subject to availability. Right now there is no mechanism to justify as to which time of the year the rooms are being booked. There must be some calendar or calculator kind of mechanism which is integrated within the application to keep track of the time and date of booking rooms, available dates and time, consultation and making appointment.
  • Another requirement as per the case study is that the specialists can book rooms at least two weeks before the consultation. This requirement is not being captured by the system right now.
  • There are some particular time slots between which the rooms are available for booking. Rooms cannot be booked beyond those timing. This requirement is not captured right now. Some apparatus or system must be integrated with the application that is able to find the available time and date for booking.
  • A room can be booked for 500 pounds per day, but this requirement is not captured via the system. It is being assumed that patients know the booking charge and they are paying this amount only.
  • Specialists have to charge from the patients before the consultation. This requirement is being captured by the system. But it does not become clear by having a look at this system that specialists book the room before and release the room after the consultation.
  • Patients have to make a phone call or visit the system in person to make an appointment. Right now it does not become clear from the system diagram as to which mode the patient adopted to contact the system.
  • Role of the receptionist is being captured by the system.
  • FTHC collects money from the patients and then hands over the money to respective specialist before they leave the system. This requirement is not being captured by the system completely. Money collection is shown but it does not become clear as to when specialist comes and when he goes. Similarly, when consultation starts and when it ends, also can be shown within the system.
  • Lastly, it does not become clear as to what mode of payment patients are using – cash/debit or credit. (ALIGNING IT WITH BUSINESS STRATEGY, 2016)
Read about Catering Database Management Assignment

Conclusion

This is a simple database assignment which discusses about various database models and schemas. SQL Server management studio is used for creation of the database. All issues of the database are properly addressed in this assignment.

References

  • Fadeyev, D. and Fadeyev, D. (2008). 10 Useful Techniques To Improve Your User Interface Designs – Smashing Magazine. [Online] Smashing Magazine. Available at: https://www.smashingmagazine.com/2008/12/10-useful-techniques-to-improve-your-user-interface-designs/ [Accessed 20 Jan. 2016].
  • Orafaq.com, (2016). What are the difference between DDL, DML and DCL commands? | Oracle FAQ. [Online] Available at: http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands [Accessed 20 Jan. 2016].
  • Softwaretestinghelp.com, (2015). What is Verification and Validation?. [Online] Available at: http://www.softwaretestinghelp.com/what-is-verification-and-validation/ [Accessed 21 Jan. 2016].
  • Cs.fit.edu, (2016). [Online] Available at: http://cs.fit.edu/~pbernhar/dbms.html [Accessed 21 Jan. 2016].
  • Softwaretestinghelp.com, (2015). All About Database Testing – Why to Test, How to Test, What to Test. [Online] Available at: http://www.softwaretestinghelp.com/database-testing-process/ [Accessed 21 Jan. 2016].
  • ALIGNING IT WITH BUSINESS STRATEGY. (2016). [Online] Available at: http://www.ictknowledgebase.org.uk/fileadmin/ICT/pdf/NCC/Align_IT_with_strategy.pdf [Accessed 21 Jan. 2016].

Read more about Computer Programming Assignment

Programming Assignments Help is the best assignment help provider in the United Kingdom. Our online assignment writing help UK is especially dedicated for the students studying in all UK colleges and universities. Submit assignment to get the best quality assignment help.



Discussion

  • Relational Database System Assignment | Program...

    […] Relational Database System Assignment Help focuses on database tools techniques, Entity Relationship. CROW’s foot notation, DDL commands, Testing Plans  […]

    01-04-16
  • MOSCOW Analysis Assignment Help

    […] Read about Relational Database System. […]

    01-04-16
  • Data Models Assignment Help - Programming Assignment Help

    […] Relational Database model […]

    01-04-16
  • Types of Database Models Assignment | Programming Help

    […] relational database model is the best known and in today’s DBMS most often implemented database model. It defines a […]

    01-04-16
  • Comments are closed.