Logical Database Design - Assignment Example

1. Description of Tables Upon exploring and analysing the Health and Lifestyle functionality of the NHS website, the following tables are proposed to be designed which could record all the information entered in the forms under all the tabs of Health and Lifestyle section. CUSTOMER: This table holds information about the customer. Every record in the customer table is unique identified by a customer id which is populated with the user id provided by the customer during registration. Apart from the customer id field, the table contains columns for customer name, Date of birth, contact no, address, email id, customer self password and an alternative password HEALTH_RECORD: This table holds information about weight, height, alcohol, smoking, calories, blood type, blood pressure, blood sugar, cholesterol, resting heart rate, maximum heart rate and peak flow under heath details, lifestyle details, blood and heart and lungs tabs. Columns include details for customer id of the customer for whom the information is recorded, record id which is unique for each customer id (In other words, the combination of customer id and record id forms the primary key for the table), as on date (date on which the measurement was taken), measurement type (type of measurement like blood sugar, heart rate, weight etc –in other words the name of the tab under which the form is displayed), measured value (value entered by the user), status (reveal or conceal) and updated date (the date on which the form is submitted). HEALTH_TARGET: This table holds the information on constant values like ‘is smoker’, blood group etc and target values under each category of measurement. Columns include details for customer id of the customer for whom the information is recorded, target type (type of measurement and is unique for each customer id. Combination of customer id and target type forms the primary key), target value (value entered by the user), status (reveal or conceal) and updated date (the date on which the form is submitted). HEALTH_NOTE: This table holds the list of health notes entered by the user under the health notes tab of the health details section. Columns include details for customer id of the customer for whom the information is recorded, notes id which is unique for each customer id (combination of customer id and notes id forms the primary key), notes entered by the user, status (reveal or conceal) and updated date (the date on which the form is submitted). MEDICATION: This table holds the medication details entered by the user under the medication tab. Columns include details for customer id of the customer for whom the information is recorded, medication id which is unique for each customer id (combination of customer id and medication id forms the primary key), the values entered by the user for name, dosage, frequency, type, isPrescribed, start date, end date and additional notes, status (reveal or conceal) and updated date (the date on which the form is submitted). ALLERGY: This table holds the list of allergies entered by the user under the allergy tab of the health details section. Columns include details for customer id of the customer for whom the information is recorded, allergy id which is unique for each customer id (combination of customer id and allergy id forms the primary key), values entered by the user for allergy details and additional notes, status (reveal or conceal) and updated date (the date on which the form is submitted). CUST_ACCESS_LOG: This table holds entry for each access (login) made by the customer or doctor. Columns include details for customer id of the customer for whom the information is recorded, log id which is unique for each customer id (combination of customer id and log id forms the primary key), access type (Override, self login, alternative login (login with alternative password) and date of access. 2. E-R Model Assumptions: i. Login form includes a textbox for user id, textbox for password and a checkbox to login as guest (user 2) and a check box for override option. ii. If the checkbox for login as guest is unchecked (default), user is treated as user 1 and cust_self_password field of the customer table is checked for password correctness. Upon successful login, all the records irrespective of the visibility status are retrieved and returned. iii. If the checkbox for login as guest is checked, user is treated as user 2 and cust_alt_password field of the customer table is checked for password correctness. Upon successful login, only the records with visibility status ‘Y’ are returned. iv. If the user checks on the override option while logging in, all the details are displayed irrespective of the visibility status of the record. v. All the fields in the forms are designed to hold value with maximum length matching the data length of each field. Business Rules: i. Each customer in the CUSTOMER table is identified by a unique user id. No two customers can share the same user id. ii. Each entry in the CUSTOMER can have zero or one or more entries in HEATLTH_RECORD, HEALTH_TARGET, HEALTH_NOTES, MEDICATION, ALLERGIES tables. But an entry in these tables must be from one and only one customer. iii. Each entry in the CUSTOMER must have one or more entries in CUST_ACCESS_LOG. While an entry in CUST_ACCESS_LOG must be from one and only one customer. The ER diagram which incorporates all the above assumption/business rules is shown in the below figure: Fig 1: ER model for NHS Health Space In the above diagram, an entry in HEATLTH_RECORD, HEALTH_TARGET, HEALTH_NOTES, MEDICATION, ALLERGIES, CUST_ACCESS_LOG exist only if a corresponding entry exists in CUSTOMER table. Hence these entities are called weak entities and forms total participation with its relationship with CUSTOMER table. Component 2 Physical Database Design 3. Stored Procedure to create tables and insert data -- ============================================= -- Author: -- Create date: 05-11-2012 -- Description: creates the physical tables CUSTOMER, HEALTH_RECORD, ----HEALTH_TARGETS, HEALTH_NOTE, MEDICATION, ALLERGY AND --CUST_ACCESS_LOG and insert data into them -- ============================================= CREATE PROCEDURE [dbo].[CREATEandINSERT] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- create CUSTOMER table CREATE TABLE CUSTOMER ( cust_id varchar(8) not null primary key, cust_name varchar(25) not null, cust_DOB date, cust_contact_no varchar(11), cust_address varchar(40), cust_email varchar(40), cust_self_password varchar(8) not null, cust_alt_password varchar(8)); --create HEALTH_RECORD table CREATE TABLE HEALTH_RECORD( cust_id varchar(8) not null, record_id integer not null, measured_on_date date, measured_on_time varchar, measurement_type varchar(30) not null, measured_value varchar(8) not null, visibiliy_status char not null default 'Y', updated_date date not null PRIMARY KEY (cust_id,record_id) FOREIGN KEY (cust_id) references CUSTOMER on delete cascade); --create HEALTH_TARGET table CREATE TABLE HEALTH_TARGET( cust_id varchar(8) not null, target_id integer not null, target_type varchar(30) not null, target_value varchar(8) not null, visibiliy_status char not null default 'Y', updated_date date not null PRIMARY KEY (cust_id,target_id) FOREIGN KEY (cust_id) references CUSTOMER on delete cascade); --create HEALTH_NOTE table CREATE TABLE HEALTH_NOTE( cust_id varchar(8) not null, notes_id integer not null, notes_value varchar(100) not null, visibiliy_status char not null default 'Y', updated_date date not null, PRIMARY KEY (cust_id,notes_id), FOREIGN KEY (cust_id) references CUSTOMER on delete cascade); --create ALLERGY table CREATE TABLE ALLERGY( cust_id varchar(8) not null, allergy_id integer not null, allergy_name varchar(100) not null, allergy_details varchar(300) not null, visibiliy_status char not null default 'Y', updated_date date not null, PRIMARY KEY (cust_id,allergy_id), FOREIGN KEY (cust_id) references CUSTOMER on delete cascade); -- create MEDICATION table CREATE TABLE MEDICATION( cust_id varchar(8) not null, med_id integer not null, med_name varchar(100) not null, dosage varchar(40) not null, frequency varchar(40) not null, med_type varchar(40) not null, isPrescribed char, med_start_date date not null, med_end_date date, other_details varchar(300), updated_date date not null, PRIMARY KEY (cust_id,med_id), FOREIGN KEY (cust_id) references CUSTOMER on delete cascade); --create CUST_ACCESS_LOG table CREATE TABLE CUST_ACCESS_LOG( cust_id varchar(8) not null, log_id integer not null, access_date varchar(22) not null, access_type varchar(20) not null, PRIMARY KEY (cust_id,log_id), FOREIGN KEY (cust_id) references CUSTOMER on delete cascade); --insert data into CUSTOMER table INSERT INTO CUSTOMER (cust_id,cust_name,cust_DOB,cust_contact_no,cust_address,cust_email,cust_self_password,cust_alt_password) values ('myuser','John','03-19-1978','7323227878','Address for customer1','John@domain.com','mypass','docpass'); --insert data into HEALTH_RECORD table INSERT INTO HEALTH_RECORD (cust_id,record_id,measured_on_date,measurement_type,measured_value,visibiliy_status,updated_date) values ('myuser',1,’2012-04-01’,'Weight','60.2','N','2012-05-11'), ('myuser',2, ‘2012-05-01','Weight','62.2','N','2012-05-11'), ('myuser',3,’2012-04-01’,'Height','6.0','N','2012-05-11'), ('myuser',4, ’2012-04-01,'Alchohol','10','N','2012-05-11'), ('myuser',5,'2012-05-01','Alchohol','20','N','2012-05-11'), ('myuser',6,’2012-04-01’,'Smoking','5','N','2012-05-11'), ('myuser',7,'2012-05-01','Smoking','3','N','2012-05-11'), ('myuser',8,’2012-04-01’,'Calories','1600','N','2012-05-11'), ('myuser',9,'2012-05-01','Calories','1800','N','2012-05-11'), ('myuser',10,’2012-04-01’,'Blood Pressure','120','Y','2012-05-11'), ('myuser',11,'2012-05-01','Blood Pressure','129','Y','2012-05-11'), ('myuser',12,’2012-04-01’,'Blood Sugar','12','Y','2012-05-11'), ('myuser',13,'2012-05-01','Blood Sugar','14','Y','2012-05-11'), ('myuser',14,’ 2012-05-01’,'Cholesterol','10','Y','2012-05-11'), ('myuser',15, ’2012-04-01,'Cholesterol','11','Y','2012-05-11'), ('myuser',16,’2012-04-01’,'Resting Heart Rate','82','Y','2012-05-11'), ('myuser',17,'2012-05-01','Resting Heart Rate','80','Y','2012-05-11'), ('myuser',18, ’2012-04-01,'Maximum Heart Rate','92','Y','2012-05-11'), ('myuser',19,'2012-05-01','Maximum Heart Rate','98','Y','2012-05-11'), ('myuser',20, ’2012-04-01,'Peak flow','600','Y','2012-05-11'), ('myuser',21,'2012-05-01','Peak flow','600','Y','2012-05-11'); -- insert into HEALTH_TARGET INSERT INTO HEALTH_TARGET (cust_id,target_id,target_type,target_value,visibiliy_status,updated_date) values ('myuser',1,'Weight','58','N','2012-05-11'), ('myuser',2,'isSmoker','Y','N','2012-05-11'), ('myuser',3,'Blood Group','O+ve','N','2012-05-11'), ('myuser',4,'Alchohol','50','N','2012-05-11'), ('myuser',5,'Smoking','2','N','2012-05-11'), ('myuser',6,'Calories','1200','N','2012-05-11'), ('myuser',7,'Blood Sugar','12','N','2012-05-11'), ('myuser',8,'Cholesterol','10','N','2012-05-11'), ('myuser',9,'Resting Heart Rate','75','N','2012-05-11'), ('myuser',10,'Peak Heart Rate','75','N','2012-05-11'), ('myuser',11,'Peak flow','75','N','2012-05-11'); --insert into HEALTH_NOTE INSERT INTO HEALTH_NOTE (cust_id,notes_id,notes_value,visibiliy_status,updated_date) values ('myuser',1,'Dont eat too much of oily food','N','2012-05-11'), ('myuser',2,'Start going for a walk','N','2012-05-11'); --insert into ALLERGY INSERT INTO ALLERGY(cust_id,allergy_id,allergy_name,allergy_details,visibiliy_status,updated_date) values ('myuser',1,'Sinusitis','Allegic to dusts and pollution','Y','2012-05-11'); -- insert into MEDICATION INSERT INTO MEDICATION (cust_id,med_id,med_name,dosage,frequency,med_type,med_start_date,med_end_date,isPrescribed,other_details,updated_date) values ('myuser',1,'FEXOFENADINE','5ml','3 times','capsule','12-12-2011','12-31-2011','Y','','2012-05-11'), ('myuser',2,'Loratadine','5ml','2 times','capsule','12-22-2011','12-31-2011','Y','','2012-05-11'), ('myuser',3,'Montelukast','5ml','3 times','capsule','12-12-2011','12-31-2011','Y','','2012-05-11'), ('myuser',4,'Cetirizine','5ml','Once at bedtime','capsule','12-12-2011','','Y','','2012-05-11'); --insert into CUST_ACCESS_LOG INSERT INTO CUST_ACCESS_LOG(cust_id,log_id,access_date,access_type) values ('myuser',1,'11-05-2012 20:14:14','Self login'), ('myuser',2,'23-04-2012 20:14:14','Alternative login'), ('myuser',3,'21-04-2012 20:14:14','Override'); END 4. Stored Procedure for testing personal health records application A user (User 1) entering his Lifestyle data. CREATE PROCEDURE insertLifestyleRec -- Add the parameters for the stored procedure here @cust_id varchar(8), @record_id integer, @measured_on_date date, @measured_on_time varchar(8), @measurement_type varchar(30), @measured_value varchar(5), @visibiliy_status char(1), @updated_date date AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO HEALTH_RECORD (cust_id,record_id,measured_on_date,measured_on_time,measurement_type,measured_value,visibiliy_status,updated_date) values (@cust_id,@record_id,@measured_on_date,@measured_on_time,@measurement_type,@measured_value,@visibiliy_status,@updated_date) END GO --Executing stored procedure exec insertLifestyleRec 'myuser',22,'05-11-2012','','Smoking','5','Y','05-11-2012'; exec insertLifestyleRec 'myuser',23,'05-11-2012','','Alchohol','50','Y','05-11-2012'; exec insertLifestyleRec 'myuser',24,'05-11-2012','','Calories','2000','Y','05-11-2012'; Stored procedure for testing the application – querying lifestyle data The below stored procedure takes 4 parameters as input user id, user type, requested data and override option. Depending upon the user (whether user1 or user 2) and the override option, the data requested (measurement type – smoking, weight, blood pleasure, etc) are returned for the supplied user id. CREATE PROCEDURE getData -- Add the parameters for the stored procedure here @userid varchar(8), @usertype varchar(5), --- user1 or user 2 @data varchar(30), --- type of requested data @override char(1) --- override option AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF (@usertype ='user2' and @override='Y') or @usertype='user1' SELECT HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_value, HEALTH_RECORD.measured_on_date,HEALTH_TARGET.target_value from HEALTH_RECORD,HEALTH_TARGET where HEALTH_RECORD.cust_id=@userid and HEALTH_RECORD.measurement_type=@data and HEALTH_TARGET.cust_id=@userid and HEALTH_TARGET.target_type=HEALTH_RECORD.measurement_type order by HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_on_date desc IF @usertype ='user2' and @override='' SELECT HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_value, HEALTH_RECORD.measured_on_date,HEALTH_TARGET.target_value from HEALTH_RECORD,HEALTH_TARGET where HEALTH_RECORD.cust_id=@userid and HEALTH_RECORD.measurement_type=@data and HEALTH_RECORD.visibiliy_status='Y' and HEALTH_TARGET.cust_id=@userid and HEALTH_TARGET.target_type=HEALTH_RECORD.measurement_type order by HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_on_date desc END User 1 querying his lifestyle data EXEC getData 'myuser','user1','smoking',' '; EXEC getData 'myuser','user1','alchohol',' '; EXEC getData 'myuser','user1','calories',' '; Output: Fig 2: User 1 querying lifestyle data Concealing records from user 2: The below stored procedure accepts two parameters measurement type (@data) and status (@status). It updates the record for the supplied measurement type with the supplied status. CREATE PROCEDURE updateStatus -- Add the parameters for the stored procedure here @data varchar(30), @status char(1) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here update HEALTH_RECORD set visibiliy_status = @status where measurement_type = @data END GO User 1 concealing certain of his smoking records from User 2 EXEC updateStatus ‘Smoking’, 'N'; User 2 querying the lifestyle and smoking data for User 1 EXEC getData 'myuser','user2','smoking',''; Output: No data returned Fig 3: User 2 trying to view the concealed smoking data of user 1 User 2 overriding the smoking data restrictions, and retrieving the full smoking data for User 1. EXEC getData 'myuser','user2','smoking','Y'; Output: Fig 4: User 2 trying to view the concealed smoking data of user 1 with override option Viewing summary health Record: This stored procedure takes three inputs user id, user type and override option and returns all the health records for the customer depending upon the supplied user type and override option. CREATE PROCEDURE queryUser1 -- Add the parameters for the stored procedure here @userid varchar(8), @password varchar(8) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF (@usertype ='user2' and @override='Y') or @usertype='user1' SELECT HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_value, HEALTH_RECORD.measured_on_date,HEALTH_TARGET.target_value from HEALTH_RECORD,HEALTH_TARGET where HEALTH_RECORD.cust_id=@userid and HEALTH_RECORD.cust_id=HEALTH_TARGET.cust_id and HEALTH_RECORD.measurement_type=HEALTH_TARGET.target_type order by HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_on_date desc IF @usertype ='user2' and @override='' SELECT HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_value, HEALTH_RECORD.measured_on_date,HEALTH_TARGET.target_value from HEALTH_RECORD,HEALTH_TARGET where HEALTH_RECORD.cust_id=@userid and HEALTH_RECORD.visibiliy_status ='Y' and HEALTH_RECORD.cust_id=HEALTH_TARGET.cust_id and HEALTH_RECORD.measurement_type=HEALTH_TARGET.target_type order by HEALTH_RECORD.measurement_type,HEALTH_RECORD.measured_on_date desc END GO User 1 querying the Summary Health Record: EXEC queryUser1 'myuser','user1',''; Output: Fig 5: User 1 querying the Summary health record User 2 querying the Summary Health Record: EXEC queryUser1 'myuser','user2',''; Output: Fig 6: User 2 querying the Summary health record User 2 querying the Summary Health Record with override option: EXEC queryUser1 'myuser','user2','Y'; Output: Fig 7: User 2 querying the Summary health record with override option 5. Design techniques for very large Database With the increase in development of online systems, the amount of database transactions involved in the system also increases proportionally. As millions of users around the globe are accessing a single system, very large databases are required to handle these millions of day to day transactions. As database forms the core of the application system, it should be designed and optimized for maximum efficiency. There are number of points to be considered while designing and implementing large databases few of which are discussed below: Basic considerations that can be followed: i. Following Naming Convention All the objects of the database including tables, view, columns, stored procedures, etc should follow a uniform naming convention and instructions in order to ease the task of design, maintenance and query formations. ii. Data field Reference All the columns of the tables should reference a valid field in the form of the application/system. The field in the form must be designed to hold data with maximum length equal to the length of the column data. The data entered in the field must also be validated to contain data matching the data type of the column. Mandatory Design Considerations that must be taken care: i. Concurrency While handling multiple users, it is mandatory to give an impression to each user as if he or she is the only user accessing the data. Concurrency is one of the features of the database that enables each user to realise this. This ensures that no two users either overwrite the changes made by other users or read the uncommitted changes that are made by other users. This is achieved by implementing concept of isolation. If necessary isolation is not implemented in the database then various data integrity issues may arise. For example, when more than one user try to update the same piece of information, the changes or the updates made by one user will be partially or completely lost by the changes made by the other users. Sometimes dirty reads may happen when users try to read uncommitted changes made by other users. For example, transaction A is modifying some piece of data and transaction B issues a query for the same data and reads few rows of uncommitted changes. Later if transaction A rolls back the changes, then the data read and processed by transaction B is not valid anymore and hence called ‘Dirty Read’. Non-Repeatable read is another issue that happens when the same query executed at a different point of time within the same application flow gives different result sets. For example, when transaction A issues a query and reads a piece of data, before the end of this transaction another transaction B updates the same data, then if transaction A issues the same query again, it would result in a different set of data. Phantom Reads may also be met when the same query executed at a different point of time within the same application flow gives unequal number of result sets. For example, when transaction A issues a query and reads a piece of data, before the end of this transaction another transaction B inserts a new row, then if transaction A issues the same query again, it would result in a new result set with different number of rows. In order to avoid these types of data integrity issues, there are many types of isolation provided by each database engine. Four common types of isolation are: Read uncommitted, which is the lowest isolation level where transactions are isolated only to the extent that the physical data that is read is not corrupted. Dirty reads and phantom reads are very much possible with this type of isolation. Read committed, which is the default level for the Database Engine, it eliminates dirty reads but allows non repeatable reads and phantom reads. Repeatable read which eliminates the problem of dirty reads and non repeatable reads but does not eliminate the problem of phantom reads. Serializable, the highest level of isolation in which transactions are completely isolated from one another. ii. Indexing In designing a large database table, index play a major role. Apart from the primary key, defining an index on frequency searchable column or group of columns on the table can improve the efficiency of the overall database. If no index exists on the table, a table scan is performed for each table on the referenced query. As the table is very large, table scanning would take a very longer time. On the other hand, if indexes are defined, index scanning if chosen by the optimizer, would run in less time as index files are smaller compared to the table’s physical files. iii. Partitioning Partitioning helps very large tables and indexes by letting us to decompose them into smaller and easily manageable portions or sections called partitions, which are entirely transparent to the application as well as to the user. There are no specific requirements for the SQL queries and DML statements to be modified in order to access partitioned tables. However, after the partitioning, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. It is mandatory that each partition of a table or index must have the same logical attributes which includes column names, data types, and constraints. However, each partition can have separate physical attributes like compression enabled or disabled, physical storage settings, tablespaces etc. When partition is employed in the database, management operations such as data loads, data unloads, index creation and rebuilding, backup/recovery, etc can be performed at the partition level, rather than on the entire table. This enables the user to run these utilities in lesser time compared to running them for the whole table. Also, the results of a query can be achieved most of the time by accessing a subset of partitions, rather than the entire table. For some queries, this technique can provide order-of-magnitude gains in performance. As maintenance operations can be performed at partition level rather than table level, it provides a sort of partition independence for partition maintenance operations. We can perform concurrent maintenance operations on different partitions of the same table or index. We can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations. Owing to this ability of concurrent maintenance operation, critical tables and indexes when divided into partitions can result in reduced maintenance windows, recovery times, and impact of failures. Above all parallel execution aids in optimized resource utilization and minimized execution time. Parallel execution against partitioned objects is an excellent solution for scalability in a clustered environment. iv. Optimization techniques When handling large databases, data manipulation using SQL queries need to be optimized for maximum efficiency in order to realize the full benefits of the overall application. There are many techniques or tips available for optimizing or tuning the query. We can re-structure the query based on various optimization considerations like Use of column names instead of * in SELECT query statements. Using having clause in the SELECT statement after the rows are fetched in order to filter only relevant rows. Minimal use of sub-queries. We may replace them with joins Use of operator like EXISTS, IN and table joins only if it is really required in the query. Usually IN has the slowest performance and is efficient only if most of the filter criteria are in the sub-query. EXISTS is only efficient when most of the filter criteria is in the main query. When using joins, DISTINCT should be avoided. Instead EXISTS can be used. This is very mandatory if the tables are related by one-to-many relationship. Union all can be used instead of Union. Use of wild card characters in WHERE clause instead of using substr () function. Large binary objects like images can be stored in the file system and the path can be added to the database instead of storing the actual image. Thus, by following the above basic considerations and design consideration we can design a database that can handle millions of database with maximum efficiency and optimization. References Introduction to Very Large Databases [WWW] Oracle. http://docs.oracle.com/cd/B28359_01/server.111/b32024/intro.htm Isolation Levels in the Database Engine [WWW] Microsoft. http://msdn.microsoft.com/en-us/library/ms189122.aspx Large database design principles and development techniques [WWW] ixwebhosting. http://www.ixwebhosting.mobi/large-database-design-principles-and-development-techniques/ Read More