Software Development Control Mechanisms

This assignment delves into the crucial topic of control mechanisms within database systems. It examines different access control models such as Discretionary Access Control (DAC), Mandatory Access Control (MAC), and Role Based Access Control (RBAC). Students are tasked with investigating these mechanisms, explaining their implementation in a relational database system, and suggesting appropriate controls for specific scenarios. The focus is on understanding how these mechanisms ensure data security and integrity.

Greenwich Health Centre (GHC)

Introduction
“Greenwich Health Centre (GHC)” is aimed to deliver a medical treatment
services for patients is operated by a group of health professionals and
located at Greenwich, South East London. GHC has 20 rooms equipped with
necessary health equipment. The administration department of the center is
responsible for managing the specialist’s room booking and patient’s
appointment system as well as other related issues. There are no restrictions
in booking rooms. However, a specialist who wants to book rooms must
compile the following rules.
1. Analysis, Design and Implement a database system
REGISTER (REGISTERID, REGISTERDATE, SPECIALISTID,
FULLNAME, MARITALSTATUS, SEX, DATEOFBIRTH,
CONTACTADDRESS, CONTACTNUMBER,
EMERGENCYCONTACTDETAILS, HIGHESTEDUCATIONS,
SPECIALIZATIONAREA, TRAININGCERTIFICATES,
MEMBERSHIPOFPROFESSIONAL, VALIDDOCUMENTS,
PHOTOGRAPHS, VALIDCRB, REFERENCES)
SPECIALIST(SPECIALISTID, FULLNAME, ADDRESS, GENDER,
PHONE, EMAIL, STATE, ZIP, REGISTERID)
BOOKING (BOOKINGID, BOOKINGDATE, AVAILABLESTATUS,
SPECIALISTID, ROOMID)
PATIENT(PATIENTID, FULLNAME, ADDRESS, GENDER, PHONE,
EMAIL, STATE, ZIP, REGISTERID )
ROOM (ROOMID, DESCRIPTION, AVAILABLESTATUS)
SESSION (SESSIONID, SESSIONNAME, TIMESLOT, ROOMID)
PAYMENT (PAYMENTID, PAYMENTDATE, CHARGE, PAYMENTTYPE,
PATIENTID, SPECIALISTID )
PAYMENTTYPE (PAYMENTTYPEID, DESCRIPTION)

2. Record entry in table
CREATE TABLE booking (
BOOKINGID int NOT NULL,
BOOKINGDATE date NOT NULL,
AVAILABLESTATUS varchar(150) NOT NULL,
SPECIALISTID int NOT NULL,
ROOMID int NOT NULL
) ;
INSERT INTO booking (BOOKINGID, BOOKINGDATE, AVAILABLESTATUS, SPECIALISTID,
ROOMID) VALUES
(1, '2016-11-03', 'Y', 1, 1),
(2, '2016-11-02', 'Y', 2, 1),
(3, '2016-11-01', 'N', 1, 1),
(4, '2016-10-03', 'Y', 3, 1),
(5, '2016-10-03', 'Y', 1, 2),
(6, '2016-09-03', 'Y', 4, 1),
(7, '2016-08-02', 'Y', 1, 3),
(8, '2016-10-07', 'Y', 2, 1),
(9, '2016-10-08', 'Y', 1, 1),
(10, '2016-10-09', 'N', 5, 2),

(11, '2016-10-12', 'Y', 1, 1),
(12, '2016-10-23', 'Y', 6, 2),
(13, '2016-10-25', 'Y', 1, 1),
(14, '2016-10-22', 'N', 7, 3),
(15, '2016-10-21', 'Y', 8, 1);
CREATE TABLE patient (
PATIENTID int NOT NULL,
FULLNAME varchar(100) NOT NULL,
ADDRESS varchar(150) NOT NULL,
GENDER char(1) NOT NULL,
PHONE varchar(50) NOT NULL,
EMAIL int NOT NULL,
STATE varchar(30) NOT NULL,
ZIP varchar(30) NOT NULL,
REGISTERID int NOT NULL
) ;

INSERT INTO patient (PATIENTID, FULLNAME, ADDRESS, GENDER, PHONE, EMAIL, STATE,
ZIP, REGISTERID) VALUES
(1, 'Jackulin', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(2, 'Janet', '203 Rupert Drive', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(3, 'James', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(4, 'Lamb', '78 The Trees', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(5, 'Frances', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(6, 'Glew', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(7, 'Gallimore', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(8, 'Julie', '55 Penny Lane', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(9, 'Evans', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(10, 'Duncan', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(11, 'Hayter', '707 Oxford Road', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(12, 'Arthur', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(13, 'Everard', '22 Rudding Park View', 'M', '882-123-4563', 0, 'NY', '25631',
1),
(14, 'Donald', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(15, 'Dennison', '29 Gelder Street', 'M', '882-123-4563', 0, 'NY', '25631', 1);

CREATE TABLE payment (
PAYMENTID int NOT NULL,
PAYMENTDATE date NOT NULL,
CHARGE decimal(10,0) NOT NULL,
PAYMENTTYPE varchar(50) NOT NULL,
PATIENTID int NOT NULL,
SPECIALISTID int NOT NULL
) ;
INSERT INTO payment (PAYMENTID, PAYMENTDATE, CHARGE, PAYMENTTYPE, PATIENTID,
SPECIALISTID) VALUES
(1, '2016-11-03', 200, '1', 1, 1),
(2, '2016-11-02', 300, '2', 3, 2),
(3, '2016-11-01', 400, '1', 1, 4),
(4, '2016-11-04', 500, '1', 2, 3),
(5, '2016-11-05', 200, '2', 7, 9),

(6, '2016-11-06', 600, '1', 8, 10),
(7, '2016-11-08', 700, '1', 1, 5),
(8, '2016-11-07', 800, '1', 9, 12),
(9, '2016-11-09', 900, '2', 4, 11),
(10, '2016-11-10', 1200, '1', 1, 6),
(11, '2016-11-11', 2200, '1', 1, 13),
(12, '2016-11-12', 2000, '1', 5, 7),
(13, '2016-11-13', 350, '2', 10, 15),
(14, '2016-11-14', 250, '1', 6, 8),
(15, '2016-11-15', 120, '1', 11, 14);
CREATE TABLE paymenttype (
PAYMENTTYPEID int NOT NULL,
DESCRIPTION varchar(100) NOT NULL
) ;

CREATE TABLE register (
REGISTERID int NOT NULL,
REGISTERDATE date NOT NULL,
SPECIALISTID int NOT NULL,
FULLNAME varchar(100) NOT NULL,
MARITALSTATUS char(1) NOT NULL,
SEX char(1) NOT NULL,
DATEOFBIRTH date NOT NULL,
CONTACTADDRESS varchar(150) NOT NULL,
CONTACTNUMBER varchar(30) NOT NULL,
EMERGENCYCONTACTDETAILS varchar(150) NOT NULL,
HIGHESTEDUCATIONS varchar(150) NOT NULL,
SPECIALIZATIONAREA varchar(150) NOT NULL,
TRAININGCERTIFICATES varchar(150) NOT NULL,
MEMBERSHIPOFPROFESSIONAL varchar(150) NOT NULL,
VALIDDOCUMENTS varchar(150) NOT NULL,
PHOTOGRAPHS varchar(250) NOT NULL,
VALIDCRB varchar(100) NOT NULL,
REFERENCE varchar(200) NOT NULL
) ;

INSERT INTO register (REGISTERID, REGISTERDATE, SPECIALISTID, FULLNAME,
MARITALSTATUS, SEX, DATEOFBIRTH, CONTACTADDRESS, CONTACTNUMBER,
EMERGENCYCONTACTDETAILS, HIGHESTEDUCATIONS, SPECIALIZATIONAREA,
TRAININGCERTIFICATES, MEMBERSHIPOFPROFESSIONAL, VALIDDOCUMENTS, PHOTOGRAPHS,
VALIDCRB, REFERENCE) VALUES
(1, '2016-11-03', 1, 'Mikasa ', 'M', 'F', '2000-11-02', '65, Jackson Height',
'852-236-5632', '001-256-5632', 'University Certificate', 'Gastroenterology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(2, '2016-11-03', 1, 'Frances', 'M', 'F', '2000-11-03', '65, Jackson Height',
'852-236-5633', '001-256-5632', 'University Certificate', 'ENT', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Robin'),
(3, '2016-11-03', 1, 'Glew', 'M', 'F', '2000-11-01', '65, Jackson Height', '852-
236-5634', '001-256-5632', 'Training Certificate', 'Orthopedic', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(4, '2016-11-03', 1, 'Julie', 'M', 'F', '2000-11-04', '65, Jackson Height', '852-
236-5635', '001-256-5632', 'University Certificate', 'Cardiology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(5, '2016-11-03', 1, 'Evans', 'M', 'F', '2000-11-05', '65, Jackson Height', '852-
236-5636', '001-256-5632', 'Medical Board Certificate', 'Endocrinology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(6, '2016-11-03', 1, 'Hayter', 'M', 'F', '2000-11-05', '65, Jackson Height',
'852-236-5637', '001-256-5632', 'Training Certificate', 'Gastroenterology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Evans'),
(7, '2016-11-03', 1, 'Duncan', 'M', 'F', '2000-11-06', '65, Jackson Height',
'852-236-5638', '001-256-5632', 'University Certificate', 'Neurology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(8, '2016-11-03', 1, 'Arthur', 'M', 'F', '2000-11-07', '65, Jackson Height',
'852-236-5639', '001-256-5632', 'Medical Board Certificate', 'Urology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(9, '2016-11-03', 1, 'Harris', 'M', 'F', '2000-11-07', '65, Jackson Height',
'852-236-5620', '001-256-5632', 'University Certificate', 'Paediatrics', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(10, '2016-11-03', 1, 'Cartright', 'M', 'F', '2000-11-09', '65, Jackson Height',
'852-236-5642', '001-256-5632', 'University Certificate', 'Rheumatology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Evans'),
(11, '2016-11-03', 1, 'Barry', 'M', 'F', '2000-11-10', '65, Jackson Height',
'852-236-5652', '001-256-5632', 'Training Certificate', 'Cardiology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(12, '2016-11-03', 1, 'Everard', 'M', 'F', '2000-11-11', '65, Jackson Height',
'852-236-5644', '001-256-5632', 'University Certificate', 'Cardiology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(13, '2016-11-03', 1, 'Roberts', 'M', 'F', '2000-11-12', '65, Jackson Height',
'852-236-5732', '001-256-5632', 'Medical Board Certificate', 'Rheumatology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),

(14, '2016-11-03', 1, 'John', 'M', 'F', '2000-11-13', '65, Jackson Height', '852-
236-5332', '001-256-5632', 'University Certificate', 'Urology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(15, '2016-11-03', 1, 'Mika', 'M', 'F', '2000-11-14', '65, Jackson Height', '852-
236-5232', '001-256-5632', 'University Certificate', 'Gastroenterology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Evans');
CREATE TABLE room (
ROOMID int NOT NULL,
DESCRIPTION varchar(100) NOT NULL,
AVAILABLESTATUS char(1) NOT NULL
) ;

INSERT INTO room (ROOMID, DESCRIPTION, AVAILABLESTATUS) VALUES
(1, 'Single', 'Y'),
(2, 'Double', 'Y'),
(3, 'Master', 'Y'),
(4, 'Classic', 'Y');

CREATE TABLE session (
SESSIONID int NOT NULL,
SESSIONNAME varchar(150) NOT NULL,
TIMESLOT varchar(150),
ROOMID int NOT NULL
) ;

INSERT INTO session (SESSIONID, SESSIONNAME, TIMESLOT, ROOMID) VALUES
(1, 'Morning Session', '0000-00-00 00:00:00', 1),
(2, 'Afternoon Session', '0000-00-00 00:00:00', 2),
(3, 'Evening Session', '0000-00-00 00:00:00', 3);

CREATE TABLE specialist (
SPECIALISTID int NOT NULL,
FULLNAME varchar(100) NOT NULL,
ADDRESS varchar(150) NOT NULL,
GENDER char(1) NOT NULL,
PHONE varchar(50) NOT NULL,
EMAIL varchar(50) NOT NULL,
STATE varchar(30) NOT NULL,
ZIP varchar(30) NOT NULL,
REGISTERID int NOT NULL
) ;
INSERT INTO specialist (SPECIALISTID, FULLNAME, ADDRESS, GENDER, PHONE, EMAIL,
STATE, ZIP, REGISTERID) VALUES
(1, 'Adam', 'California', 'M', '882-123-4563', '[e-mail]', 'CA', '41256',
1),
(2, 'Hazel', 'California', 'M', '882-123-4563', '[e-mail]', 'CA', '41256',
2),
(3, 'Lambert', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 3),

(4, 'Christopher', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 4),
(5, 'Fuller', 'California', 'F', '882-123-4563', '[e-mail]', 'CA',
'41256', 5),
(6, 'Gabrielle', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 6),
(7, 'Becks', 'California', 'M', '882-123-4563', '[e-mail]', 'CA', '41256',
7),
(8, 'Ian', 'California', 'M', '882-123-4563', '[e-mail]', 'CA', '41256', 8),
(9, 'Hamley', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 9),
(10, 'Everard', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 10),
(11, 'Cartright', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 11),
(12, 'Barry', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 12),
(13, 'Roberts', 'California', 'M', '882-123-4563', '[e-mail]', 'CA',
'41256', 13),
(14, 'Julie', 'California', 'F', '882-123-4563', '[e-mail]', 'CA',
'41256', 14),
(15, 'Mart', 'California', 'M', '882-123-4563', '[e-mail]', 'CA', '41256',
15);

3. Formulate SQL queries
A. LIST OF ALL SPECIALISTS
SELECT * FROM SPECIALIST;
B. LIST OF ALL SPECIALIST ACCORDING TO SPECIALIZATION AREA (E.G. ENT,
ORTHOPEDIC, CARDIOLOGY,
ENDOCRINOLOGY, GASTROENTEROLOGY, NEUROLOGY, UROLOGY,
PAEDIATRICS, RHEUMATOLOGY ETC.)

SELECT *
FROM SPECIALIST
WHERE SPECIALIZATIONAREA IN ('ENT', 'ORTHOPEDIC', 'CARDIOLOGY', 'ENDOCRINOLOGY',
'GASTROENTEROLOGY', 'NEUROLOGY', 'UROLOGY', 'PAEDIATRICS', 'RHEUMATOLOGY');
C. COUNT NO OF PATIENTS VISITED CENTER IN EACH DAY
SELECT PAYMENTDATE, COUNT(PATIENTID)
FROM PAYMENT
group by PAYMENTDATE
ORDER BY PAYMENTDATE;

D. VIEW PATIENTS CHECKED BY PARTICULAR SPECIALIST
SELECT P.FULLNAME, S.FULLNAME
FROM PATIENT P, SPECIALIST S
WHERE P.REGISTERID = S.REGISTERID;

E. LIST OF PATIENTS VISITED A CENTER AT SPECIFIC TIME PERIOD (DAY,
WEEK, ONE MONTH, SIX MONTHS
OR YEARS)

SELECT P.FULLNAME, S.FULLNAME, S.ADDRESS
FROM PATIENT P, SPECIALIST S, PAYMENT M
WHERE P.REGISTERID = S.REGISTERID
AND M.SPECIALISTID = S.SPECIALISTID
AND M.PAYMENTDATE = '2016-11-02';
F. LIST OF ROOM BOOKED BY DOCTORS
SELECT R.DESCRIPTION, R.AVAILABLESTATUS
FROM ROOM R, BOOKING B
WHERE R.ROOMID = B.ROOMID;

G. LIST OF AVAILABLE ROOM FOR BOOKING
SELECT * FROM BOOKING B
WHERE B.AVAILABLESTATUS ='Y';

H. LIST OF SPECIALIST THOSE WHO ARE GREATER THAN 50 YEARS OLD
SELECT *
FROM SPECIALIST S, REGISTER R
WHERE S.REGISTERID = R.REGISTERID
AND R.DATEOFBIRTH > '1966-01-01';

I. STATISTICS OF MALE AND FEMALE SPECIALIST
SELECT GENDER, COUNT(GENDER)
FROM SPECIALIST
GROUP BY GENDER;

J. PARTICULAR’S SPECIALIST DAILY INCOME :
SELECT S.FULLNAME, P.PAYMENTDATE, SUM(P.CHARGE)
FROM SPECIALIST S, PAYMENT P
WHERE S.SPECIALISTID = P.SPECIALISTID
GROUP BY S.FULLNAME, P.PAYMENTDATE;

I. LIST OF CARD AND CASH PAYMENT
SELECT PAYMENTTYPE , T.FULLNAME
FROM PAYMENT P, PATIENT T
WHERE P.PATIENTID = T.PATIENTID;

II. CALCULATE THE TOTAL MONEY (CARD AND CASH)
SELECT PAYMENTTYPE , SUM(CHARGE)
FROM PAYMENT P
GROUP BY PAYMENTTYPE;

III. CALCULATE THE TOTAL CASH PAYMENT ONLY
SELECT SUM(CHARGE)
FROM PAYMENT P
WHERE PAYMENTTYPE = 2;

K. LIST OF SPECIALIST THOSE WHO HAS NOT PAY THE ROOM CHARGE
SELECT *
FROM SPECIALIST
WHERE SPECIALISTID NOT IN ( SELECT SPECIALISTID FROM BOOKING);

LO1: Understand data models and database technologies
2.1 There are different types of data model and schema in
data analysis and design; identify the data model and schema
and critically analysis them? [P1.1]
Data modeling is the process of documenting a complex software system
design as an easily understood diagram, using text and symbols to represent
the way data needs to flow. The diagram can be used as a blueprint for the
construction of new software or for re-engineering a legacy application.
Conceptual Model
Design
Logical Model Design Physical Model Design

Conceptual Data Modeling - identifies the highest-level relationships between
different entities.
Enterprise Data Modeling - similar to conceptual data modeling, but
addresses the unique requirements of a specific business.
Logical Data Modeling - illustrates the specific entities, attributes and
relationships involved in a business function. Serves as the basis for the
creation of the physical data model.
Physical Data Modeling - represents an application and database-specific
implementation of a logical data model.
Record-based Logical Data Models
Record-based logical data models provide concepts users can understand
but are not too far from the way data is stored in the computer. Three well-
known data models of this type are relational data models, network data
models and hierarchical data models.
The relational model represents data as relations, or tables. For example, in
the membership system at Science World, each membership has many
members. The membership identifier, expiry date and address information
are fields in the membership.
The network model represents data as record types. This model also
represents a limited type of one too many relationship called a set type.

The hierarchical model represents data as a hierarchical tree structure. Each
branch of the hierarchy represents a number of related records.
2.2 critically discuss the different types of database
management system technologies and illustrate them with
benefits and limitations. [P1.2].
Database management system technologies
Data Definition Language (DDL) Specification notation for defining the
database schema E.g. create table account ( account-number char(10),
balance integer)
DDL compiler generates a set of tables stored in a data dictionary. Data
dictionary contains metadata (i.e., data about data) database schema.
Data storage and definition language. Language in which the storage
structure and access methods used by the database system are specified
usually an extension of the data definition language.
Data Manipulation Language (DML)-Language for accessing and
manipulating the data organized by the appropriate data model. DML also
known as query language. Two classes of languages.
 Procedural – user specifies what data is required and how to get those
data.
 Nonprocedural – user specifies what data is required without specifying
how to get those data. SQL is the most widely used query language.
RDBMS - A relational database management system (RDBMS) is a database
management system (DBMS) that is based on the relational model as
invented by E. F. Codd, of IBM's San Jose Research Laboratory.

Data redundancy - is a condition created within a database or data storage
technology in which the same piece of data is held in two separate places.
This can mean two different fields within a single database, or two different
spots in multiple software environments or platforms.
Data independence - is the type of data transparency that matters for a
centralized DBMS. It refers to the immunity of user applications to changes
made in the definition and organization of data. Physical data independence
deals with hiding the details of the storage structure from user applications.
Data integrity - is a fundamental component of information security. In its
broadest use, “data integrity” refers to the accuracy and consistency of data
stored in a database, data warehouse, data mart or other construct.
Normalization - is a process of organizing the data in database to avoid
data redundancy, insertion anomaly, update anomaly & deletion anomaly.
2.3 (a) Various database design approaches are applying in
the database design and development process. Investigate the
database design approaches, features and describe them
appropriately with a suitable example. [P1.3]
The ER model defines the conceptual view of a database. It works around
real world entities and the associations among them. At view level, the ER
model is considered a good option for designing databases.
Entity An entity can be a real-world object, either animate or inanimate, that
can be easily identifiable. For example, in a school database, students,
teachers, classes, and courses offered can be considered as entities. All
these entities have some attributes or properties that give them their
identity.

Attributes Entities are represented by means of their properties called
attributes. All attributes have values. For example, a student entity may
have name, class, and age as attributes.
Types of Attributes
Simple attribute: Simple attributes are atomic values, which cannot be
divided further. For example, a student's phone number is an atomic value of
10 digits.
Composite attribute: Composite attributes are made of more than one
simple attribute. For example, a student's complete name may have
first_name and last_name.
Derived attribute: Derived attributes are the attributes that do not exist in
the physical database, but their values are derived from other attributes
present in the database.
Entity-Set and Keys
Key is an attribute or collection of attributes that uniquely identifies an entity
among entity set.
Super Key: A set of attributes (one or more) that collectively identifies an
entity in an entity set.
Candidate Key: A minimal super key is called a candidate key. An entity set
may have more than one candidate key.
Primary Key: A primary key is one of the candidate keys chosen by the
database designer to uniquely identify the entity set.
An entity-relationship diagram (ERD) is a graphical representation of an
information system that shows the relationship between people, objects,
places, concepts or events within that system. An ERD is a data modeling
technique that can help define business processes and can be used as the
foundation for a relational database.
A data flow diagram (DFD) is a graphical representation of the "flow" of
data through an information system, modelling its process aspects. A DFD is

often used as a preliminary step to create an overview of the system, which
can later be elaborated.
A relationship where two entities are participating is called a binary
relationship. Cardinality is the number of instance of an entity from a relation
that can be associated with the relation.
One-to-one: When only one instance of an entity is associated with the
relationship, it is marked as '1:1'. The following image reflects that only one
instance of each entity should be associated with the relationship. It depicts
one-to-one relationship.
One-to-many: When more than one instance of an entity is associated with
a relationship, it is marked as '1:N'. The following image reflects that only
one instance of entity on the left and more than one instance of an entity on
the right can be associated with the relationship. It depicts oneto-many
relationship.
Many-to-one: When more than one instance of entity is associated with the
relationship, it is marked as 'N:1'. The following image reflects that more
than one instance of an entity on the left and only one instance of an entity
on the right can be associated with the relationship. It depicts many-to-one
relationship.
Many-to-many: The following image reflects that more than one instance of
an entity on the left and more than one instance of an entity on the right can
be associated with the relationship. It depicts many-to-many relationship.
LO2: Be able to design and implement relational database systems
REGISTER (REGISTERID, REGISTERDATE, SPECIALISTID, FULLNAME,
MARITALSTATUS, SEX, DATEOFBIRTH, CONTACTADDRESS,

CONTACTNUMBER, EMERGENCYCONTACTDETAILS, HIGHESTEDUCATIONS,
SPECIALIZATIONAREA, TRAININGCERTIFICATES,
MEMBERSHIPOFPROFESSIONAL, VALIDDOCUMENTS, PHOTOGRAPHS,
VALIDCRB, REFERENCES)
SPECIALIST(SPECIALISTID, FULLNAME, ADDRESS, GENDER, PHONE, EMAIL,
STATE, ZIP, REGISTERID)
BOOKING (BOOKINGID, BOOKINGDATE, AVAILABLESTATUS, SPECIALISTID,
ROOMID)
PATIENT(PATIENTID, FULLNAME, ADDRESS, GENDER, PHONE, EMAIL, STATE,
ZIP, REGISTERID )
ROOM (ROOMID, DESCRIPTION, AVAILABLESTATUS)
SESSION (SESSIONID, SESSIONNAME, TIMESLOT, ROOMID)
PAYMENT (PAYMENTID, PAYMENTDATE, CHARGE, PAYMENTTYPEID,
PATIENTID, SPECIALISTID )
PAYMENTTYPE (PAYMENTTYPEID, DESCRIPTION)

CREATE TABLE `booking` (
`BOOKINGID` int(11) NOT NULL,
`BOOKINGDATE` date NOT NULL,
`AVAILABLESTATUS` varchar(150) NOT NULL,
`SPECIALISTID` int(11) NOT NULL,
`ROOMID` int(11) NOT NULL,
PRIMARY KEY (`BOOKINGID`)
);
CREATE TABLE `patient` (
`PATIENTID` int(11) NOT NULL,
`FULLNAME` varchar(100) NOT NULL,
`ADDRESS` varchar(150) NOT NULL,
`GENDER` char(1) NOT NULL,
`PHONE` varchar(50) NOT NULL,
`EMAIL` int(30) NOT NULL,
`STATE` varchar(30) NOT NULL,
`ZIP` varchar(30) NOT NULL,
`REGISTERID` int(11) NOT NULL,
PRIMARY KEY (`PATIENTID`)
);
CREATE TABLE `payment` (
`PAYMENTID` int(11) NOT NULL,
`PAYMENTDATE` date NOT NULL,
`CHARGE` decimal(10,0) NOT NULL,
`PAYMENTTYPE` varchar(50) NOT NULL,
`PATIENTID` int(11) NOT NULL,
`SPECIALISTID` int(11) NOT NULL,
PRIMARY KEY (`PAYMENTID`)
);

CREATE TABLE `paymenttype` (
`PAYMENTTYPEID` int(11) NOT NULL,
`DESCRIPTION` varchar(100) NOT NULL,
PRIMARY KEY (`PAYMENTTYPEID`)
);
CREATE TABLE `register` (
`REGISTERID` int(11) NOT NULL,
`REGISTERDATE` date NOT NULL,
`SPECIALISTID` int(11) NOT NULL,
`FULLNAME` varchar(100) NOT NULL,
`MARITALSTATUS` char(1) NOT NULL,
`SEX` char(1) NOT NULL,
`DATEOFBIRTH` date NOT NULL,
`CONTACTADDRESS` varchar(150) NOT NULL,
`CONTACTNUMBER` varchar(30) NOT NULL,
`EMERGENCYCONTACTDETAILS` varchar(150) NOT NULL,
`HIGHESTEDUCATIONS` varchar(150) NOT NULL,
`SPECIALIZATIONAREA` varchar(150) NOT NULL,
`TRAININGCERTIFICATES` varchar(150) NOT NULL,
`MEMBERSHIPOFPROFESSIONAL` varchar(150) NOT NULL,
`VALIDDOCUMENTS` varchar(150) NOT NULL,
`PHOTOGRAPHS` blob NOT NULL,
`VALIDCRB` varchar(100) NOT NULL,
`REFERENCES` varchar(200) NOT NULL,
PRIMARY KEY (`REGISTERID`)
);
CREATE TABLE `room` (
`ROOMID` int(11) NOT NULL,
`DESCRIPTION` varchar(100) NOT NULL,
`AVAILABLESTATUS` char(1) NOT NULL,
PRIMARY KEY (`ROOMID`)
);
CREATE TABLE `session` (
`SESSIONID` int(11) NOT NULL,

`SESSIONNAME` varchar(150) NOT NULL,
`TIMESLOT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
`ROOMID` int(11) NOT NULL,
PRIMARY KEY (`SESSIONID`)
);
CREATE TABLE `specialist` (
`SPECIALISTID` int(11) NOT NULL,
`FULLNAME` varchar(100) NOT NULL,
`ADDRESS` varchar(150) NOT NULL,
`GENDER` char(1) NOT NULL,
`PHONE` varchar(50) NOT NULL,
`EMAIL` varchar(50) NOT NULL,
`STATE` varchar(30) NOT NULL,
`ZIP` varchar(30) NOT NULL,
`REGISTERID` int(11) NOT NULL,
PRIMARY KEY (`SPECIALISTID`)
);
DFD – Data flow diagram

DBMS Vendor Type
Rdb Oracle R
Red Brick Informix (Red Brick) R
SQL Server Microsoft R
SQLBase Centura Software R
LO3: Be able to use manipulation and querying tools
3.1 Discuss the SQL and its features and functionalities; explain the
benefits of query tools in relational database system and show the
advantages of the using query in database system. [P3.1]

SQL is Structured Query Language, which is a computer language for
storing, manipulating and retrieving data stored in relational database.
SQL is the standard language for Relation Database System. All relational
database management systems like MySQL, MS Access, Oracle, Sybase,
Informix, postgres and SQL Server use SQL as standard database language.
Also, they are using different dialects, such as:
 MS SQL Server using T-SQL,
 Oracle using PL/SQL,
 MS Access version of SQL is called JET SQL (native format) etc.
Features and functionalities
 Allows users to access data in relational database management
systems.
 Allows users to describe the data.
 Allows users to define the data in database and manipulate that data.
 Allows embedding within other languages using SQL modules, libraries
& pre-compilers.
 Allows users to create and drop databases and tables.
 Allows users to create view, stored procedure, functions in a database.
 Allows users to set permissions on tables, procedures, and views

3.2 Apply the SQL (DML) to implement a query language into your
designed relational database system [P3.2, M3]
The SQL INSERT INTO Statement is used to add new rows of data to a table
in the database.
Syntax:
There are two basic syntaxes of INSERT INTO statement as follows:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
SQL SELECT statement is used to fetch the data from a database table which
returns data in the form of result table. These result tables are called result-
sets.
Syntax:
The basic syntax of SELECT statement is as follows:
SELECT column1, column2, columnN FROM table_name;
The SQL UPDATE Query is used to modify the existing records in a table.
You can use WHERE clause with UPDATE query to update selected rows
otherwise all the rows would be affected.
Syntax:
The basic syntax of UPDATE query with WHERE clause is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
The SQL DELETE Query is used to delete the existing records from a table.
You can use WHERE clause with DELETE query to delete selected rows,
otherwise all the records would be deleted.
Syntax:
The basic syntax of DELETE query with WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];
3.3 Identify the features of query tool. Critically evaluate how
meaningful data has been extracted through the use of query tools
in your designed database system.
Advanced Query Tool (AQT) is a Microsoft Windows based SQL query
software tool used to administer and execute queries against the most
common database management systems. AQT is created by a small New
Zealand software company, Cardett, and is distributed and supported
through the Internet. AQT is designed for use by database administrators
and developers.
Database explorer
AQT allows multiple database connections to be made. The connection can
be made to any ODBC database defined. For each connection there is a
database explorer window. The database explorer panel allows the user to
display information about the database objects in that database. This is
where AQT uses catalog queries rather than ODBC definition calls, which
makes exploring database meta data very fast.
SQL editor
The SQL editor allows multiple statements, which can be run in order through
a multiple statements dialog. There is a SQL formatter that makes hard to
read SQL easier to read. The database connection that will be the target of
the SQL execution can be switched with a simple pull down. The results of
the SQL go to a data window by default, but can also be directly exported to
various file formats. Database administrators find it useful to export the
results of queries to another SQL window, which is useful for building and
executing DDL using queries against the catalog meta data.
Data window
In addition to simply displaying the results of a query, the data window
allows the updating of the data in the window. The query results can also be

exported to various file formats including MS-Excel spreadsheets, CSV files,
plain text, insert statements, and HTML tables.
A recent sophisticated addition to the data window includes the ability to edit
data in related tables. This allows the user to be editing a row of a table, and
the rows of a child table that are related within the same window.
GUI Query Builder
The GUI Query Builder allows building SQL queries with point and click
simplicity, which is handy for novice SQL programmers.
Data loader
AQT Extended Edition includes a data loader tool that makes it easy to load
data into tables from various sources. The sources can be other tables on the
same database, a different database, a completely different kind of database
(i.e. can load DB2 table from Oracle table), an Excel spreadsheet, a comma
delimited (CSV) file, a flat file, or even a complicated
LO4: Be able to test and document relational database systems
Based on the function and structure of a database, DB testing can be
categorized into three categories −
 Structural Database Testing − It deals with table and column
testing, schema testing, stored procedures and views testing, checking
triggers, etc.
 Functional Testing − It involves checking functionality of database
from user point of view. Most common type of Functional testing are
White box and black box testing.
 Nonfunctional Testing − It involves load-testing, risk testing in
database, stress testing, minimum system requirements, and deals
with the performance of the database.
4.2 Produce a suitable testing plan with appropriate documentation
to support to support testing your developed database system.
[P4.2]

Functional Testing
Functional testing is performed keeping in mind an end-user point of view;
whether the required transactions and operations run by the end-users meet
the business specifications.
Black Box Testing
Black Box Testing involves verifying the integration of database to check the
functionality. The test cases are simple and are used to verify incoming data
and outgoing data from the function.
Various techniques such as cause-effect graphing technique, equivalence
partitioning and boundary-value analysis are used to test the functionality of
the database.
Its advantages are as follows −
 It is fairly simple and is performed in the early stages of development.
 Cost of developing test-cases is less as compared to white-box testing.
Its disadvantages are as follows −
 A few errors cannot be detected
 It is unknown how much program needs to be tested.
 White Box Testing
 White Box Testing deals with the internal structure of the database and
the specification details are hidden from the users. It involves the
testing of database triggers and logical views, which are going to
support database refactoring.
It performs module testing of database functions, triggers, views, SQL
queries etc. This type of testing validates database tables, data models,
database schema etc. It checks rules of Referential integrity. It selects
default table values to check on database consistency.
The most common techniques used to perform white box testing are
condition coverage, decision coverage, statement coverage, etc.
Coding errors can be detected in white-box testing, so internal bugs in the
database can be eliminated. The limitation of white-box testing is that SQL
statements are not covered.

4.3 Describe the importance of technical and user documentation
and create a suitable user documentation for your developed
relational database system [P4.3].
Documentation is one of the systems which is used to communicate, instruct
and record the information for any reference or operational purpose. They
are very useful for representing the formal flow of the present system. With
the help of documentation it is very easy to track the flow of the system's
progress and they working of the system can be explained very easily.
It helps to provide the clear description of the work done so far. It is essential
that the documents prepared must be updated on regular basis this will help
to trace the progress of work easily. With appropriate and good
documentation it is very easy to understand the how aspects of the system
will work for the company where the system is to install. It is also help to
understand the type of data which will be inputted in the system and how
the output can be produced.
After the system is installed, and if in case the system is not working
properly it will be very easy for the administrator to understand the flow of
data in the system with documentation which will help him/ her to correct
the flaws and get the system working in no time.
Uses of Documentation
 It facilitates effective communication regarding the system between
the technical and the non technical users.
 It is very useful in training new users. With a Good documentation new
users can easily get acquainted with the flow of the systems.
 Documentation also helps the users to solve problems like trouble
shooting even a non technical user can fix the problems.
 It plays a significant role in evaluation process.
 It not only helps to exercise a better control over the internal working
of the firm, but it also external as well especially during audit.
 Documentations can help the manager to take better financial
decisions of the organization.
4.4 Verification and Validation are very important terms; explain
clearly how you have addressed verification and validation in your

developed relational database system and show how they differ
from each other. [P4.4]
Verification is a process of evaluating the intermediary work products of a
software development lifecycle to check if we are in the right track of
creating the final product.
Now the question here is : What are the intermediary products? Well, These
can include the documents which are produced during the development
phases like, requirements specification, design documents, data base table
design, ER diagrams, test cases, traceability matrix etc. We sometimes tend
to neglect the importance of reviewing these documents but we should
understand that reviewing itself can find out many hidden anomalies when if
found or fixed in the later phase of development cycle, can be very costly.
Validation is the process of evaluating the final product to check whether
the software meets the business needs. In simple words the test execution
which we do in our day to day life are actually the validation activity which
includes smoke testing, functional testing, regression testing, systems
testing etc.
4.5 There are various control mechanisms available in the database
system. Investigate the important control mechanism and explain
how control mechanisms can be used in your developed relational
database system and suggest appropriate mechanism that will fit
for this relational database system. [P4.5, D3]
Access Control is a set of controls to restrict access to certain resources. If
we think about it, access controls are everywhere around us. A door to your
room, the guards allowing you to enter the office building on seeing your
access card, swiping your card and scanning your fingers on the biometric
system, a queue for food at the canteen or entering your credentials to
access FB, all are examples of various types of access control. Here we focus
only on the logical Access Control mechanisms.
Access Control Mechanisms
Discretionary Access Control (DAC)

As the name suggests, this access control model is based on a user’s
discretion. i.e, the owner of the resource can give access rights on that
resource to other users based on his discretion. Access Control Lists (ACLs)
are a typical example of DAC. Specifying the “rwx” permissions on a unix file
owned by you is another example of DAC Most of the operating systems
including windows, flavours of unix are based on DAC Model.
Mandatory Access Control (MAC)
In this Model, users/owners do not enjoy the privilege of deciding who can
access their files. Here the operating system is the decision maker overriding
the user’s wishes. In this model every Subject (users) and Object (resources)
are classified and assigned with a security label. The security labels of the
subject and the object along with the security policy determine if the subject
can access the object. The rules for how subjects access objects are made by
the security officer, configured by the administrator, enforced by the
operating system, and supported by security technologies.
This is a stricter and rather static Access Control model as compared to DAC
and is mostly suited for military organizations where data classification and
confidentiality is of prime importance. Special types of the Unix operating
systems are based on MAC model.
Role Based Access Control (RBAC)
RBAC is the buzzword across enterprises today. In this model the access to a
resource is governed based on the role that the subject holds within an
organization. RBAC is also known as non-discretionary Access Control
because the user inherits privileges that are tied to his role. The user does
not have a control over the role that he will be assigned. Each of the above
Access Models has its own advantages and disadvantages.
Case for RBAC
For implementing any access control, the two driving factors are
1. Least privilege principle i.e, the user should only have the minimum
privileges to perform the tasks that he is supposed to do.
2. Segregation of duties i.e, having more than one user to perform a critical
task so as to reduce the risk of internal frauds.